ProxySQL – other way of caching MySQL

ProxySQL

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

Admin interface

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

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.

And we want to see something like these:

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

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

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.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *