Cache configuration

Want an easy-to-use, performant, MySQL database? Try PlanetScale now

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;

Configuring ProxySQL

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;

Testing

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.

PlanetScale Boost

PlanetScale Boost is an advanced query caching feature built into the PlanetScale platform. With Boost, you don't need to worry about configuring TTL and other cache invalidation logic, as changes made to the DB are streamed to and updated in the cache! It is already straightforward to configure and add queries using the PlanetScale application.