Releasing ProxySQL 1.4.0

  • Date:
  • Tags: proxy mysql release proxysql ha gpl
ProxySQL 1.4.0

Releasing ProxySQL 1.4.0 (beta)

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.

It can be downloaded here, and freely usable and accessible according to GPL license.
Please report any bugs or feature requests on github issue tracker

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 , now the default regex engine
  • RE2

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 FROM_UNIXTIME() .

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 mysql_users.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 mysql_servers table.
With a very large number of MySQL servers configured in mysql_servers (20000 backends):

  • ProxySQL 1.4.0 can run LOAD MYSQL SERVERS TO RUNTIME up to 40 times faster than ProxySQL 1.3.x
  • ProxySQL 1.4.0 can run SAVE MYSQL SERVERS FROM RUNTIME up 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.

More metrics

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 Performance

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 autocommit statements 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 mysql_query_rules.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

mysql_query_rules.next_query_flagIN creates a new algorithm for query routing. When mysql_query_rules.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.