Multiplexing (Mux) in ProxySQL: Use Case

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:

To create rules on ProxySQL:

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.allaboutcircuits.com/technical-articles/an-intro-to-multiplexing-basis-of-telecommunications/

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