Default my.cnf for (very) high performance servers....
Hi All, We're setting up a group of servers using MySQL Enterprise 5.1 - Rather than starting with a blank canvas I wondered if there was a suitable my.cnf that is tuned to the kind of environment I'm running where I can tweak it from there. We're running on RHEL, on Sunfire X4140's - 8 disks, 16G RAM, 2 x dual core 3000mhz 64bit... which is reasonably beefy. Environment is more read than write, but write speed is important. Anyone know where I can look? Cheers Craig -- Linux web infrastructure consulting, cr...@codenation.net Free live poker tournament listings, http://www.g5poker.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Default my.cnf for (very) high performance servers....
Craig Dunn wrote: Hi All, We're setting up a group of servers using MySQL Enterprise 5.1 - Rather than starting with a blank canvas I wondered if there was a suitable my.cnf that is tuned to the kind of environment I'm running where I can tweak it from there. We're running on RHEL, on Sunfire X4140's - 8 disks, 16G RAM, 2 x dual core 3000mhz 64bit... which is reasonably beefy. Environment is more read than write, but write speed is important. Anyone know where I can look? Cheers Craig I should add, I wanted something a bit more up to date than my-huge.cnf, which seems to think a huge server is a system with memory of 1G-2G -- Linux web infrastructure consulting, cr...@codenation.net Free live poker tournament listings, http://www.g5poker.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Default my.cnf for (very) high performance servers....
There's no such thing as a generic my.cnf for high performance MySQL servers, you will need to provide more information.. Some questions: Are you going to run InnoDB or MyISAM or both (if both, what's the split?) Is there anything else running on that server? i.e. how much of the 16GB is available for MySQL to use? Can you partition your disks as you wish? (How much data do you need host?) Will this server be a master or slave or standalone? (Do we need to deal with binlogs here?) Andrew -Original Message- From: Craig Dunn [mailto:li...@codenation.net] Sent: 06 May 2009 14:02 To: mysql@lists.mysql.com Subject: Re: Default my.cnf for (very) high performance servers Craig Dunn wrote: Hi All, We're setting up a group of servers using MySQL Enterprise 5.1 - Rather than starting with a blank canvas I wondered if there was a suitable my.cnf that is tuned to the kind of environment I'm running where I can tweak it from there. We're running on RHEL, on Sunfire X4140's - 8 disks, 16G RAM, 2 x dual core 3000mhz 64bit... which is reasonably beefy. Environment is more read than write, but write speed is important. Anyone know where I can look? Cheers Craig I should add, I wanted something a bit more up to date than my-huge.cnf, which seems to think a huge server is a system with memory of 1G-2G -- Linux web infrastructure consulting, cr...@codenation.net Free live poker tournament listings, http://www.g5poker.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.braithwa...@lovefilm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Default my.cnf for (very) high performance servers....
Andrew Braithwaite wrote: There's no such thing as a generic my.cnf for high performance MySQL servers, you will need to provide more information.. Well, I was more after something a bit more up to date than my-huge.cnf that I could use as a starting point, I see a few example ones posted to Mysql Forge, but they are very innodb orientated. Some questions: Are you going to run InnoDB or MyISAM or both (if both, what's the split?) Both, 90% MyISAM Is there anything else running on that server? i.e. how much of the 16GB is available for MySQL to use? It's a dedicated MySQL box Can you partition your disks as you wish? (How much data do you need host?) About 50G of databases - I've currently got 6 disks with RAID 10 running soley /var/lib/mysql (datadir) on an LVM with the binlogs being written to the other 2 disks (which has the OS on them too) Will this server be a master or slave or standalone? (Do we need to deal with binlogs here?) There are 3 in total, 1 master and 2 slaves (one of which is capable of being failed over to as a master) The current MySQL 4.1 servers that they are replacing have at any one time on average about 1000 open tables, about double the number of selects than inserts, between 2000 and 5000 qps - if thats any use. Cheers Craig -- Linux web infrastructure consulting, cr...@codenation.net Free live poker tournament listings, http://www.g5poker.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Default my.cnf for (very) high performance servers....
Your disk config is good and you'll need all the nessesary my.cnf entries to point all the logs and data to the correct place. Slaves should have the relay-logs going to the OS disk too. I assume you've set up the master slave config in the my.cnf too. Here's my brain dump on what you need: skip-locking max_allowed_packet = 16M key_buffer_size = 9000M max_allowed_packet = 16M table_cache = 1024 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 1000 query_cache_size = 256M # Nice big key_buffer_size - the most important one for read-heavy MyISAM DBs # query cache nice and high too - if your tables change a lot you may want to turn this off as it will be ineffective tmpdir = /somedir/tmp/ #You may want to point this somewhere else if you are writing a lot of tmp tables to disk innodb_data_home_dir = /somedir/mysql/ innodb_data_file_path = ibdata1:512M:autoextend innodb_log_group_home_dir = /somedir/mysql/ innodb_log_arch_dir = /somedir/mysql/ innodb_buffer_pool_size=1000M set-variable = innodb_additional_mem_pool_size=128M innodb_log_file_size=200M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 innodb_thread_concurrency = 8 innodb_file_per_table # Keep a GB of InnoDB in memory as you're not using that much # use the innodb_file_per_table param for easier management of disk space The most important part is your caches. You can keep an eye on your MyISAM key cache efficiency by running 'SHOW STATUS' and 'SHOW VARIABLES' and calculating the following: Cache hit ratio: 100 - ((Key_reads * 100) / Key_read_requests) Percentage of buffer in use: 100 - ((Key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size) And tweak them as you need. Of course you have to remember that these caches (and the filesystem cache) will take a while to warm up before they become super-efficient. Hope this helps, ANdrew -Original Message- From: Craig Dunn [mailto:li...@codenation.net] Sent: 06 May 2009 14:31 To: Andrew Braithwaite Cc: mysql@lists.mysql.com Subject: Re: Default my.cnf for (very) high performance servers Andrew Braithwaite wrote: There's no such thing as a generic my.cnf for high performance MySQL servers, you will need to provide more information.. Well, I was more after something a bit more up to date than my-huge.cnf that I could use as a starting point, I see a few example ones posted to Mysql Forge, but they are very innodb orientated. Some questions: Are you going to run InnoDB or MyISAM or both (if both, what's the split?) Both, 90% MyISAM Is there anything else running on that server? i.e. how much of the 16GB is available for MySQL to use? It's a dedicated MySQL box Can you partition your disks as you wish? (How much data do you need host?) About 50G of databases - I've currently got 6 disks with RAID 10 running soley /var/lib/mysql (datadir) on an LVM with the binlogs being written to the other 2 disks (which has the OS on them too) Will this server be a master or slave or standalone? (Do we need to deal with binlogs here?) There are 3 in total, 1 master and 2 slaves (one of which is capable of being failed over to as a master) The current MySQL 4.1 servers that they are replacing have at any one time on average about 1000 open tables, about double the number of selects than inserts, between 2000 and 5000 qps - if thats any use. Cheers Craig -- Linux web infrastructure consulting, cr...@codenation.net Free live poker tournament listings, http://www.g5poker.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Default my.cnf for (very) high performance servers....
Andrew Braithwaite wrote: Your disk config is good and you'll need all the nessesary my.cnf entries to point all the logs and data to the correct place. Slaves should have the relay-logs going to the OS disk too. I assume you've set up the master slave config in the my.cnf too. Yeah the replication and file location stuff is fine, I was after a rough idea of buffer sizes...etc, thanks for all that, most helpful.. Cheers Craig -- Linux web infrastructure consulting, cr...@codenation.net Free live poker tournament listings, http://www.g5poker.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Default my.cnf for (very) high performance servers....
At 07:56 AM 5/6/2009, you wrote: Hi All, We're setting up a group of servers using MySQL Enterprise 5.1 - Rather than starting with a blank canvas I wondered if there was a suitable my.cnf that is tuned to the kind of environment I'm running where I can tweak it from there. We're running on RHEL, on Sunfire X4140's - 8 disks, 16G RAM, 2 x dual core 3000mhz 64bit... which is reasonably beefy. Environment is more read than write, but write speed is important. Anyone know where I can look? Cheers Craig Take a look at High Performance MySQL 2nd Edition Chapter 6 - starting around page 265. Online: http://books.google.ca/books?id=BL0NNoFPuAQCprintsec=frontcoverdq=high+performance+mysql+baron+schwartz#PPA288,M1 Buy from: http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/0596101716/ref=sr_1_1?ie=UTF8s=booksqid=1241622110sr=8-1 Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
default my.cnf?
hi all... i have a 5.0.33 build from source on a freebsd 4.10 machine... i'm looking for a my.cnf file. ps tells me that the base dir is /usr/local but there is no my.cnf there. and i cant find one anywhere. i can get all the variables set up from the cli but i need to change some of them. i guess i can use mysqladmin but just wondering - can i just do a cnf under /usr/local/etc with the variables i need to change? ktrace is showing me that mysqld is not looking for any .cnf?! i mean i can try that but it's a very busy server and i wouldn't mess to much with it. thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default my.cnf?
I had the same problem. I found the distribution contains some prototypes, with slightly more elaborate names. Regards, Mike kalin m [EMAIL PROTECTED] 03/18/08 01:06 PM To mysql@lists.mysql.com cc Subject default my.cnf? hi all... i have a 5.0.33 build from source on a freebsd 4.10 machine... i'm looking for a my.cnf file. ps tells me that the base dir is /usr/local but there is no my.cnf there. and i cant find one anywhere. i can get all the variables set up from the cli but i need to change some of them. i guess i can use mysqladmin but just wondering - can i just do a cnf under /usr/local/etc with the variables i need to change? ktrace is showing me that mysqld is not looking for any .cnf?! i mean i can try that but it's a very busy server and i wouldn't mess to much with it. thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default my.cnf?
Hi, On Tue, Mar 18, 2008 at 1:06 PM, kalin m [EMAIL PROTECTED] wrote: hi all... i have a 5.0.33 build from source on a freebsd 4.10 machine... i'm looking for a my.cnf file. ps tells me that the base dir is /usr/local but there is no my.cnf there. and i cant find one anywhere. i can get all the variables set up from the cli but i need to change some of them. i guess i can use mysqladmin but just wondering - can i just do a cnf under /usr/local/etc with the variables i need to change? ktrace is showing me that mysqld is not looking for any .cnf?! i mean i can try that but it's a very busy server and i wouldn't mess to much with it. You can ask mysqld where it looks for its configuration files. Here's a Debian system: [EMAIL PROTECTED] ~ $ mysql --help --verbose | grep cnf /etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 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
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 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
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:
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 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
Re: default my.cnf vs huge.cnf nearly same performance with
Hello. 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? In the manual it is meant that thead_concurrency is used on Solaris. In the source files I've seen that thread_concurrency supported on those platforms which have thr_setconcurrency function. See: configure.in (AC_CHECK_FUNCS macros) http://dev.mysql.com/doc/mysql/en/server-system-variables.html Matthew Lenz [EMAIL PROTECTED] wrote: [-- text/plain, encoding 7bit, charset: US-ASCII, 61 lines --] 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 [-- text/plain, encoding 7bit, charset: UTF-8, 117 lines, name: results.txt --] 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)
Re: default my.cnf vs huge.cnf nearly same performance with
- Original Message - From: Gleb Paharenko [EMAIL PROTECTED] Hello. 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? In the manual it is meant that thead_concurrency is used on Solaris. In the source files I've seen that thread_concurrency supported on those platforms which have thr_setconcurrency function. See: configure.in (AC_CHECK_FUNCS macros) http://dev.mysql.com/doc/mysql/en/server-system-variables.html Makes sense. Its fine that its ignored. I'm still curious as to why I'm not seeing better performance from the config that give mysql more resources to play with :( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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) |
