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
