MySql Query Cache

207 views
Skip to first unread message

Austin

unread,
Feb 2, 2018, 3:01:12 PM2/2/18
to sakai-dev
Hello Sakai Developers,

We recently disabled MySql query caching (MySql 5.6) and have seen improvements in performance.

I was just curious to see if anyone else has it disabled.  Or if anyone has been seeing performance issues, perhaps that's something they could look into as well?

What happened with us, is that we upgraded to MySql 5.6 mid Fall while we were still on Sakai 10.7 and performance seemed to be on par with what it was before the MySql 5.6 upgrade.  But then after we upgraded to Sakai 11.4 at the beginning of January, we were seeing poor performance issues with pages loading slowly, etc.

Our DB admin noticed that off and on throughout the day, especially during peak traffic, a lot of queries would be "Waiting for query cache lock".  He mentioned that query caching is OFF by default in MySql 5.6, but since we had it ON in 5.5, we kept it ON in 5.6.  (Also, query caching is actually deprecated in 5.7 and removed from MySql 8.0).

So after we disabled query caching with:

query_cache_size=0

we definitely saw performance improvements and "less" complaints from users.  Also, the database's CPU load dropped by about half.

Or... has anyone else made "SAKAI_PRESENCE as an in-memory table"?  From the admin guide:
We haven't tried it yet (I didn't find that tip until after we disabled caching).  I wonder if that would have improved performance enough so that we didn't have to disable caching?

Anyway, I hope this helps if anyone else is running into similar issues.

Thanks,

Austin

Stephen Marquard

unread,
Feb 4, 2018, 7:48:48 AM2/4/18
to Austin, sakai-dev

Hi Austin,


Interesting feedback on the query cache; thanks for posting that.


Quite some time ago we tried changing SAKAI_PRESENCE to be an in-memory table and it was very bad for performance; I understood then because the locking is not as fine-grained (updates would lock the whole table rather than the row).


https://dev.mysql.com/doc/refman/5.7/en/memory-storage-engine.html

says:


"Despite the in-memory processing for MEMORY tables, they are not necessarily faster than InnoDB tables on a busy server, for general-purpose queries, or under a read/write workload. In particular, the table locking involved with performing updates can slow down concurrent usage of MEMORY tables from multiple sessions."

So I think you're better of leaving SAKAI_PRESENCE as an innodb table.


Regards

Stephen



From: saka...@apereo.org <saka...@apereo.org> on behalf of Austin <aust...@hawaii.edu>
Sent: 02 February 2018 10:01 PM
To: sakai-dev
Subject: [sakai-dev] MySql Query Cache
 
--
You received this message because you are subscribed to the Google Groups "Sakai Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sakai-dev+...@apereo.org.
To post to this group, send email to saka...@apereo.org.
Visit this group at https://groups.google.com/a/apereo.org/group/sakai-dev/.
Disclaimer - University of Cape Town This email is subject to UCT policies and email disclaimer published on our website at http://www.uct.ac.za/main/email-disclaimer or obtainable from +27 21 650 9111. If this email is not related to the business of UCT, it is sent by the sender in an individual capacity. Please report security incidents or abuse via https://csirt.uct.ac.za/page/report-an-incident.php.

Hendrik Steller

unread,
Feb 5, 2018, 1:07:11 PM2/5/18
to saka...@apereo.org, Stephen Marquard, Austin
Hi,

I found that interesting as well because I noticed that our admins enabled
that cache as well, so I read a bit about this the other day.
While it's probably not of much use given the mentioned deprecation of the
feature and that mysql actually seems to recommend that the cache is only
turned on if you know that it will improve performance for you application,
there are two things I found interesting:
1.
Making that cache (too) big can supposedly really drag down the performance
because mysql then spends way too much time managing and reordering the cache
and its index. I found a recommendation to make that cache really small (like
20 MB) and to only increase the size very gently while watching its effects
(SHOW STATUS LIKE '%qcache%';)
-> Maybe the cache's size was the reason why the CPU load dropped so
drastically for Austin after disabling the cache.

