[sakai-dev] MySql Query Cache

classic Classic list List threaded Threaded
4 messages Options
Austin Nakasone Austin Nakasone
Reply | Threaded
Open this post in threaded view
|

[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/.
Hendrik Steller Hendrik Steller
Reply | Threaded
Open this post in threaded view
|

Re: [sakai-dev] MySql Query Cache

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>
> 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:
> https://confluence.sakaiproject.org/display/DOC/Sakai+Admin+Guide+-+Full<ht
> 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
> [hidden email]<mailto:[hidden email]>.
> To post to this group, send email to
> [hidden email]<mailto:[hidden email]>. 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
> 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

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





On Mon, Feb 5, 2018 at 8:06 AM, Hendrik Steller <[hidden email]> wrote:
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>
> 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:
> https://confluence.sakaiproject.org/display/DOC/Sakai+Admin+Guide+-+Full<ht
> 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
> [hidden email]<mailto:[hidden email]>.
> To post to this group, send email to
> [hidden email]<mailto:[hidden email]>. 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
> 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:%2B27%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/.
Stephen Marquard Stephen Marquard
Reply | Threaded
Open this post in threaded view
|

Re: [sakai-dev] MySql Query Cache

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 +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/.