PerconaThreadPool performance benchmark _ MySQL

PerconaThreadPool performance benchmark test MySQL starts from 5.5.16. in the commercial MySQL version, Thread Pool is used as the plug-in 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
CASE1-thp (default)	Thread_handling = pool-of-threads other default settings
The 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:

Testing Machine	Dell pe R710
CPU	E5620 @ 2.40 GHz (4 core, 8 threads, L3 Cache 12 MB) * 2
Memory	32G (4G * 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 level	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: If you want to terminate a QUERY after the Thread Pool is enabled, write kill query connection_id instead of KILL connection_id. Otherwise, the entire connection is killed.

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