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

[Matthew Lenz]

so the sql-bench stuff doesn't push the mysqld to its limits?

----- Original Message ----- 
From: "Daniel" <mysql@stripped>
To: "Matthew Lenz" <matthew@stripped>
Cc: "mysql" <mysql@stripped>
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|
>>=============================================================================================
>>
>>
>