Since its genesis ProxySQL was designed to be reconfigurable at runtime without restarting it. There are only 2 exceptions: changing the listening ports (work in progress to fix that) and number of threads. Everything else was always reconfigurable at runtime. Adding and removing servers, adding and removing users, configure routing, configure caching, and tune all the internal variables.
This design was driven by the wish of trying to avoid to restart ProxySQL, no matter the reason.
ProxySQL creates high availability, therefore it is extremely important that it has a very high uptime. To ensure high availability, it also implements a strategy borrowed from ndbd in MySQL Cluster. When proxysql starts, it immediately forks:
- the parent process only checks the exit status of the child process and restart it if it didn’t exit gracefully
- the child process is the real proxysql service
In other words, if proxysql dies, it parents automatically restarts it. You could achieve the same also with systemd, supervisord or others, but ProxySQL tries to handle this autonomously.
Yet there is another reason when ProxySQL breaks high availability: upgrades.
If you want to upgrade from version X to version Y, the usual procedure is: – download the new version – make a copy of current database file proxysql.db in case you want to downgrade: that step is important, as an upgraded proxysql.db file is not always backward compatible – install the new version – restart the service
The restart of proxysql to upgrade from version X to version Y generally takes just a fraction of second, so this doesn’t sound a big availability hit. Although there are certain users of ProxySQL that have very complex configurations where a restart is long process. An example of this is a hosting provider where a single proxysql instance has hundreds of thousands of users in mysql_users table (yes, that many!).
Yet there are also cases in which you don’t want to upgrade immediately from version X to version Y without having first tested Y. Assume you have tried version Y in staging, all works great, and it is now time to deploy Y in production. Wouldn’t be great to have both versions running at the same time?
Do you want to run multiple proxysql instances in parallel? All listening on the same port(s)? You can!
With ProxySQL this is possible to run multiple processes on the same port(s)!
ProxySQL allows to have multiple processes, even different versions (or different configurations) running at the same time … on the same ports!
Starting from version 1.3.0, ProxySQL introduces a new feature that allows to share the same TCP sockets.
To explicitly enable this feature, proxysql needs to be started with -r or --reuseport: this enables the use of the SO_REUSEPORT socket option. This option is currently disabled by default because requires Linux kernel 3.9 or newer: although kernel 3.9 was released in April 2013, there are still a lot of production environments that use an older kernel. Future version of ProxySQL will automatically detect if the option is available, and enable it if possible.
To avoid interference between two (or more) proxysql instances running on the same host it is recommended to use different config file (using -c flag) and different datadir (using -D flag).