Get best of the week

ProxySQL - a tool for demultiplexing connections

Hello, my name is Alexander Yakovlev, I work for Citimobil and operate. Today I will tell you about a very interesting ProxySQL product - it is a high-performance MySQL Proxy that can do a lot of things - catch and kill requests by mask, with it you can search for sql injection, duplicate the load and much more. I will talk about our experience with him.
Sooner or later, any large IT project, the development of which began with a pair of servers, will encounter the situation described below. Imagine that the project initially had only one database - the master server. Gradually, a bunch of slaves were added to it. Then they introduced sharding.

And one fine day, the load suddenly increases 10 times. For example, because your main competitor has fallen, and customers have rushed to you. At this point, it seems to you that you can simply scale the load by adding web servers. But after you have done this, an unpleasant situation arises.

Consider the example of one wizard. Let's say you have 50 web servers and on every 200 php-fpm processes. Then 50 * 200 connections will arrive at the master, and at that 50 * 200 / number of slaves will come to each slave (if, of course, roundrobin is configured in haproxy) - see the picture below. Of course, 10 thousand connections to the master is a lot, but still bearable, and if there are 200 web sites, the number of connections will be even more, and one connection = one thread.
It was in this bottleneck that we ran into.

image

Then we began to argue: the connection to the wizard is always installed in the code, but is it necessary for all fpm processes? Probably not. We noticed that a large number of persist-connections to the master simply hang in slips. And they decided that we need demultiplexing.

image

To do this, we drew attention to a product called ProxySQL. It works like a normal reverse proxy: connections are established to it, and it redistributes traffic according to certain rules specified in the configuration.

We installed ProxySQL on all of our web servers, and in the application configuration we specified that the call to the master database is performed at the address 127.0.0.1. If before 200 FPM workers on each web server meant 200 connections to the master base from this machine, now the situation has changed. These 200 connections come in ProxySQL, and 50-70 go out at different times. That is, ProxySQL can reuse existing connections.

Thanks to demultiplexing, we reduced the number of connections by 3-10 times on all masters, see the current connections graph of one of the masters below.

image

Thanks to ProxySQL, we got rid of the above bottleneck. But this is not the only workflow that we have improved with this tool.
We have not yet completed the second process, but are very close to completion. Using ProxySQL, we plan to duplicate the real load in the test environment. This is necessary to check new features with combat traffic.

How will this be implemented? The application goes to ProxySQL, and it sends traffic along two routes: to the battle databases for the application to function, and to the test environment to check for new features under load.

The feature of ProxySQL is that there is its config, which in our case is rolled out through puppet (for puppet there is a ProxySQL module), but there is also the concept of the runtime zone, in order to make a change (add server, add user, delete server and user ), you do not need the usual restart / reload., Everything is done through the ProxySQL console, for example like this.

mysql -ulogin -ppassword -h 127.0.0.1 -P6032 -e "INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('sm_username','pass',1);;LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS TO DISK;"

In more detail, of course, in the official documentation proxysql.com/documentation

Thank you for your attention.

Our config, with which we solved the problem described above, see below.

Our config

datadir="/var/lib/proxysql"

admin_variables=
{
    admin_credentials="user:pass"
    mysql_ifaces="0.0.0.0:6032"
    refresh_interval=2000
    web_enabled=true
    web_port=6080
    stats_credentials="stats:admin"
}

mysql_variables =
{
    threads = 1000
    max_connections = 2000
    default_query_delay= 0
    default_query_timeout=1
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
    default_schema="information_schema"
    stacksize=1048576
    server_version="5.7.22"
    connect_timeout_server=10000
    monitor_history=60000
    monitor_connect_interval=200000
    monitor_ping_interval=200000
    ping_interval_server_msec=5000
    ping_timeout_server=200
    commands_stats=true
    sessions_sort=true
    monitor_username="root"
    monitor_password="password"
    monitor_galera_healthcheck_interval=200
    monitor_galera_healthcheck_timeout=80
}

mysql_servers =
(
  {
    address = "ip_real_mysql_server",
    port = 3306,
    max_connections = 10000,
    host_group = 1,
  })
mysql_users =
(
  {
    username = "user",
    password = "pass",
    default_hostgroup = 1,
    transaction_persistent = 0,
    active = 1,
  })

More articles:


All Articles