You need to modify the following 3 variables after logging into the MySQL server.
query_cache_size = size_in_bytes (e.g. 32*1024*1024)
query_cache_type = 1 (for ON)
query_cache_limit = size_in_bytes (e.g. 2*1024*1024)
1. Run show variables like 'query%'; to check the existing values of these variables.
MariaDB [(none)]> show variables like 'query%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_alloc_block_size | 16384 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_strip_comments | OFF |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 24576 |
+------------------------------+---------+
2. Now run the following three commands to change the values of 3 variables.
set global query_cache_size = 0*1024*1024; (replace 0 with some value)
set global query_cache_type = 1
set global query_cache_limit = 0*1024*1024; (replace 0 with some value)
query_cache_size should not be very large, so start with 16-32 MB. query_cache_limit should be 256kb-2gb.
Caveat: If you are using InnoDB, it's better to turn off query cache as innoDB uses its own buffer for caching. Enabling query cache might degrade performance. Also, if you restart mysql server, you will lose your modifications. For permanent modification, you need to modify my.cnf file.
References:
1. https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html
2. https://mariadb.com/kb/en/library/query-cache/
