From: Date: July 3 2008 10:57pm
Subject: Re: mutex contention for the query cache
In-reply-to:
 <82624B02DA0A3C4691069A83007F651D01C1E7CB@SNV-EXVS08.ds.corp.yahoo.com>
List-Archive: http://lists.mysql.com/internals/35796
Message-Id: <7690004C-FBA0-4141-88F5-089CA66DF6EE@verizon.net>
MIME-Version: 1.0 (Apple Message framework v924)
Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes
Content-Transfer-Encoding: quoted-printable

When the query cache is enabled, the exact text passed to the client =20
is the text used to check the query cache _before_ any statement =20
parsing is performed. This means that changes to the query which do =20
not affect the query result will be cached as a seperate query. Worse, =20=

if query cache is enabled, the statement hits the query cache even if =20=

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 =20
select wether the resultset is inserted into the query cache at the =20
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 =20=

> is it smart enough to postpone deciding whether to check until after =20=

> 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 =20
> SQL_CACHE where desired (which is infrequent).  Might we be better =20
> 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=3D37416).
>>
>>   Jocelyn
>>
>> MARK CALLAGHAN a =E9crit :
>>> 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 =3D=3D =
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.
>>>
>>
>> --=20
>> MySQL Internals Mailing List
>> For list archives: http://lists.mysql.com/internals
>> To unsubscribe:
>> http://lists.mysql.com/internals?unsub=3Drjames@stripped
>>
>>
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    =
http://lists.mysql.com/internals?unsub=3Dantony.curtis@stripped
>