Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I'm looking for a succinct list of common pitfalls and optimizations for tuning a MySQL server as used for mid-size websites.

In general, the type of advice I'm looking for here is information that an ordinary developer or admin can easily implement that will give a measurable benefit to the performance of his site.

As an example

Here's a tip I picked up from reading High Performance MySQL that I frequently see use for:

When using the MyISAM storage engine (the default), the server will lock the entire table when performing DELETE or UPDATE operation, or when performing an INSERT which does not get appended to the end of the table (because there's a "hole" left from a previous DELETE). No other queries can use that table until the operation has finished.

Therefore, you should use InnoDB or other row-level-locking engines on any heavily-used table if it sees a lot of modification using any operation other than "INSERT".

share|improve this question

6 Answers 6

up vote 5 down vote accepted

Here are a few of the things I've run into with MySQL optimization:

If you see that your database server (the MySQL processes in particular) are using the majority of the CPU time, it probably means that you are either missing indexes or you need to optimize queries.

Turn on the slow query logging and pursue those slow queries to find out how they can be optimized. Use "explain" with the slow queries to find out why they are slow. The MySQL documentation has several sections about how to interpret these results.

Use memcached to cache the result of any queries you can for as long as you can. This is extremely fast, and able to cache many things that the internal MySQL query caching cannot, but can only be used in cases where you know the data can be cached long-term or you manually expire the cache.

Set up "munin" on the database system to begin collecting information about the system utilization and database query load.

Make sure your system has enough RAM that you aren't swapping, and hopefully also enough that you do few if any disc reads (use vmstat or munin to monitor this). Meaning that the data is served out of MySQL or disc buffer caches.

Sean

share|improve this answer

It is not quite to the topic, but optimization of MySQL only will not necessarily will result in performance optimization of the website. What you described above is just one case of the millions possible. Is it an often case in you web app? How does this particular feature of MyIssam slows down your website? (Considering that MyIssam is tens or hundred time faster than InnoDB). How did you discover that it is your case?

Basically, your question does not make sense until you actually profile your application and find the bottleneck. Otherwise, some common advises would contradict one with another.

Moreover, performance optimization is usually all about the trade offs. You will have sacrifice some optimization for another.

Here is a good article to start: http://www.flounder.com/optimization.htm

It's not about MySQL neither about web apps, it's about optimization.

share|improve this answer
    
The question isn't, "what optimizations always work," but something more in the line of, "if the site is scaling poorly, and you've narrowed it down to DB performance, what are some common things to look for if you can't afford to hire a DBA to do it for you." The MyISAM/InnoDB issue described above is particularly common with bulletin board systems, a few of which on each pageview query and update various "last viewed" timestamps in tables that are heavily used in other queries too, effectively serializing access. It's a flawed design, but rewriting the application isn't always an option. –  tylerl Sep 3 '09 at 22:54

One thing that can adversely affect MySQL performance is the creation of on-disk temporary tables.

You can run this for several minutes:

mysqladmin -u root -p ext -ri 30 | grep Created_tmp_disk

and if the number is high and growing you can consider putting the MySQL tmpdir on a RAM based file system (e.g. tmpfs).

In some sense this might be treating the symptom rather than the cause, but it can provide some quick relief while you consider if/how to treat the cause. Following link provides information about how MySQL uses internal temporary tables:

http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html

Cheers

share|improve this answer

All great tips. Another tidbit I will add is that sometimes the indexes you have are not enough. You might think adding an index with the rows most queried is enough but the order in which they are in the index affects performance a lot. Especially for InnoDB which tries to get the data it needs from the index without having the get the row itself (ie: disk seek). If you have a particularly large table that gets queried almost equally in 2 ways, it helps to have an index for exactly the order of lookup for both types of queries. It might seem redundant to you but it actually nudges the optimizer in the right direction :)

Also, Server variables, the 'out of the box' MySQL my.cnf file is never the right one. It serves a lot to take some time to examine its contents and adjust values based on your hardware configurations and the types of queries you get the most

share|improve this answer

@TechieGurl, regarding your order lookup for the conditionals, you're wrong. That was fixed in an early version of 4.x. The query optimizer takes a number of metrics into play, and will use a key that matches the most constant conditionals, ranged conditionals, etc. and will shuffle parameters based on order presuming the SQL doesn't contain an order of precedence that might exclude such an optimization.

MyISAM will answer results from the key, InnoDB usually will not and will lock a row when answering as part of ACID compliance and is by design rather than fault.

share|improve this answer

checkout http://blog.mysqltuner.com/

MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability : http:// github.com/rackerhacker/MySQLTuner-perl

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.