Why Your Site Slows Down Every Night While mysqldump Is Running

If your site gets slow or starts throwing timeouts around the same hour every night, the usual suspects are a traffic spike, a bot, or the host's hardware. Those are worth ruling out, but a common cause is closer to home: the nightly database backup. mysqldump is simple and reliable, and with the wrong options it can block writes to your tables or saturate the disk while visitors are still clicking around.

How do I know the backup is the cause and not traffic?

The giveaway is timing. If the slowdown starts at roughly the same minute each night and ends at roughly the same point, no matter how busy the day was, you are probably looking at a scheduled job rather than visitors. Traffic is messy. Cron is punctual. Start by finding out what runs at that hour. Check the crontab for root and for the site's user, the files in /etc/cron.d, and the backup settings in your control panel. Panels often run their own database dumps on a schedule you never set yourself. Then, while the slowdown is happening, look at the MySQL process list. If you see the backup user's connection reading through tables one by one while other queries sit in states like Waiting for table level lock or Waiting for table metadata lock, you have your answer.

What is mysqldump actually locking?

By default, mysqldump runs with --opt, a bundle of options that includes --lock-tables. Before dumping a database, it takes a read lock on all of that database's tables and holds it until that database is finished. A read lock lets other sessions keep reading, but any session that wants to write has to wait. That sounds harmless for a site that mostly serves pages, but very few dynamic sites are read-only. WordPress updates options and transients, WooCommerce writes sessions and orders, and plenty of plugins log visits or bump counters on page load. When one of those writes waits on the lock, the PHP worker handling that request waits with it. A few minutes of that and every PHP worker is stuck, new requests queue up, and visitors see slow pages or 502 and 504 errors even though the CPU graph looks calm. The bigger the database, the longer the lock lasts. A dump that took seconds when the site was new can take many minutes once the order or log tables have grown. That is why this problem tends to appear gradually rather than on day one.

Doesn't --single-transaction fix this?

For InnoDB tables, mostly yes. InnoDB is the default storage engine in modern MySQL and MariaDB, and it supports consistent snapshots. With --single-transaction, mysqldump opens a transaction with a consistent snapshot and reads every table as it looked at that moment, without table locks. The site keeps writing normally and the dump is still internally consistent. Setting this option also turns off the locking that --lock-tables would do. There is one catch. While the dump transaction is open, it holds metadata locks on the tables it has read. If something tries to change a table's structure during the backup, such as an ALTER TABLE from a plugin update or a migration script, that statement waits for the dump to finish. Ordinary queries on that table then queue up behind the waiting ALTER, and the process list fills with Waiting for table metadata lock. If automatic updates or deploys are scheduled near your backup window, move one of them.

What if some of my tables are still MyISAM?

MyISAM is the older storage engine, and it still turns up on sites created years ago or imported from old dumps. It has no transactions, so --single-transaction cannot give it a snapshot. If you dump MyISAM tables with locking on, writes to them block for the whole dump. If you turn locking off, traffic keeps moving, but those tables may be captured in a half-updated state relative to each other. MyISAM also locks the entire table for every write, even outside backup time, so converting to InnoDB usually helps beyond the backup issue. Do it table by table, take a backup first, and check for anything that depends on MyISAM-specific behavior, such as full-text indexes on very old MySQL versions (InnoDB has supported full-text indexes since MySQL 5.6). Converting a large table copies it and blocks writes while it runs, so treat it as planned maintenance.

Everything is InnoDB and the flag is set. Why is it still slow?

Then the problem is usually disk and memory, not locks. A full dump reads every row of every table, and on a server with modest disk throughput that competes directly with the site's own queries. It can also push frequently used data out of the InnoDB buffer pool, the memory area where MySQL caches table and index pages. The site's queries then hit the disk more often until the cache warms up again. InnoDB has some protection against large scans flushing the cache, but on a busy server with tight memory it does not fully prevent the effect. The output side matters too. Compressing with gzip uses CPU, and writing the dump to the same disk the database lives on adds more I/O on top of the reads. On a VPS, the underlying storage may also be shared with neighbors whose backups run at the same popular midnight slot. Lowering priority with nice for CPU and ionice for disk helps with compression and writing, but keep expectations realistic. The table reads happen inside the MySQL server process, not in the mysqldump client, so ionice on mysqldump does little for them, and whether ionice does anything at all depends on the kernel's I/O scheduler. For real isolation on a busy site, the usual answers are dumping from a replica, or using a physical hot-backup tool for InnoDB such as Percona XtraBackup or mariabackup instead of a logical dump.

How do I check and adjust my own backup job?

Work through it in order: confirm the schedule, catch the backup in the act, check storage engines, then adjust the command. Everything below is read-only except the last line, which is an example dump. The mysql commands assume your credentials are in ~/.my.cnf; otherwise add -u and -p.

BASH

# What is scheduled around the slow hour
crontab -l
sudo crontab -l
ls /etc/cron.d

# Run during the slow window: look for queries waiting on locks
mysql -e 'SHOW FULL PROCESSLIST'

# List tables that still use MyISAM
mysql -e "SELECT table_schema, table_name FROM information_schema.tables WHERE engine = 'MyISAM' AND table_schema NOT IN ('mysql','information_schema','performance_schema','sys')"

# Example of a gentler dump for InnoDB tables (replace dbname and the path)
nice -n 19 mysqldump --single-transaction --quick dbname | nice -n 19 gzip > /backup/dbname.sql.gz

When you edit the real job, keep the options you already depend on, such as --routines or --triggers if you need stored procedures and triggers in the dump. If the job pipes into gzip from a bash script, add set -o pipefail so a failed mysqldump is not hidden behind gzip's successful exit. Without it you can end up with nightly files that look fine but are empty or truncated. Also time how long the dump takes, and ask whether 'night' on the server clock is actually quiet for your visitors. An audience in another time zone, or heavy bot crawling at that hour, can make a different slot a better fit. If the dumps come from the control panel rather than your own cron, the options live in the panel's backup configuration or are set by the host. Find out which options it uses before changing anything, and ask your host if you cannot see them. A nightly slowdown that keeps strict time is almost always a scheduled job, and mysqldump is the first one to check. Make sure your tables are InnoDB, use --single-transaction, keep schema changes away from the backup window, and if the dump is still heavy, move it off the primary server rather than tuning around it. From the support side, this is one of the easier slow-site cases to prove, because the process list shows exactly what is waiting on what while it happens.