2

Let's assume that I want work with query cache enabled. For example let's set it to 128mb value but my working enviorment is very random and dynamic, so cache is full in 1 minute after setting it.

When checking 

SHOW STATUS LIKE "qcache%";

Qcache_lowmem_prunes value is growing dangerously high because of high ammount of random queries.

So let's go the point of this question: How to cache only that queries that are taking longer then ie: 1 sec?

Is it even possible with query cache enabled or is it better to turn it off for good in such dynamic enviorment? Are there any different aproaches to cache those long queries?

Let's assume that 1 second for query is achieved due to low budget hardware, not from bad query construction.

Improve this question
3
  • you must search for other solution than query cache, possible in application layer "MySQL 8.0: Retiring Support for the Query Cache" mysqlserverteam.com/… – Ivanov Aug 3 '18 at 6:33
  • Thank You for letting me know. So in this case and any future case it is much better to log slow queries and cache them through application or files. +1 for info about dropping support for QC in future releases. – Eryk Wróbel Aug 3 '18 at 6:44
  • 1
    Caching purely on response time breaks any consistency expectations and introduces a bunch of problems around cache invalidation (i.e. why is by account balance still X after I transferred Y). After discovering particular slow queries work out a) if they can be faster or b) if those need to be accurate and under what conditions. – danblack Aug 18 '18 at 8:53
0

  • Instead, cache only selected queries. Go through your code and decide which queries are likely to be exactly repeatedly. On those do SELECT SQL_CACHE .... On the others do SELECT SQL_NO_CACHE ....

  • As for the long queries -- improve them. See this for getting started. When you have identified a couple of 'slow' queries, open a new Question to get help on making them faster. Sometimes we can help you make them run 10 times as fast.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.