FirstServed Tech Blog - FirstServed and the Art of Server Tuning

A frequent problem we have is the clutter of MySQL databases created by shared webhosting customers. In order to maintain a well performing MySQL server we now and then have to optimize the MySQL tables. Doing this by hand, or waiting until the user does this is no option, therefore, we run this command, through a cron job:

First, we create a user named mysql_optimizer (with a strong password) and give it select and insert priveleges on the databases. You do not want your root user to be used in a cronjob!

mysql> GRANT SELECT, INSERT ON *.* TO ‘mysql_optimizer’@'localhost’ identified by ‘strong password’;
mysql> flush privileges;
mysql> exit;

Second thing is setting up the crontab, this time a weekly simple schedule:

> crontab -e
# MySQL weekly optimisation
0 0 * * 0 /usr/bin/mysqlcheck -Aos --optimize -u mysql_optimizer -pstrongpassword > /dev/null 2>&1

That’s it!

One of our customers works with drupal… Unless you are from another planet, you know drupal is currently one of the leading open source CMS systems with nice features, but it also is quite a bottleneck on server performance. Not to mention the load on a database server etc…

We suggest drupal hosting customers to opt for a virtual server setup. Does this mean you can’t host drupal on a shared hosting platform? Not at all, but if you have a high load website, a isolated server instance might give you some advantages.This means you can set up a nice isolated environment for a Drupal driven CMS website with dedicated server performance at severe reduced cost.

When setting up the environment be sure to run through the following checklist:

• CentOS 5.3 (for example)
• Apache 2.2 webserver
• MySQL 5+ database server
• PHP 5+
• and, very important: eAccelerator 9.5.3 or higher.

Why the eAccelerator? Well, Drupal uses a lot of cached pages. Enabling Drupal caching already gives you a boost but installing eAccelerator makes sure frequently used php scripts are stored in a compiled state thus allow retrieval faster.

On top of that, you may optimize the MySQL query_caching settings, as they Drupal also uses a lot of the same queries.

You can find the latest version of eAccelerator here: http://eaccelerator.net/