ProxySQL – other way of caching MySQL

ProxySQL is a very powerful tool for the MySQL database. In simple words, it can tweak your MySQL when you have problems with slow queries or even it can be some kind of bridge between your MySQL databases placed in different localizations. Like in the name, it is a proxy, it’s placed between MySQL and application.

Basics

ProxySQL runs 2 services on 2 ports. For default ProxySQL “admin interface” is on port 6032  and ProxySQL is run on port 6033. We can access our database (or databases) simply by using this port with a MySQL connection.

ProxySQL connection

mysql -P 6033

Admin interface

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

It runs administration tool which is working separately and we have access to tweak our caching parameters live!

Initialization and reinitialization

When we install our service we can turn it on

service proxysql start

After the first startup, the DB file is used instead of the config file. So it is necessary to do an initialization of our service to read our current config file.

service proxysql initial

And we want to see something like these:

Starting ProxySQL: Main init phase0 completed in 0.001735 secs.
Renaming database file /var/lib/proxysql/proxysql.db
Main init global variables completed in 0.000392 secs.
Main daemonize phase1 completed in 2.7e-05 secs.
DONE!

After initialization we have clean connection threw ProxySQL with our configuration file /etc/proxysql.cnf   are a lot of parameters but the essence of this tool is mysql_query_rules.

A popular use of ProxySQL is to act as a query cache. By default, queries aren’t cached, but it can be enabled by setting cache_ttl in mysql_query_rules .

Query Caching

In mysql_query_rules we have:

• cache_ttl – the number of milliseconds for which to cache the result of the query.
• client_addr – match traffic from a specific source
• proxy_addr – match incoming traffic on a specific local IP
• many regular expressions – regular expressions that match the query text
• destination_hostgroup – connected with other section mysql_servers where target DB is set
• log – query will be logged

And others see in mysql_query_rules in documentation

Live administration

>Admin UPDATE mysql_query_rules set cache_ttl=5000 WHERE active=1 AND destination_hostgroup=0;

In this case, we change  cache_ttl in all active rules for host group 1 – simple SQL query run from the admin interface.

>Admin LOAD MYSQL QUERY RULES TO RUNTIME;

And bring the changes to the runtime! And it works!

MySQL query cache.

At the end few cons. We have this great tool called “Mysql query cache” till MySQL version 5.7. In my own opinion MySQL is weaker without it, but, this is a subject for another article.

“Mysql query cache” is clever – this mechanism check query and if a query is not deterministic it can’t be cached! So if you use date function (to get current date or time) or when you check connection id or when you use other random function it won’t be cached in MySQL query cache.

select id from wp_posts order by rand() limit 5;

With ProxySQL we always have the same result!

Offcourse when TTL is not over.

ProxySQL is a very powerful tool but as always in the wrong hands, it can do as many damages as good.