2.
You can sort of enable/disable it for certain tables, or rather certain SQL
statements. So it could make sense to look which Sakai tables are frequently
written to (Event? Presence? Session?) and alter the mysql statements used to
query them "from "select foo" to "select sql_no_cache foo" if hibernate
doesn't get in the way for those tables.
( https://dev.mysql.com/doc/refman/5.5/en/query-cache-in-select.html )

The way this cache works according to the documentation is to cache selects
and their results for each table and then to clear the cached data whenever
data is being written to the table.
So that cache is probably somewhere between useless and really bad for e.g.
the event table which can see multiple writes per second.

There also were some articles concluding that the cache works really bad in
environments with high concurrency (I think for more than four threads? Or was
it cores? I don't remember right now) which sounds pretty much like what you
encountered with "Waiting for query cache lock".

Regards,
Hendrik

On Sonntag, 4. Februar 2018 13:48:41 CET Stephen Marquard wrote:
> Hi Austin,
>
>
> Interesting feedback on the query cache; thanks for posting that.
>
>
> Quite some time ago we tried changing SAKAI_PRESENCE to be an in-memory
> table and it was very bad for performance; I understood then because the
> locking is not as fine-grained (updates would lock the whole table rather
> than the row).
>
>
> https://dev.mysql.com/doc/refman/5.7/en/memory-storage-engine.html
>
>
> says:
>
>
> "Despite the in-memory processing for MEMORY tables, they are not
> necessarily faster than
> InnoDB<https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html>
> tps://protect-za.mimecast.com/s/WPBJCX6XE5tOB0Z3UkEa_R>
> https://confluence.sakaiproject.org/display/DOC/Sakai+Admin+Guide+-+Databas
> e+Configuration+and+Tuning<https://protect-za.mimecast.com/s/t3X3CY6YG5tAkOX
> PCMU1rR> We haven't tried it yet (I didn't find that tip until after we
> disabled caching). I wonder if that would have improved performance enough
> so that we didn't have to disable caching?
>
> Anyway, I hope this helps if anyone else is running into similar issues.
>
> Thanks,
>
> Austin
>
> --
> You received this message because you are subscribed to the Google Groups
> "Sakai Development" group. To unsubscribe from this group and stop
> receiving emails from it, send an email to
> sakai-dev+...@apereo.org<mailto:sakai-dev+...@apereo.org>.
> To post to this group, send email to
> saka...@apereo.org<mailto:saka...@apereo.org>. Visit this group at
> https://groups.google.com/a/apereo.org/group/sakai-dev/<https://protect-za.
> mimecast.com/s/5LrtCZ4GJ0iQP2YySP8hAV>. Disclaimer - University of Cape Town

Austin

unread,
Feb 5, 2018, 1:34:47 PM2/5/18
to sakai-dev
Hello Hendrick and Stephen,

Thanks for the reply.

We had:

query_cache_size = 500MB
query_cache_limit = 1MB

So yes, it could have been set too high.  Also, our query cache hit rate was usually bad.  We decided not to try tuning it.  If we had more time, we might have, but anyway, disabling it is working for us for now.

Thanks,

Austin






> To post to this group, send email to
> saka...@apereo.org<mailto:sakai...@apereo.org>. Visit this group at

Stephen Marquard

unread,
Feb 5, 2018, 4:12:28 PM2/5/18
to Austin, sakai-dev

I just checked, and our production db has a query cache size of 160M. We're running mysql 5.7.


This graph shows queries and cache hits:


https://vula.uct.ac.za/mrtg/mysql-queries.html


so the benefit may be marginal for us, but on the other hand we've also never seen problems with contention from its use.


The query cache used to really spike for particular tool operations, like Tests & Quizzes starting an assessment, so it seemed like a good safety net for unusual load, even with a relatively low hit rate for regular use.


However, lots of the cases where Sakai generated large volumes of select queries have been fixed in the code since then (mostly related to suboptimal use of Hibernate), 


Regards

Stephen


Sent: 05 February 2018 08:34 PM
To: sakai-dev
Subject: Re: [sakai-dev] MySql Query Cache
 
Hello Hendrick and Stephen,

Thanks for the reply.

We had:

query_cache_size = 500MB
query_cache_limit = 1MB

So yes, it could have been set too high.  Also, our query cache hit rate was usually bad.  We decided not to try tuning it.  If we had more time, we might have, but anyway, disabling it is working for us for now.

Thanks,

Austin


Austin

unread,
Feb 5, 2018, 4:27:17 PM2/5/18
to Stephen Marquard, sakai-dev
Back in August of last year, when we were still on MySql 5.5 and Sakai 10.7, we were usually seeing query cache hits in the 10-15% range, which is quite low.  Although at times, I suppose depending on what queries are being run, it could be as high as 90%.   We don't keep a running log / graph of the cache hits, but I suspect we were low most of the time.

Here are a few articles I found while researching query cache


That first article mentions, "Usually I’ll keep query cache disabled if the hit rate is below 50%."   But it also says, as you mentioned, to start small with 10MB.  We had been at 500MB

Thanks for mentioning Tests & Quizzes, we'll have to keep an eye out to see if having the query cache disabled, will affect Tests & Quizzes performance.

- Austin




Reply all
Reply to author
Forward
0 new messages