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.
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.
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.
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.
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.
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.
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
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.
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.
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
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:
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
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:
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.