DB-유

Optimizing MySQL for a WordPress Site

· mysql, wordpress, performance

Friends keep asking me to look at their slow WordPress sites, and it is nearly always the database. WordPress isn't demanding, but its schema and plugins generate a peculiar workload: lots of small queries per page, a wp_options table read on every request, a wp_postmeta table that grows into millions of rows, and tables that many older hosts still leave on MyISAM. A default MySQL config is fine for a small blog and starts to hurt at a few hundred concurrent visitors.

This is my tuning pass on a single VPS running WordPress with MySQL 5.7/8.0 (or MariaDB 10.x). Before starting, get root and MySQL root credentials, know how much RAM there is and what else shares it (PHP-FPM and nginx take their part), and take a backup first: mysqldump wordpress | gzip > wp.sql.gz.

InnoDB and utf8mb4 everywhere

SELECT TABLE_NAME, ENGINE, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'wordpress';

Anything MyISAM or utf8_general_ci should be converted; WordPress has used utf8mb4 by default for years and InnoDB is what its query patterns assume:

ALTER TABLE wp_postmeta ENGINE=InnoDB;
ALTER TABLE wp_postmeta CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Generate the statements for all tables from information_schema as in any conversion, and make sure wp-config.php has define('DB_CHARSET', 'utf8mb4'); and an empty DB_COLLATE.

Sizing the server for the box

For a 4 GB VPS running nginx, PHP-FPM and MySQL together, this is a reasonable /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu) or /etc/my.cnf (CentOS):

[mysqld]
innodb_buffer_pool_size        = 1G
innodb_log_file_size           = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method            = O_DIRECT
innodb_file_per_table          = 1

max_connections                = 100
thread_cache_size              = 16
table_open_cache               = 2000
tmp_table_size                 = 64M
max_heap_table_size            = 64M
join_buffer_size               = 1M
sort_buffer_size               = 1M

slow_query_log                 = 1
slow_query_log_file            = /var/log/mysql/slow.log
long_query_time                = 1
log_queries_not_using_indexes  = 0

Why these: the buffer pool should hold the whole database (check with the size query further down); innodb_flush_log_at_trx_commit = 2 risks up to a second of comments on a crash in exchange for much cheaper writes, which is a fair trade for a blog; tmp_table_size/max_heap_table_size matter because wp_postmeta joins with GROUP BY create temporary tables constantly. max_connections should be a little above the PHP-FPM pm.max_children; more than that only adds memory pressure. I never add query_cache_size: it is removed in MySQL 8.0 and on 5.7 it serialises every query on a global mutex, which hurts precisely under the concurrent read load WordPress produces.

Restart MySQL after the change. On 8.0, innodb_dedicated_server = ON sizes the buffer pool and log automatically, but only use it if MySQL is alone on the machine.

Cleaning up what WordPress leaves behind

Three tables need periodic attention:

-- expired transients in wp_options (autoloaded rows are read on every page)
DELETE FROM wp_options WHERE option_name LIKE '\_transient\_timeout\_%' AND option_value < UNIX_TIMESTAMP();
DELETE FROM wp_options WHERE option_name LIKE '\_transient\_%' AND option_name NOT LIKE '\_transient\_timeout\_%'
  AND CONCAT('_transient_timeout_', SUBSTRING(option_name, 12)) NOT IN (SELECT option_name FROM (SELECT option_name FROM wp_options) o);

-- post revisions older than 90 days
DELETE p FROM wp_posts p WHERE p.post_type = 'revision' AND p.post_date < NOW() - INTERVAL 90 DAY;

-- orphaned postmeta
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts p ON p.ID = pm.post_id WHERE p.ID IS NULL;

Check how much autoloaded option data is read on each request:

SELECT COUNT(*), ROUND(SUM(LENGTH(option_value))/1024) AS kb FROM wp_options WHERE autoload = 'yes';

More than a megabyte here is a sign of a plugin storing large arrays with autoload=yes; the culprits are visible with ORDER BY LENGTH(option_value) DESC. Limit future revisions in wp-config.php with define('WP_POST_REVISIONS', 5);.

Two indexes WordPress lacks

wp_postmeta has an index on meta_key(191) but not on meta_value, and wp_options has a unique index on option_name but none on autoload. Two additions help most sites:

ALTER TABLE wp_options ADD INDEX autoload (autoload);
ALTER TABLE wp_postmeta ADD INDEX meta_key_value (meta_key(191), meta_value(100));

The second one speeds up meta_query lookups from WooCommerce and similar plugins. WordPress core updates do not remove custom indexes.

Finding the plugin behind slow queries

With the slow log on, run pt-query-digest /var/log/mysql/slow.log after a day, or on 5.7/8.0 without Percona Toolkit:

SELECT query, exec_count, total_latency, rows_examined_avg FROM sys.statement_analysis
WHERE db = 'wordpress' ORDER BY total_latency DESC LIMIT 10;

A SELECT ... FROM wp_posts LEFT JOIN wp_postmeta ... ORDER BY RAND() or a query touching a custom table you do not recognise points to a specific plugin. Install Query Monitor in WordPress temporarily; it attributes each query to the plugin that issued it.

Measuring the result

SELECT ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024) AS db_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'wordpress';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Threads_connected';

db_mb should be well below innodb_buffer_pool_size; after warm-up, Innodb_buffer_pool_reads should barely move and Created_tmp_disk_tables should grow slowly relative to Created_tmp_tables. Load-test with ab -n 500 -c 20 https://example.com/ before and after and compare the page time.

Errors you will meet

  • WordPress database error MySQL server has gone away for query ...: wait_timeout too low for long admin operations, or max_allowed_packet too small for a big serialized option. Set max_allowed_packet = 64M and wait_timeout = 300.
  • Error establishing a database connection under load: max_connections reached (SHOW GLOBAL STATUS LIKE 'Connection_errors_max_connections'). Lower PHP-FPM pm.max_children or raise max_connections slightly, and consider a persistent object cache (Redis) so fewer requests hit MySQL at all.
  • ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes while converting to utf8mb4 on an old MariaDB or MySQL 5.6: set the table to ROW_FORMAT=DYNAMIC and enable innodb_large_prefix, or keep WordPress's 191 prefix lengths.
  • The site is fast for logged-out visitors but slow in the admin: page caching hides database problems from anonymous traffic. Tune with the admin dashboard in mind and rely on the slow log, not the front page.

None of this is exotic. Most of the time the culprit is one plugin doing something silly, and the slow log finds it.