WordPress Multisite Database Tuning To Optimize Tripawds Network Performance

Tripawds is a user-supported community. Thank you for your support!

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

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.


To remove ads from your site and others, upgrade to a Tripawds Supporter blog!

How to Change WordPress Admin Username and Why

Tripawds is a user-supported community. Thank you for your support!

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.

Tripawds Joins SOPA Strike

On Wednesday, January 18th thousands of web sites will go dark to protest SOPA & PIPA, two US bills racing through Congress that threaten prosperity, online security, and freedom of expression.

Stop SOPA Widget Pop-up MessageTo join this effort to spread awareness about this vitally important news, two plugins have been activated across the Tripawds network:

Stop SOPA Ribbon
Help stop American Consorship by putting a ribbon that says Stop SOPA on your WordPress site or network.

Stop SOPA Widget
This plugin adds a widget that will show a modal window to increase awareness about SOPA/PIPA on Jan 18th.

Due to the sensitive nature of this community and the helpful resources all our blogs and discussion forums provide, we will not be shutting down the Tripawds network in protest. These plugins, however, shall remain activated to help us show our solidarity by doing the following.

  • As of now, all sites throughout the Tripawds network will display the Stop SOPA ribbon in the top right corner.
  • The Stop SOPA pop-up message shall display on all sites during the scheduled SOPA protest blackout time on January 18th from 8am-8pm EST (1300-0100 UTC). It is easily dismissed and will only be shown to each visitor once.

We shall keep these plugins network-activated through January 24, when congress votes on SOPA and PIPA. The pop-up message will only display during the scheduled strike but you can show it anytime until the 24th by adding #stopsopa to the end of any link to your blog like this:

Please help us put an end to these bills before they become law and end the internet as we know it.

Feel free to comment below, or chime in on this discussion about SOPA. But better yet, contact congress now!

 

How to Fix TimThumb Theme Script Security Exploit

Don’t panic. Security Breach Averted!

I first learned about the zero day vulnerability affecting many WordPress themes today on LinkedIn. Plenty has been written about the security flaw found in the timthumb.php script used by many themes that could result in a serious security breach by allowing the upload of malicious scripts to your server. I won’t go into detail, because these fine folks already have:

See the above links for plenty of information, opinions, and fixes. I’ll just quickly outline what I did to ensure we remained safe here at Tripawds.

  1. Log in to cPanel account for domain
  2. Access File Manager
  3. Search all files for “timthumb”
  4. Note location of file(s) if found
  5. Delete timthumb.php*
  6. Repeat for any other domains

*Note: Deleting the TimThumb script may break certain themes, or at least affect how they manage and display images. I chose to just delete any themes using the script.

If you need the TimThumb script running on your site, upgrade to the latest version. This issue has already been addressed. If, like me, you find some merit in the many discussions about the safety – or lack thereof – of allowing any scripts on your server to access data from third party sites, then delete the file. Or, follow these steps to remove the vulnerability:

Line 21: Ensure this constant is false:
define ('ALLOW_EXTERNAL', FALSE); // allow external website (override security precaution - not advised!)

Line 27: Remove all domains from this array:
$allowedSites = array();

What the TimThumb Issue Means for Tripawds Members

Nothing, really. Because we’re on top of things.

If, however, you are a Tripawds Supporter and had activated either of the premium themes, Magazeen or Mystique, you will need to activate a different theme. If you were using either of these themes and you cannot access your blog, again, don’t panic. Point your browser to yourblogname.tripawds.com/wp-admin/themes.php and select another of the more than 135 themes we make available to Tripawds bloggers.