If your website is using MySQL as a database back end and you think your website is slow, you are probably interested MySQL performance optimization tips and tricks. Here are a few of them:

1. First of all, check if you have too many table locks happening. If your database engine is MyISAM, which locks the whole table during for the time when any operation (select, update, delete, etc) is running over this table, it can happen that the wait time for the queries is too long, because every query has to wait for the unlock before it starts to execute. You can use the following query to find out what engine is used for your database tables:

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = ‘your_database_name’;

If your database tables are using MyISAM then table locks can be an issue for you.

show processlist;

You would like to execute the above query during the time when you have many users online. It will show how many queries are currently active in MySQL: executing or waiting for their turn. If there are too many of them there (or some of them in the state “lock” or something similar, do not remember the exact wording), you’d like to update your MySQL engine to InnoDB. This is not panacea, of course, it has it’s own limitations and problems, but InnoDB uses low-level row locking instead of table-level locking. I.e. when running table update, it’ll lock only those rows, which are affected by the update, not the whole table. It means, that other queries will have to wait shorter times. Here is how you can change table engine to InnoDB:

ALTER TABLE my_table ENGINE = InnoDB;

just replace “my_table” to your table name. You’ll have to run this query for every table in your DB.

2. MySQL configuration tuning. There is a nice tool called MySQL Performance Tuning Primer which can suggest better values for MySQL configuration parameters for you my.cnf file. Please note, for recommendations to make sense, your MySQL should be running no less then 48 hours in its current configuration.

3. Consider using memcached tool (free & open source, high-performance, distributed memory object caching system). It is generic in nature, but intended for use in speeding up dynamic web applications by alleviating database load. It’ll allow you to cache some database queries and avoid their execution every single time. Thus you’ll be able to save some time and speed up your web site. If you are using drupal, there is already a memcached drupal module ready to consume: http://drupal.org/project/memcache . Installation instructions are pretty straight forward, I didn’t have any issues with it. There are similar plugins for Word Press and other CMS, but I did not try them out, so can not do any recommendations.

4. The last, but not the least, is moving MySQL server to a different physical server. Moving MySQL to a separate server makes sense if you see that current resources are insufficient, that MySQL requires more memory than you can allocate to it on the application server. The move is much easier than it sounds (you just need a separate server!), involves a few steps only:

• install MySQL on a second server
• make a dump of your database, transfer it to new box and restore DB from the dump
• create MySQL user that way it allows connections from a remote computer (from the IP of your web application server)
• change database access credentials (host, database, user, password) in all places, ideally it should be only one place of course : )

After the move wait couple of days then run the tuning script again to adjust the my.cnf settings now when you have more resources solely available to MySQL.

These steps helped me, I hope they’ll help you too!