Tcmalloc analysis of MySQL performance optimization

Tcmalloc analysis of MySQL performance optimization

Author:echo Chen (Chenbin)

Email:[email protected]

blog:blog.csdn.net/chen19870707

Date:october 10th, 2014

Although the study found that Tcmalloc is not suitable for our existing game framework, but the accidental harvest found that tcmalloc can significantly improve MySQL performance and memory footprint, here are the results of configuration and testing:

First, the configuration

About the installation of Tcmalloc, in the "Google perftools installation Notes (Tcmalloc)" In the article has been given in detail, the following shows the steps to configure the Tcmalloc to MySQL:

1. Modify the MySQL startup script (depending on the MySQL installation location):

Vi/usr/local/mysql/bin/mysqld_safe in the next line of # executing Mysqld_safe, add:export ld_preload=/usr/local/lib/ Libtcmalloc.so Save exit and restart MySQL

2. See if the Tcmalloc works according to the Lsof Verification command：

# lsof |grep-i libtcmalloc.so If the following information is found, the Tcmalloc has been effective: mysqld 13961 mysql mem REG 253,0 1948990 196421/usr/loc al/lib/libtcmalloc.so.4.1.2

Second, the Performance test tool Sysbench

Sysbench is an open-source, modular, cross-platform, multithreaded performance testing tool that can be used for performance testing of CPUs, memory, disk I/O, threads, and databases. The currently supported databases are MySQL, Oracle, and PostgreSQL. The following actions are performed in support of the MySQL database as an example. SourceForge is hung,: http://download.csdn.net/detail/chen19870707/8060033, installation steps are as follows:

Option Description Reference: http://www.cnblogs.com/zhoujinyi/archive/2013/04/19/3029134.html

Third, performance analysis: Data preparation:

Sysbench--test=oltp--mysql-table-engine=innodb--oltp-table-size=10000--max-requests=10000--num-threads=16-- mysql-host=127.0.0.1--mysql-port=3306--mysql-user=root--mysql-password=root--mysql-db=test--mysql-socket=/tmp/ Mysql.sock Prepare

Performance test:

Sysbench--test=oltp--mysql-table-engine=innodb--oltp-table-size=10000--max-requests=10000--num-threads=16-- mysql-host=127.0.0.1--mysql-port=3306--mysql-user=root--mysql-password=root--mysql-db=test--mysql-socket=/tmp/ Mysql.sock Run >> report.txt

Data cleansing:

/usr/local/bin/sysbench--TEST=OLTP--mysql-table-engine=innodb--oltp-table-size=10000--max-requests=10000-- Num-threads=16--mysql-host=127.0.01--mysql-port=3306--mysql-user=root--mysql-password=root--mysql-db=test-- Mysql-socket=/tmp/mysql.sock cleanup

Parameter description:

--oltp-table-size=n the number of records in the test table. The default is 10000--max-requests=n limit forTotal number of requests [10000] #请求的最大数目. The default is 10000, and 0 means no limit. --max-requests=n limit forTotal number of requests [10000] #请求的最大数目. The default is 10000, and 0 means no limit. --num-threads=n number of threads to use [1] #创建测试线程的数目.       Default is 1.--mysql-host=[list,...] MySQL server host [localhost]--mysql-port=n MySQL server port [3306]--mysql-password=string mysql pas Sword []--mysql-db=string MySQL database name [sbtest]--mysql-socket=string mysql socket

Test results:

tcmalloc not used OLTP Test Statistics: Queries performed: read:140112 write:50019 other:20008 total:210139 transactions:10000 (756.11 per sec.) Deadlocks:8 (0.60 per sec.) Read/write requests:190131 (14376.02 per sec.) Other operations:20008 (1512.83 per sec.) Test Execution Summary: Total time:13.2256s Total number of events:10000 Total time taken by event execution:211.4342 Per-request Statistics: Min:2.96ms Avg:21.14ms Max:423.52ms Approx. Percentile:56.25ms

using Tcmalloc OLTP Test Statistics: Queries performed: read:140084 write:50017 other:20006 total:210107 transactions:10000 (862.83 per sec.) Deadlocks:6 (0.52 per sec.) Read/write requests:190101 (16402.39 per sec.) Other operations:20006 (1726.17 per sec.) Test Execution Summary: Total time:11.5898s Total number of events:10000 Total time taken by event execution:185.2397 Per-request Statistics: Min:2.81ms Avg:18.52ms Max:430.03ms Approx. Percentile:36.49ms

You can see that using TCMALLOC performance is significantly better than unused, here the main reason is that MySQL is a multithreaded small memory allocation, tcmalloc because each thread is wired buffer, so the allocation of such small objects without competition, efficient very good, You can see that the tcmalloc is good for MySQL optimization, recommended to use.

-

Echo chen:blog.csdn.net/chen19870707

-

Tcmalloc analysis of MySQL performance optimization