so the sql-bench stuff doesn't push the mysqld to its limits?
----- Original Message -----
From: "Daniel" <[EMAIL PROTECTED]>
To: "Matthew Lenz" <[EMAIL PROTECTED]>
Cc: "mysql" <mysql@lists.mysql.com>
Sent: Sunday, September 11, 2005 1:12 PM
Subject: Re: default my.cnf vs huge.cnf nearly same performance with
sql-bench/run-all-tests
I think it's a common misconception that MySQL will grow to meet the
settings in my.cnf.
That is, if you have 32M of actively used indexes, there is no difference
between key_buffer = 64M
and key_buffer = 512M. Similarly, if you have a need for 128 cached
tables, you'll gain
no benefit with table_cache = 1024. MySQL uses only what it needs to,
when it needs to.
Large values in my.cnf just set the upper limit. If operating below these
limits, no increase
in them will have affect. When you hit the upper limit, then these
settings can have a dramatic
affect. E.g., you have 32M of actively used indexes, but key_buffer = 16M;
this will probably
cause your key read ratio to exceed 0.01 as MySQL resorts to reading
indexes from the
hard drive, completely negating their usefulness.
In short, I would not worry about benchmarking defaults against
my-huge.cnf. Make my.cnf
fit your server, then optimize your queries.
-Daniel
Matthew Lenz wrote:
infact .. the default debian config (some of these are just explicit
defaults but this is what debian provides):
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
old_passwords = 1
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
query_cache_limit = 1048576
query_cache_size = 16777216
query_cache_type = 1
log-bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 104857600
skip-bdb
outperforms the huge.cnf example:
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
old_passwords = 1
key_buffer = 384M
max_allowed_packet = 16M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
query_cache_size = 32M
thread_concurrency = 8
log-bin = /var/log/mysql/mysql-bin.log
server-id = 1
skip-bdb
skip-external-locking
in almost every regard. What gives? :) This is a pretty beefy config:
dual 3ghz HT xeon .. 2gig 800mhz fsb mem .. U320 SCSI RAID5. I've
attached a compare-results for a few machines. the only important ones
are 1 and 2. 1 is debians my.cnf and 2 is the slightly modified
huge.cnf example. What about that thread_concurrency setting in
huge.cnf.. it doesn't seem to show up in a 'show variables' when using
it.. is it deprecated?
-Matt
------------------------------------------------------------------------
The result logs which where found and the options:
1 mysql-Linux_2.4.27_2_686_smp_i686 : MySQL 4.1.11 Debian_4sarge1
log
2 mysql-Linux_2.4.27_2_686_smp_i686_db0_te: MySQL 4.1.11 Debian_4sarge1
log
3 mysql-Linux_2.4.27_2_686_smp_i686_db1 : MySQL 4.1.11 Debian_4sarge1
log
4 mysql-Linux_2.6.10-1.770_FC3smp_i686 : MySQL 4.1.12 standard
5 mysql-Linux_2.6.10-1.770_FC3smp_i686_rai: MySQL 4.1.12 standard
6 mysql-Linux_2.6.11-1.14_FC3_x86_64 : MySQL 4.1.11 standard
7 mysql-Linux_2.6.8_2_686_smp_i686_kevinz : MySQL 4.1.11 Debian_4sarge1
log
=============================================================================================
Operation | 1| 2| 3| 4|
5| 6| 7|
|mysql-L|mysql-L|mysql-L|mysql-L|mysql-L|mysql-L|mysql-L|
---------------------------------------------------------------------------------------------
Results per test in seconds:
|
---------------------------------------------------------------------------------------------
ATIS | 8.00| 9.00| 8.00| 16.00|
17.00| 13.00| 32.00|
alter-table | 14.00| 14.00| 13.00| 13.00|
10.00| 21.00| 49.00|
big-tables | 10.00| 10.00| 10.00| 13.00|
12.00| 10.00| 36.00|
connect | 108.00| 105.00| 99.00| 72.00|
71.00| 58.00| 394.00|
create | 67.00| 89.00| 89.00| 223.00|
219.00| 98.00| 475.00|
insert | 904.00| 908.00| 873.00| 854.00|
845.00| 959.00|3751.00|
select | 76.00| 76.00| 73.00| 353.00|
351.00| 250.00| 291.00|
wisconsin | 7.00| 7.00| 7.00| 6.00|
5.00| 5.00| 20.00|
---------------------------------------------------------------------------------------------
The results per operation:
|
---------------------------------------------------------------------------------------------
alter_table_add (100) | 6.00| 6.00| 5.00| 5.00|
4.00| 9.00| 20.00|
alter_table_drop (91) | 6.00| 6.00| 6.00| 6.00|
4.00| 9.00| 18.00|
connect (10000) | 6.00| 6.00| 6.00| 5.00|
5.00| 5.00| 28.00|
connect+select_1_row (10000) | 8.00| 8.00| 8.00| 7.00|
7.00| 7.00| 33.00|
connect+select_simple (10000) | 8.00| 7.00| 8.00| 6.00|
6.00| 6.00| 32.00|
count (100) | 8.00| 9.00| 8.00| 9.00|
8.00| 6.00| 43.00|
count_distinct (1000) | 1.00| 0.00| 1.00| 11.00|
11.00| 6.00| 1.00|
count_distinct_2 (1000) | 0.00| 0.00| 0.00| 16.00|
15.00| 8.00| 0.00|
count_distinct_big (120) | 8.00| 8.00| 7.00| 19.00|
20.00| 14.00| 32.00|
count_distinct_group (1000) | 1.00| 1.00| 1.00| 14.00|
14.00| 7.00| 3.00|
count_distinct_group_on_key (1000) | 0.00| 0.00| 0.00| 15.00|
15.00| 8.00| 1.00|
count_distinct_group_on_key_parts (1| 1.00| 1.00| 1.00| 14.00|
13.00| 7.00| 3.00|
count_distinct_key_prefix (1000) | 0.00| 1.00| 0.00| 8.00|
8.00| 4.00| 0.00|
count_group_on_key_parts (1000) | 1.00| 0.00| 1.00| 15.00|
15.00| 7.00| 4.00|
count_on_key (50100) | 22.00| 23.00| 21.00| 130.00|
131.00| 86.00| 69.00|
create+drop (10000) | 13.00| 12.00| 12.00| 76.00|
71.00| 32.00| 43.00|
create_MANY_tables (10000) | 35.00| 56.00| 58.00| 72.00|
73.00| 27.00| 323.00|
create_index (8) | 1.00| 1.00| 1.00| 1.00|
1.00| 1.00| 5.00|
create_key+drop (10000) | 14.00| 14.00| 14.00| 72.00|
72.00| 35.00| 48.00|
create_table (31) | 0.00| 0.00| 0.00| 0.00|
1.00| 0.00| 0.00|
delete_all_many_keys (1) | 22.00| 21.00| 21.00| 31.00|
24.00| 134.00| 84.00|
delete_big (1) | 0.00| 0.00| 1.00| 0.00|
0.00| 0.00| 0.00|
delete_big_many_keys (128) | 22.00| 21.00| 21.00| 31.00|
24.00| 134.00| 84.00|
delete_key (10000) | 1.00| 1.00| 2.00| 1.00|
1.00| 0.00| 6.00|
delete_range (12) | 5.00| 5.00| 4.00| 4.00|
4.00| 4.00| 16.00|
drop_index (8) | 1.00| 1.00| 1.00| 1.00|
1.00| 2.00| 6.00|
drop_table (28) | 0.00| 0.00| 0.00| 0.00|
0.00| 0.00| 0.00|
drop_table_when_MANY_tables (10000) | 3.00| 4.00| 3.00| 2.00|
2.00| 2.00| 21.00|
insert (350768) | 55.00| 55.00| 52.00| 35.00|
36.00| 28.00| 149.00|
insert_duplicates (100000) | 12.00| 13.00| 11.00| 8.00|
8.00| 6.00| 35.00|
insert_key (100000) | 43.00| 44.00| 44.00| 37.00|
37.00| 240.00| 173.00|
insert_many_fields (2000) | 3.00| 3.00| 3.00| 3.00|
3.00| 3.00| 15.00|
insert_select_1_key (1) | 2.00| 2.00| 2.00| 2.00|
2.00| 2.00| 7.00|
insert_select_2_keys (1) | 2.00| 3.00| 2.00| 3.00|
2.00| 1.00| 11.00|
min_max (60) | 3.00| 3.00| 3.00| 6.00|
7.00| 4.00| 12.00|
min_max_on_key (85000) | 17.00| 17.00| 18.00| 16.00|
16.00| 11.00| 54.00|
multiple_value_insert (100000) | 1.00| 1.00| 2.00| 1.00|
1.00| 2.00| 5.00|
once_prepared_select (100000) | 24.00| 25.00| 24.00| 16.00|
14.00| 10.00| 99.00|
order_by_big (10) | 22.00| 22.00| 22.00| 24.00|
24.00| 18.00| 92.00|
order_by_big_key (10) | 21.00| 20.00| 19.00| 20.00|
20.00| 17.00| 87.00|
order_by_big_key2 (10) | 20.00| 19.00| 18.00| 20.00|
20.00| 15.00| 84.00|
order_by_big_key_desc (10) | 20.00| 21.00| 19.00| 21.00|
20.00| 17.00| 85.00|
order_by_big_key_diff (10) | 22.00| 21.00| 21.00| 22.00|
22.00| 17.00| 91.00|
order_by_big_key_prefix (10) | 20.00| 20.00| 19.00| 19.00|
20.00| 16.00| 81.00|
order_by_key2_diff (500) | 2.00| 3.00| 2.00| 2.00|
2.00| 2.00| 9.00|
order_by_key_prefix (500) | 1.00| 1.00| 1.00| 2.00|
2.00| 0.00| 5.00|
order_by_range (500) | 2.00| 1.00| 2.00| 1.00|
1.00| 2.00| 5.00|
outer_join (10) | 2.00| 2.00| 2.00| 19.00|
20.00| 13.00| 8.00|
outer_join_found (10) | 1.00| 2.00| 2.00| 19.00|
19.00| 12.00| 7.00|
outer_join_not_found (500) | 2.00| 1.00| 1.00| 13.00|
14.00| 8.00| 6.00|
outer_join_on_key (10) | 2.00| 1.00| 2.00| 16.00|
15.00| 9.00| 6.00|
prepared_select (100000) | 33.00| 32.00| 33.00| 23.00|
23.00| 16.00| 156.00|
select_1_row (100000) | 16.00| 16.00| 16.00| 9.00|
9.00| 6.00| 61.00|
select_1_row_cache (100000) | 9.00| 9.00| 8.00| 9.00|
8.00| 6.00| 16.00|
select_2_rows (100000) | 18.00| 17.00| 16.00| 10.00|
10.00| 7.00| 67.00|
select_big (80) | 20.00| 20.00| 18.00| 19.00|
21.00| 16.00| 82.00|
select_big_str (10000) | 6.00| 5.00| 5.00| 4.00|
4.00| 8.00| 54.00|
select_cache (10000) | 2.00| 2.00| 2.00| 36.00|
33.00| 23.00| 5.00|
select_cache2 (10000) | 30.00| 29.00| 28.00| 36.00|
34.00| 22.00| 142.00|
select_column+column (100000) | 18.00| 18.00| 16.00| 10.00|
10.00| 6.00| 64.00|
select_diff_key (500) | 35.00| 39.00| 35.00| 35.00|
35.00| 29.00| 187.00|
select_distinct (800) | 1.00| 2.00| 1.00| 4.00|
5.00| 3.00| 5.00|
select_group (2911) | 3.00| 3.00| 2.00| 17.00|
17.00| 11.00| 10.00|
select_group_when_MANY_tables (10000| 2.00| 3.00| 2.00| 1.00|
1.00| 2.00| 40.00|
select_join (100) | 0.00| 1.00| 0.00| 0.00|
1.00| 0.00| 1.00|
select_key (200000) | 60.00| 61.00| 58.00| 46.00|
44.00| 30.00| 268.00|
select_key2 (200000) | 63.00| 63.00| 60.00| 48.00|
46.00| 32.00| 288.00|
select_key2_return_key (200000) | 59.00| 60.00| 57.00| 43.00|
43.00| 30.00| 275.00|
select_key2_return_prim (200000) | 61.00| 62.00| 58.00| 46.00|
44.00| 31.00| 292.00|
select_key_prefix (200000) | 62.00| 62.00| 61.00| 47.00|
47.00| 33.00| 293.00|
select_key_prefix_join (100) | 4.00| 3.00| 4.00| 6.00|
5.00| 4.00| 16.00|
select_key_return_key (200000) | 59.00| 59.00| 57.00| 42.00|
42.00| 29.00| 264.00|
select_many_fields (2000) | 7.00| 7.00| 7.00| 10.00|
9.00| 7.00| 21.00|
select_range (410) | 8.00| 8.00| 8.00| 49.00|
52.00| 64.00| 33.00|
select_range_key2 (25010) | 4.00| 4.00| 3.00| 7.00|
7.00| 5.00| 12.00|
select_range_prefix (25010) | 5.00| 3.00| 5.00| 7.00|
7.00| 5.00| 12.00|
select_simple (100000) | 9.00| 10.00| 8.00| 6.00|
6.00| 3.00| 19.00|
select_simple_cache (100000) | 10.00| 9.00| 8.00| 6.00|
6.00| 4.00| 19.00|
select_simple_join (500) | 1.00| 0.00| 1.00| 1.00|
1.00| 2.00| 2.00|
update_big (10) | 18.00| 18.00| 18.00| 11.00|
12.00| 9.00| 30.00|
update_of_key (50000) | 9.00| 9.00| 9.00| 6.00|
8.00| 5.00| 37.00|
update_of_key_big (501) | 8.00| 9.00| 9.00| 8.00|
8.00| 5.00| 30.00|
update_of_primary_key_many_keys (256| 13.00| 13.00| 14.00| 17.00|
16.00| 34.00| 37.00|
update_with_key (300000) | 44.00| 43.00| 40.00| 29.00|
28.00| 21.00| 148.00|
update_with_key_prefix (100000) | 16.00| 16.00| 15.00| 11.00|
11.00| 8.00| 59.00|
wisc_benchmark (114) | 2.00| 2.00| 1.00| 2.00|
2.00| 2.00| 5.00|
---------------------------------------------------------------------------------------------
TOTALS
|1207.00|1229.00|1185.00|1557.00|1530.00|1531.00|5099.00|
=============================================================================================
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
