使用sysbench压力测试MySQL（三）(r12笔记第6天)-创新互联

  昨天使用gdb调试MySQL中事务临界状态的时候，发现其实有些场景可能比我想得还要复杂一些，所以我在昨天的测试中结尾也是快快扫过，但是表明了意思即可。这一点上我在后面会把Oracle的临界事务状态也拿出来对比一下，还是蛮有意思的。

  今天简单写了几个脚本继续对一个测试环境的MySQL进行sysbench压力测试。  

   在上一次的基础上，我们保证了能够满足短时间内1000个连接的冲击，从各个方面做了调整，其中的一个重点逐渐落到了IO的吞吐率上，redo日志的大小设置一下子成了焦点和重中之重。

   当然这次的测试中，我的思路还是保持性能持续的增长，边调整，边优化。

   首先一点是我们能够突破1000连接的大关，先用下面的脚本来进行一个初步的测试，测试时长10秒钟，看看能否初始化1500个连接。

sysbench /home/sysbench/sysbench-1.0.3/src/lua/oltp_read_write.lua --mysql-user=root --mysql-port=3306 --mysql-socket=/home/mysql/s1/s1.sock --mysql-host=localhost --mysql-db=sysbenchtest --tables=10 --table-size=5000000 --threads=1500 --report-interval=5 --time=10 run没想到就跟约好似的，抛出了如下的错误。注意这里的错误看起来已经不是数据库层面了。

FATAL: unable to connect to MySQL server on socket '/home/mysql/s1/s1.sock', aborting...
FATAL: error 2001: Can't create UNIX socket (24)
PANIC: unprotected error in call to Lua API (cannot open /home/sysbench/sysbench-1.0.3/src/lua/oltp_read_write.lua: Too many open files)
PANIC: unprotected error in call to Lua API (cannot open /home/sysbench/sysbench-1.0.3/src/lua/oltp_read_write.lua: Too many open files)是不是支持的socket数的限制呢，我们已经调整了process的值。上面的命令我们可以换个姿势来写，那就是从socket连接改为常用的TCP/IP方式的连接.

sysbench  /home/sysbench/sysbench-1.0.3/src/lua/oltp_read_write.lua  --mysql-user=perf_test   --mysql-port=3306   --mysql-host=10.127.128.78 --mysql-password=perf_test  --mysql-db=sysbenchtest  --tables=10 --table-size=5000000  --threads=1500  --report-interval=5 --time=10 run可以看到错误就显示不同了，但是已经能够看出意思来了。

FATAL: unable to connect to MySQL server on host '10.127.128.78', port 3306, aborting...
FATAL: error 2004: Can't create TCP/IP socket (24)
PANIC: unprotected error in call to Lua API (cannot open /home/sysbench/sysbench-1.0.3/src/lua/oltp_read_write.lua: Too many open files)
PANIC: unprotected error in call to Lua API (cannot open /home/sysbench/sysbench-1.0.3/src/lua/oltp_read_write.lua: Too many open files)对此应很明确了，那就是内核资源设置nofile调整一下。

修改/etc/security/limits.d/90-nproc.conf文件，添加如下的部分即可，重新登录后即可生效。

*          soft    nproc      65535
*          soft    nofile      65535
*          hard    nofile      65535
重启MySQL后可以看到设置生效了。# cat /proc/`pidof mysqld`/limits | egrep "(processes|files)"
Max processes             65535                256589               processes
Max open files            65535                65535                files

    我们继续开启压测模式，马上错误就变了样。是我们熟悉的一个错误，最开始就碰到了。

FATAL: `thread_init' function failed: /usr/local/share/sysbench/oltp_common.lua:273: SQL API error
(last message repeated 1 times)
FATAL: mysql_stmt_prepare() failed
FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 100000)"
FATAL: `thread_init' function failed: /usr/local/share/sysbench/oltp_common.lua:273: SQL API error

这里得简单说说几个相关的额参数。

Com_stmt_close             prepare语句关闭的次数
Com_stmt_execute           prepare语句执行的次数
Com_stmt_prepare           prepare语句创建的次数这一类的场景可能不是通用的，因为在有些场景下，持续的连接，不是短时间内的大批量连接，这个参数max_prepared_stmt_count其实也不一定需要设置非常大。

比如我手头一个环境连接数有近500，但是max_prepared_stmt_count还是默认值16382，也稳定运行了很长时间了。# mysqladmin pro|wc -l
424
# mysqladmin var|grep max_prepared_stmt_count
| max_prepared_stmt_count   | 16382
我们的这个压测场景中，会短时间内创建大量的连接，而考虑到性能和安全，会使用prepare的方式，我们以10秒内的sysbench连接测试威力，看看prepare statement的数量变化。

使用show global status like '%stmt%'能够得到一个基本的数据变化。

mysql> show global status like '%stmt%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Com_stmt_execute           | 477403 |
| Com_stmt_close             | 91000  |
| Com_stmt_fetch             | 0      |
| Com_stmt_prepare           | 298844 |
| Com_stmt_reset             | 0      |
| Com_stmt_send_long_data    | 0      |
| Com_stmt_reprepare         | 0      |
| Prepared_stmt_count        | 0      |
+----------------------------+--------+过几秒查看，可以看到Prepared_stmt_count已经接近阈值。

mysql> show global status like '%stmt%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Binlog_stmt_cache_disk_use | 0      |
| Binlog_stmt_cache_use      | 0      |
| Com_stmt_execute           | 477403 |
| Com_stmt_close             | 91000  |
| Com_stmt_fetch             | 0      |
| Com_stmt_prepare           | 398045 |
| Com_stmt_reset             | 0      |
| Com_stmt_send_long_data    | 0      |
| Com_stmt_reprepare         | 0      |
| Prepared_stmt_count        | 98091  |
+----------------------------+--------+
按照目前的一个基本情况，我们需要 设置为91*1500=136500，留有一定的富余，所以我们可以设置为150000

然后继续测试，就会看到这个参数逐步的飞升。

mysql> show global status like '%stmt%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Binlog_stmt_cache_disk_use | 0      |
| Binlog_stmt_cache_use      | 0      |
| Com_stmt_execute           | 624184 |
| Com_stmt_close             | 91000  |
| Com_stmt_fetch             | 0      |
| Com_stmt_prepare           | 537982 |
| Com_stmt_reset             | 0      |
| Com_stmt_send_long_data    | 0      |
| Com_stmt_reprepare         | 0      |
| Prepared_stmt_count        | 136500 |
+----------------------------+--------+整个加压的过程中，可以通过top看到负载还是有一定的潜力,离性能榨干还有距离。

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
13417 mysql     20   0 34.8g  11g  12m S 1324.2 35.2  19:18.71 /usr/local/mysql/bin/mysqld --defaults-file=/home
23108 root      20   0 8924m 1.6g 2148 S 212.3  5.0   1:32.73 sysbench /home/sysbench/sysbench-1.0.3/src/lua/olt

  下面的图是我使用100M,200M,500,1G的redo得到的TPS图。

通过这个图也能过看出一个基本的负载情况，在1G的时候，TPS相对比较平稳，但是redo切换还是多多少少都会有一定的抖动。当然redo不是越大越好，

5.5 中的设置是小于 4G， 5.6 以后是小于512G

我们持续进行优化。