And to build on my last post about Moving from Multi-DB to SharDB on WPMu, here is a rundown on some of the work done to further optimize the multiple-databases for our WPMu at UMW Blogs.
First off, we have a plugin install called WordPress MU Sitewide Tag Pages that basically republishes every public post from around UMW Blogs into one blog, the tags blog, which is located at http://tags.umwblogs.org. This plugin has a ton of uses for us, not least of which is sitewide tags and categories, as well as feeds off those sitewide tags and categories for syndication and republishing into course sites. Another function of this blog could be a searchable archive of all the public work done. So, needless to say, this blog is important enough that we have let it run now for almost two years without pruning. And when I checked the wp_posts table of this blog in the database there was what seemed to be 99,066 posts.
And that one table was weighing in at a hefty 425 MBs, which mean it was out-of-control, and I imagine some of our recent performance and down time issues might be related, so I decided to cull this table. So after backing it up, I tweeted looking for advice on how I might delete all posts from the tags blog before a certain day, say August 15, 2009—the week before the Fall semester started this year.
And in no time at all, Michigan’s finest, Gowtham (and sgowtham on Twitter) sent me the following bit of code:
DELETE FROM wp_posts WHERE post_date < "2009-08-15";
Along with the note, "please be sure to replace the right table name in place of wp_posts though." Which would like like this for the tags blog with the ID 1291:
DELETE FROM wp_1291_posts WHERE post_date < "2009-08-15";
So I just copy that bit of SQL code into the PHPMyAdmin editor that lets you run SQL queries for a specific database like so...
And within a minute 66,000 posts were removed, and the table was now 142 MBs.
Now, let's stop here for a second. How sick is that? Within minutes I got a solution to my issue and deleted 66,000 posts and potentially saved UMW Blogs a couple of more crashes this coming week, from a friend on twitter who just knew what to do. How much richer am I because of this relationship? How much richer is all of UMW? It's kinda wild to think about that. And more than that, it doesn't stop there....
When I removed those 66,000 posts the size of the table was still showing 425 MBs, but there was now a value in overhead column of the table that had a value of 140 MBs. I had o idea how to get rd of this, so asked again in Twitter, and D'Arcy Norman tweeted me back this:
He was right, optimizing the table brought the 425 Mbs down to a slender 140 MBs, and the overhead value was flushed clean. So this got me thinking, hmmmm, what if I could run a script and optimize every table in every database? I mean this site has been running non-stop for almost three years, and has been a work horse. How about giving UMW Blogs an "oil change"---to use Gowtham's analogy which works beautifully. So, when I tweeted about that optimization question, Gowtham once again sent me a message, and after I gave him the naming conventions of my 18 databases on UWM Blogs, wham, I had a PHP script to optimize every table in UMW Blogs.
People, are you getting the point here? Gowtham is an Engineering Physics grad, a hell of a photographer too, but he knows his shit. And he was willing to share that will me in all of about a few minutes the first time. But for the optimization script, he actually wrote it up and sent it to me via email. I don;t know how long that took him, but it would have taken me hours and hours of research and learning to figure out I could do it. And hours more to beg someone who knew at UMW to write it for me (if that person existed). Well, this process was a matter of minutes.
And, Gowtham not only sent me the SQL Optimization script, but was more than cool with me sharing it, so here is the code to the script as a txt file.
And here it is copied below sans opening and closing PHP calls.
# PHP script to optimize each table in a given set
# of MySQL databases and improve the overall performance.
# First written: Gowtham, Mon Feb 27 09:33:01 EST 2006
# Last modified: Gowtham, Sun, 24 Jan 2010 13:01:58 -0500
# Connect to the database
# It is expected that you will be able to connect
# to all your databases [listed below] with this
# set of credentials
$host = 'localhost';
$dbuser = 'USERNAME';
$dbpasswd = 'PASSWORD';
# The act of connecting to the MySQL server
$connect = mysql_connect($host, $dbuser, $dbpasswd) or
die('MySQL Connection Error: ' .
mysql_errno() . ': ' .
mysql_error());
# List of databases
$databases = array(
"umwblogs_wpmu_0",
"umwblogs_wpmu_1",
"umwblogs_wpmu_2",
"umwblogs_wpmu_3",
"umwblogs_wpmu_4",
"umwblogs_wpmu_5",
"umwblogs_wpmu_6",
"umwblogs_wpmu_7",
"umwblogs_wpmu_8",
"umwblogs_wpmu_9",
"umwblogs_wpmu_a",
"umwblogs_wpmu_b",
"umwblogs_wpmu_c",
"umwblogs_wpmu_d",
"umwblogs_wpmu_e",
"umwblogs_wpmu_f",
"umwblogs_wpmu_global",
"umwblogs_wpmu_vip1"
);
# FOREACH_LOOP_DATABASES BEGINS
# Loop through the array, 'databases'
# Each array element will be stored in the variable 'database'
foreach ($databases as $database) {
# Connect to the database using details specified
# above - it expects that there is one username-password
# combination that can connect to all your databases
mysql_select_db($database, $connect) or
die('Database Connection Error: ' .
mysql_errno() . ': ' .
mysql_error());
# Select all tables from 'database'
$sql_q = "SHOW TABLES FROM $database";
$result = mysql_query($sql_q) or
die('Invalid Query: ' .
mysql_errno() . ': ' .
mysql_error());
# WHILE_LOOP_OPTIMIZE BEGINS
# Loop through the list of tables from 'database'
while ($table_details = mysql_fetch_row($result)) {
$table_name = $table_details[0];
# Print a message to the screen - helps in debugging
echo "$database :: $table_name \n";
# SQL query to optimize the table
$sql_o = "OPTIMIZE TABLE `$database`.`$table_name`";
$optimize = mysql_query($sql_o) or
die('Invalid Query: ' .
mysql_errno() . ': ' .
mysql_error());
}
# WHILE_LOOP_OPTIMIZE ENDS
}
# FOREACH_LOOP_DATABASES BEGINS
# Close the connection
mysql_close($connect);
I put in the drop database sign-in information, uploaded it to UMW Blogs via FTP, and went to the script at http://umwblogs.org/mysql_optimize.php, and a few minutes later all of the tables in all 18 of the databases were optimized.
Is this not awesome, UMW Blogs has gotten the oil change it has been in desparate need of---and I don't recommend waiting every 4,000 users and blogs, every 2,000 may be wiser. So, thanks to Gowtham, UMW Blogs is purring like a cat, and I really hope it stays that way.
And just to show you I have no shame, something else I saw when the databases were optimizing is that random blogs still have the Spam Karma tables in them, I would think about 500 or 600 blog tables filled with old spam. The tables are labeled wp_blogid#_sk2_logs and wp_blogid#_sk2_spams. For example: wp_21_sk2_logs and wp_21_sk2_spams. So riddle be this Batman of Gowtham, how would you find and drop all tables with _sk2_ in them across 18 databases?
I love the internet, and I can't thank you enough Gowtham for your selfless support, people like you make this space both fun and wonder-filled.
