thread_pool_size的调整

                                                  thread_pool_size的调整

    根据mysql官方文档的定义,thread_pool_size定义为同时运行sql语句的mysql的线程数，是mysql thread最重要的
性能参数.说是商业版才可能调整，但是我使用percona 的版本也可以动态调整.

版本:percona 5.5.33-rel31.1-log Percona Server with XtraDB (GPL).
cpu: 双核,超线程
os:rhel 5.5

以下是测试结果:
             
mysql> set global thread_pool_size=1;
Query OK, 0 rows affected (0.00 sec)

[root@localhost tmp]# sysbench --test=oltp  --oltp-table-size=1000 --num-threads=32 --oltp-table-name=t1
 --mysql-user=root --mysql-password=root  --mysql-db=frank --mysql-port=3306  run

       total:                           492424
    transactions:                        10000  (87.34 per sec.)
    deadlocks:                           16451  (143.69 per sec.)
    read/write requests:                 455973 (3982.54 per sec.)
    other operations:                    36451  (318.37 per sec.)

[root@localhost tmp]# sysbench --test=oltp  --oltp-table-size=1000 --num-threads=64 --oltp-table-name=t1
 --mysql-user=root --mysql-password=root  --mysql-db=frank --mysql-port=3306  run

   transactions:                        10000  (57.63 per sec.)
    deadlocks:                           42338  (244.00 per sec.)
    read/write requests:                 871349 (5021.81 per sec.)
    other operations:                    62338  (359.27 per sec.)

mysql> set global thread_pool_size=32;
Query OK, 0 rows affected (0.00 sec)

[root@localhost tmp]# sysbench --test=oltp  --oltp-table-size=1000 --num-threads=32 --oltp-table-name=t1
--mysql-user=root --mysql-password=root  --mysql-db=frank --mysql-port=3306  run

    transactions:                        10000  (129.22 per sec.)
    deadlocks:                           15736  (203.34 per sec.)
    read/write requests:                 444481 (5743.47 per sec.)
    other operations:                    35736  (461.77 per sec.)

[root@localhost tmp]# sysbench --test=oltp  --oltp-table-size=1000 --num-threads=64 --oltp-table-name=t1
--mysql-user=root --mysql-password=root  --mysql-db=frank --mysql-port=3306  run

 transactions:                        10000  (358.17 per sec.)
    deadlocks:                           44513  (1594.33 per sec.)
    read/write requests:                 906167 (32456.43 per sec.)
    other operations:                    64513  (2310.68 per sec.)

[root@localhost tmp]# sysbench --test=oltp  --oltp-table-size=1000 --num-threads=128
 --oltp-table-name=t1 --mysql-user=root --mysql-password=root  --mysql-db=frank --mysql-port=3306  run

        total:                           1915131
    transactions:                        10000  (174.78 per sec.)
    deadlocks:                           100061 (1748.87 per sec.)
    read/write requests:                 1795070 (31374.22 per sec.)
    other operations:                    120061 (2098.43 per sec.)

从上面的可以看出在thread_pool_size 为同时连接数(thread_running)的2倍的时候，性能最好.这个时候TPS的吞吐
量最大,达到358 TPS。通过极端，我设置为1,对应的TPS只有57.