mysql sysbench 1.0.X-yiyi
1.安装sysbenchyum install sysbench
2.download source code
sysbench-1.0.14\src\lua,所有文件上传Linux /opt目录
3.准备mysql
CREATE DATABASE tdb01;
CREATE USER 'root'@'192.168.0.130'> 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.执行过程
# 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'...
#
# 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 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.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
#
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'...
#
6.注意事项
1).max_prepared_stmt_count参数应该设置的高一点否则会提示超过阈值
2).由于数据库数据需要装载到内存变成热数据，run应该执行多次，第一次的TPS,QPS一般偏低，应该取执行几次后的值作为参考。
页:
[1]