Shulou(Shulou.com)05/31 Report--

Editor to share with you how to mysqld pressure test to crash restart, I hope you will learn something after reading this article, let's discuss it!

First, the preparation of pressure testing environment tools:

Centos7.5

Sysbench2.0.9

Mysql5.7.22

Machine configuration: host is vmware esxi

DELL R730

Hard disk: ordinary 10K SAS

Memory: 18G

CPU:8 kernel

Very common cpu:

[root@yw-gz-hd-test-211 log] # lscpu Architecture: x86_64CPU op-mode (s): 32-bit 64-bitByte Order: Little EndianCPU (s): 8On-line CPU (s) list: 0-7Thread (s) per core: 1Core (s) per socket: 1Socket (s): 8NUMA node (s): 1Vendor ID: GenuineIntelCPU family: 6Model: 79Model name: Intel (R) Xeon ( R) CPU E5-2640 v4 @ 2.40GHzStepping: 1CPU MHz: 2399.361BogoMIPS: 4799.99Hypervisor vendor: VMwareVirtualization type: fullL1d cache: 32KL1i cache: 32KL2 cache: 256KL3 cache: 25600KNUMA node0 CPU (s): 0-7Flags: 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

Compile and install mysql, set up innodb_buffer_pool_size=5G innodb_buffer_pool_instance=5. Other parameters change redo to 4 groups, io thread to 8, and so on.

Second, begin to prepare the stress test database:

Insert 10 tables, each table data 10 million, the entire msyql library 25G.

[root@yw-gz-hd-test-211 ~] # ls / data/mysql3308/sbtest/-lhtotal 25G Murray rwmurr-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/-lhtotal 1.4G auto.cnf-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 ibtmp1drwxr-x--- 2 mysql mysql 4.0K Jul 17 17: 56 mysqlsrwxrwxrwx 1 mysql mysql 0 Jul 18 14:42 mysql.sock-rw- 1 mysql mysql 6 Jul 18 14:42 mysql.sock.lockdrwxr-x--- 2 mysql mysql 8.0K Jul 17 17:56 performance_schemadrwxr-x--- 2 mysql mysql 4.0K Jul 17 19:36 sbtestdrwxr-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

3. Start the pressure test:

The first 300 threads, start on. You will find that you report the wrong right away:

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

Baidu can solve the problem by setting some parameters: max_prepared_stmt_count=150000

Fourth, the best part appears:

Error troubleshooting, pressure test to 300 threads, a total of 240s, until the pressure test to 120s, the mysql process suddenly collapsed. The cause of the mysql crash is not recorded in the error log, only that after the mysql crash, it is monitored by the mysqld_safe process, and then immediately pulls up the mysqld process. The mysqld_safe process monitors the mysqld process all the time, finds it dead, and immediately pulls up the mysqld process. I suspect there is not enough memory. But there is no evidence that insufficient memory caused the mysqld process to collapse. At this point, I found the top command very useful. How do I use it? Let me talk about it. As mentioned earlier, the first 120 seconds of the stress test, there is no problem, you can open the top when the stress test is 0: 120 seconds, and you observe the memory usage of the mysqld thread. If you look at the RES column, you will see that the mysqld process res value has been growing from 500m to 5G, when the duang~, crashed. You can see that there are times when mysql can't stand it.

To verify your conjecture, simply don't change any parameters and increase the machine memory to 18 gigabytes. Once again, the stress test verified my idea that the mysqld process used 6 gigabytes of memory in another 300 concurrent threads.

Let's take a look at 300 concurrent pressure tests.

