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]
