你的位置:首页 > 数据库

[数据库]TCMalloc 对MYSQL 性能 优化的分析


  虽然经过研究发现TCMalloc不适合我们现有的游戏框架,但意外收获发现TCMalloc可以大幅度提高MYSQL 性能及内存占用,这里给出配置及测试的结果:

1.配置

    关于TCMalloc的安装,在《Google perftools 安装手记(TCMalloc)》 一文中已经详细给出,下面给出将TCMalloc配置到MYSQL的步骤:

1.1 修改MySQL启动脚本(依据MySQL安装位置):

1 vi /usr/local/mysql/bin/mysqld_safe

  在# executing mysqld_safe的下一行,添加:
1 export LD_PRELOAD=/usr/local/lib/libtcmalloc.so

 保存退出,并重启MySQL

1.2 依据lsof验证命令查看TCMalloc是否起效: 

1 # lsof |grep -i libtcmalloc.so

 如果发现以下信息,说明tcmalloc已经起效:
1 mysqld 13961  mysql mem  REG 253,0 1948990   196421/usr/local/lib/libtcmalloc.so.4.1.2

2.性能测试工具sysbench


    sysbench是一个开源的、模块化的、跨平台的多线程性能测试工具,可以用来进行CPU、内存、磁盘I/O、线程、数据库的性能测试。目前支持的数据库有MySQL、Oracle和PostgreSQL。以下操作都将以支持MySQL数据库为例进行。sourceforge已挂,下载地址:http://download.csdn.net/detail/chen19870707/8060033,安装步骤如下:

1 tar zxf sysbench-0.4.10.tar.gz 2 cd sysbench-0.4.103 ./configure && make && make install 4 strip /usr/local/bin/sysbench 

 

  选项说明参考:http://www.cnblogs.com/zhoujinyi/archive/2013/04/19/3029134.html

3.性能分析:

  数据准备:
1 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

  性能测试:
1 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

  数据清理:
1 /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

  参数说明:
1 --oltp-table-size=N     测试表的记录数。默认是10000 --max-requests=N      limit for total number of requests [10000] #请求的最大数目。默认为10000,0代表不限制。2 --max-requests=N      limit for total number of requests [10000] #请求的最大数目。默认为10000,0代表不限制。3 --num-threads=N      number of threads to use [1] #创建测试线程的数目。默认为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

  测试结果:

                                   未使用TCMalloc

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.  95 percentile:     56.25ms

                                使用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.  95 percentile:              36.49ms


      可以看到使用TCMalloc性能明显优于未使用,这里主要原因是mysql是多线程小内存分配,TCMalloc由于每个线程均有线程缓冲区,所以对这样的小对象分配无竞争,效率非常好,可以看到TCMalloc对MYSQL优化效果不错 ,建议使用。