使用sysbench壓力測(cè)試MySQL（二）

   昨天有了第一篇的測(cè)試之后，僅僅是一個(gè)開(kāi)始。

   我接下來(lái)做sysbench壓測(cè)的主要思路是根據(jù)現(xiàn)有的配置作出調(diào)整，能夠持續(xù)性的優(yōu)化和壓力測(cè)試達(dá)到目的，而不是簡(jiǎn)單的去對(duì)比連接數(shù)在不同數(shù)量級(jí)會(huì)有多大的差別，所以你會(huì)在里面看到一些問(wèn)題的排查，一些問(wèn)題的解決，可能有些又不是壓測(cè)相關(guān)的。

  我設(shè)置了max_connections為3000，但是壓測(cè)的時(shí)候到了300個(gè)線程就跑不上去了。這個(gè)問(wèn)題很有典型性。

sysbench拋出的錯(cuò)誤如下：

FATAL: mysql_stmt_prepare() failed
FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)"
FATAL: `thread_init' function failed: /usr/local/share/sysbench/oltp_common.lua:282: SQL API error

MySQL的錯(cuò)誤日志信息如下：

2017-03-14T15:01:57.839154Z 348 [Note] Aborted connection 348 to db: 'sysbenchtest' user: 'root' host: 'localhost' (Got an error reading communication packets)
2017-03-14T15:01:57.839185Z 346 [Note] Aborted connection 346 to db: 'sysbenchtest' user: 'root' host: 'localhost' (Got an error reading communication packets)
看起來(lái)兩者關(guān)聯(lián)不大，所以有些信息就會(huì)有一些誤導(dǎo)了。
根據(jù)錯(cuò)誤的信息，當(dāng)前的參數(shù)max_prepared_stmt_count設(shè)置值為16382，是安裝后的默認(rèn)值。

mysql> show variables like 'max_prepared_stmt_count';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| max_prepared_stmt_count | 16382 |
+-------------------------+-------+

而packet的參數(shù)設(shè)置為4M的樣子，也是默認(rèn)值

mysql> show variables like '%pack%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 4194304    |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+

到底是不是參數(shù)max_allowed_packet引起的呢，我們可以簡(jiǎn)單模擬一下。

set global max_allowed_packet=33554432;

然后繼續(xù)運(yùn)行sysbench腳本：

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=200 --report-interval=5 --time=10 run

結(jié)果還是拋出了同樣的錯(cuò)誤，這也就間接證明了這個(gè)問(wèn)題和這個(gè)參數(shù)無(wú)關(guān)，所以我恢復(fù)了原來(lái)的設(shè)置。

然后我們繼續(xù)調(diào)整這個(gè)參數(shù)max_prepared_stmt_count，把值從16382調(diào)整到30000

set global max_prepared_stmt_count=30000;
然后再次運(yùn)行200個(gè)線程，就可以看到?jīng)]問(wèn)題了，運(yùn)行過(guò)程中我們可以使用show global status的方式來(lái)查看這個(gè)值的變化,這個(gè)參數(shù)的主要含義是應(yīng)對(duì)瞬間新建的大量prepared statements，通過(guò)max_prepared_stmt_count 變量來(lái)控制，這個(gè)值是怎么算出來(lái)的，還需要細(xì)細(xì)挖挖。

mysql> show global status like 'Prepared_stmt_count';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| Prepared_stmt_count        | 18200    |
+----------------------------+----------+

執(zhí)行完200個(gè)線程后，繼續(xù)300個(gè)，打算以此類推，一直到1000個(gè)線程。

執(zhí)行300個(gè)線程的時(shí)候，抓取了一下這個(gè)參數(shù)值，發(fā)現(xiàn)已經(jīng)快溢出了。

mysql> show global status like '%stmt%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| Prepared_stmt_count        | 27300    |
+----------------------------+----------+
10 rows in set (0.00 sec)

所以自己簡(jiǎn)單做了個(gè)計(jì)算，

200個(gè)線程參數(shù)值為  18200
300個(gè)線程參數(shù)值為  27300

通過(guò)簡(jiǎn)單的計(jì)算可以看出100個(gè)線程對(duì)應(yīng)參數(shù)值9100，按照這個(gè)參數(shù)設(shè)置，我要運(yùn)行500個(gè)線程，30000這個(gè)參數(shù)值是肯定不夠的。很快就驗(yàn)證了我的這個(gè)想法，拋出錯(cuò)誤了。所以我調(diào)整了參數(shù)值為100000，在900個(gè)線程都沒(méi)有任何問(wèn)題。

這是分別對(duì)應(yīng)50個(gè)，300個(gè)，500個(gè)線程時(shí)候的TPS測(cè)試結(jié)果，QPS基本是TPS的20倍。

  然后我繼續(xù)測(cè)試1000個(gè)線程的時(shí)候，發(fā)現(xiàn)跑不上去了。

壓到1000的時(shí)候，拋出了下面的錯(cuò)誤。
FATAL: unable to connect to MySQL server on socket '/home/mysql/s1/s1.sock', aborting...
FATAL: error 1135: Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
這個(gè)時(shí)候查看資源的使用情況如下：

# ulimit -a

。。。
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
max user processes              (-u) 1024
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

值得一提的是在RedHat 6中，在/etc/security/limits.conf設(shè)置用戶為*的時(shí)候，完全不會(huì)生效，需要制定具體的用戶，

比如下面的配置就不會(huì)生效，需要指定為root

*                soft    nproc           65535
*                hard    nproc           65535

再次可以引申出兩點(diǎn)，一個(gè)是修改了資源設(shè)置之后，已有的MySQL服務(wù)就需要重啟，因?yàn)橘Y源設(shè)置還是舊的值，如何查看呢?？梢允褂胮idof來(lái)查看/proc下的設(shè)置。

#  cat /proc/`pidof mysqld`/limits | egrep "(processes|files)"
Max processes             1024                 256589               processes
Max open files            15000                15000                files 
第二點(diǎn)就是在RedHat 6中，我們其實(shí)需要設(shè)置另外一個(gè)參數(shù)文件，/etc/security/limits.d/90-nproc.conf

在這個(gè)文件中做如下的配置，對(duì)所有用戶生效。

*          soft    nproc      65535
修改后重啟MySQL服務(wù)即可生效，再次開(kāi)啟測(cè)試就沒(méi)有問(wèn)題了，說(shuō)明這個(gè)地方的錯(cuò)誤和參數(shù)nproc還是有密切的關(guān)系，但是open files目前還是1024暫時(shí)還沒(méi)有問(wèn)題。
這是當(dāng)時(shí)測(cè)試的一個(gè)初步結(jié)果。可以看到線程50,500,1000的時(shí)候的TPS情況，線程1000的指標(biāo)似乎沒(méi)有什么提升。

    線程達(dá)到了1000，我們的基準(zhǔn)測(cè)試也有了一個(gè)階段性的成果，那就是最起碼支持1000的連接是沒(méi)有問(wèn)題了，但是測(cè)試結(jié)果還是讓人不大滿意，至少?gòu)臄?shù)字上來(lái)看還是有很大的瓶頸。

    問(wèn)題出在哪里了呢，首先機(jī)器的配置較差，這是個(gè)不爭(zhēng)的事實(shí)，所以你看到的結(jié)果和很多網(wǎng)上高大上的結(jié)果有較大的出入。

    另外一點(diǎn)是默認(rèn)參數(shù)層面的優(yōu)化，我們使用的是Lua模板讀寫兼有。壓力測(cè)試的過(guò)程中生成了大量的binlog,而對(duì)于InnoDB而言，我們需要明確在IO上的幾點(diǎn)可能，一個(gè)是刷數(shù)據(jù)的效率，一個(gè)是redo的大小，還有一些已有的優(yōu)化方式改進(jìn)。我們來(lái)簡(jiǎn)單說(shuō)一下。

MySQL里有類似Oracle中LGWR的一個(gè)線程，在5.7中默認(rèn)是16M,早先版本是8M.因?yàn)樗⑿骂l率很高，所以這個(gè)參數(shù)一般不需要調(diào)整。

mysql> show variables like 'innodb_log_buffer_size';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| innodb_log_buffer_size      | 16777216 |

對(duì)于redo的大小，目前是50M，還有個(gè)group的參數(shù)innodb_log_files_in_group，默認(rèn)是2，即兩組，可以理解為Oracle中的兩組redo日志。

mysql> show variables like 'innodb_log%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| innodb_log_buffer_size      | 16777216 |
| innodb_log_checksums        | ON       |
| innodb_log_compressed_pages | ON       |
| innodb_log_file_size        | 50331648 |
| innodb_log_files_in_group   | 2        |
| innodb_log_group_home_dir   | ./       |
| innodb_log_write_ahead_size | 8192     |
+-----------------------------+----------+

在這個(gè)壓力測(cè)試中，頻繁的寫入binlog,勢(shì)必會(huì)對(duì)redo的刷新頻率極高。

我們抓取一個(gè)測(cè)試中的InnoDB的狀態(tài)：

mysql -e "show engine innodb status\G"|grep -A12 "Log sequence"
Log sequence number 39640783274
Log flushed up to   39640782426
Pages flushed up to 39564300915
Last checkpoint at  39562272220
0 pending log flushes, 0 pending chkp writes
93807 log i/o's done, 198.50 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 26386366464
Dictionary memory allocated 555380
Buffer pool size   1572768
Free buffers       1048599

簡(jiǎn)單來(lái)做個(gè)計(jì)算，可以看到Log sequence number的值減去Last checkpoint at的值，大概是70M左右

mysql> select 39640783274-39562272220;
+-------------------------+
| 39640783274-39562272220 |
+-------------------------+
|                78511054 |
+-------------------------+
redo文件設(shè)置為多大，其實(shí)沒(méi)有一個(gè)絕對(duì)的概念，在Percona的建議中，在壓力測(cè)試中可以設(shè)置為1G或者2G，最大設(shè)置為4G，因?yàn)楸旧頃?huì)直接影響到恢復(fù)的效率。

    調(diào)整redo的大小還是尤其需要注意，在這一點(diǎn)上MySQL沒(méi)準(zhǔn)以后會(huì)有所改進(jìn)，Oracle中的redo修改還是值得借鑒的。像5.7中的undo已經(jīng)可以截?cái)?，逐步的剝離出來(lái)，都是一點(diǎn)點(diǎn)的改進(jìn)。

  怎么修改redo的大小呢，要正常停庫(kù)，然后刪除默認(rèn)的兩個(gè)redo文件，保險(xiǎn)起見(jiàn)，你可以先備份出來(lái)也行，然后修改參數(shù)文件的redo文件參數(shù)，啟動(dòng)MySQL，當(dāng)然開(kāi)始的時(shí)候會(huì)識(shí)別不到redo文件會(huì)自動(dòng)創(chuàng)建兩個(gè)新的。

下面的結(jié)果很有代表性，是暫時(shí)修改redo為100M時(shí)的TPS情況。左下角藍(lán)色的部分是原來(lái)10分鐘內(nèi)的TPS情況，紅色的部分是2個(gè)多小時(shí)里的TPS情況，可以看到基本是穩(wěn)定的，而且是原高于原來(lái)同樣線程數(shù)的TPS.

后續(xù)改如何優(yōu)化呢，還有更多內(nèi)容值得分享出來(lái)。

http://www.ceus-now.com/mysql-innodb-page-cleaner-settings-might-not-be-optimal/

https://www.percona.com/blog/2013/02/04/cant_create_thread_errno_11/

https://www.percona.com/blog/2006/07/03/choosing-proper-innodb_log_file_size/

https://www.percona.com/blog/2011/07/09/how-to-change-innodb_log_file_size-safely/