[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-user=root-- mysql-password=123456-- table_size=10000000-- tables=10-- threads=300-- time=240-- report-interval=30 runsysbench 1.0.9 (using system LuaJIT 2.0.4) Running the test with following options:Number of Threads: 300Report intermediate results every 30 second (s) Initializing random number generator from current timeInitializing worker threads...Threads started! [30s] thds: 300 tps: 189.42 qps: 3911.05 (r/w/o: 2753.06) lat (ms) 95%): 3151.62 err/s: 0.00 reconn/s: 0.00 [60s] thds: 406.65 tps: 406.65 qps: 8146.63 (r/w/o: 5702.77 Maple 1630.57 reconn/s 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.74Unimax 4115.19) lat (ms) 95%): 909.80 err/s: 0.00 reconn/s: 0.00 [120s] thds: 915.33 tps: 915.33 qps: 18308.17 (r/w/o: 12818.23 reconn/s 3659.271830.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 reconn/s 3391.60) lat (ms) 95%): 787.74 err/s: 0.00 reconn/s: 0.00 [180s] thds: 1015.47 tps: 1015.47 qps: 20327.15 (r/w/o: 14231.78 4064.44 reconn/s 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) 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 lat 3403.37) lat (ms 419.45 err/s: 0.00 reconn/s: 0.00SQL 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: 222144Latency (ms): min: 2.52 avg: 324.16 Max: 50333.39 95th percentile: 1050.76 sum: 72010070.69Threads fairness: events (avg/stddev): 740.4800 execution time (avg/stddev): 240.0336

Not bad grades, QPS:18490,TPS:924. 95% of the response time is 1050ms, which is 1 second, which is acceptable

Let's take a look at 600 concurrent connection threads.

[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-user=root-- mysql-password=123456-- table_size=10000000-- tables=10-- threads=600-- time=240-- report-interval=30 runsysbench 1.0.9 (using system LuaJIT 2.0.4) Running the test with following options:Number of Threads: 600Report intermediate results every 30 second (s) Initializing random number generator from current timeInitializing worker threads...Threads started! [30s] thds: 600 tps: 177.45 qps: 3866.55 (r/w/o: 2740.46) lat (ms) 95%): 6594.16 err/s: 0.00 reconn/s: 0.00 [60s] thds: 508.61 qps: 10190.12 (r/w/o: 7130.15 reconn/s 2042.76 reconn/s 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 reconn/s 3312.26 lat 1666.20) 95%): 1506.29 err/s: 0.00 reconn/s: 0.00 [120s] thds: 712.40 qps: 14275.18 (r/w/o: 9994.28) 2856.20 qps 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) lat (ms 95%): 1280.93 err/s: 0.00 reconn/s: 0.00 [180s] thds: 1152.15 qps: 23046.54 (r/w/o: 16115.87tha 4626.50 reconn/s 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 Charley 5707.53 2844.74) lat (ms 707.07 err/s: 0.00 reconn/s: 0.00 [240s] thds: 1874.42 tps: 1874.42 qps: 37511.54 (r/w/o: 26257.48 r/w/o 7505.04) lat (ms 601.29 err/s: 0.00 reconn/s: 0.00SQL 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: 225841Latency (ms): min: 2.75 avg: 637.78 Max: 44200.42 95th percentile: 1678.14 sum: 144036928.60Threads fairness: events (avg/stddev): 376.4017 execution time (avg/stddev): 240.0615

At this time, we see a large number of slow query statements, 95% of the response time is 1678ms, that is, 1.6 seconds, a little slow. Take a look at what slow queries are:

# Time: 2018-07-18T14:22:07.662597+08:00# User@Host: root [root] @ localhost [] Id: 59 Lock_time Query_time: 7.400737 Lock_time: 0.000028 Rows_sent: 0 Rows_examined: 1SET 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: 20 years Query_time: 4.220504 Lock_time: 0.000027 Rows_sent: 0 Rows_examined: 1SET 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: 544th Query_time: 3.662601 Lock_time: 0.000021 Rows_sent: 0 Rows_examined: 1SET timestamp=1531894927;DELETE FROM sbtest5 WHERE id=5024577 # Time: 2018-07-18T14:22:07.662634+08:00# User@Host: root [root] @ localhost [] Id: 40 Query_time: 4.832428 Rows_sent: 0 Rows_examined: 1SET timestamp=1531894927;UPDATE sbtest5 SET cantilever 53575816661-90198037463-61731021712-17992612508-02527517402-89815419518-53211578757-17129425245-97225103738-94879199437' WHERE id=5024586

They are all update statements. These statements consume a lot of IO's

Let's take a look at 900 concurrent threads.

[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-user=root-- mysql-password=123456-- table_size=10000000-- tables=10-- threads=900-- time=240-- report-interval=30 runsysbench 1.0.9 (using system LuaJIT 2.0.4) Running the test with following options:Number of Threads: 900Report intermediate results every 30 second (s) Initializing random number generator from current timeInitializing worker threads...Threads started! [30s] thds: 900 tps: 347.86 qps: 7432.37 (r/w/o: 5273.60) 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 reconn/s 2252.28 reconn/s 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.29max 2580.13) lat (ms) 95%): 2932.60 err/s: 0.00 reconn/s: 0.00 [120s] thds: 900tps: 360.53 qps: 7200.07 (r/w/o: 5039.67max 1439.33reconn/s 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.68max 2933.03) lat (ms) 95%): 2585.31 err/s: 0.00 reconn/s: 0.00 [180s] thds: 1268.27 qps: 25410.63 (r/w/o: 17798.37) 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) lat (ms: 23477.06) 1869.60 err/s: 0.00 reconn/s: 0.00 [240s] thds: 900 tps: 2290.01 qps: 45719.85 (r/w/o: 31996.75 lat 4574.31) lat (ms 1352.03 err/s: 0.00 reconn/s: 0.00SQL 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: 237007Latency (ms): min: 2.76 avg: 911.43 Max: 31437.18 95th percentile: 2778.39 sum: 216015485.39Threads fairness: events (avg/stddev): 263.3411 execution time (avg/stddev): 240.0172

As you can see, 95% of the response time is 2.7 seconds, and the database mysql response time is getting slower and slower and more and more overwhelmed. Collapse in an instant. As I can see, 1000 concurrent threads caused mysqld to crash during innodb_buffer_pool_size=18G. I can't stand it at last.

Let's roughly estimate how much memory is required for 100 concurrency:

Concurrency whether innodb_buffer_pool_sizemysqld crashes 2005G No 3005G crash 60018G No 90018G No 100018G crash

It seems that with 100 concurrent threads, mysqld needs at least 2 GB of memory, and consider leaving 2 GB of memory to the operating system. So a 4-core 8G machine, the number of threads should not be set to more than 250. This is not only to protect the database from crashing and ensure that the response time is within a reasonable range (1 second), but also, when the connection reaches the upper limit, the program reports an error, indicating that DBA needs to increase the memory of the machine.

After reading this article, I believe you have a certain understanding of "how to mysqld crash restart". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.