小编给大家分享一下如何把mysqld压测到崩溃重启，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

一、压测环境工具准备：

centos7.5

sysbench2.0.9

mysql5.7.22

机器配置：宿主机是vmware esxi

DELL R730

硬盘：普通10K SAS

内存：18G

CPU：8核

非常普通的cpu：

[root@yw-gz-hd-test-211log]#lscpuArchitecture:x86_64CPUop-mode(s):32-bit,64-bitByteOrder:LittleEndianCPU(s):8On-lineCPU(s)list:0-7Thread(s)percore:1Core(s)persocket:1Socket(s):8NUMAnode(s):1VendorID:GenuineIntelCPUfamily:6Model:79Modelname:Intel(R)Xeon(R)CPUE5-2640v4@2.40GHzStepping:1CPUMHz:2399.361BogoMIPS:4799.99Hypervisorvendor:VMwareVirtualizationtype:fullL1dcache:32KL1icache:32KL2cache:256KL3cache:25600KNUMAnode0CPU(s):0-7Flags:fpuvmedepsetscmsrpaemcecx8apicsepmtrrpgemcacmovpatpse36clflushdtsmmxfxsrssesse2sssyscallnxpdpe1gbrdtscplmconstant_tscarch_perfmonpebsbtsnoplxtopologytsc_reliablenonstop_tsceagerfpupnipclmulqdqssse3fmacx16pcidsse4_1sse4_2x2apicmovbepopcnttsc_deadline_timeraesxsaveavxf16crdrandhypervisorlahf_lmabm3dnowprefetchfsgsbasetsc_adjustbmi1hleavx2smepbmi2invpcidrtmrdseedadxsmapxsaveoptarat

编译安装好mysql，设置 innodb_buffer_pool_size=5Ginnodb_buffer_pool_instance=5. 其他参数更改redo 为4组，io thread 为8 等等一些参数。

二、开始准备压测数据库：

插入10张表，每个表数据1000万，整个msyql库25G。

[root@yw-gz-hd-test-211~]#ls/data/mysql3308/sbtest/-lhtotal25G-rw-r-----1mysqlmysql61Jul1719:24db.opt-rw-r-----1mysqlmysql8.5KJul1719:32sbtest10.frm-rw-r-----1mysqlmysql2.5GJul1814:58sbtest10.ibd-rw-r-----1mysqlmysql8.5KJul1719:32sbtest1.frm-rw-r-----1mysqlmysql2.5GJul1814:58sbtest1.ibd-rw-r-----1mysqlmysql8.5KJul1719:32sbtest2.frm-rw-r-----1mysqlmysql2.5GJul1814:58sbtest2.ibd-rw-r-----1mysqlmysql8.5KJul1719:32sbtest3.frm-rw-r-----1mysqlmysql2.5GJul1814:58sbtest3.ibd-rw-r-----1mysqlmysql8.5KJul1719:32sbtest4.frm-rw-r-----1mysqlmysql2.5GJul1814:58sbtest4.ibd-rw-r-----1mysqlmysql8.5KJul1719:32sbtest5.frm-rw-r-----1mysqlmysql2.5GJul1814:58sbtest5.ibd-rw-r-----1mysqlmysql8.5KJul1719:32sbtest6.frm-rw-r-----1mysqlmysql2.5GJul1814:58sbtest6.ibd-rw-r-----1mysqlmysql8.5KJul1719:32sbtest7.frm-rw-r-----1mysqlmysql2.5GJul1814:58sbtest7.ibd-rw-r-----1mysqlmysql8.5KJul1719:32sbtest8.frm-rw-r-----1mysqlmysql2.5GJul1814:58sbtest8.ibd-rw-r-----1mysqlmysql8.5KJul1719:32sbtest9.frm-rw-r-----1mysqlmysql2.5GJul1814:58sbtest9.ibd

