Analyze WordPress Performance – Plugin!

Recently Joost mentioned in a post on yoast.com, how he used my Plugin “Debug Queries” to optimize his WordPress database performance (BTW, thanks for the patch).

Performance of WordPress can be quickly and easily negative effected by using some Plugins. You don’t have to be an expert to know, that more function will provide more load. However, provides the simplicity of creating a Plugin for WordPress, which have made it so popular and I very much appreciate, a problem – You don’t have to be a professional developer to create a Plugin, but an optimized syntax lead to a much improved performance of a WordPress installation.

But the real problem: How to find the weak point in your blog?

You can use some tools to analyze performance, for example the Add-on Firebug in Firefox, especially with YSlow. This helps to find scripts which slowing down your blog and vulnerabilities in a code. Also makes sense that “many” scripts, which get installed with some Plugins, should not be load by wp_head but in wp_footer. Scripts should be placed in the footer to improve the performance.

However, the analysis via disabling / enabling all Plugins is a hassle. It would be useful, to examine the different queries and look at what function they refer.

WordPress offers the possibility to display the total number of queries, as well as the time needed. With the following syntax, preferably put in the footer of the page, this is done quickly.

num_queries; ?>q, s

But the variable $wpdb offers more to look into the /wp-includes/wp-db.php.

if (!defined('SAVEQUERIES'))
define('SAVEQUERIES', false);
 
class wpdb {
 
var $show_errors = true;
var $num_queries = 0;
var $last_query;
var $col_info;
var $queries;
var $prefix = '';
 
// Our tables
var $posts;
var $users;
var $categories;
var $post2cat;
var $comments;
var $links;
var $options;
var $postmeta;
var $usermeta;
var $terms;
var $term_taxonomy;
var $term_relationships;
var $tables = array('users', 'usermeta', 'posts', 'categories', 'post2cat', 'comments', 'links', 'link2cat', 'options', 'postmeta', 'terms', 'term_taxonomy', 'term_relationships');
var $charset;
var $collate;

You will notice the constant SAVEQUERIES which is not defined in wp-config.php. Setting this constant to TRUE, it leaves the door open for other possibilities.
But I don’t want to get in too much details, because it would be too much for just one post. Who has an interest can find a whole range of information in the said file.

My goal was not to get the total number of queries in the blog as a result, but the individual queries including their syntax, because then I can find the problem explicitly in the code.

To ensure that this will work easily and quickly on every blog, I created a Plugin and just have to enable it, if I want to get an analysis. In addition, the analysis will only start and the result displayed when an administrator is logged in.

The result may look like this for example:

16. Time: 0.000431060791016
Query: SELECT object_id, term_taxonomy_id FROM fb122_term_relationships INNER JOIN fb122_posts ON object_id = ID WHERE term_taxonomy_id IN (6,5,1) AND post_type = 'post' AND post_status = 'publish'
Call from: wp-includes\taxonomy.php(2093): wpdb->get_results()
 
17. Time: 0.00243401527405
Query: SELECT t.*, tt.* FROM fb122_terms AS t INNER JOIN fb122_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('link_category') AND tt.count > 0 ORDER BY t.name ASC
Call from: wp-includes\taxonomy.php(777): wpdb->get_results()
 
18. Time: 0.00080418586731
Query: SELECT * , IF (DATE_ADD(link_updated, INTERVAL 120 MINUTE) >= NOW(), 1,0) as recently_updated FROM fb122_links INNER JOIN fb122_term_relationships AS tr ON (fb122_links.link_id = tr.object_id) INNER JOIN fb122_term_taxonomy as tt ON tt.term_taxonomy_id = tr.term_taxonomy_id WHERE 1=1 AND link_visible = 'Y' AND ( tt.term_id = 2 ) AND taxonomy = 'link_category' ORDER BY link_name ASC
Call from: wp-includes\bookmark.php(255): wpdb->get_results()
 
* Total query time: 0.02111s for 18 queries.
* Page generated in 0.36373s, 94.20% PHP, 5.80% MySQL

The work certainly is not necessarily pleasant, but I can find the real problems in the database query and maybe improve the corresponding query or deactivate it. The Plugin uses some more information than the above tutorial, so it is understandable and more informational.

Debug Queries (plugin)

After activating the plugin, the individual queries will be written in the footer of the page, as an HTML comment. So you must analyze the source code to retrieve the values. The values are analyzed and displayed only when an administrator is logged in.

You can Download Debug Queries here!

Further Plugins to analyze and improve your WordPress database

• WPDB Profiling
• Clean Options
• WP Tuner
• SQL Monitor