From: Date: July 3 2008 10:39pm
Subject: RE: mutex contention for the query cache
List-Archive: http://lists.mysql.com/internals/35795
Message-Id: <82624B02DA0A3C4691069A83007F651D01C1E7E4@SNV-EXVS08.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Thanks :(

Seems like a quick, though partial, fix to improve the performance is =
not to lock on lookup.  Of course, that assumes you won't trip over some =
other thread in the middle of adding an item.

(I'm about to send out a recommendation against query cache, except =
where it is heavily used.)
=20
Rick James
MySQL Geeks - Consulting & Review

=20

> -----Original Message-----
> From: Jocelyn Fournier [mailto:joc@stripped]=20
> Sent: Thursday, July 03, 2008 1:26 PM
> To: Rick James
> Cc: MARK CALLAGHAN; internals@stripped
> Subject: Re: mutex contention for the query cache
>=20
> Hi,
>=20
> I've just done the test, and search indeed also happen with mysql=20
> configured in DEMAND mode.
>=20
>    Jocelyn
>=20
> Rick James a =E9crit :
> > What happens with DEMAND (2)?  Does it _always_ check the=20
> cache?  Or is it smart enough to postpone deciding whether to=20
> check until after the parse?  The bug seemed to be focused on=20
> the ON case.
> >=20
> > The in-house recommendation here is to set it to DEMAND,=20
> then use SQL_CACHE where desired (which is infrequent). =20
> Might we be better off with OFF?  Especially on multi-core boxes?
> >=20
> > =20
> > Rick James
> > MySQL Geeks - Consulting & Review
> >=20
> > =20
> >=20
> >> -----Original Message-----
> >> From: Jocelyn Fournier [mailto:joc@stripped]=20
> >> 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=20
> >> implementation of=20
> >> the qcache, you can't use SQL_NO_CACHE keyword to skip this=20
> >> search, and=20
> >> only use the qcache when it really makes sense (see=20
> >> 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=20
> >> 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=20
> 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:   =20
> >> http://lists.mysql.com/internals?unsub=3Drjames@stripped
> >>
> >>
> >=20
>=20