Tips : Improving Drupal Performance Part III

Introduction

Using the tools and techniques from Part II, we'll look at the areas of Drupal that can be improved. We'll go over queries, indexes and mysql configuration. This will be based upon loading the home page only.

Initial Load

Looking at a single loading of the home page produces a total of 122 SELECT queries, 3 SET commands, and 2 UPDATES. Only 30 SELECTS are unique, so there's plenty of duplicated queries. The slow query log contains 5 queries when the system is loaded with 10 concurrent connections for a 30 second period. The queries in the slow query log could also be queries that are not using indexes. We'll start with these queries first.

Slow Query Log

mysql» SELECT name, filename, throttle FROM system WHERE type = 'module' AND status = 1 ORDER BY weight ASC, filename ASC;
+----------------+-----------------------------------------------+----------+
| name           | filename                                      | throttle |
+----------------+-----------------------------------------------+----------+
| block          | modules/block/block.module                    |        0 | 
| color          | modules/color/color.module                    |        0 | 
| comment        | modules/comment/comment.module                |        0 | 
| filter         | modules/filter/filter.module                  |        0 | 
| help           | modules/help/help.module                      |        0 | 
| menu           | modules/menu/menu.module                      |        0 | 
| node           | modules/node/node.module                      |        0 | 
| system         | modules/system/system.module                  |        0 | 
| taxonomy       | modules/taxonomy/taxonomy.module              |        0 | 
| user           | modules/user/user.module                      |        0 | 
| watchdog       | modules/watchdog/watchdog.module              |        0 | 
| devel_generate | sites/all/modules/devel/devel_generate.module |        0 | 
+----------------+-----------------------------------------------+----------+
12 rows in set (0.00 sec)

mysql» EXPLAIN SELECT name, filename, throttle FROM system WHERE type = 'module' AND status = 1 ORDER BY weight ASC, filename ASC\G
*************************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: system
  type: ALL
possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
  rows: 35
  Extra: Using where; Using filesort
1 row in set (0.00 sec)

mysql» SHOW CREATE TABLE system;
CREATE TABLE `system` (
  `filename` varchar(255) NOT NULL default '',
  `name` varchar(255) NOT NULL default '',
  `type` varchar(255) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `status` int(11) NOT NULL default '0',
  `throttle` tinyint(4) NOT NULL default '0',
  `bootstrap` int(11) NOT NULL default '0',
  `schema_version` smallint(6) NOT NULL default '-1',
  `weight` int(11) NOT NULL default '0',
  PRIMARY KEY (`filename`),
  KEY `weight` (`weight`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8


mysql»

We can see that this query isn't using any indexes. The EXPLAIN output shows NULL for possible_keys, key, key_len and ref. This table contains 35 rows, so it's doing a full table scan to return the result. Benchmarking this query produces:

| Read more..

Courtesy : Marksitblog.blogspot.com