如何把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-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压测到崩溃重启”有了一定的了解,如果想了解更多相关知识,欢迎关注亿速云行业资讯频道,感谢各位的阅读!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。