如何把mysqld壓測到崩潰重啟

小編給大家分享一下如何把mysqld壓測到崩潰重啟，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

一、壓測環境工具準備：

centos7.5   

sysbench2.0.9

mysql5.7.22

機器配置：宿主機是vmware esxi

DELL R730 

硬盤：普通10K SAS

內存：18G

CPU：8核

非常普通的cpu：

[root@yw-gz-hd-test-211 log]# lscpu 
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                8
On-line CPU(s) list:   0-7
Thread(s) per core:    1
Core(s) per socket:    1
Socket(s):             8
NUMA node(s):          1
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 79
Model name:            Intel(R) Xeon(R) CPU E5-2640 v4 @ 2.40GHz
Stepping:              1
CPU MHz:               2399.361
BogoMIPS:              4799.99
Hypervisor vendor:     VMware
Virtualization type:   full
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              25600K
NUMA node0 CPU(s):     0-7
Flags:                 fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts mmx fxsr sse sse2 ss syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts nopl xtopology tsc_reliable nonstop_tsc eagerfpu pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 invpcid rtm rdseed adx smap xsaveopt arat

 編譯安裝好mysql，設置   innodb_buffer_pool_size=5G innodb_buffer_pool_instance=5. 其他參數更改redo 為4組，io thread 為8 等等一些參數。                      

二、開始準備壓測數據庫：

插入10張表，每個表數據1000萬，整個msyql庫25G。

[root@yw-gz-hd-test-211 ~]# ls /data/mysql3308/sbtest/ -lh
total 25G
-rw-r----- 1 mysql mysql   61 Jul 17 19:24 db.opt
-rw-r----- 1 mysql mysql 8.5K Jul 17 19:32 sbtest10.frm
-rw-r----- 1 mysql mysql 2.5G Jul 18 14:58 sbtest10.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 17 19:32 sbtest1.frm
-rw-r----- 1 mysql mysql 2.5G Jul 18 14:58 sbtest1.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 17 19:32 sbtest2.frm
-rw-r----- 1 mysql mysql 2.5G Jul 18 14:58 sbtest2.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 17 19:32 sbtest3.frm
-rw-r----- 1 mysql mysql 2.5G Jul 18 14:58 sbtest3.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 17 19:32 sbtest4.frm
-rw-r----- 1 mysql mysql 2.5G Jul 18 14:58 sbtest4.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 17 19:32 sbtest5.frm
-rw-r----- 1 mysql mysql 2.5G Jul 18 14:58 sbtest5.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 17 19:32 sbtest6.frm
-rw-r----- 1 mysql mysql 2.5G Jul 18 14:58 sbtest6.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 17 19:32 sbtest7.frm
-rw-r----- 1 mysql mysql 2.5G Jul 18 14:58 sbtest7.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 17 19:32 sbtest8.frm
-rw-r----- 1 mysql mysql 2.5G Jul 18 14:58 sbtest8.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 17 19:32 sbtest9.frm
-rw-r----- 1 mysql mysql 2.5G Jul 18 14:58 sbtest9.ibd

[root@yw-gz-hd-test-211 ~]# ls /data/mysql3308/ -lh
total 1.4G
-rw-r----- 1 mysql mysql   56 Jul 17 17:56 auto.cnf
-rw-r----- 1 mysql mysql 1.5K Jul 18 14:17 ib_buffer_pool
-rw-r----- 1 mysql mysql 384M Jul 18 15:13 ibdata1
-rw-r----- 1 mysql mysql 256M Jul 18 15:13 ib_logfile0
-rw-r----- 1 mysql mysql 256M Jul 18 14:50 ib_logfile1
-rw-r----- 1 mysql mysql 256M Jul 18 15:13 ib_logfile2
-rw-r----- 1 mysql mysql 256M Jul 18 14:49 ib_logfile3
-rw-r----- 1 mysql mysql  12M Jul 18 15:21 ibtmp1
drwxr-x--- 2 mysql mysql 4.0K Jul 17 17:56 mysql
srwxrwxrwx 1 mysql mysql    0 Jul 18 14:42 mysql.sock
-rw------- 1 mysql mysql    6 Jul 18 14:42 mysql.sock.lock
drwxr-x--- 2 mysql mysql 8.0K Jul 17 17:56 performance_schema
drwxr-x--- 2 mysql mysql 4.0K Jul 17 19:36 sbtest
drwxr-x--- 2 mysql mysql 8.0K Jul 17 17:56 sys
-rw-r----- 1 mysql mysql    6 Jul 18 14:42 yw-gz-hd-test-211.pid

三、開始壓測：

首先300個線程，開始上。你會發現，立馬報錯：

FATAL: mysql_stmt_prepare() failed
FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 100000)"

百度一下，設置一下參數可以解決：max_prepared_stmt_count=150000

四、高潮出現：

