What configuration options for MySQL provide the biggest speed improvements?

What configuration options for MySQL provide the biggest speed improvements?

I'm wondering about actual configuration file improvements, table types, hardware setups, replication, etc. Anything other than the query structure and table structure (these are easy to find on the website and Stack Overflow). Are things like query cache settings what gave you the most speed? How about drives; is it better to have it on an external RAID or internal? Did replication give you better performance, especially with read large queries?

What other settings/changes have you made to improve the performance of MySQL?

Note: I realize these are very usage dependent (that is, small website vs data warehouse), but as I think most of us probably work on a variety of sites/systems, it's good to know a variety of techniques that can apply to different situations. Also, I think some techniques can be transferred between situations.

Here are my recommendations (your millage may vary)

• Use hardware RAID. This goes contrary to my recommendations to use software RAID in other posts, however this is a specific situation where you want the hardware RAID card. Specifically you want the battery backed NVRAM on the RAID card to reduce the time to takes the fsync the log file to disk.
• Use ONLY RAID 1 or RAID 10 volumes. The cost of RAID 5 or 6 writes is too high to tolerate in a mixed read/write workload.
• Use separate LUNs for the data, log and tmp volumes. These should all be separate from the OS and swap volumes.
• Use InnoDB.
• Use innodb_file_per_table
• Use a 64-bit OS
• Set your InnoDB buffer pool to ~ 80% of your available RAM
• Set your log files to 1/4 of your buffer pool size, you between 2 to 4 log files. Larger log files mean slower shutdown and recovery times, but allow you to restore large database dumps faster.
• log_slow_queries, log-queries-not-using-indexes, set-variable = long_query_time=1, investigate every query in that log, refactor your schema to avoid table scans and tmp tables whenever possible.

Once again Dave Cheney really knocked it out of the park here. I really can't add anything to his answer to your question. However, I'd like to point out what you didn't ask. As Jeremy Zawodny and Peter Zaitsev taught me years ago, your ROI for time spent tracking down and optimizing bad queries is going to out perform your ROI for time spent making configuration changes 10 fold. Sure, you don't want to have a poor configuration, the wrong RAID setup, or insufficient RAM. But, among excellent, and even marginal, MySQL DBAs bad queries (usually from developers/frameworks, not the DBA) is a chronic condition, where bad configuration is an endurable one.

(I dug for those adjectives for a while and still am not pleased with the ones I chose.)

I'd like to stress again that if your developers are using an ORM like those common in frameworks like Ruby on Rails and Django, you REALLY MUST monitor the queries that hit your DB. When developers stop thinking about SQL and let the DB get abstracted away, really nasty this creep in. I love the two frameworks I just mentioned. (Don't vote me down for bad mouthing them.) It just makes Query Sleuthing very important. (Read: Job Security)

Few other things (which haven't been mentioned in Dave Cheney's answer)

• Try setting innodb_flush_method to O_DIRECT to avoid double buffering of data. Avoid this if your RAID card doesn't have a battery backed write cache or your data is on a SAN.

• Also play with the innodb_thread_concurrency. I believe the default is 8 but it's worth tweaking this to see if it improves performance

• Make sure query cache is turned on and check the stats to see what the hit rate is. If it's good try increasing it to see if it improves the hit rate.

• Depending on the applications run you might be able to change the default isolation level. The default is REPEATABLE_READ but READ_COMMITTED might give you better performance

• If your statements are mostly UPDATEs and DELETEs then you could try priming the cache on the slave by doing a SELECT query that returns the result set which is to be modified. Check out the mk-slave-prefetch tool which will do this for you

• Take a look at other storage engines apart from MyISAM and InnoDB

Can't say anything about hardware, but you might give http://blog.mysqltuner.com a try. It's a Perl script which analyzes your MySQL settings.

You can see a sample output at http://www.thomas-krenn.com/de/wiki/MySQL_Performance_Tuning#mysqltuner.pl