Cron Job to Optimize Web Database

After your websites start to get a little bit of traffic, you'll need to optimize their performance to keep them speedy. There's no point in having a bunch of people get a "server busy" error. One could literally write books about optimizing performance. I just want to give you one handy little trick that will ensure that more people see your sites and see them more quickly. It's really simple, and if you already know how to run an optimize query through a cron job, just stop reading now because it's a waste of your time to read further.

I'm assuming two things: 1) that you're using MySQL as your database back-end (most webmasters do, but this would work just as well with PostgresSQL and probably even on Microsoft's platform, whatever it's called now -- DotNet or whatever) and 2) that your host allows cron tasks (and if they don't, you really should look into getting a new host.)

When surfers start finding your sites, a lot of calls are made to your database. Even if you have aggressive page caching enabled, heavy traffic will still garble some of your database tables. The solution is to periodically run an OPTIMIZE query. This will not work on every platform, but it will work on 99 percent of the common CMSs so it's good for our purposes here.

By the way, as a webmaster or sysadmin, any time that word, "periodically," comes into play, you should look for an opportunity to set up a cron job. It will make your life a lot easier.

On a POSIX-compliant system, it's simple to set up a cron task. You either add a line to the crontab file or use the graphical user interface. In cPanel, go to the "Cron jobs" section and hit the button that says "Advanced (Unix Style)".

crontab entries come in the form of minute, hour, day, month, weekday command.

It might look like this:

30 22 * * * /home/username/public_html/cgi-bin/some.cgi

Translated, that means that every day at 10:30, we're going to run a script called "some.cgi," residing in our /home/username/public_html/cgi-bin directory.

For our purposes here, we're just going to run a very simple php script. We'll put the script in our /home/username/public_html/scripts directory and call it "optimizer.php".

Here's the php script:


<?php
mysql_connect("localhost", "uradminuser", "urpassword") or die(mysql_error());
mysql_select_db("urdatabase") or die(mysql_error());
mysql_query("OPTIMIZE TABLE 'table1','table2','table3','table4','table5'")
or die(mysql_error());
?>

(Of course replace "uradminuser" "urpassword" and "urdatabase" with your actual user name, password, and database name. Also replace "table1" etc. with your actual table names, and if your MySQL server is not on "localhost," you'll need to adjust that as well.)

Place that in the above-mentioned directory, and you're ready to set up your cron job!

Now for the magic, making it happen every day, automatically. I want to optimize all my tables at 8:00 in the morning, before the daily rush of people looking for helpful tips slams my site, at 12:00 noon just to freshen up, and at 4:00 in the afternoon, after those people are done mangling my databases. I'm going to enter the following cron job:

00 8,12,16 * * * /path/to/php -q /home/myusername/public_html/scripts/optimizer.php > /dev/null

(Note: "/path/to/php" is usually /usr/bin/php. "> /dev/null" dumps any output in the trash.)

You might have to hack around with that a little bit depending on your server setup, but the basics are right there for you -- run an OPTIMIZE query at regular intervals by setting up a cron task.