Can't enable MySQL 5.6 Query Cache

Question

I'm trying to enable MySQL Query Cache on Ubuntu 15.04 with MySQL 5.6.25

I've added this to end end of /etc/mysql/my.cnf and /etc/mysql/conf.d/mysql.cnf:

query_cache_type = 1
query_cache_size = 4096M
query_cache_limit = 2M
query_cache_strip_comments =1

The whole server has been restarted more than once.

user@myhost:/$ mysql
mysql: unknown variable 'query_cache_type=1'

Using SHOW VARIABLES LIKE '%query_cache%' confirms that query_cache_type = OFF

SET GLOBAL query_cache_type = 1;
/* SQL Error (1651): Query cache is disabled; restart the server with query_cache_type=1 to enable it */

How can I solve this?

Answer 1

You shouldn't put those lines at the very bottom of the file.

Look for the [mysqld] group header in my.cnf and put those lines under it

[mysqld]
query_cache_type = 1
query_cache_size = 4096M
query_cache_limit = 2M
query_cache_strip_comments =1

Those line cannot go under any other group header.

Then, restart mysqld.

Answer 2

4GB for the query cache? NO, NO.

That will slow down your system.

Every change to a table requires scanning the 4GB to purge entries for that table.

Limit the value to, say, 50M.