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.

Step 5: Choose the Right VPS and Optimize MariaDB Database Server

Choosing the right VPS (virtual private server) is super important for a WordPress site with medium to large database. Many folks praise Google cloud platform (GCP) for its speed, but it’s too expensive, compared to other VPS providers, and I didn’t get the desired performance for my WordPress site with GCP.  Actually my site was pretty slow even with Intel skylake CPU and SSD.

A while back I migrated my site to DigitalOcean. I chose the 3 CPU, 1GB RAM plan ($15/month). The right number of CPUs and the optimization of MariaDB database server are the key to solve WordPress performance problems, as I later found out.

Here is the InnoDB configuration in my /etc/mysql/my.cnf file.

innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_buffer_pool_instances = 3

The InnoDB buffer pool size needs to be at least half of your RAM. (Update: For VPS with small amount of RAM, I recommend setting the buffer pool size to a smaller value, like 400M, or your VPS would run out of RAM.)

InnoDB log file size needs to be 25% of the buffer pool size. Set the read IO threads and write IO thread to the maximum (64) and make MariaDB to use 3 instances of InnoDB buffer pool. The number of instances needs to be the same number of CPUs on your system.

With the above configuration, the upstream response time (PHP-FPM and MariaDB processing time) of my WordPress site is reduced from about 2 seconds to about  0.1 seconds for GET requests of normal WordPress pages. GET requests to /wp-admin/admin-ajax.php is reduced from about 6 seconds to about 1 second. And I didn’t even set up Redis or Memcached.

Note that you won’t see the performance boost immediately after applying the above optimizations. In fact, it’s after several days that I saw the performance boost.

Wrap Up

I hope this article helped you improve WordPress database performance on a Linux VPS.

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 *