MySQL Stress test

MySQL Stress test

First, testing tools: Sysbench

Sysbench Supported test modes:

1, CPU performance 2, disk IO performance 3, Scheduler performance 4, memory allocation and transmission speed 5, POSIX thread performance 6, database performance (OLTP benchmark) currently sysbench mainly support mysql,drizzle,pgsql,oracle and several other databases.

Second, compile and install Sysbench

Download Link: http://imysql.com/wp-content/uploads/2014/09/sysbench-0.4.12-1.1.tgz

[Email protected] package]# tar zxvf sysbench-0.4.12-1.1.tgz

[Email protected] sysbench-0.4.12-1.1]#./autogen.sh

[Email protected] sysbench-0.4.12-1.1]#./configure

[[email protected] sysbench-0.4.12-1.1]# make

[[email protected] sysbench-0.4.12-1.1]# make install

Third, the OLTP test environment

Initialize the test library environment and operate on the prepared database

you first need to create a library named Sbtest in the prepared database, the name must be called for non-change . , can understand the Lua script can be arbitrarily changed, this is a major premise

1, need to cut into this directory to operate, if you do not cut the directory can also use absolute path, Oltp.lua script path

[Email protected] db]# cd/data/package/sysbench-0.4.12-1.1/sysbench/tests/db

2, in the Sbtest Library, create 10 test tables, 100,000 records (rows) in each test table, fill the randomly generated data

[Email protected] db]# sysbench--mysql-host=192.168.4.100--mysql-user=admin--mysql-password=fjrudkeislwo--tes T=oltp.lua--oltp_tables_count=10--oltp-table-size=100000--rand-init=on Prepare

Parameter explanation:

--test=tests/db/oltp.lua indicates that the./tsysbench-0.4.12-1.1/sysbench/tests/db/oltp.lua script is called for OLTP mode testing--oltp_tables_ COUNT=10 indicates that 10 test tables are generated--oltp-table-size=100000 indicates that each test table is populated with 100000--rand-init=on indicating that each test table is populated with random data

Others experience:

in a real-world test scenario, the data table suggests no less than 10 x , the single-table data volume is not less than 5 million rows , of course, depends on the server hardware configuration. If you have a high-iops device with an SSD or PCIe SSD, it is recommended that you have a minimum of 100 million rows per table of data .

Iv. Conducting OLTP testing

[Email protected] db]# sysbench--mysql-host=10.10.95.235--mysql-user=admin--mysql-password=fjrudkeislwo--test =oltp.lua--oltp_tables_count=10--oltp-table-size=10000000--num-threads=10--oltp-read-only=off--report-interval =10--rand-type=uniform--max-time=120--max-requests=0--percentile=99 Run >/data/log/sysbench.log

Parameter explanation:

--num-threads=8 # indicates the initiation of 8 concurrent connections--oltp-read-only=off #表示不要进行只读测试, that is, a read-write mixed mode test--report-i nterval=10 #表示每10秒输出一次测试进度报告--rand-type=uniform #表示随机类型为固定模式, several other optional random modes: uniform (fixed), Gaussian (Gaussian), special (specific), p Areto (Pareto)--max-time=120 #表示最大执行时长为 120 seconds--max-requests=0 #表示总请求数为 0 because the total execution time is defined above, so the total number of requests can be set to 0 , or you can set the total number of requests, do not set the maximum execution time--percentile=99 #表示设定采样比例, the default is 95%, that is, discard 1% long requests, the remaining 99% to take the maximum value

Others experience:

in the real test scenario, it is recommended that the duration of continuous pressure measurement is not less than 30 minutes , otherwise the test data may not be of reference significance.

V. Interpretation of test results

--reports test results every 10 seconds, TPS, read per second, write per second, 99% + response time statistics [  10s] Threads:8, tps:1111.51, reads/s: 15568.42, writes/s: 4446.13, Response time:9.95ms (99%) [ 20s] Threads : 8, tps:1121.90, reads/s: 15709.62, writes/s: 4487.80, Response time:9.78ms (99%) [ 30s] Threads:8, tps:1120.00, READS/S: 15679.10, writes/s: 4480.20, Response time:9.84ms (99%) [ 40s] Threads:8, tps:1114.20, reads/s: 15599.39 , WRITES/S: 4456.30, Response time:9.90ms (99%) [ 50s] Threads:8, tps:1114.00, reads/s: 15593.60, writes/s: 4456.7 0, Response Time:9.84ms (99%) [ 60s] Threads:8, tps:1119.30, reads/s: 15671.60, writes/s: 4476.50, Response time: 9.99ms (99%) OLTP Test Statistics:    queries performed:        read:                            938224    --read total        write:                            268064    -- Total        other:                         & nbsp 134032    --Other operations (SELECT, INSERT, UPDATE, delete, such as commit, etc.)        total:                           1340320    --Total totals   &N Bsp;transactions:                        67016   (1116.83 Per Sec.)    --Total transactions (transactions per second)    deadlocks:                          0       (0.00 per sec)                --total number of deadlocks   &NBSP ; Read/write requests:                 1206288 (20103.01 per sec.)    -- Total reads and writes (read and write times per second)    other operations:                      13 4032 (2233.67 per sec.)    --Total other operations (other operations per second) General statistics:    --Some statistical results    total time :                                   6 0.0053s    --Total time    total number of events:              67016 &NB Sp  --total number of transactions    total time taken by event execution:479.8171s    --All transactions take place (regardless of parallelism)   &NB Sp;response Time:    --response duration Statistics         min:                                  4.27ms    --min Time-consuming         AVG:                                  7.16MS   &NB sp;--average time-consuming         Max:                       & nbsp         13.80ms    --longest time-consuming         approx  99 percentile:   & nbsp           9.88ms    --over 99% average time threads fairness:    events (Avg/stddev): &NB Sp         8377.0000/44.33    execution Time (Avg/stddev):   59.9771/0.00

The most important parameter indicators are:

Total number of things, transactions per second, time statistics (maximum, minimum, average, 99% + statement response time)

The above test method is in the Cloud RDS (UDB), db instance verified, I test the time is using the Schindler cloud and Ucloud Cloud contrast test, easier to come up with data.

In the test environment, that is, the self-built database, there will be a variety of situations, only rely on personal troubleshooting ability.

This article from "10946218" blog, declined reprint!

MySQL Stress test