TRIPAWDS: Home to 25059 Members and 2171 Blogs.
HOME » NEWS » BLOGS » FORUMS » CHAT » YOUR PRIVACY » RANDOM BLOG

WordPress Multisite Database Tuning To Optimize Tripawds Network Performance

tripawds wordpress multisite network home pageIn our continuing efforts to keep the Tripawds Blogs and discussion forums tuned up, we have been doing some work behind the scenes here recently optimizing our MySQL database that drives this WordPress multisite network.

What does this mean for Tripawds members?

This means that we’ve spent hours removing a whole bunch unnecessary gunk from your blogs and comments that you never knew was there but may have been slowing things down.

Anyone interested in the gory details can skip down to read how we went about deleting more than a gigabyte of worthless data from our WordPress database via PHP MyAdmin.

Do you have a Tripawds blog you don’t use?

If you created a blog when you registered for Tripawds, but have never used it and never intend to, please let us know so we can delete it. Unused sites take up additional space in the database, causing overhead every time people visit the blogs or forums.

Please complete the simple Tripawds Account Modification Request that we announced in our first Tripawds Newsletter, and give us your username if you’d like us to get rid of your blog. You can always create another site for free anytime.

As always, thank you for your support that makes it possible for us to keep this community online!

Love your Tripawds Blog? Consider upgrading it to Supporter status and immediately get rid of the ads and access additional features!

Now, for the technical bits…

How to Optimize WordPress Multisite Database Using Helpful SQL Queries in PHP MyAdmin

When is the last time you took a look at your WordPress installation’s  MySQL database with PHP MyAdmin? When is the last time you optimized database tables having overhead?  It’s not as daunting as it sounds, and it’s more important than you think!

Assuming you know how to navigate cPanel and access your database using PHP MyAdmin, regularly select tables having overhead and optimize often. But that is just the beginning.

php myadmin optimize database tables

After noticing some serious impacts on server performance, topped off with myriad MySQL errors, I recently spent some time reducing the size of our WordPress multisite network database by more than a gigabyte! I literally deleted hundreds of thousands of rows from numerous tables related to many sites on our network, and am already noticing improved performance.

I started by focusing on the largest tables and researching how I could reduce their size. I won’t get into the specific details since that is explained at the links provided below. But I will describe how I modified and ran the SQL queries I found to make them work when managing a WordPress multisite network.

php myadmin sort mysql database tables by sizeTIP: Click the “Size” column label in PHP MyAdmin to sort tables by size and find the largest ones.

The steps I describe below focus on how to optimize a WordPress database in the following ways:

  • Removing old comment meta data from wp_commentmeta
  • Deleting transients from wp_options
  • Delete unwanted post revisions
  • Bulk delete SimplePress Forum members

DISCLAIMER: Always backup your database before performing any maintenance. use this information at your own risk.

How To Reduce Size of wp_commentmeta WordPress Database Table for Multisite Network

For step by step details about how to delete old WordPress comment meta data, please see the following articles:

Apparently, there was an issue a while back with the Akismet plugin causing bloated wp_commentmeta tables. The issue has reportedly since been resolved, but is likely partly to blame for our 67+ MB db table!

These links helped me find SQL queries to clean up comment meta data and reduce the size of our WordPress database by more than 66 megabytes.

wp_commentmeta database table cleanup

Select your database and navigate to the wp_commentmeta table, then run the following query to select and preview data rows to be deleted. For WordPress multisite networks, replace “1” with the ID of the site for which you wish to perform this operation.

SELECT * FROM wp_1_commentmeta WHERE comment_id
NOT IN (
SELECT comment_id
FROM wp_1_comments
)

Review the results and once you are satisfied that no pertinent data will be affected, run the follwing SQL query to delete all the unwanted junk form the table.

DELETE FROM wp_1_commentmeta WHERE comment_id
NOT IN (
SELECT comment_id
FROM wp_1_comments
)

Check out the results!

wp_commentmeta sql query

Additionally, you can run the following queries on your MySQL database to remove additional spam comment data from the wp_commentmeta table left behind by Akismet. These have been modified for use on our multisite network, where we have the Akismet plugin activated on the main blog (site ID: 1). Edit as necessary for the sites you wish to remove

To select and preview Akismet related meta data:

