PerconaThreadPool Performance Benchmark Test

MySQL starts from 5.5.16 and uses ThreadPool as the plug-in MySQL commercial version to provide official function support. Later, MariaDB also implemented this function, and Percona also followed up. The threadpool comparison test for Percona5.6.16 has been conducted over the past few days, trying to find a more suitable configuration parameter. The following is a comparison of several test modes:

MySQL starts from 5.5.16 and uses the Thread Pool as the plug-in MySQL commercial version to provide official function support. Later, MariaDB also implemented this function, and Percona also followed up. In the past few days, we have conducted a thread pool comparison test on Percona 5.6.16, trying to find a more suitable configuration parameter. The following is a comparison of several test modes:

MySQL starts from 5.5.16 and uses the Thread Pool as the plug-in MySQL commercial version to provide official function support. Later, MariaDB also implemented this function, and Percona also followed up. In the past few days, we have conducted a thread pool comparison test on Percona 5.6.16, trying to find a more suitable configuration parameter.

The following is a comparison of several test modes:

Mode configuration parameters

Percona 5.6.16-nothp thread pool Mode not enabled

CASE0-thp (128)-oversub (16)-max (2048) thread_handling = pool-of-threads
Thread _ pool_size = 128
Thread_pool_oversubscribe = 16
Thread _ pool_max_threads = 2048

The CASE1-thp (default) thread_handling = pool-of-threads
Other default settings

CASE2-thp (default)-oversub (10) thread_handling = pool-of-threads
Thread_pool_oversubscribe = 10
Other default settings

CASE3-thp (default)-oversub (10)-max (10000) thread_handling = pool-of-threads
Thread_pool_oversubscribe = 10
Thread _ pool_max_threads = 100000
Other default settings

CASE4-thp (default)-oversub (16) thread_handling = pool-of-threads
Thread_pool_oversubscribe = 16
Other default settings

CASE5-thp (128)-oversub (16)-max (100000) thread_handling = pool-of-threads
Thread _ pool_size = 128
Thread_pool_oversubscribe = 16
Thread _ pool_max_threads = 100000

The tpcc-mysql testing tool is still used. Reference value:

Number of test Warehouse: 100

Warmup time: 60 s

Run time: 1200 s

Concurrent threads: 64 ~ 1920

Test Environment Information:

Test Machine dell pe R710

CPU E5620 @ 2.40 GHz (4 core, 8 threads, L3 Cache 12 MB) * 2

32 GB memory (4 GB * 8)

RAID card PERC H700 Integrated, 512 MB, BBU, 12.10.1-0001

System Red Hat Enterprise Linux Server release 6.4 (Santiago)

Kernel 2.6.32-358. el6.x86 _ 64 #1 SMP

Raid 0

File System xfs

Hard Disk SSD: Intel 520 series SSD, 800 GB * 1

Percona version: 5.6.16-64.2-rel64.2-log Percona Server with XtraDB (GPL), Release rel64.2, Revision 569, Percona-related key configurations include:

Innodb_buffer_pool_size = 26G

Innodb_flush_log_at_trx_commit = 1

For test scripts, see MySQL stress testing experience.

For test results, see:

Based on the test results, we can draw some conclusions:

1. Generally, you only need to enable the pool-of-threads mode;

2. You can adjust the thread_pool_oversubscribe option appropriately based on the actual pressure to increase the TPS. The value of this option is generally within 3 ~ 20;

3. The default value of thread-pool-size is the number of logical CPUs and the maximum value is 128. It is not recommended to adjust or explicitly set it. If the value of thread-pool-size is explicitly set, it may bring anti-effect;

4. The default value of thread_pool_max_threads is 100000. It is strongly not recommended to modify it.

To sum up, we generally recommend that you set the following two options for the Thread Pool:

Thread_handling = pool-of-threads

Thread_pool_oversubscribe = 10 # recommended value range: 3 ~ 20. If you are not clear about them, you do not need to set them.

Note: To terminate a QUERY after the Thread Pool is enabled, write kill query connection_id instead of KILLconnection_id. Otherwise, the entire connection is killed.

If you have any questions, please join our QQ group (272675472.