- For a general overview read the High Performance MySQL book and/or the MySQL Performance Blog, 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 connections we recommend using persistent DB connections. Note: Deprecated as of PHP 5.5.0.
- 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 database server you can use:
mysqlcheck -o -A -p
- MySQL supports many different engine types including Archive (MySQL 5+), MyISAM, and InnoDB. 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.
- Make use of the Percona Toolkit, especially pt-variable-advisor to check for problem settings and pt-mysql-summary to get an at-a-glance look at your configuration.
- Avoid RAID-5 in a high write environment. It can degrade performance significantly.
- Consider changing the IO scheduler if you are running on a dedicated MySQL server. The noop or deadline schedulers can have significant performance increases over anticipatory and cfq.