Re: mutex contention for the query cache

[Sergei Golubchik]

Hi!

On Jul 03, Brian Aker wrote:
> On Jul 3, 2008, at 12:23 PM, Sergey Petrunia wrote:
>
>> I suppose a storage engine could do a better job at detecting cases
>> when some INSERT/UPDATE/DELETE operation didn't affect the cached
>> fragments, but that seems to be a problem that is not related to poor
>> qcache concurrency.  Or they are somehow related?
>
> Bingo, this is the problem you are solving. Ever noticed with a light
> load how often (and somewhat buggily I might add) that Innodb purges
> the query cache? The mechanics of MVCC require you purge a global
> cache nearly constantly if it does not understand the current context
> of a query.

This is easy to solve. I won't say for InnoDB, but it'd be trivial to
extent query cache so that Maria would use exact visibility rules and
maximize cache using - all transactions that could see the entry would
see it, those that couldn't - wouldn't.

> If you do the cache inside of the engine you get the advantage of 
> understanding version... this buys you a cache you can use a lot more 
> frequently.

And using it becomes much more expensive - the server needs to redo a
lot of stuff even if results are cached in the engine.

Query cache as it's done now is dirt cheap (concurrency can be fixed).
If the query is in the cache - you get the result intantly - poof, the
server doesn't even see the query.

Regards / Mit vielen Grüssen,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
 / /|_/ / // /\ \/ /_/ / /__  Principal Software Engineer/Server Architect
/_/  /_/\_, /___/\___\_\___/  Sun Microsystems GmbH, HRB München 161028
       <___/                  Sonnenallee 1, 85551 Kirchheim-Heimstetten
Geschäftsführer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Häring