Sysbench stress test mysql database

Sysbench is a stress testing tool that can be used to test the hardware performance of the system and can also be used to benchmark the database. It is very easy to use.

Sysbench also supports the following test modes:

1. CPU computing performance

2. Disk IO performance

3. Scheduler performance

4. Memory allocation and transmission speed

5. POSIX thread performance

6. Database performance (OLTP benchmark)

At present, sysbench mainly supports several databases such as mysql,drizzle,pgsql,oracle.

1.

Install sysbench:

Yum install epel-release

Yum install sysbench-y

Use sysbench-- help to view help.

2.sysbench provides many built-in stress test scripts, which are placed in the / usr/share/sysbench directory. This article uses the installation sysbench stress test mysql database:

Create user and database information on the database that is ready for the stress test:

Grant all privileges on *. * to sysbench identified by 'sysbench'

Flush privileges

Create database sysbench_db

a. Prepare the pressure test data:

Sysbench/ usr/share/sysbench/oltp_insert.lua\

-- mysql-host=192.168.56.102\

-- mysql-port=3306\

-- mysql-user=sysbench\

-- mysql-password='sysbench'\

-- mysql-db=sysbench_db\

-- db-driver=mysql\

-- tables=15\

-- table-size=500000\

-- report-interval=10\

-- threads=128\

-- time=120\

Prepare

[root@my-mm sysbench] # sysbench/ usr/share/sysbench/oltp_insert.lua\

>-- mysql-host=192.168.56.102\

>-- mysql-port=3306\

>-- mysql-user=sysbench\

>-- mysql-password='sysbench'\

>-- mysql-db=sysbench_db\

>-- db-driver=mysql\

>-- tables=15\

>-- table-size=500000\

>-- report-interval=10\

>-- threads=128\

>-- time=120\

> prepare

Sysbench 1.0.9 (using system LuaJIT 2.0.4)

Initializing worker threads...

Creating table 'sbtest14'...

Creating table 'sbtest12'...

Creating table 'sbtest3'...

Creating table 'sbtest5'...

Creating table 'sbtest11'...

Creating table 'sbtest4'...

Creating table 'sbtest13'...

Creating table 'sbtest10'...

Creating table 'sbtest9'...

Creating table 'sbtest8'...

Creating table 'sbtest15'...

Creating table 'sbtest1'...

Creating table 'sbtest2'...

Creating table 'sbtest6'...

Creating table 'sbtest7'...

Inserting 500000 records into 'sbtest12'

Inserting 500000 records into 'sbtest11'

Inserting 500000 records into 'sbtest10'

Inserting 500000 records into 'sbtest2'

Inserting 500000 records into 'sbtest5'

Inserting 500000 records into 'sbtest8'

Inserting 500000 records into 'sbtest13'

Inserting 500000 records into 'sbtest4'

Inserting 500000 records into 'sbtest3'

Inserting 500000 records into 'sbtest7'

Inserting 500000 records into 'sbtest15'

Inserting 500000 records into 'sbtest1'

Inserting 500000 records into 'sbtest6'

Inserting 500000 records into 'sbtest9'

Inserting 500000 records into 'sbtest14'

Creating a secondary index on 'sbtest9'...

Creating a secondary index on 'sbtest11'...

Creating a secondary index on 'sbtest1'...

Creating a secondary index on 'sbtest5'...

Creating a secondary index on 'sbtest14'...

Creating a secondary index on 'sbtest6'...

Creating a secondary index on 'sbtest13'...

Creating a secondary index on 'sbtest4'...

Creating a secondary index on 'sbtest15'...

Creating a secondary index on 'sbtest8'...

Creating a secondary index on 'sbtest2'...

Creating a secondary index on 'sbtest7'...

Creating a secondary index on 'sbtest12'...

Creating a secondary index on 'sbtest3'...

Creating a secondary index on 'sbtest10'...

View Monitoring:

b. Pressure test:

Sysbench/ usr/share/sysbench/oltp_insert.lua\

-- mysql-host=192.168.56.102\

-- mysql-port=3306\

-- mysql-user=sysbench\

-- mysql-password='sysbench'\

-- mysql-db=sysbench_db\

-- db-driver=mysql\

-- tables=15\

-- table-size=500000\

-- report-interval=10\

-- threads=128\

-- time=120\

Run

View qps:

[root@my-mm sysbench] # sysbench/ usr/share/sysbench/oltp_insert.lua\

>-- mysql-host=192.168.56.102\

>-- mysql-port=3306\

>-- mysql-user=sysbench\

>-- mysql-password='sysbench'\

>-- mysql-db=sysbench_db\

>-- db-driver=mysql\

>-- tables=15\

