To its benchmarking limits, yes. But benchmarks and real-world
performance almost
never closely parallel. If the purpose here is to set my.cnf optimally,
then it needs to
be set in accordance with your real usage patterns.
You probably won't see a big improvement in the benchmark between my.cnf
configs
because the benchmark is mostly generalized. If it were, however,
designed to benchmark
more specific things like a large spread of various queries on various
large indexes causing
MySQL to really use its key_buffer, then there would be a more
noticeable difference
between a 16M key_buffer and 384M.
-Daniel
Matthew Lenz wrote:
> 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|
>>>
> =============================================================================================
>
>>>
>>>
>>>
>>
>
>
