怎麼樣把mysqld壓測到崩潰重啟？什麼情況下mysqld崩潰重啟？
一、壓測環境工具準備：
centos7.5
sysbench1.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個併發需要多大的記憶體：
| 併發數 | innodb_buffer_pool_size | mysqld是否崩潰 |
| 200 | 5G | 否 |
| 300 | 5G | 崩潰 |
| 600 | 18G | 否 |
| 900 | 18G | 否 |
| 1000 | 18G | 崩潰 |
看來100併發執行緒，mysqld至少需要2G記憶體，另外考慮留給作業系統佔用2G記憶體。所以一個4核8G機器，執行緒數不要設定超過250個。這個既是保護資料庫不崩潰，保證響應時間在合理範圍之內(1秒)，又是，當連線達到上限的時候，程式有報錯，提示DBA需要增加機器的記憶體。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30393770/viewspace-2158042/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 執行緒崩潰為什麼不會導致 JVM 崩潰執行緒JVM
- Linux中程式崩潰及重啟的原因詳解!Linux
- WkWebView 令人崩潰的崩潰WebView
- 360瀏覽器總是崩潰是為什麼 360瀏覽器經常崩潰怎麼解決瀏覽器
- M1 Macbook崩潰了怎麼辦？Mac
- win10 pr崩潰怎麼解決_win10 pr崩潰解決辦法Win10
- [解決] Goland 開啟後直接崩潰GoLand
- WKWebView崩潰WebView
- Redis崩潰Redis
- windows10桌面崩潰怎麼修復_win10桌面無限崩潰解決方法WindowsWin10
- win10查詢崩潰日誌方法 win10怎麼檢視崩潰日誌Win10
- win10系統下資源管理器頻繁崩潰重啟如何解決Win10
- wegame自動關閉怎麼辦 wegame一啟動就崩潰閃退怎麼解決GAM
- 華為手機6.0系統系列崩潰，情況未明
- Crittercism：KitKat崩潰率0.7% iOS 7.1崩潰率1.6%iOS
- Win8.1資源管理器崩潰或重啟的解決辦法
- APP防崩潰APP
- 絕地求生崩潰怎麼解決win10_win10絕地求生崩潰的解決方法Win10
- win10經常網頁崩潰怎麼解決 win10網頁崩潰如何修復Win10網頁
- [ZT]智慧ABC一嚴重Bug可使任意程式崩潰
- WWDC 2018：理解崩潰以及崩潰日誌
- 360瀏覽器總是崩潰是為什麼 360經常崩潰解決修復方法介紹瀏覽器
- win10工作管理員崩潰怎麼解決_win10工作管理員崩潰黑屏修復方法Win10
- win10 qq影片崩潰怎麼修復_win10系統qq影片老是崩潰解決方法Win10
- Android Service入門到崩潰Android
- win10輸入法lol崩潰怎麼修復_win10輸入法lol崩潰如何解決Win10
- w10玩lol經常崩潰怎麼辦_w10玩英雄聯盟崩潰的解決方法
- win10資源管理器不停的崩潰怎麼辦 資源管理器不停崩潰的方法Win10
- iOS Crash不崩潰iOS
- linux mint 崩潰Linux
- ios 崩潰集錦iOS
- app 崩潰的原因APP
- win10系統崩潰藍屏什麼原因 win10系統出現崩潰藍屏如何修復Win10
- win10 qq視訊崩潰怎麼修復_win10系統qq視訊老是崩潰解決方法Win10
- win10系統驅動崩潰怎麼辦_win10系統驅動經常崩潰解決方法Win10
- 為什麼Android App質量不高並且卡頓崩潰AndroidAPP
- win10瀏覽器老是奔潰怎麼辦 win10瀏覽器崩潰怎麼解決Win10瀏覽器
- win10系統下AMD驅動程式崩潰了怎麼解決Win10