We just published results with improvements in Thread Pool in Percona Server:
Percona Server: Thread Pool Improvements for Transactional Workloads
Percona Server: Improve Scalability with Thread Pool
What I am happy to see is that Percona Server is able to handle a tremendous amount of user connections. From our charts you can see it can go to 16000 active connections without a decline in throughput.
In fact, in our experiments we used 25000 connections and even more, so I think now we face OS limitations in handling connections as opposed to Percona Server’s limitations. From the posts above, both MySQL and MariaDB drop to the floor somewhere along the way.
You may ask a valid question if 16000 connections are really necessary. I would say that in regular workloads we do see this, but I propose to think about this as an electrical fuse, one that provides overcurrent protection. See my post SimCity outages, traffic control and Thread Pool for MySQL.
Even you do not have many connections on regular days, one mention from Yahoo’s homepage or from Hacker News will change that in a moment. Thread Pool will act as protection from overloading a database.
There is also another application for Thread Pool. I often see that application layer creates its own connection pool (think Java), and with ~20-30 app servers it can get to ~3000 connections, and this is already problematic… but not for Percona Server!
Hi,
I wonder if Percona server provides any improvements over Mysql server if I have about 20 Drupal sites with mostly read query? All databases will be hosted on the same mysql cluster.
Vadim, can you share any details of the sysbench benchmark run and the mysql server configuration?
Partha,
some details about the test run on the chart already, below more detailed info:
– box specs
– sysbench dataset – 32 tables 12M rows each (~100GB)
– server settings
max_connections=20000
innodb_buffer_pool_size=25G
innodb_flush_log_at_trx_commit=1
innodb_doublewrite=1
innodb_flush_neighbors=0
innodb_io_capacity=10000
innodb_io_capacity_max=25000
innodb_lru_scan_depth=8192
metadata_locks_hash_instances=256
performance_schema=0
query_cache_type=OFF
innodb_file_per_table = true
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 15
– sysbench-0.5/OLTP_RW
– test sequence
server start -> warmup -> test run
I do not actually have any knowledge of mysql server benchmark, and currently all the databases are hosted on a single mysql server with another server serving as a cold backup (manually failover) since our sites are not e-commerce, so minimum downtime may be acceptable. However, I do want to improve SLA of web service, so I am going to test mysql cluster and Percona XtraDB cluster.
The current mysql server run as basic configuration, with no modification in configuration. I just want to have some advices on whether to focus on duing the test phase.
Thank you,
You should also compare MySQL Enterprise. We’ve seen connection counts in 8k+ range before on 5.1. The thread pool is, after all, one of the major selling points of the Enterprise version. Also, a more realistic test other than sysbench would be useful (joins).
I echo Partha’s question.
Rolf,
>You should also compare MySQL Enterprise. We’ve seen connection counts in 8k+ range before on >5.1. The thread pool is, after all, one of the major selling points of the Enterprise version.
We of course would do it but we just can’t:
When you download software from the Oracle Technology Network (OTN) standard license does state that you’re not allowed to disclose benchmarks.
You may not:
…
– disclose results of any program benchmark tests without our prior consent.
>Also, a more realistic test other than sysbench would be useful (joins).
I don’t think that it’s realistic case to have 16k queries all doing joins. OLTP workload (where query/transaction is short and fast) is much more typical. But you of course can try thread pool with your workload and share results.
>I echo Partha’s question.
See above.
And of course I forgot to mention, IMPRESSIVE results!
Great article. Can’t wait to do some load testing on this!