WordPress MU

I received the following email from mediatemple, my new host:

(mt) Media Temple's automated MySQL monitoring systems have detected an increase in database activity for your Grid-Service (blogpeoria.com). Your databases are now being served from a MySQL BurstContainer to help your web applications scale during this surge in activity. MySQL BurstContainers are an innovative new component of our MySQL SmartPool v.2 that allows your websites to instantly handle intense, temporary bursts of database activity.

There are a lot of factors for choosing which services we will place in a container. It is very likely that this will only be temporary. We have found that roughly 3 out of 4 customers can return to our SmartPool without ever needing to be "bursted" again.

Your databases will remain in the BurstContainer for 3 days (until 07/24/2008 08:50 AM). At the end of this 3 day window our system will attempt to move your databases back into the SmartPool. If your database activity continues to exceed the resources of the pool you will receive an email with additional options.

Question: Any advice out there for making my database a little less of a strain on servers. It's a large database, soemthing liek 180. I do believe there are still tables in there that refer to splogs that have singe been deleted. Can/should I manually delete unused tables? I've also started and discontinues many plugins over the years. Should I manually delete inactive plugins?

My WPMu site is running on 1.3.1. Will upgrading help with this issue?

Upgrade for sure to 1.5.1 for no other reason than security issues. Not to mention it has some performance increases involved.

Other things to consider:

1) Look into wp-super-cache from Donncha as it creates static versions of pages and less database queries
2) Only have essential plugins in your /mu-plugins/ folder as they are all executed on every page load. Inefficient plugins can be 100's of queries that don't need to be there.
3) Look into using object cache. I recommend trying out http://neosmart.net/dl.php?id=14

Good place to start anyways.

Trent

Thank you. I'll upgrade to 1.5.1 asap. I'll delete the unused mu-plugins, too.

wp-super-cashe caused all sorts of grief when I tried to use it in 1.3.

As well, you always have the choice of getting a mySQL container from MT and quit just using the Smartpool that comes with every (gs) account. That are think about a VPS. Either way, the previous suggestions would apply regardless of what hardware you are using. :)

And yes, clean up all the unused stuff out of the database as well. (Make a backup first!)

andrea_r: Some advice on how to tell which stuff in the data base is unused and which is not would be helpful. Do I compare table numbers to blog ID numbers?

Removing stale database data just saves disk space. If they keep having to put you in a burst server, you're gonna have to pay more for hosting and think about going dedicated.

I'm hoping to avoid goign dedicated. mediatemple offers a product called "Burst Containers" that ought to do the trick if I'm not able to reduce the load on my own after the upgrade and the use of object cache.

I just now deleted a mu-plugin called "database" manager that as I recall was something of a hog.

How many blogs on your system?

I have about 30 active, some more than others. But over the years, well more than 1000 splogs.

you may have a log of processes that have taen longer than 30 seconds. There may also be a slow MySQL log. Check those to see whats eating your resources. for me, it was firestats and xiando's social networking admin bar. once i got rid of them, I had no more issues. I'm on VPS now, so it's no problem. But try checking those.

MediaTemple used to have a little graph thing that showed which scripts were using the most processing - it was in the Admin sections of the Grid Service. I'll look and see if I can find what it was called.

If nothing else it might show what scripts are taking longer to process and then you can investigate their sql usage and think about indexing, etc..

EDIT: It's called GPU Usage reports

I just fired off an email to mediatemple. I ran the sql query, and NOT of the processes were talking longer than a minute. Of course, this happened after I deleted every unnecessary fle in mu-plugins and plugins.

I've been trying to download a database backup, but I must be doing it wrong in phpmyadmin, because the downloaded zipped file contains nothing, and is just 4K. The database I uploaded a few days ago was 180MB. The tables appear to be there, though, and my site is loading fine. And I'm not seeing any tables that don't corresponding to blogs.

Do you have anything running in addition to MU? I tried running openAds at one point to show some ads on my blogs, but that took me into a burst container so I quickly removed it again.

phpmyadmin can't handle a backup that big. instead of selecting all the tables to make an export, just select some. You'll probably have to do 1/10th at a time.

@billdennis5: Enable SSH access on your Grid Server account, login and use mysqldump to create a back file for your database.

Search the MediaTemple help pages for database backup. They used to have a handy backup method right on the control panel, but I see they have removed that now.

http://www.gosammy.com/2007/12/30/automating-your-mysql-database-backup-on-media-temples-grid-server/

;)

Trent

andrea_r: I ran the mu-plugin you mentioned. It generated a report that ended with:

DELETE FROM wp_1302_comments WHERE (comment_approved= '0' ORcomment_approved= 'spam') ANDcomment_date_gmt< DATE_SUB(CURDATE(), INTERVAL 2 DAY) (0)

The others were similar.

This should clean up the data base, huh?

Oh yeah. :D You'd be surprised as how much spam is probably floating around in there.