The art of multiplexing MySQL connections with ProxySQL

Rene Cannao, ProxySQL's creator discusses multiplexing best practices for MySQL at FOSDEM 2019.

Handling tens of thousands of database connections poses seriously performance issues on any database system because resources are poorly utilized. ProxySQL, a MySQL protocol aware, reverse proxy for database servers using the MySQL protocol, is able to track connections statuses and utilize very few connections to MySQL server to serve traffic from thousands of client connections. This session will focus on explaining implementation details and how this algorithm is able to boost performance.

Transcript

1. ProxySQL The art of Multiplexing
2. ProxySQL LLC We provide services to help build, support and improve the performance & reliability of your Cloud-Based and On-Premise MySQL infrastructure. © ProxySQL 2013-2019. All rights reserved.
3. ProxySQL LLC • ProxySQL Development • ProxySQL Support Services • ProxySQL, MySQL, DevOps & Outsourcing • Consulting Services © ProxySQL 2013-2019. All rights reserved.
4. About me René Cannaò • Author of ProxySQL • Founder of ProxySQL LLC • MySQL DBA © ProxySQL 2013-2019. All rights reserved.
5. What is ProxySQL? MySQL protocol aware data gateway – Clients connect to ProxySQL – Requests are evaluated – Various actions are performed © ProxySQL 2013-2019. All rights reserved.
6. • MySQL, MariaDB, Percona and ClickHouse backends • Supports millions of users and tens of thousands of database servers • High Availability and infinite scalability • Dynamic runtime reconfiguration • Seamless planned and unplanned failover • MySQL Session Load balancing ProxySQL features © ProxySQL 2013-2019. All rights reserved.
7. • Connection pooling and multiplexing • Read caching outside of the database server • Complex query routing and read/write split • Query throttling, firewalling and mirroring • On-the-fly query rewrite • Advanced data masking ProxySQL features © ProxySQL 2013-2019. All rights reserved.
8. • Real time statistics and monitoring • Scheduling of external scripts • Support for PXC and Group Replication • Native ProxySQL Clustering • Launching multiple instances on same port ProxySQL features © ProxySQL 2013-2019. All rights reserved.
9. • SSL support for frontend & SSL v1.2 • Native support for Galera Replication • Integration with Amazon RDS Aurora (more to come) • Causal reads by tracking GTIDs across backend servers • Support for LDAP authentication Whats new in ProxySQL 2.0? © ProxySQL 2013-2019. All rights reserved.
10. MySQL performance degrades with an elevate number of connections, even if idle. The problem © ProxySQL 2013-2019. All rights reserved.
12. Threading Models • One thread per connection • Easier to develop • Blocking I/O • Thread pooling • Non blocking I/O • Scalable
13. Cost of MySQL’s one-thread-per-connection • Too many software threads per hardware thread • CPU registres save/restore and context switching • Mutexes/locks contentions • CPU cache almost useless • High cost for access to memory
14. Common Thread Pool Implementations • One thread accepts connections • Connections are passed to worker threads • One or more threads perform network I/O • I/O queuing occurs • Fixed or dynamic number of worker threads
15. MySQL Enterprise has thread pool. MariaDB and Percona too. • New client connections are accepted by a listener • The connection is then handled over to a group of a thread pool. • Each client connection is always handled by the same thread pool. This drastically improves performance, because it solves all the issues associated with one-thread-per-connection. Thread pool in MySQL © ProxySQL 2013-2019. All rights reserved.
16. • Threads in ProxySQL are known as "MySQL Threads" • Fixed number of worker threads (configurable) • All threads listen on the same port(s) • Client connections are not shared between threads • All threads perform their own network I/O • Uses a mix of poll() and epoll() Thread pool in ProxySQL © ProxySQL 2013-2019. All rights reserved.
17. Threads never share client connections • Pros: • Thread contention is reduced • No need for synchronization • Each thread calls poll()/epoll() • Cons: • Possibly imbalanced load Thread 1 Thread 2 Thread 3
18. Thread pool in ProxySQL: • Solves the problem of contention in ProxySQL • Doesn’t solve the problem of high number of connections in MySQL server In a normal level 4 proxy, but also in other layer 7 proxies, there is a one-to-one mapping between client connections and backend connections. For each client connection a backend connection is created. Is this the solution? © ProxySQL 2013-2019. All rights reserved.
19. ProxySQL tracks the status of every connections, including transactions, session variables, temporary tables, etc It can determine if the same backend connection can shared between multiple frontend connections. A backend connection is returned to the connection pool as soon as each query completes (assuming the connection can be shared). This leads to the fact that requests from thousands of frontend connections can be funneled into just few backend connections. ProxySQL connection pool © ProxySQL 2013-2019. All rights reserved.
20. • Reduce the number of connections against mysqld (configurable) • Many clients connections (tens of thousands) can use few backend connections (few hundreds) Multiplexing © ProxySQL 2013-2019. All rights reserved.
21. Active transaction Table locks: • LOCK TABLE(S) and FLUSH TABLES WITH READ LOCK • Until UNLOCK TABLES GET_LOCK() When multiplexing is disabled © ProxySQL 2013-2019. All rights reserved.
22. Disable binlog: • SET SQL_LOG_BIN=0 • Until SET SQL_LOG_BIN=1 CREATE TEMPORARY TABLE SQL_CALC_FOUND_ROWS PREPARE (text protocol) When multiplexing is disabled © ProxySQL 2013-2019. All rights reserved.
23. Several session variables: • SET UNIQUE_CHECKS • SET AUTO_INCREMENT_INCREMENT • SET FOREIGN_KEY_CHECKS • others When multiplexing is disabled © ProxySQL 2013-2019. All rights reserved.
24. User variables or session variables used with @ It is possible to create rules to not disable multiplexing in such cases When multiplexing is disabled © ProxySQL 2013-2019. All rights reserved.
25. Multiplexing doesn’t disable routing Active transaction is the only exception, yet configurable Multiplexing and routing © ProxySQL 2013-2019. All rights reserved.
26. ProxySQL Multiplexing Examples at Shopify
27. No ProxySQL: ProxySQL:
28. No ProxySQL: ProxySQL:
29. ProxySQL: No ProxySQL:
32. It is also possible to configure proxysql to use only a very limited number of backend connections What happens if the number of active clients exceed the number of total backend connections? The requests from such clients are put on hold until a backend connection becomes free. To ensure fair access to backends, when deciding which client should take the connections, the clients are ordered by waiting time. Use very few backend connections © ProxySQL 2013-2019. All rights reserved.
33. Use very few backend connections © ProxySQL 2013-2019. All rights reserved. https://www.percona.com/blog/2016/05/19/fixing-mysql-scalability-problems-proxysql-thread-pool/
34. Questions? © ProxySQL 2013-2019. All rights reserved.