WordPress Optimization: Database Edition

Optimizing AutoLoad

The autoload attribute of a MySQL table controls whether the table is automatically loaded into memory when the database starts.

For relatively large WordPress sites, be sure to check the size and row count of the “wp-postmeta” table. In general, this table may be the largest table in your database. You can use the following command to find out which table is the largest in your database, as well as how many MiB and how many rows of data this table has

SELECT table_name,
       (data_length + index_length) / 1024 / 1024 AS total_mib,
       table_rows
FROM information_schema.tables
WHERE table_schema = 'wordpress'
ORDER BY (data_length + index_length) DESC
LIMIT 1;

Its output may look similar to this: table_name refers to the table name of the largest table, total_mib refers to the size of the largest table (MiB), and total_rows refers to how many rows of data the largest table has

| table_name  | total_mib | total_rows |
| ----------- | --------- | ---------- |
| large_table | 10.24     | 100000000  |

If these relatively large tables are tables you frequently use, you can set them to autoload. This sacrifices some memory space in exchange for faster query speed. The memory usage depends on the size of the tables you set to autoload; in general, it will occupy an additional 10%-50% on top of the table size

We will use a simple method (I don't know the complex method) to set the autoload for WordPress options. First, you can install a plugin (highly recommended) called “Database Cleaner”. This plugin lets you clean the database directly within WordPress without needing to understand complex SQL query commands


As you can see, you can directly set whether items in Options are autoloaded. If you want to disable autoload, you can uncheck the checkbox on the far right

If you don't know what this option does, you can refer to Used By and click the query icon in the Size column to see what is actually stored in this option


For plugins that have been disabled or deleted, there will also be corresponding hints in the Used By column


Reduce the Number of Junk Tables


Still using this plugin, click the Tables section to see which tables are used by which plugins. Before deleting a table, it is recommended to carefully investigate which plugin created the table; otherwise, accidental deletion may cause strange problems


You can use the buttons here to switch between table optimization and table deletion modes

Clean Up Post Revisions


In the WordPress Core section, you can see the number of post revisions, auto-drafts, and posts in the trash. If you do not need version revisions, you can safely delete them. Of course, you can also limit the maximum number of revisions per post; once this number is exceeded, the oldest revision will be deleted when a new revision is generated

// 最大修订数量
define( 'WP_POST_REVISIONS', 3 );

Defragment

Run this query in the database to defragment; MySQL will rebuild the table. After execution completes, you can see that the query took 29.1617 seconds


Reference:

MySQL :: MySQL 8.0 Reference Manual :: 17.11.4 Defragmenting a Table

Choose the Correct Database Version

As is well known, the currently major versions of MySQL in use are 5.7 and 8.x. You can refer to online performance tests when choosing a database. In addition, if you are running a large website with WordPress, you can try MariaDB, because MariaDB has a slight advantage in queries on large databases

When querying large amounts of data with indexes, MariaDB's query speed is indeed faster than MySQL's

Item\DatabaseMySQLMariaDBQuery max value of time field0.006 seconds0.001 secondsFilter time field and sort0.398 seconds0.020 secondsQuery level field0.065 seconds0.049 secondsQuery message field0.003 seconds0.005 secondsMySQL Read/Write Separation

In most cases, MySQL read/write separation can bring significant performance improvements. It is recommended to use a primary with multiple replicas architecture: write to the primary and read from replicas, and performance will improve considerably when the replicas are in read-only mode


You can use some middleware to implement read/write separation without modifying application source code. If you use WordPress and are too lazy to deploy middleware, you can try the existing open-source plugin LudicrousDB (github.com) to implement read/write separation. However, this method usually only works properly on certain PHP versions, so you need to test it thoroughly, and after it is stable in the development environment, gradually deploy it to production

To do this, we need to use multiple servers to form a MySQL read/write separation cluster or a sharded database cluster. Deploying multiple MySQL instances on the same server with Docker may not necessarily bring a large improvement. The best approach is to use multiple cloud servers to form a cluster. We can choose a cloud provider that supports private network interconnection to avoid certain latency caused by traffic going over the public network


At present, some nodes of 雨云 already support private network interconnection between multiple servers, which is very suitable for MySQL database clusters. If you want, you can also set up a Redis cluster along the way. If you want to try it, please purchase at least 3 servers to build it, or purchase one server with a slightly higher configuration and use Docker to run multiple instances for practice

Article repost policy

Request authorization before reposting and retain the authorization code, original URL, and go/affiliate links.

Original URL: https://www.geekcorelab.cn/en-US/post/database-optimization-for-wordpress