Re: [sakai-dev] MySql Query Cache

classic Classic list List threaded Threaded
2 messages Options
Stephen Marquard Stephen Marquard
Reply | Threaded
Open this post in threaded view
|

Re: [sakai-dev] MySql Query Cache

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: [hidden email] <[hidden email]> on behalf of Austin <[hidden email]>
Sent: 02 February 2018 10:01 PM
To: sakai-dev
Subject: [sakai-dev] MySql Query Cache
 
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

--
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 [hidden email].
To post to this group, send email to [hidden email].
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.

--
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/a/apereo.org/group/sakai-dev/.
Austin Nakasone Austin Nakasone
Reply | Threaded
Open this post in threaded view
|

Re: [sakai-dev] MySql Query Cache

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





On Mon, Feb 5, 2018 at 11:12 AM, Stephen Marquard <[hidden email]> wrote:

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



From: [hidden email] <[hidden email]> on behalf of Austin <[hidden email]>
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


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 <a href="tel:+27%2021%20650%209111" value="+27216509111" target="_blank">+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.

--
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/a/apereo.org/group/sakai-dev/.