SELECT * FROM wp_1_commentmeta WHERE meta_key
LIKE "%akismet%"

To delete unnecessary data:

DELETE FROM wp_1_commentmeta WHERE meta_key
LIKE "%akismet%"

Finally, to delete all comments marked as spam from a specific site on your network in your database, change the site ID and run this query form the SQL tab in PHP MyAdmin. in this case, the site ID is 1.

DELETE FROM wp_1_comments WHERE comment_approved = 'spam'

You’re not quite done yet. Return to your database view and select the wp_commentmeta table. Scroll down and choose “Optimize” from the drop-down menu to remove overhead created by deleting all the junk.

php myadmin optimize database tables overhead

TIP: To maintain your database performance, make a habit of regularly reviewing it in PHP MyAdmin, clicking “Check tables having overhead” and selecting Optimize table.”

How to Delete Old Transients and Reduce Size of WordPress Options Database Table

The next step I took to reduce the size of our WordPress multisite network database was to remove old transients from the wp_options table. Review the following articles for information about how WordPress uses transient data, and why some developers believe it is a flawed subsystem since all transients do not get regularly deleted as they should. This was clearly the case for us since the options table for our site had consumed nearly another 60 MB of our database!

wp_options table size

There is really no need to preview the transient data to be deleted. I discovered the general consensus is that all transients can be safely removed. Modify this query with the desired site  ID (e.g.; wp_1_) and run it via the SQL tab in PHP MyAdmin.

DELETE FROM `wp_1_options` WHERE `option_name` LIKE ('%_transient_%');

Bam! Look at how many rows I deleted with one simple MySQL query. Our wp_options table for the main site on our WordPress multisite network is now approximately 2.5 MB.

wp_options table delete sql query

This is another  quick way to reduce the size of your WordPress database. Remember to optimize the table after running this query to remove any overhead.

How to Delete Old Post Revisions in WordPress using PHP MyAdmin

Before we installed a plugin to limit post revisions for WordPress on our multisite network, we had a few years worth of sites with a growing number of post revisions. I searched and found plenty of bloggers offering suggestions for how to delete all WordPress post revisions at once. These two articles, however, provided a more complex SQL query that also removes related rows from the the postmeta and term_relationship tables:

Visit the SQL tab in PHP MyAdmin for your database and run this query to remove all post revisions in a standalone WordPress installation:

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

Notice the changes in the SQL query below, which I used to remove all post revisions from the main site (ID: 1) on our WordPress multisite:

DELETE a,b,c
FROM wp_1_posts a
LEFT JOIN wp_1_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_1_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

Optimize your wp_{id}_posts table after running the query and notice the results! Here are mine:

wp_posts delete post revisionsThis may not seem like a big difference, but by running this query for a number of old sites on our network I was able to further reduce the size of our WordPress database.

Bulk Remove SimplePress Forum Members Without Deleting WordPress Users

Tripawds SimplePress ForumsI took one more step to optimize our database which may only be of interest to those running SimplePress forums on their WordPress multisite network. We run the Tripawds discussion forums on the main site of our network. By default, all users of the network, are added to site ID 1, making them members of the forums. Many of our users, however, never post in the forums, or haven’t visited in a long time. While SimplePress does offer it’s own plugin for removing “spam registrations” with similar criteria, using it will also delete the selected users from the WordPress tables, removing them entirely from the network – not such a good thing if they have their own blog!

Run the following SQL query to identify SimplePress Forum members on your site who have never posted AND not visited since a given date. Be sure to change the site ID (e.g.; wp_1) as necessary and edit the lastvisit datestamp to meet your needs.

SELECT *
FROM `wp_1_sfmembers`
WHERE posts = "0"
AND lastvisit < "2013-01-01 00:00:00"

This allowed me to review all those members of our discussion forums who have never posted and not visited since January 1, 2013. I then ran the following to safely delete 1,751 rows from the sfmembers table,without deleting these users from our multisite network.

DELETE
FROM `wp_1_sfmembers`
WHERE posts = "0"
AND lastvisit < "2013-01-01 00:00:00"

This reduced the size of our WordPress database by another 7± MB. I then ran this final SQL query to clean out related orphaned data from the sfmemberships table:

