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
