Percona5.6.15 thread pool stress test

Http://www.mysqlperformanceblog.com/2013/03/16/simcity-outages-traffic-control-and-thread-pool-for-mysql/

This article vividly describes the beauty of the thread pool, which can solve the performance degradation of high-concurrency databases.

Imagine if you do not set a traffic signal and do not have the command of the traffic police to let their vehicles go freely, then the consequence is that the traffic is paralyzed and everyone is busy. As a result, no one is able to get through and the traffic is blocked. Then some measures must be taken to allow some vehicles first, and then wait in line until the traffic is smooth. -- This is the role of the thread pool.

Each client connects to MySQL and runs an SQL statement to create a thread. Once the number of connections increases beyond a specific point, the performance will decrease. This feature enables the server to maintain optimal performance. Even if a large number of client connections are established, the number of server threads in the thread pool will be reduced, which will reduce the competition for CPU context switching and hot locks.

Next I used a virtual machine to perform a stress test.

Virtual Machine Configuration:

Root @ percona1 :~ /Percona-toolkit-2.2.4 # pt-summary # Percona Toolkit System Summary Report ###################### Date | 01:51:35 UTC (local TZ: CST + 0800) Hostname | percona1 Uptime |, 3 users, load average: 0.62, 9.56, 17.38 System | VMware, Inc.; VMware Virtual Platform; vNone (Other) Service Tag | VMware-56 4d 4e ce 5e 61 55 0d-cb 94 85 3f 9e 9a 3e 79 Platform | Linux Release | Debian GNU/Linux 7.3 (wheezy) Kernel | 3.2.0-4-amd64 Architecture | CPU = 64-bit, OS = 64-bit Threading | NPTL 2.13 SELinux | No SELinux detected Virtualized | VMWare # Processor ###################################### ############ Processors | physical = 2, cores = 4, virtual = 4, hyperthreading = no Speeds | 4x1895.729 Models | 4 xIntel (R) Core (TM) i3-3227U CPU @ 1.90 GHz Caches | 4x3072 KB # Memory ###################################### ############### Total | 996.0 M Free | 69.8 M Used | physical = 926.2 M, swap allocated = 1.3G, swap used = 439.8 M, virtual = 1.3G Buffers | 2.4 M Caches | 20.0 M Dirty | 384 kB UsedRSS | 810.2 M Swappiness | 60 DirtyPolicy | 20, 10 DirtyStatus | 0, 0

Stress test parameters:

sysbench  --test=oltp  --mysql-table-engine=innodb --oltp-table-size=1000000 --max-requests=10000 --num-threads=1000 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-db=test --mysql-socket=/tmp/mysql.sock run

1000 concurrent connections with a maximum of 10000 requests.

Result

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/140209/235532HR-0.jpg "title =" Thread Pool .jpg "alt =" wKiom1Lw7iLSeWNIAAENKPBSU0I517.jpg "/>

Obviously, enabling the thread pool TPS is better than not enabling it.

Note: MySQL5.5 and 5.6 are only supported in Enterprise Edition. Community Edition does not support this function.

Reference: http://www.percona.com/doc/percona-server/5.5/performance/threadpool.html

Appendix Percona official test:

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/140209/23553252I-1.jpg "title =" thread_pool.p2.io_bound.jpg "alt =" wKioL1LzpqnznG4yAAG4a-mK3oQ663.jpg "/>

Http://www.mysqlperformanceblog.com/2014/02/04/16000-active-connections-percona-server-continues-work-others-die/

Appendix MySQL official test:

MySQL Thread Pool

(For commercial version only)

To meet the increasing performance and scalability requirements of users, queries, and data communication, MySQL Enterprise Edition provides a MySQL thread pool. The thread pool provides a highly scalable thread processing model to reduce the overhead of managing client connections and statement execution threads. The performance and scalability of online applications with the increasing number of user connections and large traffic are continuously improved, as shown in:

60 times higher Scalability: read/write 650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/140209/2355325106-2.jpg "title =" 1.JPG" alt = "wKiom1Lx2EOCsPSEAAFBVnRIscw183.jpg"/>

Compared with MySQL Community edition server, MySQL Enterprise Edition with MySQL Thread Pool provides 60 times of scalability.

18 times higher Scalability: Read-Only

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/140209/2355321Z2-3.jpg "title =" 2.JPG" alt = "wKiom1Lx2HSj7KypAAFOgnMvtAk532.jpg"/>

Compared with MySQL Community edition server, MySQL Enterprise Edition with MySQL Thread Pool provides 18 times of scalability.

Configuration:

• MySQL 5.6.11

• Oracle Linux 6.3, Unbreakable Kernel 2.6.32

• 4 sockets, 24 cores, 48 Threads

• Intel (R) Xeon E7540 2 GHz CPUs

• 512 GB DDR RAM

Http://www.mysql.com/products/enterprise/scalability.html

This article is from the "hechun's technical column" blog, please be sure to keep this source http://hcymysql.blog.51cto.com/5223301/1356326