What is the correct way to enable query cache?

Question

Based on the documentation, the super privilege is not supported, which means that the following query:

SET GLOBAL query_cache_size = 1000000;

results in an error message

Access denied; you need (at least one of) the SUPER privilege(s) for this operation

and does not allow us to set the query cache size.

What's the correct way to accomplish the task?

Statuette

Hi Alex. At this time, we don't support query_cache_size for Cloud SQL. Sorry :( – Arbutus

Thank you. Any idea if/when that might change? – Statuette

I'm not allowed speculate about the future, I'm afraid, but I have kept a note of your request. Sorry I couldn't do more for you right now :( – Arbutus

Answer 1

Unfortunately, Cloud SQL does not support query caching and query_cache_size cannot be set.

If you are experiencing performance issues, you can try changing your instance tier to give your instance access to more resources. Also, it is preferable to use InnoDB over MyISAM tables. The reason for this is because when a Cloud SQL instance is started, it gives most of the available memory to the InnoDB buffer pool.

Mettah

does it means my SELECT SQL_CACHE * query will be ineffective ? – Loseff

Answer 2

As mhalt hints at, there is a good reason not to use the query cache:

• You should be using InnoDB rather than MyISAM, as MyISAM is not robust enough for the cloud environment.
• InnoDB has built in caching as part of it's buffer pool. This caches individual pages of data, rather than entire result sets.
• The buffer pool generally provides superior caching to the query cache: 1) it does not get flushed after writes 2) multiple different queries can be served using the same cache entries 3) it supports partial caching if the active set is larger than available ram.
• The only workload where the query cache is superior is if you have a very low write rate and almost all your queries are exactly the same.
• For this reason Cloud SQL is optimized by maximizing RAM allocated to the buffer pool instead of having a query cache.

Iridissa

Answer 3

CloudSQL now support query_cache flags.

https://cloud.google.com/sql/docs/mysql/flags

But these options may break the SLA coverage.

Gourmandise