Optmize Multiple WordPress Websites Automatically

Posted on December 12, 2007
Filed Under WordPress Admin, WordPress Database |

If your WordPress blog is actively updated, you need to optimize its MySQL database constantly in order to keep the performance from slashing. When new posts are entered, you database becomes bigger and the query consumes more CPU and memory resources, eventually your site may get “run out of CPU quota” message and become unavailable.

The ultimate solution is to optimize your database very often. There are generally two approaches:

(1) Login to web host account and optimize each database on MyPHPAdmin tool panel.

(2) Setup a plugin and do the optimization within WP’s admin panel.

If you only have couple of WP sites, either of the above approaches would work. but what if you have 5, 10, or a lot more WP websites running? Doing it one by one is very time consuming. In this case, an automated process is preferred. For this purpose, I wrote the following PHP code and run it as a script from my website. This script get all my databases (each for a WP site) and “analyze” and “optimize” each table in the database. Quick and simple.

I only need to run this single script to get all my WP sites’ MySQL databases optimized. I don’t need to change my script code when I add new websites (databases) or delete one, since the script can find what databases and tables I have and do the job by itself.

Even better, I can setup this script as a corn job to get run regularly. In this way, I can make sure my WP sites are always optimized for the best performance.

Here’s the script:

<?php

// unlimit time to run
set_time_limit(0);

$dbUser = “richbase_root”; // MySQL username
$dbPass = “snowbark”; // MySQL password
$dbServer = “localhost”; // Database server

// connect to database
echo “Conecting to database… “;
$conection = @mysql_connect($dbServer, $dbUser, $dbPass);
if ($conection) {echo “OK”;} else {exit(’Could not connect: ‘ . mysql_error()); }

$dbcnt = 0;
$tabcnt = 0;

$alldb = mysql_query(”SHOW DATABASES”);
while($database = mysql_fetch_row($alldb)) {
$dbname = $database[0];
$dbcnt++;
$tabcnt = 0;
echo “<p> $dbcnt. DATABASE: ” . $dbname . “</p>”;
if ( !mysql_select_db($dbname) ) {exit(’Unable to set db: ‘ . mysql_error());}

$alltables = mysql_query(”SHOW TABLES”);
while ($table = mysql_fetch_assoc($alltables))
{
$tabcnt++;
foreach ($table as $dbinfo => $tabname)
{
$cmd = “ANALYZE TABLE $tabname”;
echo $cmd . “<br>”;
if (!mysql_query($cmd)) {echo “Error: ” . mysql_error() . ‘<br>’;}
$cmd = “OPTIMIZE TABLE $tabname”;
echo $cmd . “<br>”;
if (!mysql_query($cmd)) {echo “Error: ” . mysql_error() . ‘<br>’;}
}
}
}

mysql_close($conection);
echo “<p>Total of $dbcnt databases analyzed and optimized.</p>”;

?>

Comments

One Response to “Optmize Multiple WordPress Websites Automatically”

  1. Makas on March 2nd, 2008 11:11 pm

    Hi?

    i,m very much interested in your script. Lately i,ve been getting some problems with my host, my cpu was processing to much.

    I have about 20 wordpress blogs and all posting a lot everyday.

    Please supply more details about using your script. Like the setup and cronjobs.

    This maybe what i’ve been searching for the whole week.

    Thanks.

Leave a Reply




*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word