WordPress Database Performance Tuning on Linux VPS
New Post has been published on https://seostepbysteplab.com/web-performance-optimization/wordpress-database-performance-tuning-on-linux-vps
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.
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.
Once it’s installed, go to Tools -> Plugins Garbage Collector to scan the database. Garbage tables are colored in red, which you can delete.
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.
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.
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.
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.