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.
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.
mysql -P 6033
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
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
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
>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.