Tcmalloc analysis of MySQL performance optimization

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:

1. 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.1 Modify the MySQL startup script (depending on the MySQL installation location):

1 VI /usr/local/mysql/bin/mysqld_safe

On the next line of # executing Mysqld_safe, add:

1 Export ld_preload=/usr/local/lib/libtcmalloc.so

Save exit and restart MySQL

1.2 See if the Tcmalloc works according to the Lsof Verification command:

1 # Lsof | grep -I. libtcmalloc.so

If you find the following information, Tcmalloc is already working:

1 mysqld  13961   mysql  mem    REG  253,0  1948990      196421/usr/local/lib/libtcmalloc.so.  4.1. 2

2. 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:

1 tar zxf sysbench-0.4. . Tar  2 cd sysbench-0.4. Ten 3  Make  Make Install 4

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

3. Performance Analysis:Data preparation:

1 sysbench--test=oltp--mysql-table-engine=innodb--oltp-table-size=10000 --max-requests=  10000 --num-threads= --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:

1 sysbench--test=oltp--mysql-table-engine=innodb--oltp-table-size=10000 --max-requests=  10000 --num-threads= --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:

1 /usr/local/bin/sysbench--test=oltp--mysql-table-engine=innodb--oltp-table-size=10000 -- max-requests=10000 --num-threads= --mysql-host=127.0.  on  --mysql-port=3306 --mysql-user=root--mysql-password=root--mysql-db=test--mysql-socket=/tmp/mysql.sock Cleanup

parameter Description:

1--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. 2--max-requests=n limit forTotal number of requests [10000] #请求的最大数目. The default is 10000, and 0 means no limit. 3--num-threads=n number of threads to use [1] #创建测试线程的数目. The default is 1.4--mysql-host=[LIST,...] MySQL server host [localhost]5--mysql-port=n MySQL server port [3306]6--mysql-password=STRING MySQL Password []7--mysql-db=STRING MySQL database name [Sbtest]8--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.

Tcmalloc analysis of MySQL performance optimization