What is multiplexing?
Multiplexing in ProxySQL is a feature that allows multiple frontend connections to re-use the same database backend connection. MySQL uses a “thread per connection” rather than a “thread pool” implementation. This results in a single “software thread” being opened for each connection which will consume resources and gradually degrade performance as the number of connections increase (even when idle). ProxySQL uses a “thread pool” and by Multiplexing, ProxySQL further reduces the number of resources being allocated and managed by the database backends which greatly improves performance.
ProxySQL will track certain session specific variables in order to identify whether an in-use backend connection can be shared by other frontend connections, while it is desired to share connections it is not always possible as certain conditions implicitly require multiplexing to be disabled.
Conditions where multiplexing is disabled
When a transaction is active in a connection, multiplexing is disabled until the transaction commits or rollbacks.
LOCK TABLES or
FLUSH TABLES WITH READ LOCK is executed, multiplexing is disabled until
UNLOCK TABLES is executed.
GET_LOCK() is executed, multiplexing is disabled and is never enabled again.
use of specific session/user variables
All queries that have
@ in their query_digest will disable multiplexing, and it will never be enabled again.
NOTE: If you are selecting a variable (e.g. select @test_var) and you are not getting the results you expected, it’s most probably due to a query rule which is routing your statement to a different hostgroup (review your query rules for this).
Similarly, the same happens if these commands are executed:
SET SQL_SAFE_UPDATES=?,SQL_SELECT_LIMIT=?,MAX_JOIN_SIZE=? (
SET AUTO_INCREMENT_INCREMENT (v 1.4.4+)
SET AUTO_INCREMENT_OFFSET (v 1.4.4+)
SET GROUP_CONCAT_MAX_LEN (v 1.4.4+)
There are 2 exceptions hardcoded that do not disable multiplexing:
These exceptions are hardcoded because many applications run these in every connection.
If a query contains
SQL_CALC_FOUND_ROWS, multiplexing is disabled and is never enabled again on the connection.
CREATE TEMPORARY TABLE is executed, multiplexing is disabled and is never enabled again on the connection.
Note: disabling multiplexing doesn’t disable routing, so it might happen that after a
CREATE TEMPORARY TABLE is executed, a
SELECT query on the same table returns a
'schemaname.temporary_tablename' doesn't exist error message. The reason for this is that while multiplexing is disabled, routing isn’t – and if the two statements are sent to two different hostgroups, the error message will appear. To prevent this, it is advised to use query routing. See MySQL query rules.
PREPARE is executed (creation of prepared statements using the TEXT protocol and not the BINARY protocol), multiplexing is disabled and is never enabled again.
SQL_LOG_BIN sets to 0
SQL_LOG_BIN is set to 0, multiplexing is disabled until
SQL_LOG_BIN is set back to 1.
If multiplexing is disabled by configuration (using the configuration parameter
mysql-multiplexing) multiplexing is globally disabled for all sessions.
ProxySQL multiplex delay parameters
When configuration parameters
mysql-connection_delay_multiplex_ms have been set to a non-zero value, multiplexing will be disabled on a connection for X-amount of queries or milliseconds. Both variables have been introduced in version 1.4.14 where
mysql-auto_increment_delay_multiplex is set to a default of 5.
For an explanation of the impact of both parameters, read the section on Multiplex Delay below.
How multiplexing affects session variables
Session variables set on a frontend connection are not automatically issued to a backend connection. ProxySQL maintains a set of
mysql-default_XXX global variables for each handled session variable, for example “mysql-default_time_zone=SYSTEM”. ProxySQL assumes that this is the default value set on all backends and ProxySQL will only propagate this to the backend connection if a client issues a different value (e.g. to set the timezone to “UTC”). This is done in order to improve connection re-usability and in order to achieve optimal performance it is best to evaluate the commonly used setting and ensure this is set as default.
Handling of switchovers from nodes in OFFLINE_SOFT status
When multiplexing is disabled due to any of the reasons described here, an active connection will remain connected to a node that has gone to OFFLINE_SOFT status. Queries will also continue to be routed to this node. If you use a connection pool mechanism in the application make sure to recycle connections prior to performing the failover to ensure that connections have been moved off the OFFLINE_SOFT node.
If an active transaction was the reason for disabling multiplexing, then the connection is automatically moved off the OFFLINE_SOFT node after the transaction has finished.
Impact of multiplexing delay parameters
Several applications rely, explicitly or implicitly, on the value returned by LAST_INSERT_ID(). If multiplexing is not configured correctly, or if the query-pattern is really unpredictable (for example if new queries are often deployed), it is possible that the query using LAST_INSERT_ID() uses a connection different than the connection where an auto-increment was used. To prevent this from happening the two delay parameters allow you to have the connection stop multiplexing for a number of queries or milliseconds.
In the MySQL binary protocol every successful query returns an OK packet to the client. Whenever an insert or update would trigger an auto increment column this OK packet also returns the last inserted identifier. ProxySQL will pick up this combination to delay multiplexing for the set values of both parameters.
mysql-connection_delay_multiplex_ms have been introduced in version 1.4.14 where
mysql-auto_increment_delay_multiplex is set to a default of 5. This means that as of this version multiplexing is disabled on a connection whenever ProxySQL encounters an OK packet with last inserted identifier set. Also keep in mind that both parameters are global variables and will affect all connections and hostgroups on a ProxySQL host.
multiplexing allows us to enable or disable multiplexing based on matching criteria.
The field currently accepts these values:
- 0 : disable multiplex
- 1 : enable multiplex
- 2 : do not disable multiplex for this specific query containing
For example, in your application is using
SET SQL_SELECT_LIMIT , you can create these 2 rules:
INSERT INTO mysql_query_rules (active,match_digest,multiplex) VALUES
('1','^SET SQL_SELECT_LIMIT=?',0), (1,'^SET SQL_SELECT_LIMIT=DEFAULT,1);
If your application is sending something like
SELECT @@max_allowed_packet and you want to prevent multiplexing to be disabled because of this, you can create the following rule:
INSERT INTO mysql_query_rules (active,match_digest,multiplex) VALUES
… and maybe even cache it:
INSERT INTO mysql_query_rules (active,match_digest,multiplex,cache_ttl) VALUES
If your application is trying to set a variable that will lead to the disabling of multiplexing, and you think that it can be filtered, you can create a filter that returns OK without executing the query:
INSERT INTO mysql_query_rules (active,match_digest,OK_msg) VALUES
('1','^SET @@wait_timeout = ?','');
Tracking multiplexing status
In ProxySQL version 2 an
extended_info JSON field containing information about the underlying session has been added. This can be queried in the
stats_mysql_processlist table by executing
select extended_info from stats_mysql_processlist;.
This will return highly detailed information regarding the session and helps to identify if Multiplexing has been disabled as well as the reason for this, specifically the following can be identified:
Generally sessions that a.) are running or b.) have multiplexing disabled will have a backend associated. Within the backend data you should look for the following fields in order to identify if conditions are present to disable multiplexing:
"MultiplexDisabled": true – Multiplexing is globally disabled via
- “sql_log_bin”:”0″ – Multiplexing is implicitly disabled when
SET SQL_LOG_BIN=0 is executed
- “status”: Any of the following conditions being true will disable multiplexing
- “server_status”:33 – SERVER_STATUS_IN_TRANS is defined as the first bit (0x0001) in the MySQL status flags i.e. when a transaction is active, the server status will result in an odd number.
- “last_errno”:0 – When this is != 0 then multiplexing is disabled until “last_errno” becomes 0 again
- “autocommit”:”OFF”- If this is set and
mysql-autocommit_false_is_transaction=true then ProxySQL interprets the connection on which it is set as a running transaction and keeps multiplexing disabled.
The above list provides the most common set of conditions that might disable multiplexing, all of which are present per session in the
stats_mysql_processlist table. This is by no means a complete list of factors that might disable multiplexing, other global variables which are mentioned within this document need to also be verified.