Mysql 性能测试
sysbench是一个模块化的、跨平台、多线程基准测试工具，主要用于评估测试各种不同系统参数下的数据库负载情况。关于这个项目的详细介绍请看：http://sysbench.sourceforge.net。
它主要包括以下几种方式的测试：
1、cpu性能
2、磁盘io性能
3、调度程序性能
4、内存分配及传输速度
5、POSIX线程性能
6、数据库性能(OLTP基准测试)
目前sysbench主要支持 MySQL,pgsql,oracle 这3种数据库。
测试参数: 对myisam存储引擎
指定表最大记录数为 1000000
Sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-user=test --mysql-host=192.168.1.77 --mysql-password=test run
测试结果如下:
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 1
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (382.85 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (7274.20 per sec.)
other operations: 20000 (765.70 per sec.)
Test execution summary:
total time: 26.1197s
total number of events: 10000
total time taken by event execution: 26.0579
per-request statistics:
min: 1.93ms
avg: 2.61ms
max: 53.32ms
approx. 95 percentile: 3.09ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 26.0579/0.00
测试参数: 对innodb存储引擎
Sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-user=test --mysql-host=192.168.1.77 --mysql-password=test run
测试结果如下:
指定表最大记录数为 1000000
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 1
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (385.92 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (7332.39 per sec.)
other operations: 20000 (771.83 per sec.)
Test execution summary:
total time: 25.9124s
total number of events: 10000
total time taken by event execution: 25.8501
per-request statistics:
min: 1.90ms
avg: 2.59ms
max: 47.83ms
approx. 95 percentile: 3.03ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 25.8501/0.00
测试参数: 对myisam存储引擎,16个线程，最大查询100000
Sysbench --test=oltp --num-threads=16 --max-requests=100000 --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-user=test --mysql-host=192.168.1.77 --mysql-password=test run
测试结果
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 16
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 100000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 1400000
write: 500000
other: 200000
total: 2100000
transactions: 100000 (1250.17 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1900000 (23753.27 per sec.)
other operations: 200000 (2500.34 per sec.)
Test execution summary:
total time: 79.9890s
total number of events: 100000
total time taken by event execution: 1278.7309
per-request statistics:
min: 2.00ms
avg: 12.79ms
max: 457.76ms
approx. 95 percentile: 22.56ms
Threads fairness:
events (avg/stddev): 6250.0000/40.46
execution time (avg/stddev): 79.9207/0.01
测试参数: 对innodb存储引擎,16个线程，最大查询100000
Sysbench --test=oltp --num-threads=16 --max-requests=100000 --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-user=test --mysql-host=192.168.1.77 --mysql-password=test run
测试结果
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 16
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 100000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 1400126
write: 500045
other: 200018
total: 2100189
transactions: 100009 (1261.87 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1900171 (23975.50 per sec.)
other operations: 200018 (2523.74 per sec.)
Test execution summary:
total time: 79.2547s
total number of events: 100009
total time taken by event execution: 1266.9906
per-request statistics:
min: 2.02ms
avg: 12.67ms
max: 334.18ms
approx. 95 percentile: 22.61ms
Threads fairness:
events (avg/stddev): 6250.5625/35.64
execution time (avg/stddev): 79.1869/0.01
