Find out whether the database is the problem

Before tuning anything, confirm the database is what makes pages slow. A query monitor plugin (on WordPress, Query Monitor) shows how many queries each page runs and which are slow. Hundreds of queries per page, or single queries that take more than a second, point to the database.

For cPanel users (no root access)

Clean up the data

• Delete old post revisions, spam comments, expired transients and leftover tables from removed plugins.
• On WordPress, check the size of autoloaded options. Large autoloaded data is loaded on every page.
• Use phpMyAdmin’s Optimize table after deleting a lot of data, to reclaim space. It is not needed as routine maintenance on InnoDB tables.

-- WordPress: size of autoloaded options (run in phpMyAdmin, SQL tab)
SELECT ROUND(SUM(LENGTH(option_value))/1024) AS autoload_kb
FROM wp_options WHERE autoload IN ('yes','on');

Replace wp_ with your table prefix. More than about 1 MB is worth investigating.

Add caching

Page caching stops most visitors from triggering database queries at all. An object cache such as Redis keeps the results of repeated queries in memory. Our guide to Redis Manager for cPanel shows how to add one.

Fix slow plugins and queries

• Remove plugins that run heavy queries on every page, such as some statistics and related-post plugins.
• Ask the developer of a custom application to add indexes to columns used in WHERE and JOIN clauses.
• Use the newest PHP version your site supports; it also speeds up the code that talks to the database.

For server administrators (root access)

Size the InnoDB buffer pool

The buffer pool keeps table data and indexes in memory. On a server that also runs a web server and PHP, a common starting point is a quarter to a half of the RAM, and no more than the total size of your databases. Set it in /etc/my.cnf:

[mysqld]
innodb_buffer_pool_size = 2G

Restart the service with /scripts/restartsrv_mysql, then watch memory use. If the server starts to swap, lower the value.

Turn on the slow query log

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1

After a day, summarise the log to find the worst queries:

mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

Check other common settings

MySQLTuner is a free script that reviews a running server and suggests changes. Treat its output as hints and change one setting at a time.

Keep the database server current

Newer MySQL and MariaDB versions are faster and still get security fixes. See our guide to choosing MariaDB or MySQL on cPanel.

When to move to more resources

If the database still struggles after cleanup, caching and tuning, the server is too small for the workload. A Cloud VPS gives the database its own memory and fast NVMe storage; very large stores may need a dedicated server.