[root@yw-gz-hd-test-211~]#ls/data/mysql3308/-lhtotal1.4G-rw-r-----1mysqlmysql56Jul1717:56auto.cnf-rw-r-----1mysqlmysql1.5KJul1814:17ib_buffer_pool-rw-r-----1mysqlmysql384MJul1815:13ibdata1-rw-r-----1mysqlmysql256MJul1815:13ib_logfile0-rw-r-----1mysqlmysql256MJul1814:50ib_logfile1-rw-r-----1mysqlmysql256MJul1815:13ib_logfile2-rw-r-----1mysqlmysql256MJul1814:49ib_logfile3-rw-r-----1mysqlmysql12MJul1815:21ibtmp1drwxr-x---2mysqlmysql4.0KJul1717:56mysqlsrwxrwxrwx1mysqlmysql0Jul1814:42mysql.sock-rw-------1mysqlmysql6Jul1814:42mysql.sock.lockdrwxr-x---2mysqlmysql8.0KJul1717:56performance_schemadrwxr-x---2mysqlmysql4.0KJul1719:36sbtestdrwxr-x---2mysqlmysql8.0KJul1717:56sys-rw-r-----1mysqlmysql6Jul1814:42yw-gz-hd-test-211.pid

三、开始压测：

首先300个线程，开始上。你会发现，立马报错：

