Last updated May 3, 2012. Created by Amazon on February 25, 2006.
Edited by skandalfish, travelertt, chx, bryan kennedy. Log in to edit this page.
- For a general overview read High Performance MySQL especially the InnoDB performance basics article.
- Start with an appropriate MySQL option file. For servers with at least 2GB RAM dedicated to MySQL we recommend my-huge.cnf. For servers with a lot of writes, we recommend my-innodb.cnf instead of the default MyISAM engine type.
- To reduce the overhead of creating connnections we recommend using persistent DB connections.
- If a query is called at least twice with no modifications to the queried tables a significant performance improvement can be gained by avoiding the processing of the query and the execution of the query by reading the query from the MySQL query cache. To learn how to set up the query cache read a practical set-up.
- Be sure to have enough cached threads or you will launch too many new threads as described in this story about a Yahoo site.
- The biggest performance boosts can come from identifying and tuning the slowest queries using the MySQL slow query logs.
- You can use the DB Maintenance module or use the mysqlcheck commands below in a cronjob.
echo "OPTIMIZE TABLE accesslog,cache,comments,node,users,watchdog;FLUSH TABLES;" |mysql -u user -ppasswd
If you have complete control of the datbase server you can use:
mysqlcheck -o -A -p
- MySQL supports many different engine types including MyISAM, InnoDB, and Archive(MySQL 5). Performance sites should use InnoDB for most tables particularly tables such as node that get a lot of writes and reads. MyISAM exclusive table locks for updates before selects versus InnoDB's row level locking can mean MyISAM blocks reads if there are many writes. Convert from MyISAM to InnoDB.
- In MySQL 5 a new table type called the archive table type was introduced to deal with common requirements for web applications like access logs where only INSERTS and SELECTS were done. If tables such as the Access Log table are determined to not have DELETE, UPDATE, and REPLACE then this MySQL engine type can often offer significant performance improvements as they have done for sites like Slashdot, Yahoo, and Live Journal.