mysqldump Freezes forum

xlegends

Member
Hi,

I use webmin panel because of convenience. I especially like the DB management for its scheduled cron DB dumps and backups. My backups are set hourly. When this process runs, DB use freezes for about 10 seconds. This isnt a webmin script issue. It also happens with a command line mysqldump.

This is a dedicated server centos7/4c8t-cpu/mysql57/32gb ram. Resource use are very low any other time and even when this freeze happens, there is no cpu or memory spike. This issue never use to happen before xenforo on much slower machines.

Im using the default mysql57 on Cento7 mycnf configuration. Are there any settings I can configure to improve this dump process or would switching to MariaDB improve this issue?
 

Slavik

XenForo moderator
Staff member
The backup script probably locks the database or similar while doing the dump.

You can try adding
Code:
--single-transaction --skip-lock-tables
instead.
 

Chromaniac

Well-known member
bigger databases are complicated to backup if the server is underpowered or has a lot of activity. your other databases might be small if they are backing up without any issues. i tend to shut down the forum before taking the database backup.
 

Mr. Jinx

Well-known member
The backup script probably locks the database or similar while doing the dump.

You can try adding
Code:
--single-transaction --skip-lock-tables
instead.
If I'm correct, --single-transaction is perfectly safe for InnoDB backups.
--skip-lock-tables can affect MyISAM tables, where locking the tables is a good idea.
So I would only use --single-transaction for consistent backups.
 

xlegends

Member
It does pause/lock it, but only for 6-10 seconds in our case. I doubt anyone would even notice. We do a dump/backup at 3am and 3pm daily.

I do cron hourly and seems like Im always browsing at the time it happens. Maybe that's excessive. I do have a billing site on same DB that I prefer hourly. Maybe I should back them up separately and the forum less often and will use those backup options. Thx guys for the tips.
 

Mr. Jinx

Well-known member
Not if you're taking a backup of a production DB, thus the usage of --skip-lock-tables
In fact, that is not a good idea for production if you want a consistent backup. This only counts for MyISAM tables, so the lock won't take too long as most data is in InnoDB.
 
Top