Re: default my.cnf vs huge.cnf nearly same performance with sql-bench/run-all-tests
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" > > 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
Re: default my.cnf vs huge.cnf nearly same performance with sql-bench/run-all-tests
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" 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: | --
Re: default my.cnf vs huge.cnf nearly same performance with sql-bench/run-all-tests
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" 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
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:
default my.cnf vs huge.cnf nearly same performance with sql-bench/run-all-tests
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 (1) | 6.00| 6.00| 6.00| 5.00| 5.00| 5.00| 28.00| connect+select_1_row (1)| 8.00| 8.00| 8.00| 7.00| 7.00| 7.00| 33.00| connect+select_simple (1) | 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) |
