1.安装sysbench

yum install sysbench2.download source codesysbench-1.0.14\src\lua,所有文件上传Linux /opt目录

3.准备mysql

CREATE DATABASE tdb01;

CREATE USER 'root'@'192.168.0.130' IDENTIFIED BY 'root123';GRANT ALL PRIVILEGES ON tdb01.* TO ‘'root'@'192.168.0.130';FLUSH PRIVILEGES;

4.执行步骤

/ 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.执行过程

[root@mdb01 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'...[root@mdb01 lua]#

[root@mdb01 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: 300Report intermediate results every 10 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

[ 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.00[ 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.00[ 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.00SQL statistics:queries performed:read: 42518write: 12148other: 6074total: 60740transactions: 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.9920stotal number of events: 3037

Latency (ms):

min: 114.27avg: 6085.46max: 26580.9095th percentile: 12609.11sum: 18481536.44

Threads fairness:

events (avg/stddev): 10.1233/1.86execution time (avg/stddev): 61.6051/0.23

[root@mdb01 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 cleanupsysbench 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'...[root@mdb01 opt]#

6.注意事项

1).max_prepared_stmt_count参数应该设置的高一点否则会提示超过阈值2).由于数据库数据需要装载到内存变成热数据，run应该执行多次，第一次的TPS,QPS一般偏低，应该取执行几次后的值作为参考。