>-- table-size=500000\

>-- report-interval=10\

>-- threads=128\

>-- time=120\

> run

Sysbench 1.0.9 (using system LuaJIT 2.0.4)

Running the test with following options:

Number of threads: 128

Report intermediate results every 10 second (s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[10s] thds: 128 tps: 1475.71 qps: 1475.71 (r/w/o: 0.00Universe 1475.71) lat (ms,95%): 164.45 err/s: 0.00 reconn/s: 0.00

[20s] thds: 128 tps: 895.50 qps: 895.50 (r/w/o: 0.000.00 tps) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00

[30s] thds: 128 tps: 1490.81 qps: 1490.81 (r/w/o: 0.000.81 lat) lat (ms,95%): 189.93 err/s: 0.00 reconn/s: 0.00

[40s] thds: 128 tps: 1385.42 qps: 1385.42 (r/w/o: 0.00Universe 1385.42) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00

[50s] thds: 128 tps: 1300.72 qps: 1300.72 (r/w/o: 0.00lap1300.72) lat (ms,95%): 193.38 err/s: 0.00 reconn/s: 0.00

[60s] thds: 128 tps: 2194.74 qps: 2194.74 (r/w/o: 0.000 err/s 2194.74 reconn/s 0.00) lat (ms,95%): 92.42 err/s: 0.00 reconn/s: 0.00

[70s] thds: 128tps: 2226.52 qps: 2226.52 (r/w/o: 0.00Compare 2226.52 tps) lat (ms,95%): 86.00 err/s: 0.00 reconn/s: 0.00

[80s] thds: 128 tps: 1149.59 qps: 1149.59 (r/w/o: 0.00Compare 1149.59 lat) lat (ms,95%): 272.27 err/s: 0.00 reconn/s: 0.00

[90s] thds: 128 tps: 881.20 qps: 881.20 (r/w/o: 0.00Compare 881.20 lat) lat (ms,95%): 320.17 err/s: 0.00 reconn/s: 0.00

[100s] thds: 128 tps: 1602.52 qps: 1602.52 (r/w/o: 0.00Universe 1602.52) lat (ms,95%): 142.39 err/s: 0.00 reconn/s: 0.00

[110s] thds: 128 tps: 1722.31 qps: 1722.31 (r/w/o: 0.000.31) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00

[120s] thds: 128 tps: 1956.95 qps: 1956.95 (r/w/o: 0.00 1956.95) lat (ms,95%): 108.68 err/s: 0.00 reconn/s: 0.00

SQL statistics:

Queries performed:

Read: 0

Write: 182948

Other: 0

Total: 182948

Transactions: 182948 (1522.89 per sec.)

Queries: 182948 (1522.89 per sec.)

Ignored errors: 0 (0.00 per sec.)

Reconnects: 0 (0.00 per sec.)

General statistics:

Total time: 120.1302s

Total number of events: 182948

Latency (ms):

Min: 3.65

Avg: 84.00

Max: 3444.05

95th percentile: 189.93

Sum: 15367235.04

Threads fairness:

Events (avg/stddev): 1429.2812 12.53

Execution time (avg/stddev): 120.0565 take 0.03

c. Clean up the pressure test data:

Sysbench/ usr/share/sysbench/oltp_insert.lua\

-- mysql-host=192.168.56.102\

-- mysql-port=3306\

-- mysql-user=sysbench\

-- mysql-password='sysbench'\

-- mysql-db=sysbench_db\

-- db-driver=mysql\

-- tables=15\

-- table-size=500000\

-- report-interval=10\

-- threads=128\

-- time=120\

Cleanup

[root@my-mm sysbench] # sysbench/ usr/share/sysbench/oltp_insert.lua\

>-- mysql-host=192.168.56.102\

>-- mysql-port=3306\

>-- mysql-user=sysbench\

>-- mysql-password='sysbench'\

>-- mysql-db=sysbench_db\

>-- db-driver=mysql\

>-- tables=15\

>-- table-size=500000\

>-- report-interval=10\

>-- threads=128\

>-- time=120\

> cleanup

Sysbench 1.0.9 (using system LuaJIT 2.0.4)

Dropping table 'sbtest1'...

Dropping table 'sbtest2'...

Dropping table 'sbtest3'...

Dropping table 'sbtest4'...

Dropping table 'sbtest5'...

Dropping table 'sbtest6'...

Dropping table 'sbtest7'...

Dropping table 'sbtest8'...

Dropping table 'sbtest9'...

Dropping table 'sbtest10'...

Dropping table 'sbtest11'...

Dropping table 'sbtest12'...

Dropping table 'sbtest13'...

Dropping table 'sbtest14'...

Dropping table 'sbtest15'...