In this blog post, we’ll discuss fixing MySQL scalability problems using either ProxySQL or thread pool.
In the previous post I showed that even MySQL 5.7 in read-write workloads is not able to maintain throughput. Oracle’s recommendation to play black magic with innodb_thread_concurrency and innodb_spin_wait_delay doesn’t always help. We need a different solution to deal with this scaling problem.
All the conditions are the same as in my previous run, but I will use:
- ProxySQL limited to 200 connections to MySQL. ProxySQL has a capability to multiplex incoming connections; with this setting, even with 1000 connections to the proxy it will maintain only 200 connections to MySQL.
- Percona Server with enabled thread pool, and a thread pool size of 64
You can see final results here:
There are good and bad sides for both solutions. With ProxySQL, there is a visible overhead on lower numbers of threads, but it keeps very stable throughput after 200 threads.
With Percona Server thread pool, there is little-to-no overhead if the number of connections is less than thread pool size, but after 200 threads it falls behind ProxySQL.
There is chart with response times
I would say the correct solution depends on your setup:
- If you already use or plan to use ProxySQL, you may use it to prevent MySQL from saturation
- If you use Percona Server, you might consider trying to adjust the thread pool
About the Author
Vadim TkachenkoVadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.
Nice!
I guess in both cases additionally to contention issue we can reduce number of transactions in flight and as such have less row level lock waits.
What about latency? How much does using a proxy increase latency?
Andy,
I added the chart with letency
> With Percona Server thread pool, there is little-to-no overhead if the number of threads is less than thread pool size
Did you mean “there is little-to-no overhead if the number of connections is less than thread pool size”? I’d think by definition the “number of threads” is the thread pool size, no?
Andy,
That’s correct (I made that change).
I often user “connections” and “user threads” as synonyms, but in the case with thread pool, “connection” is more correct term.
Vadim,
Did you try way more client connections?
Hardware and network configuration are powerful enough that I believe ProxySQL will successful handle 2k, 4k and 8k connections without any specific issue, multiplexing all of them into just 200 backend connections.
With the right configuration (max_connections and ulimit) I am sure ProxySQL can handle way more active client connections (16k, 32k, etc), but with the default value of mysql-connect_timeout_server_max (10000) will probably fail.
In fact, ProxySQL guarantees a maximum wait time before a backend connection is granted to a client, that is mysql-connect_timeout_server_max .
If ProxySQL is not able to assign a backend connection to a client within mysql-connect_timeout_server_max milliseconds (because all the backend connections are busy), it will return the error “Error 1045 Max connect timeout reached while reaching hostgroup 0 after 10000ms” .
That said, with a more permissive value of mysql-connect_timeout_server_max (or with less statements per transaction) ProxySQL will be to handle tens of thousands of active client transactions.
Thank you!