DELETE FROM wp_1_sfmemberships WHERE user_id
NOT IN (
SELECT user_id
FROM wp_1_sfmembers
)

Should we ever decide to remove these users from the network, the following query would do the trick:

DELETE FROM wp_users WHERE id
NOT IN (
SELECT user_id
FROM wp_1_sfmembers
)

To review all these users of the site who are not members of the forums before deleting them, run this query first:

SELECT * FROM wp_users WHERE id
NOT IN (
SELECT user_id
FROM wp_1_sfmembers
)

How To Reduce Size of wp_site_comments DB Table

The Comment Indexer plugin creates the “wp_site_comments” table to index comments on all sites in a multisite network, for showing recent global comments in a widget for instance. After noticing that table was 360+ MB, I discovered spam comments and unapproved comments were getting indexed. So I used the following queries to reduce the table size to approximately 32 MB!

To review spam comments:

SELECT *
FROM `wp_site_comments`
WHERE `comment_approved` = 'spam'

To delete all spam comments:

DELETE
FROM `wp_site_comments`
WHERE `comment_approved` = 'spam'

Note: The following will show results for all comments that have not been approved. Many of these comments may be legitimate!

SELECT *
FROM `wp_site_comments`
WHERE `comment_approved` = '0'

Finally, you can use queries similar to the following* to review and/or delete all comments containing a certain keyword:

DELETE
FROM `wp_site_comments`
WHERE `comment_content` LIKE "%viagra%"

*Replace “DELETE” with “SELECT *” to review comments before deleting them

How to Delete Orphan User Meta

To find and delete orphaned user meta from wp_usermeta  table:

SELECT * First!

DELETE FROM wp_usermeta WHERE user_id NOT IN (SELECT ID FROM wp_users)

WPMU DEV - The WordPress Experts

More Resources to Help Optimize WordPress Database

One of the greatest values of being a Premium WPMU Dev member, is the ability to discuss advanced WordPress topics like this with experts. in addition to the best WordPress multisite plugins, themes and support, the folks at WPMU.org offer plenty of helpful advice like the article below which I also used  during our recent efforts to optimize network performance.

If you’ve read this far, congratulations, and thank you! I hope you’ve found this information as helpful as I did. If you have additional resources or helpful MySQL queries to optimized database performance, please leave a comment below.

How to Change WordPress Admin Username and Why

I’m reblogging this from our business site for any WordPress Multisite Network Admins looking for an easy way to change their username in the wake of recent brute force login attacks, or wondering what happened to their Super Admin menu items after they did so.

What does this means to Tripawds members? Only that we are on top of things wen it comes to maintaining and securing this community. Thank you for your support.

From the Team Agreda News Blog…

WordPress BadgeFair Warning: This is a bit more technical than our usual home based business tips.

With all the news about recent brute force bot attacks on WordPress sites, however, and considering the number of WordPress sites out there, this is vital information for anyone with a WordPress Admin account.

There are plenty of articles about the WordPress Admin Botnet, so I’ll get straight to dealing with it. In short, hackers are breaking into WordPress sites using brute force login attempts on any “Admin” accounts, the default username for site administrators.

The first line of defense is to ensure you have a strong password. Change yours now, I’ll wait. that’s the first thing I did a few days ago as news of the attacks surfaced. As reports increased, more drastic steps were clearly necessary to protect all our websites.

The second (and most effective) step in thwarting these particular attacks is to change your default Admin username. You can’t just do that from your user profile, but there are various methods. One easy way to change the Admin username is to create a new user account with Admin permissions, then delete the original Admin account and transfer all posts to the new user. That’s great for the basic WP install, but it doesn’t help those of us running SimplePress Forums or multisite communities.

NOTE: Deleting an account and transferring the user’s blog posts will not reassign that user’s forum posts, permissions, subscriptions, etc. when running SimplePress.

How to Change WordPress Admin Username via PHP MyAdmin

The following are steps to quickly and easily change the default WordPress “Admin” account username in your database using PHP MyAdmin. This is less daunting than it sounds, but it does assume you have cPanel access and are familiar launching PHP MyAdmin. If not, review these simple steps with screenshots for doing so.

SIDENOTE: What that article does not address, however, is how doing so will affect your Super Admin capabilities if you are a multisite network administrator. I found out the hard way. Read on for details.

