Tuning MySQL for Drupal
- For a general overview read MySQL performance tuning.
- 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.
- Read and use the PHP Page Snippet for MySQL Tuning
Thanks for this
All the stuff it contains led me to the info which I've put together here (more on configuring mysql).
too many connections error
I've experienced very bad site shutdown due to the too many opened (not closed) connections in mysql. (Already posted, but as I was looking for help in this section, I would add here as well)
DEADLOCK
Reading/surfing I found that InnoDB tables can have this problem. One of the causes is the very high traffic and loss of connections (but deadlock is even something different). Combined you may easily reach the max_users_connection limit and this will render your site useless.
some hints
- use cache, if you can
- use throttle
- use bad behaviour module (as this should shield you against spammers)
- set interactive_timeout so that your connections expire quickly
I have made these changes to the database.mysql.inc - to the function mysql_connect
the old version:
$connection = mysql_connect($url['host'], $url['user'], $url['pass'], TRUE) or die(mysql_error());
the new version:
$connection = mysql_connect($url['host'], $url['user'], $url['pass'], TRUE, MYSQL_CLIENT_INTERACTIVE) or die(mysql_error());
mysql_query("SET SESSION interactive_timeout=120", $connection);
to get the timeout run phpinfo() and watch for the connection timeout inside your mysql, and also timeout of your apache process (or IIS if you use IIS)
basically, after the timeout, your script will be cut off, BUT the opened connection will be there eating out of your max_users_connections. If you set the interactive_timeout limit, it will be closed automatically (note: mysql has a global wait_timeout limit, but as a default there is oftten 28800 = 8 hours, the connection will expire in 8 hours!)
hth
OPTIMIZE considered harmful
I have multiple times tried the OPTIMIZE command and that mysqlcheck command as well, usually it results in a broken website. Why have I tried this multiple times? Well sometimes I forget things so, e.g., the other day I was browsing these Drupal tuning tips, saw the above discussion, saw it say to run this mysqlcheck command and I said .. "okay, whatever, they're not going to lead me wrong" ...
Well... I'd forgotten about the last time I did this, and that time I'd forgotten about the previous time, etc... my server has 6 web sites with data provided in a MySQL instance, and the mysqlcheck command "optimized" all those sites into a state of brokenness.
What happens is that some tables becomes broken or missing. The typical problem is reports of e.g. cannot open the cache or watchdog table because the underlying .MYI file is missing or corrupted. Eek. Okay, so cache and watchdog are innocuous to have destroyed but some other tables can be hurt or damaged as well.
On one of my sites every node was being shown as blank. It knew the nodes were there, and listed all 200 or so pages of the listings of the node teasers, but instead of the teaser content each one read "n/a".
The most beneficial thing to do if you follow the above advice and end up in the same situation is
REPAIR TABLE xyzzy;This fixes most of the problems.
- David Herron - http://7gen.com/
how do you know what table
how do you know what table is damaged tho?
You don't
Just hit 'mysqlrepair ' on the command line and hope for the best.
Most of the times it helped, but sometimes restoring from backup is your best bet.. (node_revisions table got damaged.. *sigh*)
--yuval
You did remember to backup
You did remember to backup before optimizing, right :P ?