I was really stuck recently, when client websites started to show unbearable 5-11s latency after importing to their new home WordPress installations. It took time to debug and it let me learn a trick or two on mysql performance optimisation. This is something I’d like to share.
Firstly, do not run into mysqld variables shuffle and editing. Check the source of the latency. It may arise for quite different reasons. And definitely – do not alter ‘localhost’ in your wp-config.php to your hosting IP address, that won’t work anymore. What I recommend – make some changes in wp-config.php and the footer of your theme to enable MySQL query debugging.
Add
|
|
define('SAVEQUERIES', true); |
instruction to wp-config.php to enable debugging mysql queries. To output them, add
|
|
<?php if (current_user_can('administrator')){ global $wpdb; echo "<pre>"; print_r($wpdb->queries); echo "</pre>"; } ?> |
to your theme’s footer. It will make output visible while you are logged in as administrator. Now you can sum up all the time from your queries and check if too many or too long queries are the reason of your website latency. Now you have strategy. Either cache bad long queries, make database optimisation, reindex what is needed, or increase memory allowance to PHP or WordPress itself.
If you are running MultiSite, it is always good to explicitly define the memory limit available for WordPress by adding
|
|
define('WP_MEMORY_LIMIT', '64M'); |
to your wp-config.php.
Such problem can arise when you have large image galleries to show, when you run MultiSite with new sites imported from older installations, or when you have caching disabled in MySQL.
Suggested reading for further enlightenment:
What to edit in wp-config.php
How to optimise MySQL performance
If your site generates way too many queries to database, i.e. more than 1000 per GET, you should check your wp-options table and .htaccess rules. The root of all evils (up to WP version 3.2.1 is to keep permalinks structure like /%category%/%postname%/. Start it with /%year%/ and your website latency may become immediately cured.