-
Notifications
You must be signed in to change notification settings - Fork 992
Multiplexing
- active transaction
When a transaction is active in a connection, multiplexing is disabled until the transaction commits or rollbacks.
- table(s) locked
If LOCK TABLE, LOCK TABLES or FLUSH TABLES WITH READ LOCK is executed, multiplexing is disabled until UNLOCK TABLES is executed.
- use of
GET_LOCK()
If 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 results as you expected, its 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=?(mysql --safe-updates) SET FOREIGN_KEY_CHECKSSET UNIQUE_CHECKS-
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:
SELECT @@tx_isolationSELECT @@version
These exceptions are hardcooded because many applications run these in every connection.
- use of
SQL_CALC_FOUND_ROWS
If a query contains SQL_CALC_FOUND_ROWS, multiplexing is disabled and is never enabled again on the connection.
- Temporary tables
If CREATE TEMPORARY TABLE is executed, multiplexing is disabled and is never enabled again on the connection.
- use of
PREPARE
If 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_BINsets to 0
If SQL_LOG_BIN is set to 0, multiplexing is disabled until SQL_LOG_BIN is set back to 1.
- ProxySQL configuration
If multiplexing is disabled by configuration (using the configuration parameter mysql-multiplexing) multiplexing is disabled for all sessions.
- ProxySQL multiplex delay parameters
When configuration parameters mysql-auto_increment_delay_multiplex or 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.
Most of the session variables are not handled automatically.
For example, if a client issues SET TX_ISOLATION=? , multiplexing is NOT disabled.
This is a bug if you have clients that use different tx_isolation, while it is a feature if all the clients specify the same tx_isolation that is also the default (this is the common case, in my experience).
When multiplexing is disabled due to any of the reasons described here, an active connection will remain connected to a node that has gone the OFFLINE_SOFT status. Queries will also remain to be routed to this node. If you use a connection pool mechanism in the application, make sure you recycle your connections often enough in a Galera cluster. If an active transaction was the reason for multiplexing to be disabled, the connection is moved after the transaction has finished.
Several applications rely, explicitly or implicitly, to the value returned by LAST_INSERT_ID(). If multiplexing is not configured correctly, or if the queries 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 paramaters allow you to have the connection stop multiplexing for a number of queries or milliseconds.
In the MySQL binary protocol every successfully 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.
Parameters mysql-auto_increment_delay_multiplex or 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 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.
mysql_query_rules.multiplexing allows 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
('1','^SELECT @@max_allowed_packet',2);
... and maybe even cache it:
INSERT INTO mysql_query_rules (active,match_digest,multiplex,cache_ttl) VALUES
('1','^SELECT @@max_allowed_packet',2,60000);
If your application is trying to set a variable that will lead to disable 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 = ?','');