Multiplexing Background
Historically it’s a technique used in networking to integrate multiple analog and digital signals via a shared medium. The goal of multiplexing over the network was to enable signals to be transmitted more efficiently for a given communication channel, thus achieving cost efficiency.
Since the term Multiplexing comes from telecommunications, the industry has heavily used a device called Multiplexers – aka Mux. There was even a term called muxing where signals were often analog and digital to be combined in a single line.
The technique was developed in the early 1870s, it’s origins to be found in telegraphy, and it has become a standard for digital telecommunications in the 20th Century.
Following multiplexing methods are currently available:
- Frequency Division Multiplexing (FDM)
- Time Division Multiplexing (TDM)
- Wavelength Division Multiplexing (WDM)
There’s the other way in telco that we aren’t going to get into too much, which is called demultiplexer. Demux involves reanalyzing composite signals to separate them.
How can we achieve Multiplexing for MySQL database connections?
We would basically need a Mux between the database and the application server. This means a proxy layer that can combine communication channels to the backend database. Again the goal is to reduce the overhead of opening several connections and maintaining a minimal number of open connections to reduce memory footprint.
Thread pooling is available in the following MySQL distributions:
- MySQL Enterprise Edition
- Percona Server
- MariaDB
The way they work is the listener accepts the connections and hands it over to a group of thread pools. This way each client connection is handled by the same thread pool. The above implementations aren’t as efficient as ProxySQL’s implementation.
ProxySQL comes to help when we need to Mux our communication channels to a database server, where it can often be flooded.
Main Use Cases
- Any application with a persistent connection to the database
- Java applications to be specific with built-in connection pools
Goals to Achieve
- Reduce connections similar to Aurora
- Reduce huge number of connections from hardware
- Reduce context switching
- Reduce mutex/contention
- Reduce CPU cache usage
ProxySQL Technique Used:
Threading Models
- One thread per connection
- Easier to develop
- Blocking I/O
- Thread Pooling
- Non-blocking I/O
- Scalable architecture
Here’s what we can control using ProxySQL
Pros
- Reduced number of connections and overhead to the backend database
- Control over database connections
- Collect metrics of all database connections and monitor
- Can be enabled and disabled.
Cons
- Certain conditions and limitations apply to use.
- Can cause unexpected behavior to application logic.
How it works:
Ad-hoc enable/disable of multiplexing
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 @
Also, ProxySQL has some default behavior that can lead to unexpected results. In fact, ProxySQL can disable on purpose the multiplexing on a connection in the following cases:
- When a transaction is active
- When you issued commands like LOCK TABLE, GET_LOCK() and others
- When you created a temporary table using CREATE TEMPORARY TABLE
- When you used in the query session or user variables starting the @ symbol
In the majority of cases, ProxySQL can return the connection to the pool enabling the multiplexing. But only in the last case, when using session and user variables starting with @, the connections never return to the pool.
In a recent investigation on one of our client’s systems, we discovered a high amount of connections and threads used, despite the ProxySQL layer. The problem was related to queries containing the @ symbol in the Java connector when establishing a new connection. In a matter of seconds after enabling it, the multiplexing was disabled for all the connections. The effect is having no multiplexing at all: for example, 2000 connections from the front ends were reflected on the same number of connections to the back ends. We needed a way to avoid automatic disabling of multiplexing.
How to find on ProxySQL stats all the executed queries containing the @ symbol:
1 2 3 4 5 6 7 | SELECT DISTINCT digest, digest_text FROM stats_mysql_query_digest WHERE digest_text LIKE '%@%'; *************************** 1. row *************************** digest_text: select @@version_comment limit ? *************************** 2. row *************************** digest_text: SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout *************************** 3. row *************************** digest_text: SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout |
To create rules on ProxySQL:
1 2 3 4 5 | INSERT INTO mysql_query_rules(active,digest,multiplex,apply) SELECT DISTINCT 1,digest,2,0 FROM stats_mysql_query_digest WHERE digest_text LIKE '%@%'; LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK; |
And the result revealed huge savings over connections and thread pool directly captured from PMM.
Conclusion
In conclusion, ProxySQL’s multiplexing feature is something every high traffic site would benefit from. What we always observe is a lower number of connections to the backend lowers the overhead of both memory utilization and context switching. In order to fully benefit multiplexing, be aware of the above limitations and investigate connection types even after implementing.
References
https://www.techopedia.com/definition/8472/multiplexing
Understanding Multiplexing in Telecommunications
https://medium.com/searce/reduce-mysql-memory-utilization-with-proxysql-multiplexing-cbe09da7921c
https://en.wikipedia.org/wiki/Multiplexing
Thanks for their valuable input :
- Rene Cannao
- Marco Tusa
- Daniel Guzman Burgos
- Corrado Pandiani
- Tom De Comman
The Anome, Multiplexing diagram, modified, CC BY-SA 3.0