Re: mutex contention for the query cache In-reply-to: <[e-mail]>

[Antony T Curtis]

When the query cache is enabled, the exact text passed to the client  
is the text used to check the query cache _before_ any statement  
parsing is performed. This means that changes to the query which do  
not affect the query result will be cached as a seperate query. Worse,  
if query cache is enabled, the statement hits the query cache even if  
it makes no sense to ever cache or if the user has requested no-caching.

The only thing that the cache/no cache select modifiers do is to  
select wether the resultset is inserted into the query cache at the  
end of statement execution.

Regards,
Antony.

(my own opinions, not those of my employer nor former employer)


On 3 Jul 2008, at 13:03, Rick James wrote:

> What happens with DEMAND (2)?  Does it _always_ check the cache?  Or  
> is it smart enough to postpone deciding whether to check until after  
> the parse?  The bug seemed to be focused on the ON case.
>
> The in-house recommendation here is to set it to DEMAND, then use  
> SQL_CACHE where desired (which is infrequent).  Might we be better  
> off with OFF?  Especially on multi-core boxes?
>
>
> Rick James
> MySQL Geeks - Consulting & Review
>
>
>
>> -----Original Message-----
>> From: Jocelyn Fournier [mailto:joc@stripped]
>> Sent: Thursday, July 03, 2008 12:44 PM
>> To: MARK CALLAGHAN
>> Cc: internals@stripped
>> Subject: Re: mutex contention for the query cache
>>
>> And what is also frustrating is because of the current
>> implementation of
>> the qcache, you can't use SQL_NO_CACHE keyword to skip this
>> search, and
>> only use the qcache when it really makes sense (see
>> http://bugs.mysql.com/bug.php?id=37416).
>>
>>   Jocelyn
>>
>> MARK CALLAGHAN a écrit :
>>> The query cache has a mutex that is locked while it is
>> searched. This
>>> is not a spin lock, so many threads will go to sleep when there is
>>> contention. And it is made worse because work is done to create the
>>> search key in Query_cache::send_result_to_client() (work == memory
>>> allocation and other byte copying) after the mutex is locked.
>>>
>>> Are there plans to fix this?
>>> I don't have benchmark results (yet), but I am willing to bet that
>>> this is a problem.
>>>
>>
>> -- 
>> MySQL Internals Mailing List
>> For list archives: http://lists.mysql.com/internals
>> To unsubscribe:
>> http://lists.mysql.com/internals?unsub=1
>>
>>
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals?unsub=1
>