Sunday, April 17, 2005

MySQL benchmark (ran 2005-08-17)

MySQL Benchmark
Hardware: AMD Athlon64 3000+ 512MB software raid-0 on 2 WDD 120 gig HDD (partition RAID)
my.cnf as follows:

[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
set-variable = key_buffer_size=32M
set-variable = join_buffer_size=32M
set-variable = max_allowed_packet=2M
set-variable = table_cache=256K
set-variable = sort_buffer_size=24M
set-variable = net_buffer_length=256K
set-variable = myisam_sort_buffer_size=8M
set-variable = read_buffer_size=4M
set-variable = long_query_time=5
set-variable = thread_cache_size=1M
set-variable = read_rnd_buffer_size=8M
set-variable = tmp_table_size=256M
set-variable = record_buffer=1M
set-variable = log-slow-queries=/var/log/mysql_slow_queries.log
set-variable = long_query_time=3
set-variable = query_cache_type=1
set-variable = query_cache_size=32M
set-variable = query_cache_limit=4M
set-variable = max_connections=200
skip-innodb

[mysql]
prompt="\\d\\R\\m>"

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
#err-log=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

[root@localhost sql-bench]# perl run-all-tests
Benchmark DBD suite: 2.15
Date of test: 2004-08-17 14:04:30
Running tests on: Linux 2.6.7-1.478 x86_64
Arguments:
Comments:
Limits from:
Server version: MySQL 4.1.3 beta standard log
Optimization: None
Hardware:

alter-table:
Total time: 20 wallclock secs ( 0.04 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.06 CPU)
ATIS: Total time: 6 wallclock secs ( 4.16 usr 0.83 sys + 0.00 cusr 0.00 csys = 4.99 CPU)
big-tables: Total time: 7 wallclock secs ( 1.89 usr 1.58 sys + 0.00 cusr 0.00 csys = 3.47 CPU)
connect: Total time: 80 wallclock secs (23.86 usr 10.12 sys + 0.00 cusr 0.00 csys = 33.98 CPU)
create: Total time: 108 wallclock secs ( 1.21 usr 0.41 sys + 0.00 cusr 0.00 csys = 1.62 CPU)
insert:

Total time: 1277 wallclock secs (279.28 usr 49.60 sys + 0.00 cusr 0.00 csys = 328.88 CPU)
select: Total time: 56 wallclock secs (21.93 usr 3.56 sys + 0.00 cusr 0.00 csys = 25.49 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 4 wallclock secs ( 1.63 usr 0.43 sys + 0.00 cusr 0.00 csys = 2.06 CPU)

All 9 test executed successfully

Totals per operation:
Operation seconds usr sys cpu tests
alter_table_add 8.00 0.01 0.00 0.01 100
alter_table_drop 9.00 0.02 0.00 0.02 91
connect 5.00 2.63 0.52 3.15 10000
connect+select_1_row 8.00 3.69 0.75 4.44 10000
connect+select_simple 6.00 3.19 0.66 3.85 10000
count 7.00 0.03 0.01 0.04 100
count_distinct 0.00 0.08 0.01 0.09 1000
count_distinct_2 0.00 0.09 0.01 0.10 1000
count_distinct_big 5.00 3.75 0.75 4.50 120
count_distinct_group 1.00 0.48 0.08 0.56 1000
count_distinct_group_on_key 1.00 0.11 0.02 0.13 1000
count_distinct_group_on_key_parts 0.00 0.46 0.07 0.53 1000
count_distinct_key_prefix 0.00 0.09 0.02 0.11 1000
count_group_on_key_parts 0.00 0.45 0.08 0.53 1000
count_on_key 12.00 3.95 0.54 4.49 50100
create+drop 29.00 0.15 0.03 0.18 10000
create_MANY_tables 21.00 0.03 0.00 0.03 10000
create_index 2.00 0.00 0.00 0.00 8
create_key+drop 31.00 0.18 0.11 0.29 10000
create_table 0.00 0.00 0.00 0.00 31
delete_all_many_keys 32.00 0.01 0.00 0.01 1
delete_big 0.00 0.00 0.00 0.00 1
delete_big_many_keys 32.00 0.01 0.00 0.01 128
delete_key 1.00 0.15 0.09 0.24 10000
delete_range 3.00 0.00 0.00 0.00 12
drop_index 1.00 0.00 0.00 0.00 8
drop_table 0.00 0.00 0.00 0.00 28
drop_table_when_MANY_tables 3.00 0.16 0.09 0.25 10000
insert 31.00 5.14 2.84 7.98 350768
insert_duplicates 8.00 1.58 0.84 2.42 100000
insert_key 39.00 3.00 1.16 4.16 100000
insert_many_fields 3.00 0.21 0.06 0.27 2000
insert_select_1_key 2.00 0.00 0.00 0.00 1
insert_select_2_keys 3.00 0.00 0.00 0.00 1
min_max 2.00 0.02 0.00 0.02 60
min_max_on_key 17.00 7.10 0.99 8.09 85000
multiple_value_insert 2.00 0.20 0.03 0.23 100000
once_prepared_select 16.00 3.43 1.38 4.81 100000
order_by_big 14.00 10.84 2.38 13.22 10
order_by_big_key 16.00 11.15 2.41 13.56 10
order_by_big_key2 13.00 10.78 2.39 13.17 10
order_by_big_key_desc 16.00 11.22 2.44 13.66 10
order_by_big_key_diff 14.00 10.95 2.35 13.30 10
order_by_big_key_prefix 14.00 10.86 2.26 13.12 10
order_by_key2_diff 2.00 0.97 0.18 1.15 500
order_by_key_prefix 1.00 0.53 0.10 0.63 500
order_by_range 2.00 0.61 0.09 0.70 500
outer_join 1.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 24.00 9.12 1.49 10.61 100000
select_1_row 10.00 1.78 1.34 3.12 100000
select_1_row_cache 4.00 1.36 1.23 2.59 100000
select_2_rows 12.00 1.81 1.46 3.27 100000
select_big 14.00 10.88 2.34 13.22 80
select_big_str 13.00 4.72 0.76 5.48 10000
select_cache 1.00 0.76 0.10 0.86 10000
select_cache2 27.00 1.85 0.16 2.01 10000
select_column+column 12.00 1.76 1.17 2.93 100000
select_diff_key 35.00 0.15 0.01 0.16 500
select_distinct 1.00 0.80 0.14 0.94 800
select_group 2.00 0.52 0.09 0.61 2911
select_group_when_MANY_tables 24.00 0.69 0.18 0.87 10000
select_join 0.00 0.24 0.04 0.28 100
select_key 146.00 27.12 3.51 30.63 200000
select_key2 164.00 28.05 3.45 31.50 200000
select_key2_return_key 160.00 27.43 3.08 30.51 200000
select_key2_return_prim 162.00 27.87 3.18 31.05 200000
select_key_prefix 80.00 23.80 3.15 26.95 200000
select_key_prefix_join 3.00 2.13 0.42 2.55 100
select_key_return_key 154.00 27.36 3.04 30.40 200000
select_many_fields 4.00 1.68 1.52 3.20 2000
select_range 7.00 4.22 0.85 5.07 410
select_range_key2 2.00 1.76 0.30 2.06 25010
select_range_prefix 2.00 1.78 0.33 2.11 25010
select_simple 5.00 1.53 1.11 2.64 100000
select_simple_cache 5.00 1.38 1.12 2.50 100000
select_simple_join 0.00 0.28 0.06 0.34 500
update_big 11.00 0.00 0.00 0.00 10
update_of_key 6.00 0.72 0.43 1.15 50000
update_of_key_big 5.00 0.01 0.01 0.02 501
update_of_primary_key_many_keys 18.00 0.01 0.01 0.02 256
update_with_key 24.00 4.00 2.59 6.59 300000
update_with_key_prefix 9.00 2.52 0.95 3.47 100000
wisc_benchmark 1.00 1.17 0.17 1.34 114
TOTALS 1583.00 329.57 65.53 395.10 3425950

No comments: