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