Drupal does a lot of work in the database, especially for authenticated users and custom modules. It is common for the database to be the cause of the bottleneck. Here are some basic strategies for optimizing Drupal's use of the database.
Enabling MySQL's Query Cache
MySQL is the most common database used with Drupal. MySQL has the ability to cache frequent queries in RAM so that the next time a given query is issued, MySQL will return it instantly from the cache. However, in most MySQL installations, this feature is disabled by default. To enable it, add the following lines to your MySQL option file; the file is named my.cnf and specifies the variables and behavior for your MySQL server (see http://dev.mysql.com/doc/refman/5.1/en/ option-files. html). In this case, we're setting the query cache to 64MB:
# The MySQL server [mysqld]
The current query cache size can be viewed as output of MySQL's SHOW VARIABLES command: mysql>SHOW VARIABLES;
| query_cache_size | 67108864
Experimenting with the size of the query cache is usually necessary. Too small a cache means cached queries will be invalidated too often. Too large a cache means a cache search may take a relatively long time; also, the RAM used for the cache may be better used for other things, like more web server processes or the operating system's file cache.
Was this article helpful?