In this lesson, I show how to get a query cache running using MySQL and ProxySQL.
On your existing MySQL instance, do the following to prepare it to be used with ProxySQL's query cache:
$ mysql -u root
mysql> CREATE USER 'monitor'@'%' IDENTIFIED WITH mysql_native_password BY 'monitor';
mysql> GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
mysql> CREATE USER 'application'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT ALL PRIVILEGES on *.* TO 'application'@'%';
mysql> FLUSH PRIVILEGES;
After getting ProxySQL downloaded and installed, configure it in the following way:
$ mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt 'proxy> '
proxy> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'database_ip',3306);
proxy> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
proxy> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
proxy> LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
proxy> INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('application', 'password', 1);
proxy> LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;
proxy> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
After completing this configuration, you can test out a workload on your database without enabling the cache. You can then jump back into ProxySQL and decide which queries to add to the cache.
To add queries to the cache, run this query:
proxy> SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest_reset WHERE digest_text LIKE '%SELECT%' ORDER BY sum_time DESC;
This will show which queries have been observed by ProxySQL. For each query that you want to be able to store cached results for, add it into the mysql_query_rules table in the following way:
proxy> INSERT INTO mysql_query_rules (rule_id,active,digest,cache_ttl,apply) VALUES (ID,1,HASH_CODE,10000,1);
When finished, be sure to run:
proxy> LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;
Now, you can test again and see what kinds of performance gains you get! For more information about ProxySQL, visit their website's documentation page.