FATAL:mysql_stmt_prepare()failedFATAL:MySQLerror:1461"Can'tcreatemorethanmax_prepared_stmt_countstatements(currentvalue: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=30runsysbench1.0.9(usingsystemLuaJIT2.0.4)Runningthetestwithfollowingoptions:Numberofthreads:300Reportintermediateresultsevery30second(s)InitializingrandomnumbergeneratorfromcurrenttimeInitializingworkerthreads...Threadsstarted![30s]thds:300tps:189.42qps:3911.05(r/w/o:2753.06/769.16/388.83)lat(ms,95%):3151.62err/s:0.00reconn/s:0.00[60s]thds:300tps:406.65qps:8146.63(r/w/o:5702.77/1630.57/813.30)lat(ms,95%):1903.57err/s:0.00reconn/s:0.00[90s]thds:300tps:1027.51qps:20561.94(r/w/o:14391.74/4115.19/2055.01)lat(ms,95%):909.80err/s:0.00reconn/s:0.00[120s]thds:300tps:915.33qps:18308.17(r/w/o:12818.23/3659.27/1830.67)lat(ms,95%):802.05err/s:0.00reconn/s:0.00[150s]thds:300tps:848.33qps:16954.26(r/w/o:11865.99/3391.60/1696.67)lat(ms,95%):787.74err/s:0.00reconn/s:0.00[180s]thds:300tps:1015.47qps:20327.15(r/w/o:14231.78/4064.44/2030.93)lat(ms,95%):682.06err/s:0.00reconn/s:0.00[210s]thds:300tps:1293.73qps:25882.66(r/w/o:18120.80/5174.40/2587.47)lat(ms,95%):493.24err/s:0.00reconn/s:0.00[240s]thds:300tps:1705.07qps:33979.32(r/w/o:23772.88/6803.07/3403.37)lat(ms,95%):419.45err/s:0.00reconn/s:0.00SQLstatistics:queriesperformed:read:3110016write:888576other:444288total:4442880transactions:222144(924.53persec.)queries:4442880(18490.54persec.)ignorederrors:0(0.00persec.)reconnects:0(0.00persec.)Generalstatistics:totaltime:240.2250stotalnumberofevents:222144Latency(ms):min:2.52avg:324.16max:50333.3995thpercentile:1050.76sum:72010070.69Threadsfairness:events(avg/stddev):740.4800/78.20executiontime(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=30runsysbench1.0.9(usingsystemLuaJIT2.0.4)Runningthetestwithfollowingoptions:Numberofthreads:600Reportintermediateresultsevery30second(s)InitializingrandomnumbergeneratorfromcurrenttimeInitializingworkerthreads...Threadsstarted![30s]thds:600tps:177.45qps:3866.55(r/w/o:2740.46/751.20/374.88)lat(ms,95%):6594.16err/s:0.00reconn/s:0.00[60s]thds:600tps:508.61qps:10190.12(r/w/o:7130.15/2042.76/1017.21)lat(ms,95%):2828.87err/s:0.00reconn/s:0.00[90s]thds:600tps:833.10qps:16581.88(r/w/o:11603.42/3312.26/1666.20)lat(ms,95%):1506.29err/s:0.00reconn/s:0.00[120s]thds:600tps:712.40qps:14275.18(r/w/o:9994.28/2856.20/1424.70)lat(ms,95%):1589.90err/s:0.00reconn/s:0.00[150s]thds:600tps:828.53qps:16595.37(r/w/o:11637.94/3300.27/1657.17)lat(ms,95%):1280.93err/s:0.00reconn/s:0.00[180s]thds:600tps:1152.15qps:23046.54(r/w/o:16115.87/4626.50/2304.17)lat(ms,95%):1032.01err/s:0.00reconn/s:0.00[210s]thds:600tps:1422.39qps:28470.31(r/w/o:19918.05/5707.53/2844.74)lat(ms,95%):707.07err/s:0.00reconn/s:0.00[240s]thds:600tps:1874.42qps:37511.54(r/w/o:26257.48/7505.04/3749.01)lat(ms,95%):601.29err/s:0.00reconn/s:0.00SQLstatistics:queriesperformed:read:3161774write:903364other:451682total:4516820transactions:225841(939.46persec.)queries:4516820(18789.23persec.)ignorederrors:0(0.00persec.)reconnects:0(0.00persec.)Generalstatistics:totaltime:240.3923stotalnumberofevents:225841Latency(ms):min:2.75avg:637.78max:44200.4295thpercentile:1678.14sum:144036928.60Threadsfairness:events(avg/stddev):376.4017/48.51executiontime(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.400737Lock_time:0.000028Rows_sent:0Rows_examined:1SETtimestamp=1531894927;UPDATEsbtest5SETk=k+1WHEREid=5024619;#Time:2018-07-18T14:22:07.662786+08:00#User@Host:root[root]@localhost[]Id:202#Query_time:4.220504Lock_time:0.000027Rows_sent:0Rows_examined:1SETtimestamp=1531894927;UPDATEsbtest5SETk=k+1WHEREid=5024572;#Time:2018-07-18T14:22:07.662829+08:00#User@Host:root[root]@localhost[]Id:544#Query_time:3.662601Lock_time:0.000021Rows_sent:0Rows_examined:1SETtimestamp=1531894927;DELETEFROMsbtest5WHEREid=5024577;#Time:2018-07-18T14:22:07.662634+08:00#User@Host:root[root]@localhost[]Id:402#Query_time:4.832428Lock_time:0.000023Rows_sent:0Rows_examined:1SETtimestamp=1531894927;UPDATEsbtest5SETc='53575816661-90198037463-61731021712-17992612508-02527517402-89815419518-53211578757-17129425245-97225103738-94879199437'WHEREid=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=30runsysbench1.0.9(usingsystemLuaJIT2.0.4)Runningthetestwithfollowingoptions:Numberofthreads:900Reportintermediateresultsevery30second(s)InitializingrandomnumbergeneratorfromcurrenttimeInitializingworkerthreads...Threadsstarted![30s]thds:900tps:347.86qps:7432.37(r/w/o:5273.60/1433.11/725.65)lat(ms,95%):5124.81err/s:0.00reconn/s:0.00[60s]thds:900tps:561.28qps:11176.43(r/w/o:7801.59/2252.28/1122.55)lat(ms,95%):10158.80err/s:0.00reconn/s:0.00[90s]thds:900tps:643.33qps:12944.09(r/w/o:9077.29/2580.13/1286.67)lat(ms,95%):2932.60err/s:0.00reconn/s:0.00[120s]thds:900tps:360.53qps:7200.07(r/w/o:5039.67/1439.33/721.07)lat(ms,95%):6135.91err/s:0.00reconn/s:0.00[150s]thds:900tps:728.53qps:14524.71(r/w/o:10134.68/2933.03/1457.00)lat(ms,95%):2585.31err/s:0.00reconn/s:0.00[180s]thds:900tps:1268.27qps:25410.63(r/w/o:17798.37/5075.80/2536.47)lat(ms,95%):1561.52err/s:0.00reconn/s:0.00[210s]thds:900tps:1676.04qps:33561.08(r/w/o:23477.06/6731.82/3352.21)lat(ms,95%):1869.60err/s:0.00reconn/s:0.00[240s]thds:900tps:2290.01qps:45719.85(r/w/o:31996.75/9148.79/4574.31)lat(ms,95%):1352.03err/s:0.00reconn/s:0.00SQLstatistics:queriesperformed:read:3318098write:948028other:474014total:4740140transactions:237007(985.74persec.)queries:4740140(19714.74persec.)ignorederrors:0(0.00persec.)reconnects:0(0.00persec.)Generalstatistics:totaltime:240.4346stotalnumberofevents:237007Latency(ms):min:2.76avg:911.43max:31437.1895thpercentile:2778.39sum:216015485.39Threadsfairness:events(avg/stddev):263.3411/37.88executiontime(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压测到崩溃重启”有了一定的了解，如果想了解更多相关知识，欢迎关注亿速云行业资讯频道，感谢各位的阅读！