The site is slow: what to look at in MySQL and PostgreSQL

"The site is slow" nearly always comes down to the database. The check takes a few minutes if done in the right order: first what is visible during the slowdown itself, then what has been accumulating for weeks.

Look while the problem is happening

The main rule: the most valuable information is available exactly while everything is slow. The first command for MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

For PostgreSQL:

sudo -u postgres psql -c "SELECT pid, state, wait_event, now()-query_start AS dur, query
FROM pg_stat_activity WHERE state != 'idle' ORDER BY dur DESC;"

You will see what is running right now and for how long. Usually the answer is immediate: one heavy query is holding the rest, or a hundred identical queries are waiting for a lock to clear.

Connections

mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'"
mysql -e "SHOW VARIABLES LIKE 'max_connections'"

If Max_used_connections comes close to max_connections, the application periodically gets a "too many connections" refusal. Raising the limit is not the first thing to do: every connection takes memory, and raising it on a server already short of memory only speeds the journey into swap. First work out why connections are not being released: usually a long query, or the absence of pooling on the application side.

It is also worth looking at the same thing from the other side: a sharp rise in connections can follow not from growing popularity but from bots hammering a heavy page — a search, a catalogue filter, a report generator.

Slow queries

Without a slow query log there is nowhere further to go. MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

A day later:

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

PostgreSQL, in postgresql.conf:

log_min_duration_statement = 1000

Then the pg_stat_statements extension, which shows total time per query directly:

SELECT calls, round(total_exec_time) AS total_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Note: what matters is not the longest query but the one with the greatest total time. A 50-millisecond query run a thousand times a minute does more damage than a ten-second report once a day.

The setting that gets forgotten most often

For MySQL and MariaDB it is the InnoDB buffer pool size. The default is 128 megabytes, and it has not changed in decades. On a server where the database occupies several gigabytes, that means constant reading from disk instead of from memory.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

A reasonable guideline for a dedicated database server is half the RAM; for a server that also runs a web server and PHP, a quarter, with an eye on not starting to swap. That one setting usually gives more than a week of query optimisation.

Two things that quietly eat the disk

MySQL binary logs. They are needed for replication and point-in-time recovery, grow continuously, and by default are sometimes never removed. To check:

sudo du -sh /var/lib/mysql/*bin.*
mysql -e "SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'"

If you have no replication and do not use point-in-time recovery, set the retention to a sensible few days.

PostgreSQL WAL files with a forgotten replication slot. A less known and more dangerous case: if a replication slot exists but its consumer no longer connects, PostgreSQL is obliged to keep every log since the disconnection — and it will keep them until the disk runs out.

sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

An inactive slot nobody needs has to be dropped. This is one of the few situations where a database is guaranteed to drive a disk to zero.

Size and growth

SELECT table_schema, round(sum(data_length+index_length)/1024/1024) AS mb
FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;

What is useful is less the current figure than the rate at which it changes. A database that doubled in a month without a matching rise in traffic usually means some logging table is being written and never cleaned — sessions, task history, a plugin's event log.

Security in two lines

Since you have the database console open, two things are worth checking. First, whether the database is reachable from outside (ss -tulpn | grep 3306): it should hardly ever face outwards. Second, accounts that may connect from any address:

mysql -e "SELECT user, host FROM mysql.user"

Entries with host = '%' mean "connections from anywhere". If the port is open as well, a password is all that stands between the database and the internet.

Backups

One last thing. The existence of a backup file guarantees nothing; only a tested restore does. A dump that has never been restored is as likely as not to turn out truncated, taken with a locking error, or to contain the wrong database. That has to be checked in advance and on a separate machine, not on the day the copy is needed.

For everyday watching, three figures are enough: the number of connections, the count of slow queries per day, and the size of the database. All three change slowly and predictably, and any sharp move in one of them is worth a look. What they look like on one page is on the demo below.