I guess I can understand that. I still think it blows that using huge.cnf vs. the standard config results in the huge.cnf showing worse benchmarking results. if what you are saying is correct (I don't doubt your statements in the slightest) I would have hoped to at least seen identical performance between the two. One of mysql's strong point has always been its ease of configuration (as far as I'm concerned anyway) it would be nice if tuning was similar. maybe some kind of built in tuning functionality that allows it to grow its needs inside the constraints of the servers physical memory or something. :)
On Mon, 2005-09-12 at 09:35 -0600, Daniel wrote: > 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" <[EMAIL PROTECTED]> > > To: "Matthew Lenz" <[EMAIL PROTECTED]> > > Cc: "mysql" <mysql@lists.mysql.com> > > 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| > >>> ============================================================================================= > >>> > >>> > >>> > >>> > >> > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
