MySQL Sysbench 1.0.X

1. Installing Sysbench
Yum Install Sysbench
2.download Source Code
Sysbench-1.0.14\src\lua, all files uploaded linux/opt directory

3. Prepare MySQL

CREATE DATABASE tdb01;
CREATE USER ' root ' @ ' 192.168.0.130 ' identified by ' root123 ';
GRANT all privileges the tdb01.* to ' root ' @ ' 192.168.0.130 ';
FLUSH privileges;

4. Implementation steps
/ Prepare /
Sysbench/opt/lua/oltp_read_write.lua--db-driver=mysql--mysql-host=192.168.0.130--mysql-db=tdb01--mysql-user= Root--mysql-password=root123--table_size=5000000--tables=10--threads=300--time=60--report-interval=10 Prepare
/ Run /
Sysbench/opt/lua/oltp_read_write.lua--db-driver=mysql--mysql-host=192.168.0.130--mysql-db=tdb01--mysql-user= Root--mysql-password=root123--table_size=5000000--tables=10--threads=300--time=60--report-interval=10 Run
/ clean/
Sysbench/opt/lua/oltp_read_write.lua--db-driver=mysql--mysql-host=192.168.0.130--mysql-db=tdb01--mysql-user= Root--mysql-password=root123--table_size=5000000--tables=10--threads=300--time=60--report-interval=10 cleanup

5. Execution process

[Email protected] lua]# Sysbench/opt/lua/oltp_read_write.lua--db-driver=mysql--mysql-host=192.168.0.130-- MYSQL-DB=TDB01--mysql-user=root--mysql-password=root123--table_size=5000000--tables=10--threads=300--time=60-- report-interval=10 Prepare
Sysbench 1.0.14 (using bundled Luajit 2.1.0-beta2)

Initializing Worker Threads ...

Creating table ' sbtest10 ' ...
Creating table ' Sbtest1 ' ...
Creating table ' Sbtest7 ' ...
Creating table ' Sbtest9 ' ...
Creating table ' Sbtest6 ' ...
Creating table ' Sbtest8 ' ...
Creating table ' Sbtest3 ' ...
Creating table ' Sbtest2 ' ...
Creating table ' Sbtest5 ' ...
Creating table ' Sbtest4 ' ...
Inserting 5000000 records into ' Sbtest1 '
Inserting 5000000 records into ' Sbtest2 '
Inserting 5000000 records into ' Sbtest7 '
Inserting 5000000 records into ' Sbtest5 '
Inserting 5000000 records into ' sbtest10 '
Inserting 5000000 records into ' Sbtest4 '
Inserting 5000000 records into ' Sbtest3 '
Inserting 5000000 records into ' Sbtest9 '
Inserting 5000000 records into ' Sbtest6 '
Inserting 5000000 records into ' Sbtest8 '
Creating a secondary index on ' sbtest4 ' ...
Creating a secondary index on ' sbtest6 ' ...
Creating a secondary index on ' sbtest5 ' ...
Creating a secondary index on ' sbtest1 ' ...
Creating a secondary index on ' sbtest9 ' ...
Creating a secondary index on ' sbtest8 ' ...
Creating a secondary index on ' sbtest2 ' ...
Creating a secondary index on ' sbtest7 ' ...
Creating a secondary index on ' sbtest10 ' ...
Creating a secondary index on ' sbtest3 ' ...
[Email protected] lua]#

[Email protected] opt]# Sysbench/opt/lua/oltp_read_write.lua--db-driver=mysql--mysql-host=192.168.0.130-- MYSQL-DB=TDB01--mysql-user=root--mysql-password=root123--table_size=5000000--tables=10--threads=300--time=60-- report-interval=10 Run
Sysbench 1.0.14 (using bundled Luajit 2.1.0-beta2)

Running the test with following options:
Number of threads:300
Report intermediate results every second (s)
Initializing random number generator from current time

Initializing Worker Threads ...

Threads started!

[10s] thds:300 tps:28.98 qps:998.95 (r/w/o:753.86/157.17/87.92) Lat (ms,95%): 9799.46 err/s: 0.00 RECONN/S: 0.00< br>[20s] thds:300 tps:44.61 qps:921.51 (r/w/o:644.08/188.22/89.21) Lat (ms,95%): 12163.09 err/s: 0.00 RECONN/S: 0.00
[30s] thds:300 tps:43.02 qps:892.44 (r/w/o:643.05/163.36/86.03) Lat (ms,95%): 12163.09 err/s: 0.00 RECONN/S: 0.0 0
[40s] thds:300 tps:51.40 qps:997.46 (r/w/o:686.07/208.59/102.80) Lat (ms,95%): 16819.24 err/s: 0.00 RECONN/S: 0 . XX
[50s] thds:300 tps:47.00 qps:978.98 (r/w/o:680.65/204.32/94.01) Lat (ms,95%): 14302.94 err/s: 0.00 RECONN/S: 0.00
[60s] thds:300 tps:58.69 qps:1103.34 (r/w/o:783.19/202.77/117.38) Lat (ms,95%): 10531.32 err/s: 0.00 reconn/ s:0.00
SQL Statistics:
Queries performed:
read:42518
write:12148
other:6074
total:60740
transactions:3037 (48.99 per sec.)
queries:60740 (979.77 per sec.)
Ignored errors:0 (0.00 per Sec.)
reconnects:0 (0.00 per sec.)

General Statistics:
Total time:61.9920s
Total number of events:3037

Latency (ms):
min:114.27
avg:6085.46
max:26580.90
95th percentile:12609.11
sum:18481536.44

Threads Fairness:
Events (Avg/stddev): 10.1233/1.86
Execution Time (Avg/stddev): 61.6051/0.23

[Email protected] opt]#
Sysbench/opt/lua/oltp_read_write.lua--db-driver=mysql--mysql-host=192.168.0.130--mysql-db=tdb01--mysql-user= Root--mysql-password=root123--table_size=5000000--tables=10--threads=300--time=60--report-interval=10 cleanup
Sysbench 1.0.14 (using bundled Luajit 2.1.0-beta2)

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 ' ...
[Email protected] opt]#

6. Precautions
1). The Max_prepared_stmt_count parameter should be set to a higher point otherwise it will prompt to exceed the threshold value
2). As the database data needs to be loaded into the memory to become hot data, run should be executed several times, the first time the TPS,QPS is generally low, should take a few times after the value as a reference.

MySQL Sysbench 1.0.X