java-mans
- 浏览: 11432546 次
-
文章分类
最新评论
-
wahahachuang8：
我觉得这种东西自己开发太麻烦了，就别自己捣鼓了，找个第三方，方 ...
WebSocket和node.js -
xhpscdx：
写的这么详细，全面，对架构师的工作职责，个人能力都进行了梳理。 ...
架构师之路---王泽宾谈架构师的职责 -
xgbzsc：
是http://www.haoservice.com 吗？
android WIFI定位 -
lehehe：
http://www.haoservice.com/docs/ ...
android WIFI定位 -
lehehe：
http://www.haoservice.com/docs/ ...
android WIFI定位
MySQL测试 5.1 innodb VS innodb_plugin VS innodb_plugin+tcmalloc VS 5.5+tcmalloc
最近在搞mysql测试,实验环境选取限制在2cpu+4GB内存+单硬盘,采用相同的配置文件.相同的业务,现将结果汇报如下:
如果你的服务cpu数量少于8core,单硬盘,推荐大家使用高版本的mysql5.1,如果你的服务器性能强劲,特别是在cpu和磁盘吞吐上,那请使用mysql 5.5并启用相应的配置.
MySQL5.1 innodb 1.0
Benchmark DBD suite: 2.15
Date of test: 2012-04-11 18:12:30
Running tests on: Linux 2.6.32-220.4.2.el6.x86_64 x86_64
Arguments: --socket=/opt/mysql_5_1_45/innbase/mysql.sock
Comments:
Limits from:
Server version: MySQL 5.1.45
Optimization: None
Hardware:
alter-table: Total time: 43 wallclock secs ( 0.02 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.03 CPU)
ATIS: Total time: 6 wallclock secs ( 3.74 usr 0.28 sys + 0.00 cusr 0.00 csys = 4.02 CPU)
big-tables: Total time: 7 wallclock secs ( 2.05 usr 0.33 sys + 0.00 cusr 0.00 csys = 2.38 CPU)
connect: Total time: 108 wallclock secs (29.60 usr 12.49 sys + 0.00 cusr 0.00 csys = 42.09 CPU)
create: Total time: 1173 wallclock secs ( 1.67 usr 0.42 sys + 0.00 cusr 0.00 csys = 2.09 CPU)
insert: Total time: 1158 wallclock secs (262.33 usr 39.67 sys + 0.00 cusr 0.00 csys = 302.00 CPU)
select: Total time: 84 wallclock secs (24.91 usr 2.91 sys + 0.00 cusr 0.00 csys = 27.82 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 7 wallclock secs ( 2.10 usr 0.53 sys + 0.00 cusr 0.00 csys = 2.63 CPU)
All 9 test executed successfully
Totals per operation:
Operation seconds usr sys cpu tests
alter_table_add 19.00 0.00 0.00 0.00 100
alter_table_drop 18.00 0.00 0.00 0.00 91
connect 7.00 4.03 1.40 5.43 10000
connect+select_1_row 10.00 3.98 1.55 5.53 10000
connect+select_simple 9.00 3.90 1.46 5.36 10000
count 10.00 0.02 0.00 0.02 100
count_distinct 0.00 0.12 0.02 0.14 1000
count_distinct_2 0.00 0.13 0.03 0.16 1000
count_distinct_big 4.00 3.62 0.04 3.66 120
count_distinct_group 1.00 0.49 0.02 0.51 1000
count_distinct_group_on_key 1.00 0.14 0.02 0.16 1000
count_distinct_group_on_key_parts 0.00 0.49 0.02 0.51 1000
count_distinct_key_prefix 0.00 0.11 0.02 0.13 1000
count_group_on_key_parts 1.00 0.46 0.02 0.48 1000
count_on_key 13.00 5.22 0.85 6.07 50100
create+drop 217.00 0.33 0.08 0.41 10000
create_MANY_tables 177.00 0.17 0.04 0.21 10000
create_index 3.00 0.00 0.00 0.00 8
create_key+drop 694.00 0.71 0.12 0.83 10000
create_table 1.00 0.00 0.00 0.00 31
delete_all_many_keys 10.00 0.01 0.00 0.01 1
delete_big 0.00 0.00 0.00 0.00 1
delete_big_many_keys 10.00 0.01 0.00 0.01 128
delete_key 1.00 0.27 0.14 0.41 10000
delete_range 5.00 0.01 0.00 0.01 12
drop_index 3.00 0.00 0.00 0.00 8
drop_table 0.00 0.00 0.00 0.00 28
drop_table_when_MANY_tables 82.00 0.15 0.04 0.19 10000
insert 53.00 9.86 4.55 14.41 350768
insert_duplicates 14.00 3.39 1.42 4.81 100000
insert_key 42.00 3.96 1.40 5.36 100000
insert_many_fields 5.00 0.12 0.04 0.16 2000
insert_select_1_key 4.00 0.00 0.00 0.00 1
insert_select_2_keys 5.00 0.00 0.00 0.00 1
min_max 4.00 0.02 0.01 0.03 60
min_max_on_key 17.00 8.65 1.53 10.18 85000
multiple_value_insert 2.00 0.16 0.01 0.17 100000
once_prepared_select 26.00 5.33 1.45 6.78 100000
order_by_big 10.00 9.32 0.16 9.48 10
order_by_big_key 10.00 9.36 0.18 9.54 10
order_by_big_key2 10.00 9.18 0.12 9.30 10
order_by_big_key_desc 10.00 9.43 0.16 9.59 10
order_by_big_key_diff 10.00 9.32 0.12 9.44 10
order_by_big_key_prefix 10.00 9.25 0.13 9.38 10
order_by_key2_diff 2.00 0.86 0.02 0.88 500
order_by_key_prefix 0.00 0.46 0.02 0.48 500
order_by_range 1.00 0.47 0.01 0.48 500
outer_join 2.00 0.00 0.00 0.00 10
outer_join_found 1.00 0.00 0.00 0.00 10
outer_join_not_found 1.00 0.00 0.00 0.00 500
outer_join_on_key 1.00 0.00 0.00 0.00 10
prepared_select 33.00 10.62 1.74 12.36 100000
select_1_row 18.00 3.07 1.31 4.38 100000
select_1_row_cache 6.00 2.21 1.28 3.49 100000
select_2_rows 19.00 3.22 1.32 4.54 100000
select_big 11.00 9.40 0.19 9.59 80
select_big_str 4.00 1.38 0.43 1.81 10000
select_cache 2.00 1.04 0.15 1.19 10000
select_cache2 50.00 1.43 0.21 1.64 10000
select_column+column 19.00 3.03 1.37 4.40 100000
select_diff_key 0.00 0.06 0.01 0.07 500
select_distinct 1.00 0.68 0.02 0.70 800
select_group 1.00 0.54 0.07 0.61 2911
select_group_when_MANY_tables 3.00 0.31 0.14 0.45 10000
select_join 0.00 0.20 0.00 0.20 100
select_key 107.00 22.70 3.46 26.16 200000
select_key2 121.00 23.53 3.58 27.11 200000
select_key2_return_key 125.00 23.76 3.82 27.58 200000
select_key2_return_prim 130.00 23.87 3.75 27.62 200000
select_key_prefix 76.00 22.63 3.41 26.04 200000
select_key_prefix_join 2.00 1.74 0.03 1.77 100
select_key_return_key 112.00 22.44 3.57 26.01 200000
select_many_fields 2.00 1.92 0.29 2.21 2000
select_range 5.00 4.13 0.05 4.18 410
select_range_key2 2.00 2.11 0.24 2.35 25010
select_range_prefix 3.00 2.08 0.26 2.34 25010
select_simple 8.00 2.41 1.23 3.64 100000
select_simple_cache 8.00 2.37 1.14 3.51 100000
select_simple_join 1.00 0.24 0.01 0.25 500
update_big 33.00 0.00 0.00 0.00 10
update_of_key 13.00 1.41 0.65 2.06 50000
update_of_key_big 23.00 0.02 0.01 0.03 501
update_of_primary_key_many_keys 42.00 0.02 0.01 0.03 256
update_with_key 51.00 8.36 4.05 12.41 300000
update_with_key_prefix 20.00 4.73 1.42 6.15 100000
wisc_benchmark 1.00 1.14 0.10 1.24 114
TOTALS 2588.00 322.31 56.52 378.83 3425950
sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=10000000 --mysql-user=root --mysql-socket=/opt/mysql_5_1_45/innbase/mysql.sock --num-threads=200 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: 200
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: 140014
write: 50005
other: 20002
total: 210021
transactions: 10001 (429.61 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190019 (8162.54 per sec.)
other operations: 20002 (859.21 per sec.)
Test execution summary:
total time: 23.2794s
total number of events: 10001
total time taken by event execution: 4629.2326
per-request statistics:
min: 3.59ms
avg: 462.88ms
max: 1610.34ms
approx. 95 percentile: 861.49ms
Threads fairness:
events (avg/stddev): 50.0050/2.96
execution time (avg/stddev): 23.1462/0.09
MySQL 5.1 innodb_plugin 1.0.6
Benchmark DBD suite: 2.15
Date of test: 2012-04-11 21:45:25
Running tests on: Linux 2.6.32-220.4.2.el6.x86_64 x86_64
Arguments: --socket=/opt/mysql_5_1_45/plugin/mysql.sock
Comments:
Limits from:
Server version: MySQL 5.1.45
Optimization: None
Hardware:
alter-table: Total time: 42 wallclock secs ( 0.02 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.04 CPU)
ATIS: Total time: 6 wallclock secs ( 3.81 usr 0.21 sys + 0.00 cusr 0.00 csys = 4.02 CPU)
big-tables: Total time: 6 wallclock secs ( 1.73 usr 0.30 sys + 0.00 cusr 0.00 csys = 2.03 CPU)
connect: Total time: 89 wallclock secs (25.75 usr 9.94 sys + 0.00 cusr 0.00 csys = 35.69 CPU)
create: Total time: 1293 wallclock secs ( 1.18 usr 0.35 sys + 0.00 cusr 0.00 csys = 1.53 CPU)
insert: Total time: 1022 wallclock secs (231.41 usr 34.48 sys + 0.00 cusr 0.00 csys = 265.89 CPU)
select: Total time: 66 wallclock secs (18.57 usr 1.99 sys + 0.00 cusr 0.00 csys = 20.56 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 7 wallclock secs ( 1.92 usr 0.46 sys + 0.00 cusr 0.00 csys = 2.38 CPU)
All 9 test executed successfully
Totals per operation:
Operation seconds usr sys cpu tests
alter_table_add 23.00 0.00 0.00 0.00 100
alter_table_drop 18.00 0.00 0.00 0.00 91
connect 6.00 3.58 1.15 4.73 10000
connect+select_1_row 10.00 3.64 1.41 5.05 10000
connect+select_simple 8.00 3.72 1.22 4.94 10000
count 8.00 0.01 0.00 0.01 100
count_distinct 0.00 0.08 0.00 0.08 1000
count_distinct_2 0.00 0.06 0.01 0.07 1000
count_distinct_big 4.00 2.95 0.03 2.98 120
count_distinct_group 0.00 0.47 0.02 0.49 1000
count_distinct_group_on_key 0.00 0.07 0.01 0.08 1000
count_distinct_group_on_key_parts 1.00 0.25 0.01 0.26 1000
count_distinct_key_prefix 0.00 0.07 0.01 0.08 1000
count_group_on_key_parts 0.00 0.24 0.01 0.25 1000
count_on_key 9.00 3.26 0.47 3.73 50100
create+drop 185.00 0.17 0.05 0.22 10000
create_MANY_tables 203.00 0.12 0.03 0.15 10000
create_index 1.00 0.00 0.00 0.00 8
create_key+drop 833.00 0.40 0.09 0.49 10000
create_table 2.00 0.00 0.00 0.00 31
delete_all_many_keys 8.00 0.00 0.00 0.00 1
delete_big 0.00 0.00 0.00 0.00 1
delete_big_many_keys 8.00 0.00 0.00 0.00 128
delete_key 2.00 0.27 0.12 0.39 10000
delete_range 3.00 0.00 0.00 0.00 12
drop_index 0.00 0.00 0.00 0.00 8
drop_table 0.00 0.00 0.00 0.00 28
drop_table_when_MANY_tables 69.00 0.18 0.04 0.22 10000
insert 53.00 9.26 4.45 13.71 350768
insert_duplicates 11.00 2.72 1.10 3.82 100000
insert_key 69.00 3.88 1.39 5.27 100000
insert_many_fields 4.00 0.15 0.02 0.17 2000
insert_select_1_key 5.00 0.00 0.00 0.00 1
insert_select_2_keys 6.00 0.00 0.00 0.00 1
min_max 3.00 0.01 0.00 0.01 60
min_max_on_key 15.00 7.04 1.20 8.24 85000
multiple_value_insert 2.00 0.15 0.01 0.16 100000
once_prepared_select 24.00 4.96 1.37 6.33 100000
order_by_big 8.00 7.97 0.09 8.06 10
order_by_big_key 8.00 6.98 0.10 7.08 10
order_by_big_key2 8.00 8.22 0.09 8.31 10
order_by_big_key_desc 9.00 8.19 0.19 8.38 10
order_by_big_key_diff 9.00 7.71 0.09 7.80 10
order_by_big_key_prefix 9.00 8.93 0.09 9.02 10
order_by_key2_diff 1.00 0.81 0.02 0.83 500
order_by_key_prefix 1.00 0.48 0.00 0.48 500
order_by_range 1.00 0.47 0.02 0.49 500
outer_join 1.00 0.00 0.00 0.00 10
outer_join_found 2.00 0.00 0.00 0.00 10
outer_join_not_found 0.00 0.00 0.00 0.00 500
outer_join_on_key 1.00 0.00 0.00 0.00 10
prepared_select 28.00 9.49 1.38 10.87 100000
select_1_row 14.00 2.62 1.01 3.63 100000
select_1_row_cache 4.00 1.85 0.89 2.74 100000
select_2_rows 15.00 2.72 1.07 3.79 100000
select_big 10.00 9.09 0.15 9.24 80
select_big_str 3.00 1.23 0.34 1.57 10000
select_cache 1.00 0.91 0.15 1.06 10000
select_cache2 41.00 1.01 0.14 1.15 10000
select_column+column 15.00 2.40 1.01 3.41 100000
select_diff_key 0.00 0.07 0.01 0.08 500
select_distinct 0.00 0.64 0.01 0.65 800
select_group 1.00 0.55 0.04 0.59 2911
select_group_when_MANY_tables 3.00 0.31 0.14 0.45 10000
select_join 0.00 0.24 0.00 0.24 100
select_key 89.00 19.80 2.91 22.71 200000
select_key2 102.00 20.64 3.06 23.70 200000
select_key2_return_key 100.00 19.96 3.10 23.06 200000
select_key2_return_prim 103.00 20.77 3.11 23.88 200000
select_key_prefix 66.00 20.46 2.95 23.41 200000
select_key_prefix_join 2.00 1.77 0.02 1.79 100
select_key_return_key 97.00 20.27 2.91 23.18 200000
select_many_fields 2.00 1.58 0.28 1.86 2000
select_range 3.00 3.23 0.06 3.29 410
select_range_key2 3.00 1.81 0.21 2.02 25010
select_range_prefix 3.00 1.87 0.20 2.07 25010
select_simple 7.00 2.01 0.93 2.94 100000
select_simple_cache 6.00 1.98 0.91 2.89 100000
select_simple_join 1.00 0.27 0.02 0.29 500
update_big 34.00 0.00 0.00 0.00 10
update_of_key 10.00 1.14 0.60 1.74 50000
update_of_key_big 17.00 0.02 0.01 0.03 501
update_of_primary_key_many_keys 34.00 0.01 0.01 0.02 256
update_with_key 49.00 7.31 3.64 10.95 300000
update_with_key_prefix 16.00 3.77 1.38 5.15 100000
wisc_benchmark 1.00 1.09 0.07 1.16 114
TOTALS 2531.00 280.36 47.63 327.99 3425950
sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=10000000 --mysql-user=root --mysql-socket=/opt/mysql_5_1_45/plugin/mysql.sock --num-threads=200 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: 200
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 (442.48 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (8407.13 per sec.)
other operations: 20000 (884.96 per sec.)
Test execution summary:
total time: 22.5999s
total number of events: 10000
total time taken by event execution: 4499.6869
per-request statistics:
min: 2.99ms
avg: 449.97ms
max: 2430.56ms
approx. 95 percentile: 795.08ms
Threads fairness:
events (avg/stddev): 50.0000/2.58
execution time (avg/stddev): 22.4984/0.05
MySQL 5.1 innodb_plugin 1.0.6 + tcmalloc
Benchmark DBD suite: 2.15
Date of test: 2012-04-18 19:48:41
Running tests on: Linux 2.6.32-220.4.2.el6.x86_64 x86_64
Arguments: --socket=/opt/mysql_5_1_45/plugin/mysql.sock
Comments:
Limits from:
Server version: MySQL 5.1.45
Optimization: None
Hardware:
alter-table: Total time: 36 wallclock secs ( 0.04 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.05 CPU)
ATIS: Total time: 6 wallclock secs ( 3.56 usr 0.20 sys + 0.00 cusr 0.00 csys = 3.76 CPU)
big-tables: Total time: 5 wallclock secs ( 1.83 usr 0.29 sys + 0.00 cusr 0.00 csys = 2.12 CPU)
connect: Total time: 73 wallclock secs (20.71 usr 8.07 sys + 0.00 cusr 0.00 csys = 28.78 CPU)
create: Total time: 1202 wallclock secs ( 1.35 usr 0.43 sys + 0.00 cusr 0.00 csys = 1.78 CPU)
insert: Total time: 851 wallclock secs (195.64 usr 29.37 sys + 0.00 cusr 0.00 csys = 225.01 CPU)
select: Total time: 57 wallclock secs (16.89 usr 2.07 sys + 0.00 cusr 0.00 csys = 18.96 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 7 wallclock secs ( 1.75 usr 0.46 sys + 0.00 cusr 0.00 csys = 2.21 CPU)
All 9 test executed successfully
Totals per operation:
Operation seconds usr sys cpu tests
alter_table_add 18.00 0.01 0.00 0.01 100
alter_table_drop 17.00 0.01 0.00 0.01 91
connect 4.00 2.37 0.79 3.16 10000
connect+select_1_row 9.00 3.96 1.37 5.33 10000
connect+select_simple 4.00 2.13 0.75 2.88 10000
count 6.00 0.02 0.01 0.03 100
count_distinct 0.00 0.08 0.01 0.09 1000
count_distinct_2 0.00 0.08 0.01 0.09 1000
count_distinct_big 2.00 2.07 0.02 2.09 120
count_distinct_group 0.00 0.32 0.02 0.34 1000
count_distinct_group_on_key 1.00 0.09 0.02 0.11 1000
count_distinct_group_on_key_parts 0.00 0.31 0.01 0.32 1000
count_distinct_key_prefix 0.00 0.06 0.02 0.08 1000
count_group_on_key_parts 0.00 0.36 0.02 0.38 1000
count_on_key 9.00 3.84 0.62 4.46 50100
create+drop 176.00 0.24 0.08 0.32 10000
create_MANY_tables 195.00 0.14 0.04 0.18 10000
create_index 1.00 0.00 0.00 0.00 8
create_key+drop 757.00 0.46 0.09 0.55 10000
create_table 0.00 0.00 0.00 0.00 31
delete_all_many_keys 6.00 0.00 0.00 0.00 1
delete_big 0.00 0.00 0.00 0.00 1
delete_big_many_keys 5.00 0.00 0.00 0.00 128
delete_key 2.00 0.25 0.13 0.38 10000
delete_range 2.00 0.00 0.00 0.00 12
drop_index 0.00 0.00 0.00 0.00 8
drop_table 1.00 0.00 0.00 0.00 28
drop_table_when_MANY_tables 71.00 0.24 0.07 0.31 10000
insert 47.00 8.32 3.98 12.30 350768
insert_duplicates 10.00 2.21 0.87 3.08 100000
insert_key 66.00 3.75 1.30 5.05 100000
insert_many_fields 3.00 0.12 0.03 0.15 2000
insert_select_1_key 4.00 0.00 0.00 0.00 1
insert_select_2_keys 5.00 0.00 0.00 0.00 1
min_max 3.00 0.01 0.00 0.01 60
min_max_on_key 13.00 6.29 1.09 7.38 85000
multiple_value_insert 1.00 0.14 0.02 0.16 100000
once_prepared_select 18.00 3.96 1.02 4.98 100000
order_by_big 7.00 6.91 0.06 6.97 10
order_by_big_key 7.00 6.68 0.10 6.78 10
order_by_big_key2 6.00 5.78 0.07 5.85 10
order_by_big_key_desc 6.00 5.99 0.10 6.09 10
order_by_big_key_diff 7.00 6.66 0.08 6.74 10
order_by_big_key_prefix 7.00 6.45 0.08 6.53 10
order_by_key2_diff 1.00 0.84 0.02 0.86 500
order_by_key_prefix 0.00 0.34 0.00 0.34 500
order_by_range 1.00 0.41 0.01 0.42 500
outer_join 0.00 0.00 0.00 0.00 10
outer_join_found 1.00 0.00 0.00 0.00 10
outer_join_not_found 1.00 0.00 0.00 0.00 500
outer_join_on_key 1.00 0.00 0.00 0.00 10
prepared_select 22.00 7.75 1.17 8.92 100000
select_1_row 12.00 2.20 0.88 3.08 100000
select_1_row_cache 3.00 1.35 0.76 2.11 100000
select_2_rows 15.00 2.44 0.97 3.41 100000
select_big 7.00 6.33 0.08 6.41 80
select_big_str 3.00 1.23 0.35 1.58 10000
select_cache 1.00 0.88 0.14 1.02 10000
select_cache2 34.00 0.81 0.11 0.92 10000
select_column+column 13.00 2.13 0.91 3.04 100000
select_diff_key 0.00 0.05 0.01 0.06 500
select_distinct 1.00 0.61 0.01 0.62 800
select_group 0.00 0.51 0.04 0.55 2911
select_group_when_MANY_tables 3.00 0.27 0.15 0.42 10000
select_join 0.00 0.18 0.01 0.19 100
select_key 76.00 17.71 2.61 20.32 200000
select_key2 83.00 17.82 2.61 20.43 200000
select_key2_return_key 83.00 17.82 2.55 20.37 200000
select_key2_return_prim 89.00 19.19 2.77 21.96 200000
select_key_prefix 52.00 16.55 2.38 18.93 200000
select_key_prefix_join 2.00 1.71 0.01 1.72 100
select_key_return_key 79.00 17.49 2.56 20.05 200000
select_many_fields 2.00 1.71 0.26 1.97 2000
select_range 3.00 2.54 0.04 2.58 410
select_range_key2 2.00 1.47 0.14 1.61 25010
select_range_prefix 3.00 1.88 0.22 2.10 25010
select_simple 5.00 1.53 0.68 2.21 100000
select_simple_cache 5.00 1.37 0.61 1.98 100000
select_simple_join 0.00 0.23 0.01 0.24 500
update_big 25.00 0.00 0.00 0.00 10
update_of_key 9.00 1.02 0.55 1.57 50000
update_of_key_big 17.00 0.01 0.00 0.01 501
update_of_primary_key_many_keys 32.00 0.02 0.00 0.02 256
update_with_key 40.00 6.60 3.32 9.92 300000
update_with_key_prefix 11.00 2.61 0.96 3.57 100000
wisc_benchmark 1.00 0.98 0.06 1.04 114
TOTALS 2234.00 238.90 40.84 279.74 3425950
sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=10000000 --mysql-user=root --mysql-socket=/opt/mysql_5_1_45/plugin/mysql.sock --num-threads=200 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: 200
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 (535.97 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (10183.52 per sec.)
other operations: 20000 (1071.95 per sec.)
Test execution summary:
total time: 18.6576s
total number of events: 10000
total time taken by event execution: 3703.9368
per-request statistics:
min: 1.94ms
avg: 370.39ms
max: 1449.35ms
approx. 95 percentile: 658.23ms
Threads fairness:
events (avg/stddev): 50.0000/1.78
execution time (avg/stddev): 18.5197/0.07
MySQL 5.5 innodb 1.1 + tcmalloc
Benchmark DBD suite: 2.15
Date of test: 2012-04-19 10:01:56
Running tests on: Linux 2.6.32-220.4.2.el6.x86_64 x86_64
Arguments: --socket=/opt/mysql5_5_22/mysql.sock
Comments:
Limits from:
Server version: MySQL 5.5.22
Optimization: None
Hardware:
alter-table: Total time: 46 wallclock secs ( 0.04 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.05 CPU)
ATIS: Total time: 7 wallclock secs ( 4.19 usr 0.28 sys + 0.00 cusr 0.00 csys = 4.47 CPU)
big-tables: Total time: 7 wallclock secs ( 1.98 usr 0.33 sys + 0.00 cusr 0.00 csys = 2.31 CPU)
connect: Total time: 113 wallclock secs (30.78 usr 12.72 sys + 0.00 cusr 0.00 csys = 43.50 CPU)
create: Total time: 1629 wallclock secs ( 2.32 usr 0.60 sys + 0.00 cusr 0.00 csys = 2.92 CPU)
insert: Total time: 1213 wallclock secs (284.13 usr 41.38 sys + 0.00 cusr 0.00 csys = 325.51 CPU)
select: Total time: 96 wallclock secs (24.98 usr 3.10 sys + 0.00 cusr 0.00 csys = 28.08 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 8 wallclock secs ( 2.04 usr 0.54 sys + 0.00 cusr 0.00 csys = 2.58 CPU)
All 9 test executed successfully
Totals per operation:
Operation seconds usr sys cpu tests
alter_table_add 23.00 0.00 0.00 0.00 100
alter_table_drop 21.00 0.00 0.00 0.00 91
connect 7.00 3.78 1.25 5.03 10000
connect+select_1_row 11.00 4.12 1.61 5.73 10000
connect+select_simple 8.00 3.82 1.43 5.25 10000
count 12.00 0.01 0.01 0.02 100
count_distinct 0.00 0.12 0.02 0.14 1000
count_distinct_2 1.00 0.11 0.02 0.13 1000
count_distinct_big 6.00 3.61 0.06 3.67 120
count_distinct_group 1.00 0.50 0.01 0.51 1000
count_distinct_group_on_key 0.00 0.14 0.02 0.16 1000
count_distinct_group_on_key_parts 0.00 0.47 0.03 0.50 1000
count_distinct_key_prefix 0.00 0.13 0.01 0.14 1000
count_group_on_key_parts 1.00 0.48 0.02 0.50 1000
count_on_key 14.00 5.22 0.94 6.16 50100
create+drop 348.00 0.62 0.14 0.76 10000
create_MANY_tables 189.00 0.17 0.04 0.21 10000
create_index 1.00 0.00 0.00 0.00 8
create_key+drop 932.00 0.87 0.19 1.06 10000
create_table 1.00 0.00 0.00 0.00 31
delete_all_many_keys 16.00 0.00 0.00 0.00 1
delete_big 0.00 0.01 0.00 0.01 1
delete_big_many_keys 16.00 0.00 0.00 0.00 128
delete_key 2.00 0.29 0.14 0.43 10000
delete_range 3.00 0.00 0.00 0.00 12
drop_index 0.00 0.01 0.00 0.01 8
drop_table 0.00 0.00 0.00 0.00 28
drop_table_when_MANY_tables 157.00 0.31 0.09 0.40 10000
insert 61.00 9.65 4.90 14.55 350768
insert_duplicates 15.00 3.25 1.36 4.61 100000
insert_key 53.00 4.05 1.45 5.50 100000
insert_many_fields 4.00 0.13 0.02 0.15 2000
insert_select_1_key 4.00 0.00 0.00 0.00 1
insert_select_2_keys 5.00 0.00 0.00 0.00 1
min_max 4.00 0.01 0.00 0.01 60
min_max_on_key 18.00 8.93 1.61 10.54 85000
multiple_value_insert 2.00 0.15 0.01 0.16 100000
once_prepared_select 28.00 5.74 1.66 7.40 100000
order_by_big 11.00 10.77 0.12 10.89 10
order_by_big_key 12.00 11.26 0.19 11.45 10
order_by_big_key2 12.00 10.99 0.12 11.11 10
order_by_big_key_desc 12.00 11.29 0.17 11.46 10
order_by_big_key_diff 11.00 10.98 0.14 11.12 10
order_by_big_key_prefix 11.00 10.99 0.12 11.11 10
order_by_key2_diff 2.00 1.01 0.02 1.03 500
order_by_key_prefix 1.00 0.56 0.01 0.57 500
order_by_range 1.00 0.55 0.01 0.56 500
outer_join 1.00 0.00 0.00 0.00 10
outer_join_found 2.00 0.00 0.00 0.00 10
outer_join_not_found 1.00 0.00 0.00 0.00 500
outer_join_on_key 1.00 0.01 0.00 0.01 10
prepared_select 34.00 11.41 1.63 13.04 100000
select_1_row 19.00 3.36 1.43 4.79 100000
select_1_row_cache 6.00 2.62 1.32 3.94 100000
select_2_rows 20.00 3.28 1.37 4.65 100000
select_big 11.00 11.01 0.15 11.16 80
select_big_str 4.00 1.52 0.50 2.02 10000
select_cache 1.00 0.99 0.20 1.19 10000
select_cache2 62.00 1.43 0.20 1.63 10000
select_column+column 20.00 3.14 1.46 4.60 100000
select_diff_key 0.00 0.07 0.01 0.08 500
select_distinct 1.00 0.79 0.02 0.81 800
select_group 2.00 0.61 0.06 0.67 2911
select_group_when_MANY_tables 3.00 0.35 0.14 0.49 10000
select_join 0.00 0.22 0.01 0.23 100
select_key 110.00 24.36 3.72 28.08 200000
select_key2 123.00 24.87 3.81 28.68 200000
select_key2_return_key 122.00 24.10 3.82 27.92 200000
select_key2_return_prim 122.00 24.47 3.78 28.25 200000
select_key_prefix 80.00 24.10 3.70 27.80 200000
select_key_prefix_join 2.00 1.91 0.02 1.93 100
select_key_return_key 117.00 24.08 3.78 27.86 200000
select_many_fields 2.00 1.85 0.30 2.15 2000
select_range 5.00 4.15 0.07 4.22 410
select_range_key2 3.00 2.27 0.27 2.54 25010
select_range_prefix 3.00 2.22 0.28 2.50 25010
select_simple 9.00 2.50 1.20 3.70 100000
select_simple_cache 8.00 2.64 1.14 3.78 100000
select_simple_join 0.00 0.29 0.00 0.29 500
update_big 33.00 0.00 0.00 0.00 10
update_of_key 13.00 1.51 0.74 2.25 50000
update_of_key_big 26.00 0.01 0.01 0.02 501
update_of_primary_key_many_keys 43.00 0.02 0.00 0.02 256
update_with_key 56.00 8.44 4.12 12.56 300000
update_with_key_prefix 21.00 4.88 1.53 6.41 100000
wisc_benchmark 1.00 1.14 0.08 1.22 114
TOTALS 3124.00 345.72 58.81 404.53 3425950
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 200
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 (384.75 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (7310.34 per sec.)
other operations: 20000 (769.51 per sec.)
Test execution summary:
total time: 25.9906s
total number of events: 10000
total time taken by event execution: 5175.9165
per-request statistics:
min: 4.39ms
avg: 517.59ms
max: 1197.76ms
approx. 95 percentile: 732.47ms
Threads fairness:
events (avg/stddev): 50.0000/1.52
execution time (avg/stddev): 25.8796/0.06
配置文件:很多位置信息都被隐掉了
[client] [mysql] prompt=mysql--\\u@\\h:\\d \\R:\\m:\\s>> skip-reconnect [mysqld] character-set-server=latin1 character_set_client=latin1 skip-character-set-client-handshake skip-external-locking key_buffer_size = 4M max_allowed_packet = 128M table_cache = 400 join_buffer_size = 8M sort_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 128M query_cache_size = 32M query_cache_limit = 16M max_tmp_tables = 128 tmp_table_size = 64M max_heap_table_size = 32M thread_cache = 16 #thread_concurrency = 8 max_connections = 300 max_user_connections =259 max_connect_errors = 99999999 #wait_timeout = 60 wait_timeout =2880000 #interactive_timeout = 60 interactive_timeout = 2880000 back_log = 50 myisam_repair_threads = 1 myisam-recover = DEFAULT #binlog_format = STATEMENT #skip-slave-start old_passwords=1 bulk_insert_buffer_size = 16M default-storage-engine = innodb innodb_file_per_table = 1 innodb_buffer_pool_size = 2G innodb_log_file_size = 128M innodb_log_files_in_group = 3 innodb_log_buffer_size =16M #innodb_lock_wait_timeout= 120 innodb_lock_wait_timeout = 60 innodb_thread_concurrency = 32 innodb_additional_mem_pool_size = 8M #sync_binlog = 0 #log-slave-updates skip-name-resolve innodb_support_xa = 0 [client] user = password = no-auto-rehash port = socket = [myisamchk] key_buffer = 256M sort_buffer_size = 128M read_buffer = 8M write_buffer = 8M
分享到：
发表评论
相关推荐
在测试环境下没有设置过多的详细参数就初始化并启动了服务，后期优化的过程中发现innodb_data_file_path设置过小： root@node1 14:59: [(none)]> show variables like '%innodb_data_file_path%'; +
mysql 5.6 新特性 innodb
InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小： mysql> show variables like 'innodb_page_size'; 而系统一个磁盘块的...
内容包括： 1.MySQL的Innodb引擎配置 1.如何配置MySQL服务器的最大连接数量 2.如何配置innodb_open_files,table_open_cache,innodb_file_io_threads和innodb_buffer_pool_size，innodb_log_file_size
MySQL理论上使用的内存 = 全局共享内存 + max_connections×线程独享内存。 也就是：innodb_buffer_pool_size + innodb_log_buffer_size + thread_cache_size +table_open_cache + table_definition_cache +key_...
最近在学习MySQL技术内幕 InnoDB存储引擎 第2版，整理了一些文档分享出来，同时也方便以后查看。若有不当之处，烦请批评指正。 1. MySQL体系结构和存储引擎 2. InnoDB存储引擎 2.1 InnoDB体系结构 2.2 ...
MySQL5.6版InnoDB深度介绍。
py_innodb_page_info工具为《INNODB存储引擎》作者姜承尧写的。 该工具用来分析表空间中的各页得类型和信息，用python编写。 网上多是python2版本的，这里给出python3版本的。
4> innodb_data_home_dir = /usr/local/mysql/var/存放innodb表引擎表空间的地方 5> innodb_data_file_path = ibdata1:10M:autoextend表空间的名字以8M增长，可以将其大小修改：例如50M 6> innodb_log_group_home_...
MySQL Innodb 参数详解与优化实践
MySQL内核_InnoDB存储引擎(卷1)_753281.pdf
mysql> show create table test_autoinc_lock\G *************************** 1. row *************************** Table: test_autoinc_lock Create Table: CREATE TABLE `test_autoinc_lock` ( `id` int(11) NOT...
innodb_plugin-1.0.6.tar.gz工具兼容mysql插件
MySQL InnoDB 源码实现分析
使用py_innodb_page_info 查看分析各种log以及data file，深入研究mysql的存储引擎底层原理与实现。 mysql innodb undo redo
MySQL-5.0.22(源码包)+5.1中文手册+innodb_plugin1.0.6文档
浅析在线调整 innodb_buffer_pool_size 作者：zhou mysql版本：5.7 先介绍一下 buffer pool: 在innodb存储引擎中数据访问以page为单位，page也是innodb管理数据库的最小磁盘单位，每个page的默认大小为16KB(可以通过...
Inside.MySQL_InnoDB.Storage.Engine_zh-CN[www.TopSage.com].part2
Inside.MySQL_InnoDB.Storage.Engine_zh-CN[www.TopSage.com].part1