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
|
1 |
mysql -P 6033 |
Admin interface
|
1 |
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
|
1 |
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.
|
1 |
service proxysql initial |
And we want to see something like these:
|
1 2 3 4 5 |
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_ttlinmysql_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 sourceproxy_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 setlog– query will be logged
And others see in mysql_query_rules in documentation
Live administration
|
1 |
>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.
|
1 |
>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.
|
1 |
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.