Drupal Troubleshooting (part 8)

Sometimes your installation will be slow for no apparent reason. Time to grab your toolkit and get started on finding and eliminating the problem. Here are some of the common problems faced by developers.

Database Queries

The database is generally the first place you look when trying to identify problems on a page. It is possible to identify problems through MySQl’s slow query log or through the query log of the Devel module.

High Performance MySQL: Optimization, Backups, Replication, and More: Must have book for anyone serious about getting the most from MySQL.

Views and indexes

The Views module makes building queries easy and sometimes a crucial part of the query will rely on a CCK column with no index. This tends to happen with sorting or filtering. Queries can run slow in these cases. The solution is to place an index on the column in question.

Large Sites: CCK need index

Views and Left Joins

The Views module will often design queries with LEFT JOIN rather than INNER JOIN, especially when joining from the node table to a content type CCK table. In many cases you might only want an inner join, especially when the node table is very big. In these cases it is possible to rewrite the query by hacking the query in hook_views_pre_execute.

Large Sites: Some View SQL inefficient

Ability to INNER JOIN to node for a specific field: Discussion of Views SQL for joining between node and content type tables.

Inefficient schema

The database design of some modules could possibly be improved. It is up to you the developer/administrator to ensure that you are happy with the internal design of contributed modules. If you find an inefficiency then submit an issue to the module, provide a patch or remove the module from your site.

Composite indexes

MySQL is limited to using one index per table when sorting and filtering. This can make it tricky when you wish to use multiple AND clauses or involve a sort and a filter at the same time. In these cases adding a composite index can get you out of trouble.

Problems in Core

In a number of cases there are problems in core of Drupal where queries are very slow on very big Drupal installations with millions of nodes. The author has experienced problems with:

Inability to browse content in Admin section due to join from member to user table
Inability to edit nodes with many CCK fields. Massive RAM usage when loading nodes.
Taxonomy pages take a very long time to display
Search system unable to index content

These problems can be avoided partly by swapping Solr in for search and having it override the taxonomy pages. The other problems you just have to live with

Drupal with millions of nodes: Some good research from Wesley Tanaka into problems with many nodes. Attitude from some here seems a little dismissive of the issues.

Slow Modules

Modules can also be badly written leading to poor performance in certain circumstances. Generally this happens when the module’s creator did not test the module against (i) large installations with many nodes (ii) complex installations with heavy nodes or detailed taxonomies. You generally will only run into these problems if your site is large. In some cases you can fix the module by creating more indexes in the DB. In others you just have to remove the module from your installation…. or submit a patch.

Some known offenders for large sites include:

XML Sitemap
Node access
Taxonomy Browser
Fivestar

How Drupal’s node_access table can negatively impact site performance
Scalability of the Taxonomy Browser module: Restricting number of terms: “Query from hell” with many joins leading to queries which never finish.
XML Sitemap 6.x-2.x: How Drupal modules can overload a site during cron, with solutions: XML Sitemap module needs to be configured correctly

This article forms part of a series on Drupal performance and scalability. The first article in the series is Squeezing the last drop from Drupal: Performance and Scalability.

Be Sociable, Share!