ProxySQL is a high performance, high availability, protocol aware proxy for MySQL, with a GPL license!
Today I am excited to announce the release of ProxySQL 1.4.0, the first beta release of series 1.4.x .
All ProxySQL versions 1.3.x will be mostly bug fixes releases, and unlikely will introduce new features: all the new features will go into ProxySQL 1.4.x .
This also mean that if you are using ProxySQL in production, you should continue using 1.3.x .
Instead, if you are not using ProxySQL in production yet, I strongly recommend trying 1.4.0 and report bugs if you find any.
What’s unique in ProxySQL 1.4.0?
What’s next is not a change log or release note, but a way to highlight the most interesting new features.
Native Support for MySQL Group Replication
ProxySQL is generally replication agnostic, therefore is suitable for any sort of replication implementation: asynchronous replication, NDB Cluster, Galera / XtraDB Cluster, Group Replication .
For NDB Cluster no special support is required: HA is provided by MySQL itself.
ProxySQL is able to perform some basic checks to support asynchronous replication like checking
Seconds_Behind_Master : this implementation has a lot of limitations, especially in cases of parallel replication or multi masters, but it generally works for a lot of scenarios.
For all other cases, ProxySQL support for clustering solution can be extended with the use of ProxySQL’s Scheduler: a feature that allows to call custom scripts at regular interval and perform action, for example monitoring the backend and react to events.
proxysql-admin is a very good example of how the Scheduler can be used to perform extensive checks in XtraDB Cluster and reconfigure ProxySQL. On this topic, I strongly recommend to attend the session on Percona XtraDB Cluster 5.7 with ProxySQL.
Another use case was the use of the Scheduler to support MySQL Group Replication.
In version 1.4.0, support for MySQL Group Replication doesn’t require anymore the use of the Scheduler, and it is now a built-in feature.
Note: why not adding also native support for Galera? Galera is still a very complex solution, and it is unlikely that embedded checks could cover all possible scenarios. Yet it is likely that a basic native support could be introduced.
Multiple Regex engines
All ProxySQL versions before 1.4.0 supports only one regex engine: RE2.
RE2 was chosen for its general performance, yet at the cost of having some limitations.
ProxySQL 1.4.0 now supports two regex engines:
PCRE is now the default regex engine in order to provide more features compared to RE2. Yet, it is still possible to switch to RE2 at runtime.
Although RE2 is generally faster than PCRE, the performance of the two regex engines may vary depends from the queries that have to be processed. In fact, in this blog post, PCRE performs better than RE2.
Improved internal database
ProxySQL internal database is an abstraction layer on top of SQLite3. The same layer is used to create portable resultsets that internal ProxySQL modules can exchange. Some part of this layer was rewritten in order to improve performance, mainly reducing the memory allocation overhead.
Furthermore, as ProxySQL’s users are mostly MySQL’s DBAs, ProxySQL’s SQLite3 now supports
Background thread to reset connections
One of the most interesting features of ProxySQL is connection multiplexing: the same connection can be used by multiple clients at the same time as long as it is safe to share the connection. When the connection cannot be shared anymore, it is linked with the client using it. What happens when the client disconnects depends from ProxySQL version.
Before ProxySQL 1.4.0, when the client disconnects the backend connection not safe to be shared is destroyed.
In ProxySQL 1.4.0, when the client disconnects the backend connection not safe to be shared is sent into a reset queue, and a background thread reset the status of these connections. Depending from the workload, this can drastically reduce the number of connections that are closed and re-created on the database servers.
change default in transaction_persistent
The default value for
transaction_persistent changed from 0 to 1. This change makes ProxySQL safer for small setups where simplicity is required. For complex setups where ProxySQL can be tuned extensively,
transaction_persistent probably needs to be set to 0.
support for FreeBSD
New features introduced in ProxySQL 1.3.x made impossible to compile ProxySQL on FreeBSD.
ProxySQL 1.4.0 now can compile on FreeBSD (and even on Mac OS X) although no binaries are currently provided.
Memory management improvements
Although the memory footprint in ProxySQL 1.4.0 is higher than in 1.3.x , this is mostly due preallocated buffers/arenas of memories.
Nonetheless, a lot of code was changed to reduce the overhead related to the memory allocator, thus improving performance.
Furthermore, in ProxySQL 1.4.0 it is now possible to enable memory profiling at runtime without restarting ProxySQL itself.
Performance improvement on large number of users
There was never a limit in the number of users that ProxySQL could support in
mysql_users table, yet some code was not optimal to handle many users.
Because a large company using ProxySQL has more than 100k users in
mysql_users table, it was required to improve such functionality.
ProxySQL 1.4.0 can run
LOAD MYSQL USERS TO RUNTIME 12 times faster than ProxySQL 1.3.x
Performance improvement on large number of MySQL servers
Also in this case, there was never a limit in the number of mysql servers that ProxySQL could support in
With a very large number of MySQL servers configured in
mysql_servers (20000 backends):
- ProxySQL 1.4.0 can run
LOAD MYSQL SERVERS TO RUNTIMEup to 40 times faster than ProxySQL 1.3.x
- ProxySQL 1.4.0 can run
SAVE MYSQL SERVERS FROM RUNTIMEup to 20 times faster than ProxySQL 1.3.x
YES: ProxySQL uses the GPL license and it is free to use even with more than 2 backends. In fact, there is no limit at all, and benchmarks have been executed with thousands of backend servers.
ProxySQL 1.4.0 exports few more metrics not available in 1.3.x . Although the new metrics aren’t many, more metrics will be available only in 1.4.x and not in 1.3.x .
It also introduces a new table
stats_mysql_connection_pool_reset to atomically selects and reset metrics from Connection Pool.
Mirroring in 1.2.x and 1.3.x didn’t support any flow control mechanism: on a very busy system, this could cause severe performance implication.
ProxySQL 1.4.0 introduces several performance improvements, including a flow control mechanism configurable defining the number of mirror sessions that can run in parallel, and a maximum length of queues uses for mirrored traffic.
If the maximum length is reached, mirrored requests are dropped.
Reload the configuration database
Although it is recommended to reconfigure ProxySQL executing SQL commands in the Admin interface, it is also possible to replace the configuration database
proxysql.db. Before version 1.4.0 it is required to restart proxysql to read the new file. ProxySQL 1.4.0 introduces a new command (
PROXYSQL FLUSH CONFIGDB) to open the new
proxysql.db file without restarting the process.
Forward SET autocommit
Because ProxySQL is designed to support sharding and very large deployments,
set autocommit is not forward to any backend but tracked internally and the connection pool will take care to set the right autocommit value when processing traffic.
Although this technique fits very well for sharded environments, it is not ideal for setups with only one shard where
set autocommit=0 is used to start a transaction on the single writer available.
ProxySQL 1.4.0 introduces a new variable (
mysql-forward_autocommit) that defines whatever
set autocommit should be tracked and applied when needed, or forwarded:
mysql-forward_autocommit=false(default) : ProxySQL internally tracks
set autocommitstatements and apply them when required
mysql-forward_autocommit=true: ProxySQL forwards the request to the right backend based on query rules
Disable/enable multiplexing using query rules
ProxySQL has several internal rules to determine when multiplexing should be disabled. Yet not all possible cases can be covered, and it is even possible that ProxySQL disables multiplexing when it shouldn’t.
A new column
multiplex allows to configure ProxySQL to explicitly disable multiplexing when certain queries are processed, or ignore internal rules that otherwise would disable multiplexing (that is, keep multiplexing enabled).
next_query_flagIN creates a new algorithm for query routing. When
next_query_flagIN is set, its value will become the
flagIN value for the next queries.
I will need to publish a blog post to better explain how to configure this algorithm and when when to use it. For now, you can read this pull request.
Temporarily disable/delay multiplexing
Generally, when multiplexing is enabled a backend connection goes immediately back to the connection pool as soon as it completes processing a single query.
ProxySQL 1.4.0 allows to define a time interval in which an idle backend connection stays associated with the client connection before returning to the connection pool.
Although this algorithm increases complexity, it ensures that if a client issues many requests in a short time interval, all requests will be sent to the same backend connection.