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

[Daniel]

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