Drupal 7 performance tuning

What:

Drupal 7

Problem:

Some tweaks to optimize Drupal's performance.

Solution:

We all know that Drupal makes loads of SQL queries and some of them take a way to much time, so the goal mainly is to minimize database hits.

Before you start doing any changes make sure you have a working backup.

Enable caching

That's the most commonly recommended thing to do. You can use Drupal's build in caching mechanisms or install modules like Boost for anonymous and Authcache for authenticated user page caching. Modules that make Drupal scale

Before you start cashing let's take a look what's actually happening behind the scenes.

Analyse slow queries

1. Install Devel module
2. Navigate to admin/config/development/devel and enable "Display query log", sort query log by duration
3. Now go to a different sections of your website, at the bottom of every page you will see a log with the slowest queries listed at the top

In Drupal 7 there was a storage engine switch from MyISAM to InnoDB. In theory that should improve overall performance, but that's not always the true.

I did testing on my local PC, I checked all the slowest queries which mostly were related to cache tables and decided to convert them to MyISAM.
I ran below query on the following tables: cache, cache_field, cache_menu, cache_path.

alter table `cache` ENGINE=MYISAM

All the previously listed cache queries were gone from the top of the log.
I also converted "semaphore" and "variable" tables.

If you are using Locale module to translate Drupal's interface then every call to t() function will query "locales_source" and "locales_target" tables. I also changed them to MYISAM.

 You can also delete strings without a translation using the following sql query:

DELETE locales_source FROM locales_source left join locales_target lt using (lid) where lt.lid is null;

There is a bit more info about performance issue with Locale module [here]

Use "File Cache" module

From above tests it seems that Drupal makes a lot of cache related queries, so to avoid hitting database directly why not to store it in files?
There is a module called File Cache, which will make that happen.
After installing and configuring it on my local development platform, number of database queries has been reduced by half and there is a noticeable performance boost.

Use "Field SQL norevisions" module

Field SQL norevisions provides a field storage backend using SQL that does not create a revision table for each field. If you don't need revisions on your website then you should consider enabling it. It will reduce duplicate data, so entire database size should take much less space and it will also reduce number of queries by half when you create entities with fields, which is another performance benefit.


I am not an SQL expert, this post is based on my research and a bit of testing performed on my local PC, but I hope it will give you some clues on what can be done to speed Drupal up specially on shared web hosting with low to medium traffic websites.