You've already cleared transients and optimized your tables. Your database still drags. This guide goes beyond plugin cleanup into the territory of InnoDB configuration, query profiling, index strategy, and safe schema changes that improve performance without causing outages.
Understanding Your Database Workload Before Optimizing
Before adjusting configuration files or running ALTER statements, capture your actual workload patterns. Enable the slow query log temporarily to identify bottlenecks:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
Run your site under normal traffic for a few hours, then analyze the log with pt-query-digest from Percona Toolkit or review it manually. Look for queries with high execution counts or long durations. Common culprits include uncached meta queries, missing indexes on custom taxonomies, and plugin queries that scan entire tables.
Check your storage engine distribution:
SELECT ENGINE, COUNT(*) AS tables,
SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS size_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
GROUP BY ENGINE;
WordPress core tables use InnoDB. If you see MyISAM tables from legacy plugins, consider converting them, but test first. MyISAM lacks transaction support and row-level locking, which causes contention under write-heavy loads.
InnoDB Buffer Pool Tuning
The InnoDB buffer pool is your most important performance lever. It caches table data and indexes in memory, reducing disk I/O. The default allocation is often too conservative for dedicated WordPress servers.
Calculate an appropriate buffer pool size:
SELECT SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS total_mb
FROM information_schema.TABLES
WHERE ENGINE = 'InnoDB';
Allocate 60-70% of available RAM to innodb_buffer_pool_size on a dedicated database server, or 40-50% on a combined web/database server to leave headroom for PHP-FPM and other processes. Edit /etc/mysql/my.cnf or /etc/my.cnf:
[mysqld]
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
Key directives:
- innodb_buffer_pool_instances: Set to 8 or the number of CPU cores for buffer pools larger than 1GB to reduce contention.
- innodb_log_file_size: Larger log files reduce checkpoint frequency but increase crash recovery time. 256-512MB balances write performance with recovery speed.
- innodb_flush_log_at_trx_commit: Default is 1 (full ACID compliance). Setting to 2 commits logs to the OS buffer every second instead of on every transaction, improving write throughput at the cost of potentially losing one second of transactions in a crash.
- innodb_flush_method:
O_DIRECTbypasses the OS file cache to avoid double-buffering when you have a large buffer pool.
Restart MySQL and monitor buffer pool efficiency:
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
Calculate your hit ratio: (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests. Aim for 99% or higher.
Index Strategy for WordPress
WordPress core tables are reasonably indexed, but custom post types, meta queries, and taxonomy joins often benefit from composite indexes.
Identifying Missing Indexes
Check the slow query log for queries with Using filesort or Using temporary in EXPLAIN output:
EXPLAIN SELECT * FROM wp_posts
WHERE post_type = 'product'
AND post_status = 'publish'
ORDER BY post_date DESC LIMIT 20;
If Using filesort appears, the query cannot use an index for sorting. Add a composite index:
ALTER TABLE wp_posts
ADD INDEX idx_type_status_date (post_type, post_status, post_date);
For wp_postmeta, the default index on meta_key does not cover queries filtering by both key and value. Add:
ALTER TABLE wp_postmeta
ADD INDEX idx_meta_key_value (meta_key, meta_value(100));
The (100) prefix length limits index size while covering most value comparisons. Test queries before and after with EXPLAIN to confirm index usage.
Safe ALTER TABLE Execution
ALTER TABLE locks the table during execution, causing downtime on large tables. Use Percona's pt-online-schema-change for non-blocking schema changes:
pt-online-schema-change \
--alter "ADD INDEX idx_type_status_date (post_type, post_status, post_date)" \
D=your_database,t=wp_posts \
--execute
This tool creates a shadow table, copies rows in chunks, applies triggers to capture ongoing changes, and swaps tables atomically. Always test on a staging database first.
Query Cache Considerations
MySQL's query cache was deprecated and removed in MySQL 8.0 due to scalability issues. If you're still on MySQL 5.7, disable it. WordPress uses application-level caching via object cache (Redis, Memcached) which is more effective:
[mysqld]
query_cache_type = 0
query_cache_size = 0
Instead, implement persistent object caching with Redis:
apt install redis-server php-redis
systemctl enable redis-server
Install a Redis object cache plugin and configure /var/www/html/wp-content/object-cache.php. This offloads repeated queries (post metadata, options, term relationships) from the database entirely.
Advanced Cleanup Strategies
Post Revisions and Autosaves
Revisions accumulate quickly. Set a limit in wp-config.php:
define('WP_POST_REVISIONS', 5);
define('AUTOSAVE_INTERVAL', 300); // seconds
Delete excess revisions in batches to avoid locking:
DELETE FROM wp_posts WHERE post_type = 'revision'
AND post_modified < DATE_SUB(NOW(), INTERVAL 90 DAY) LIMIT 1000;
Run repeatedly until LIMIT returns fewer rows. Follow with:
OPTIMIZE TABLE wp_posts;
Orphaned Metadata
Plugin deletions often leave orphaned rows in wp_postmeta, wp_usermeta, and wp_termmeta. Identify orphans:
SELECT COUNT(*) FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;
Delete in batches:
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL LIMIT 5000;
Repeat for wp_commentmeta, wp_usermeta, and wp_termmeta, adjusting the JOIN condition.
Transients in Options Table
Transients stored in wp_options (when object cache is unavailable) bloat the table. The autoload column is particularly problematic because WordPress loads all autoloaded options on every request.
Find large autoloaded values:
SELECT option_name, LENGTH(option_value) as size
FROM wp_options
WHERE autoload = 'yes'
ORDER BY size DESC LIMIT 20;
If you see plugin transients or large serialized arrays, investigate whether they should autoload. Some plugins incorrectly set autoload = 'yes' for large datasets. Update them:
UPDATE wp_options SET autoload = 'no'
WHERE option_name = 'problem_option_name';
Delete expired transients:
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 option_name NOT IN (
SELECT REPLACE(option_name, '_transient_timeout_', '_transient_')
FROM wp_options
WHERE option_name LIKE '_transient_timeout_%'
);
Table Maintenance and Defragmentation
InnoDB tables can become fragmented after many DELETE or UPDATE operations, wasting disk space and degrading read performance.
Check fragmentation:
SELECT TABLE_NAME,
ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
ROUND(DATA_FREE / 1024 / 1024, 2) AS free_mb,
ROUND(DATA_FREE / DATA_LENGTH * 100, 2) AS fragmentation_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
AND DATA_FREE > 0
ORDER BY fragmentation_pct DESC;
Reclaim space with OPTIMIZE TABLE, which rebuilds the table:
OPTIMIZE TABLE wp_posts, wp_postmeta, wp_comments;
This locks tables during execution. For large tables, use pt-online-schema-change with ENGINE=InnoDB to trigger a non-blocking rebuild:
pt-online-schema-change \
--alter "ENGINE=InnoDB" \
D=your_database,t=wp_posts \
--execute
Connection Pool and Thread Tuning
WordPress opens a new database connection for each PHP request. Under high concurrency, insufficient max_connections causes connection errors.
Check current usage:
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';
If Max_used_connections approaches max_connections, increase the limit:
[mysqld]
max_connections = 300
thread_cache_size = 50
thread_cache_size caches connection threads to reduce overhead of repeated connect/disconnect cycles. Set it to approximately 10-15% of max_connections.
Monitor connection efficiency:
SHOW STATUS LIKE 'Threads_created';
SHOW STATUS LIKE 'Connections';
If Threads_created grows rapidly relative to Connections, increase thread_cache_size.
Monitoring and Benchmarking
Establishing a Baseline
Before and after any optimization, benchmark query performance:
mysqlslap --user=root --password --host=localhost \
--auto-generate-sql --auto-generate-sql-load-type=mixed \
--number-of-queries=1000 --concurrency=50 --verbose
For WordPress-specific load testing, use Apache Bench or Loader.io to simulate traffic and measure response times.
Continuous Monitoring
Set up periodic checks for buffer pool hit ratio, slow queries, and table fragmentation. Use a monitoring tool like Percona Monitoring and Management or integrate checks into your server monitoring stack.
Track these key metrics:
- Buffer pool hit ratio (target: >99%)
- Slow query count (target: near zero under normal load)
- InnoDB row lock waits (high values indicate contention)
- Table fragmentation (optimize when >10% free space)
Backup Before Every Change
Database optimization can go wrong. Always take a full backup before running ALTER statements or bulk deletes:
mysqldump -u root -p your_database > backup_$(date +%Y%m%d_%H%M%S).sql
For large databases, use Percona XtraBackup for faster hot backups:
xtrabackup --backup --target-dir=/backups/$(date +%Y%m%d)/
Test your backups regularly by restoring to a staging environment.
Edge Cases and Gotchas
Multi-Site Installations
WordPress multi-site stores all sites in shared tables with blog_id columns. Queries filtering by blog_id must use indexes that include it. Check EXPLAIN output for multi-site queries and add composite indexes as needed.
Custom Table Structures
Some plugins create custom tables with poor schema design. If a plugin query dominates your slow query log, review the table structure:
SHOW CREATE TABLE plugin_custom_table;
Look for missing indexes on columns used in WHERE, JOIN, or ORDER BY clauses. Adding indexes to third-party tables is safe and survives plugin updates.
Storage Engine Mixing
Running mixed MyISAM and InnoDB tables causes MySQL to use less efficient query plans. Convert MyISAM tables to InnoDB, but test thoroughly:
ALTER TABLE old_plugin_table ENGINE=InnoDB;
Some ancient plugins expect MyISAM full-text indexes, which InnoDB also supports but with different syntax.
Character Set and Collation
WordPress now uses utf8mb4 to support all Unicode characters, including emoji. Older databases may still use utf8. Check your tables:
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';
Convert to utf8mb4_unicode_ci for consistent behavior:
ALTER TABLE wp_posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
This requires adequate innodb_log_file_size and can take time on large tables.
Conclusion
Advanced database optimization for WordPress requires understanding your workload, measuring before and after changes, and applying configuration tuning carefully. Focus on InnoDB buffer pool sizing, strategic indexes, and safe schema changes using tools like pt-online-schema-change. Combine database optimization with application-level object caching for maximum impact. Always back up before making changes, test in staging, and monitor performance continuously to maintain gains over time.