WordPress Database Performance Tuning on Linux VPS

As your WordPress site grows, there are more and more posts and comments in your WordPress database, which will become the bottleneck of WordPress site performance. It’s important that you optimize the underlying MySQL/MariaDB database server as much as possible. In this article, I’m going to share with you how I improve the performance of WordPress database.

Prerequites

This article assumes that you have installed WordPress a Linux VPS (Virtual Private Server) or you have root access to the underlying OS. If you use shared hosting, it’s likely that your hosting provider have already done performance optimizations on MySQL/MariaDB database server.

Step 1: Delete Unused Plugins

Plugins will slow down your database and they can have vulnerabilities. You should remove those plugins that you don’t use.

Step 2: Delete Garbage Tables in your WordPress Database

Many plugins will create their own tables in the database when you install them on your WordPress site. However, the table can still reside in the database when you delete the plugin. Deleting garbage tables is helpful to keep a fast database.

You can install the Plugins Garbage Collector plugin to scan garbage tables.

wordpress Plugins Garbage Collector

Once it’s installed, go to Tools -> Plugins Garbage Collector to scan the database. Garbage tables are colored in red, which you can delete.

wordpress database performance tuning

If a garbage table can’t be deleted from the WordPress admin panel (possibly because it uses uppercase), you can log into your MySQL/MariaDB console and enter SQL command to delete that table.

drop table table_name;

If you are not certain if a table belongs to a deleted plugin, you can use the following SQL command to check the content of that table.

describe table_name;

Step 3: Delete Garbage Metadata

There are 4 basic tables for storing metadata in WordPress:

  • wp_postmeta: metadata for posts
  • wp_commentmeta: metadata for comments
  • wp_usermeta:metadata for users
  • wp_termmeta: metadata for categories and tags

If you have enabled multisite functionality, then there is the wp_sitemeta table. Over time, your database will accumulate orphaned metadata. For example, when you delete a post, a comment, a user, a category or a tag, the metadata is still in the wp_postmeta table, but you don’t need them any more. So you can delete them manually.

You can use the following SQL query to find out if there are orphaned post metadata.

SELECT COUNT(pm.meta_id) as row_count FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

To delete orphaned post metadata,

DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

Query the number of orphaned comment metadata.

SELECT COUNT(*) as row_count FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

Delete orphaned comment metadata.

DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

If you allow user registration on your WordPress site, then it’s likely that there are many spam users. After you delete spam users, the metadata is still there. You can delete them with:

DELETE FROM wp_usermeta WHERE NOT EXISTS ( SELECT * FROM wp_users WHERE wp_usermeta.user_id = wp_users.ID );

Step 4: Clean Up wp_options Table

Many plugins or themes adds options in the wp_options table. However, when you delete a plugin or theme, their options can still be in the wp_options table. For example, I uninstalled the Disqus comment plugin and switched back to the WordPress native comment system. But there are still Disqus related options in the table, which you can find out with teh following SQL query.

select * from wp_options where option_name like '%disqus%';

To delete these options, run

delete from wp_options where option_name like '%disqus%';

You can use the free plugin named Clean Options to find out all possibly orphaned options. Even though this plugin hasn’t updated for 9 years, I find it very useful. Once you install and activate this plugin, go to Tools -> CleanOptions and scan the wp_options table.

Please keep in mind that this plugin isn’t 100% accurate in detecting orphan options. So you should do a Google search about the option name to be certain.

wordpress clean orphaned options

Sometimes, the option value can give you a clue which plugin or theme this option belongs to. You can use the following SQL query. Replace option_name with the real option name.

select * from wp_options where option_name like 'option_name';

If you still are not certain about a particular option, then please keep it in your WordPress database. Better safe than sorry.

Rate this tutorial
[Total: 0 Average: 0]

One Response to “WordPress Database Performance Tuning on Linux VPS

Leave a Reply

Your email address will not be published. Required fields are marked *