1. Log into your cPanel account, launch PHP MyAdmin and open the database for your WordPress installation.

2. Select your wp_users table and edit the row for your Admin account, usually the first, with ID 1.

edit wordpress admin username

3. Enter your new desired username in the user_login data field.

4. Click Go. That’s it! Log in with your new username and existing password.

That’s it? Well not quite if you are a multisite Super Admin. Stop here and you will discover the Super Admin menu items have disappeared once you log back in. Don’t panic.

How To Change Default User Name For Network Super Admin Account

WordPress keeps track of Super Admin users in the wp_sitemeta table. Follow a couple more steps to ensure you retain Super Admin powers when changing your Admin username.

1. Change Admin username as described above.

2. Browse your database for the wp_sitemeta table and edit the site_admins row.

wp_sitemeta site admins 3. Note the meta value for existing Super Admins. It will look something like this:

a:1:{i:0;s:5:”admin”;}

In this case, the 5 indicates the username has five characters, and the username is admin. Other variables may be included in this array if you have more than one Super Admin, but you get the point, right?

wp_sitemeta site admins

4. Edit the meta value for site_admins to include the new user name you changed in the first steps above. For example:

a:1:{i:0;s:9:”mynewname”;}

Note that the integer must change in relation to the number of characters in the username.

5. Click Go. That’s it, really!

Follow the simple steps above and log back into your site with your new user name and existing password. If you’re a Super Admin, you will still have your magic menus for doing all your network related stuff. And if you’re a SimplePress Forums Admin, you will still have all your posts and the permissions you need to keep managing your forums. you can even keep your display name as Admin and nobody will be the wiser.

How To Prevent Users from Creating Admin Accounts

If you’re running your own WordPress multisite network, you may want to consider this one extra step to ensure nobody creates another account with the Admin username. Not that they would actually have administrator capabilities, but better safe than sorry…

banned wordpress account user names

While logged in as Super Admin visit your Network Settings page and ensure that your list of banned usernames includes “Admin” and your site will never have another Admin user account.

Any questions?

How to Delete All Spam Comments on WordPress Multisite Network

This one is for WordPress mulitisite admins out there looking for a way to bulk delete all spam comments across their entire network and reduce database overhead. Tripawds members can feel free to skip the geekspeak.

Why WordPress stores spam comments in the first place is beyond me, but that’s a different discussion. When I discovered our wp_site_comments table was consuming more than a gigabyte of the database for our Tripawds Blogs multisite network, I was happy to find this gem of an SQL query to delete spam comments and modified it slightly to do so from all blogs at once.

DELETE FROM wp_site_comments WHERE comment_approved = 'spam';

In a matter of seconds I reduced the size of our database by more than a gigabyte!

How To Delete WordPress Network Spam Comments

Yes, WordPress does provide the ability to delete spam comments from the dashboard, but not from all sites on a multisite network at once. This simple query allowed me to delete spam from all sites in one batch. By using phpMyAdmin to execute this SQL query the job is completed quickly, efficiently and without the need to visit each site’s dashboard or the memory overhead created when WordPress attempts to process a large number of comments.

It’s that easy!

  1. Backup your database (Highly Recommended)
  2. Launch PHP MyAmin
  3. Load your network’s database
  4. Click the SQL tab
  5. Run the query above
  6. Optimize the wp_site_comments table to remove any overhead

What does this mean to Tripawds Members?

Nothing really. This is just a reminder of our ongoing efforts to keep this free community online. We spend countless hours managing the Tripawds Blogs network and discussion forums to keep everything operating at top performance. Your continuing support helps make this possible. Thank you.

Welcome to Aspen.

Monotone Rocky Mountain Colorado Aspen Grove by ReneThat’s the name of our new server here at Tripawds. The name is really more appropriate for Jerry’s three legged dog blog community, along with our snowbird blog and other sites. And from the first feedback, performance kicks ass over Elm. That was the name of our first server we upgraded to a couple years ago.

“Holy cow…. it’s so fast I’m getting dizzy.”
— krun15

“SUPER FAST!!!!”
— cometdog

Remember? We had that 2010 fundraising campaign to help cover the expense of hosting our own web server to meet the demands of our growing membership. With more than 3379 members, 848 blogs, and 73280 forum posts, and counting, the Tripawds community continues to grow daily.

