What is the correct way to enable query cache?

问题:

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?

回答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.

回答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.

回答3:

CloudSQL now support query_cache flags.

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

But these options may break the SLA coverage.