Brief Drupal Optimization Tips - Intermediate and above

Not tested for all Drupal Installations, so advanced users might like to go through these and correct some of the techniques I use.

I’m sure you’ve all noticed upon a fresh installation of Drupal, the cms is super fast, allowing you to instantly switch to different pages and nodes, provided that your hosting environment is fast.

Speed is an important fact in webpages, because with so much competition, users may not wish to wait that extra 5 – 10 seconds, for your jam-packed featured pages to show.

So having used Drupal for over 6 months, in this time period, I’ve learnt a few lessons of what can bog down the pages. So I’ll go through some common ones and techniques that can help.

Many of the techniques require either an ssh connection to the server, so please contact your administrator to make the changes accordingly.

First A few hours ago I typed top on the server, and that resulted in a load of 30.!!!

As I just installed the image block for imagegallery, I knew that this was affecting the performance, so I went into admin/blocks and disabled imagegallery

2 minute later I typed top, and the server figure dropped to 6.3, which was still very high.

As the webpage is a Medium traffic site, I knew that there was some modules I had to let go of, many of these modules will be fine for low traffic sites, but for large sites, they can clog up the site.

So In my case, I’ve disabled, Statistics, I also have urchin stats that gives me adequate stats, so I came to the conclusion that the extra resources that the statistics module was using wasn’t worth it.

I also had in addition to statistics, search engine referrer module. So I also disabled that.
Also to go were tracker, tracking, and blocks that make regular calls to the database, such as popular content, who’s online, recemt comments.

From doing this, I already noticed an improvement.
To continue I’ve followed these steps

1. ssh into your server, then connect to mysql
In my case its mysql –u user –p
Then enter password

1a. Now Navigate to Database.
In mysql shell Type: use database_name;

1b. In mysql shell Type: delete from cache;
delete from watchdog;
delete from accesslog;
delete from sessions;

4 tables, that for me were extremely chunky in size and wouldn’t affect my drupal, as I don’t have any modules that rely on statistics.

2. In mysql shell Type: show tables;

3. Now optimize some of the more bulkier tables, to do this
In mysql shell Type: OPTIMIZE TABLE access;

4. Heres a fresh out of the box drupal Installation with the standard tables.
OPTIMIZE TABLE access, ,
accesslog ,
aggregator_category ,
aggregator_category_feed ,
aggregator_category_item ,
aggregator_feed ,
aggregator_item ,
authmap ,
blocks ,
book ,
boxes ,
cache ,
comments ,
directory ,
files ,
filter_formats ,
filters ,
flood ,
forum ,
history ,
locales_meta ,
locales_source ,
locales_target ,
menu ,
moderation_filters ,
moderation_roles ,
moderation_votes ,
node ,
node_access ,
node_comment_statistics ,
node_counter ,
permission ,
poll ,
poll_choices ,
profile_fields ,
profile_values ,
queue ,
role ,
search_index ,
search_total ,
sequences ,
sessions ,
system ,
term_data ,
term_hierarchy ,
term_node ,
term_relation ,
term_synonym ,
url_alias ,
users ,
users_roles ,
variable ,
vocabulary ,
vocabulary_node_types ,
watchdog;

Add or remove any items accordingly.
I suggest run this at least two times.

Then after type: FLUSH TABLES;

After this
I entered top, and my load dropped to 0.5. Which is a drastic improvement, during peak server time.

After typing top
My stats showed me that during peak time
Mem: 1019396k av, 605912k used

So a bit less than half, so
In ssh I typed: nano /etc/myc.cnf

Change
set-variable = key_buffer=128M

For mysql users of version 4 and above

and if its not there add
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1

Then to clean up, repair and sort some of the database files
I go into my mysql directory
In my case /var/lib/mysql/database_name

There I can see hundreds of myi files

So In ssh I type:
myisamchk -r --sort-index --analyze *.MYI

These are some of the results

- recovering (with sort) MyISAM-table 'url_alias.MYI'
Data records: 2550
- Fixing index 1
- Fixing index 2
- Sorting index for MyISAM-table 'url_alias.MYI'

- recovering (with sort) MyISAM-table 'term_node.MYI'
Data records: 302
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Sorting index for MyISAM-table 'term_node.MYI'

- recovering (with sort) MyISAM-table 'sessions.MYI'
Data records: 55374
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Sorting index for MyISAM-table 'sessions.MYI'

Also for further optimisation to apache, this is a very good guide
http://www.webhostgear.com/49_print.html

Especially the logging slow queries bit.

Also Install install eaccelator
http://eaccelerator.net/HomeUk

Well that pretty much raps it up, hope the guide has been useful.