錯誤排除，壓測到線程300個，總共時長是240秒，等到壓測到120秒的時候，mysql進程突然奔潰。錯誤日志中沒有記錄mysql奔潰的原因，只記錄到mysql崩潰后，被mysqld_safe 進程監控，然后立即拉起mysqld 進程。mysqld_safe 進程會一直監控mysqld進程，發現死掉，立即拉起mysqld進程。我懷疑是內存不夠。但是沒有證據證明：是內存不夠導致的mysqld進程奔潰。這個時候，我發現top命令還是很好用的。怎么用呢？讓我娓娓道來。前面不是講到了，壓測剛開始的120秒，沒有問題，你可以在這個壓測0~120秒的時候，打開top，你觀察mysqld線程使用內存情況。你觀察RES這一列，你會發現，mysqld進程RES值，從500M一直增長，增長到5G的時候，duang~，崩潰了。看出來了吧，mysql也有承受不住的時候。

        為了驗證自己是猜想，很簡單，不要更改任何參數，增加機器內存到18G。再一次壓測，驗證了我的想法，mysqld進程再300個并發線程中使用掉了6G內存。

來看下300個并發壓測情況

[root@yw-gz-hd-test-211 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-socket=/data/mysql3308/mysql.sock  --mysql-port=3308  --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=10000000 --tables=10 --threads=300  --time=240 --report-interval=30 run
sysbench 1.0.9 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 300
Report intermediate results every 30 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 30s ] thds: 300 tps: 189.42 qps: 3911.05 (r/w/o: 2753.06/769.16/388.83) lat (ms,95%): 3151.62 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 300 tps: 406.65 qps: 8146.63 (r/w/o: 5702.77/1630.57/813.30) lat (ms,95%): 1903.57 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 300 tps: 1027.51 qps: 20561.94 (r/w/o: 14391.74/4115.19/2055.01) lat (ms,95%): 909.80 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 300 tps: 915.33 qps: 18308.17 (r/w/o: 12818.23/3659.27/1830.67) lat (ms,95%): 802.05 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 300 tps: 848.33 qps: 16954.26 (r/w/o: 11865.99/3391.60/1696.67) lat (ms,95%): 787.74 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 300 tps: 1015.47 qps: 20327.15 (r/w/o: 14231.78/4064.44/2030.93) lat (ms,95%): 682.06 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 300 tps: 1293.73 qps: 25882.66 (r/w/o: 18120.80/5174.40/2587.47) lat (ms,95%): 493.24 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 300 tps: 1705.07 qps: 33979.32 (r/w/o: 23772.88/6803.07/3403.37) lat (ms,95%): 419.45 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            3110016
        write:                           888576
        other:                           444288
        total:                           4442880
    transactions:                        222144 (924.53 per sec.)
    queries:                             4442880 (18490.54 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
General statistics:
    total time:                          240.2250s
    total number of events:              222144
Latency (ms):
         min:                                  2.52
         avg:                                324.16
         max:                              50333.39
         95th percentile:                   1050.76
         sum:                            72010070.69
Threads fairness:
    events (avg/stddev):           740.4800/78.20
    execution time (avg/stddev):   240.0336/0.06

成績還不錯，QPS：18490，TPS：924。95%的響應時間是1050ms，就是1秒，可以接受

來看看600并發連接線程情況

[root@yw-gz-hd-test-211 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-socket=/data/mysql3308/mysql.sock  --mysql-port=3308  --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=10000000 --tables=10 --threads=600  --time=240 --report-interval=30 run
sysbench 1.0.9 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 600
Report intermediate results every 30 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 30s ] thds: 600 tps: 177.45 qps: 3866.55 (r/w/o: 2740.46/751.20/374.88) lat (ms,95%): 6594.16 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 600 tps: 508.61 qps: 10190.12 (r/w/o: 7130.15/2042.76/1017.21) lat (ms,95%): 2828.87 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 600 tps: 833.10 qps: 16581.88 (r/w/o: 11603.42/3312.26/1666.20) lat (ms,95%): 1506.29 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 600 tps: 712.40 qps: 14275.18 (r/w/o: 9994.28/2856.20/1424.70) lat (ms,95%): 1589.90 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 600 tps: 828.53 qps: 16595.37 (r/w/o: 11637.94/3300.27/1657.17) lat (ms,95%): 1280.93 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 600 tps: 1152.15 qps: 23046.54 (r/w/o: 16115.87/4626.50/2304.17) lat (ms,95%): 1032.01 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 600 tps: 1422.39 qps: 28470.31 (r/w/o: 19918.05/5707.53/2844.74) lat (ms,95%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 600 tps: 1874.42 qps: 37511.54 (r/w/o: 26257.48/7505.04/3749.01) lat (ms,95%): 601.29 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            3161774
        write:                           903364
        other:                           451682
        total:                           4516820
    transactions:                        225841 (939.46 per sec.)
    queries:                             4516820 (18789.23 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
General statistics:
    total time:                          240.3923s
    total number of events:              225841
Latency (ms):
         min:                                  2.75
         avg:                                637.78
         max:                              44200.42
         95th percentile:                   1678.14
         sum:                            144036928.60
Threads fairness:
    events (avg/stddev):           376.4017/48.51
    execution time (avg/stddev):   240.0615/0.03

這個時候我們看到大量的慢查詢語句，95%響應時間是1678ms，就是1.6秒，有些慢了。看看慢查詢都是些什么語句：

# Time: 2018-07-18T14:22:07.662597+08:00
# User@Host: root[root] @ localhost []  Id:   592
# Query_time: 7.400737  Lock_time: 0.000028 Rows_sent: 0  Rows_examined: 1
SET timestamp=1531894927;
UPDATE sbtest5 SET k=k+1 WHERE id=5024619;
# Time: 2018-07-18T14:22:07.662786+08:00
# User@Host: root[root] @ localhost []  Id:   202
# Query_time: 4.220504  Lock_time: 0.000027 Rows_sent: 0  Rows_examined: 1
SET timestamp=1531894927;
UPDATE sbtest5 SET k=k+1 WHERE id=5024572;
# Time: 2018-07-18T14:22:07.662829+08:00
# User@Host: root[root] @ localhost []  Id:   544
# Query_time: 3.662601  Lock_time: 0.000021 Rows_sent: 0  Rows_examined: 1
SET timestamp=1531894927;
DELETE FROM sbtest5 WHERE id=5024577;
# Time: 2018-07-18T14:22:07.662634+08:00
# User@Host: root[root] @ localhost []  Id:   402
# Query_time: 4.832428  Lock_time: 0.000023 Rows_sent: 0  Rows_examined: 1
SET timestamp=1531894927;
UPDATE sbtest5 SET c='53575816661-90198037463-61731021712-17992612508-02527517402-89815419518-53211578757-17129425245-97225103738-94879199437' WHERE id=5024586;

都是更新語句。這些語句非常耗費IO的

再來看看900個并發線程的情況。

[root@yw-gz-hd-test-211 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-socket=/data/mysql3308/mysql.sock  --mysql-port=3308  --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=10000000 --tables=10 --threads=900  --time=240 --report-interval=30 run
sysbench 1.0.9 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 900
Report intermediate results every 30 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 30s ] thds: 900 tps: 347.86 qps: 7432.37 (r/w/o: 5273.60/1433.11/725.65) lat (ms,95%): 5124.81 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 900 tps: 561.28 qps: 11176.43 (r/w/o: 7801.59/2252.28/1122.55) lat (ms,95%): 10158.80 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 900 tps: 643.33 qps: 12944.09 (r/w/o: 9077.29/2580.13/1286.67) lat (ms,95%): 2932.60 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 900 tps: 360.53 qps: 7200.07 (r/w/o: 5039.67/1439.33/721.07) lat (ms,95%): 6135.91 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 900 tps: 728.53 qps: 14524.71 (r/w/o: 10134.68/2933.03/1457.00) lat (ms,95%): 2585.31 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 900 tps: 1268.27 qps: 25410.63 (r/w/o: 17798.37/5075.80/2536.47) lat (ms,95%): 1561.52 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 900 tps: 1676.04 qps: 33561.08 (r/w/o: 23477.06/6731.82/3352.21) lat (ms,95%): 1869.60 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 900 tps: 2290.01 qps: 45719.85 (r/w/o: 31996.75/9148.79/4574.31) lat (ms,95%): 1352.03 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            3318098
        write:                           948028
        other:                           474014
        total:                           4740140
    transactions:                        237007 (985.74 per sec.)
    queries:                             4740140 (19714.74 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
General statistics:
    total time:                          240.4346s
    total number of events:              237007
Latency (ms):
         min:                                  2.76
         avg:                                911.43
         max:                              31437.18
         95th percentile:                   2778.39
         sum:                            216015485.39
Threads fairness:
    events (avg/stddev):           263.3411/37.88
    execution time (avg/stddev):   240.0172/0.02

看到了，95%響應時間是2.7秒，數據庫mysql響應時間越來越慢，越來越不堪重負。崩潰就在一瞬間。如我所見，innodb_buffer_pool_size=18G時候，1000個并發線程導致mysqld崩潰了。終于承受不住。

我們來大概測算一下，100個并發需要多大的內存：

看來100并發線程，mysqld至少需要2G內存，另外考慮留給操作系統占用2G內存。所以一個4核8G機器，線程數不要設置超過250個。這個既是保護數據庫不崩潰，保證響應時間在合理范圍之內(1秒)，又是，當連接達到上限的時候，程序有報錯，提示DBA需要增加機器的內存。

看完了這篇文章，相信你對“如何把mysqld壓測到崩潰重啟”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！