Home / server / Questions / 7630
In Process
Darryl Hein
Asked: 2009-05-13 18:47:23 +0800 CST

What configuration options for MySQL provide the biggest speed improvements?

  • 772

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.

mysql optimization

5 Answers

  1. 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.
    • 20

  2. 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)

    • 11

  3. 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

    • 4

  4. 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

    • 3

  5. The first general thing you should do is look at the memory parameters. The default settings for MySQL are very very conservative. Whatever engine you use, you will probably need to raise a number of the memory parameters by ten or even a hundred-fold.

    The next thing you should do is look at the table cache. The default value is 64, which is only useful if you have no more than about 60 tables. You will want to raise that a long way.

    The third thing you should do is look at the thread and connection parameters. The default wait_timeout is hugely long for most web-based applications and can be reduced to something like 30 seconds. This will improve memory usage as well, as MySQL will reap connections sooner, leaving much less lying around in a 'sleep' state.

    • 1