With increased traffic and use of a popular WordPress multisite network like Tripawds, comes the need to address server load capacity and bandwidth limitations. With that comes added expense. But first, the specs.

WordPress Multisite Server hardware requirements

WordPress network administrators and anyone considering building a niche blog network might want need to know what to expect regarding hardware if any sort of growth is expected. Our new box, in a nutshell:

  • Xeon 3450 with Hyper Threading
  • Server runs with 8 cores
  • 4 GB of RAM
  • 2 TB Bandwidth2 250 GB Hard Drives
  • Cent OS 5 x 64 Bit
  • cPanel Unlimited Domains
  • On Call Server Management

We bit the bullet to outfit this system, but we want only the best for our members. And we don’t want to do this again anytime soon. We chose this system over a Core 2 Duo 8400 with 8 GB of RAM since CPU speed/power is generally more important in processor heavy sites and RAM can be increased if needed. Elm was a Pentium 4 with 2 single core processors and 2 GB RAM. It served us well over the past couple years, but recent sporadic spiraling server loads necessitated this upgrade.

Having a server manager is highly recommended for this type of job. That, or some serious command line confidence. While DNS propagation took a bit longer than expected, the actual migration went pretty smooth thanks to the hard work of our guys at Morse Media. And thank you to those Tripawds members who helped us iron out a few kinks.

What does this mean for Tripawds?

If you’ve ever had a comment not save when posting in the forums, or visited the site only to find it wouldn’t load, this upgrade is for you. Say goodbye to database connection errors, and feel free to join us in the chat with all of your friends. This upgrade means you are part of the largest online community for three legged dogs, and it will always be here for you.

Tripawds is a user-supported community.
Please do what you can to help keep it online.

It also means we did it without holding another fundraising campaign! With any luck, and a little support from members like you, we won’t need to raise funds this year either. Soon you will be hearing more about a certain Tripawds member who helped make this server migration possible. Others continue to give on a regular basis. Some of you quite generously. You know who you are, and we can’t thank you enough.

In the mean time, chat away and post in the forums to your hearts content. Publish blog posts, upload photos, search for answers, share your videos and do what you can to show your support for this site by shopping online and spreading the word about the Tripawds Featured Blogs. Together, we shall continue to show the world Tripawds Rule!

PS: We couldn’t do any of this without our WPMU Dev membership either, for the best WordPress multisite plugins, themes and support!

WPMU DEV - The WordPress Experts

Jim & Rene aka Admin and JerryPPS: The quicker Rene and I can grow our team of home-based marketing executives, the sooner we will never have to ask for money to help keep Tripawds online, ever again. Please contact us directly for information about supplementing or replacing your income while working from anywhere.

No More Smelly Facebook Blogs

We just disabled the Smells Like Facebook premium theme we had activated for use on Tripawds Supporter blogs. In fact, we deleted the theme files altogether because we don’t intend to bring it back. Why? Because we don’t want to smell like Facebook around here anyway.

Smells Like Facebook Copyright InfringementI just read over on the  WPCandy WordPress News blog about how Facebook sued the theme developer over copyright infringement. As if a simple cease and desist letter from such a behemoth wouldn’t do the trick, the company filed three charges against the theme developer with a maximum penalty of more that $220,000. Cough.

As a small user supported community, we don’t care to go anywhere near there. Facebook has every right to protect their intellectual property, but to sue some guy who gave away a free blog theme for six figures!? I’m just sayin’ …

What I am sayin’ for any Tripawds bloggers who may have activated the Smells Like Facebook theme on their site is, don’t panic. If you see a blank white screen when trying to view your site, it is not broken. Simply visit the Appearnace tab in your dashboard by visiting yoursite.tripawds.com/wp-admin and selecting a new theme.

Who knows how Zuckerberg may unleash his legal dogs on all those sites out their unknowingly using a theme that infringes on his copyright. But I do now how Facebook came up with the huge penalty. That’s probably what they had to pay their lawyers to file the charges!

Behind the Scenes is brought to you by Tripawds.
HOME » NEWS » BLOGS » FORUMS » CHAT » YOUR PRIVACY » RANDOM BLOG