default my.cnf vs huge.cnf nearly same performance with sql-bench/run-all-tests

[Matthew Lenz]

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|
=============================================================================================