mysqldump Freezes forum

[xlegends]

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]

The backup script probably locks the database or similar while doing the dump.

You can try adding

--single-transaction --skip-lock-tables

instead.

 

[xlegends]

The backup script probably locks the database or similar while doing the dump.

ya but it also does it from CLI mysql dump.

 

[Walter]

@xlegends The options Slaviks gave you were options for the cli (mysqldmp).

 

[xlegends]

@Slavik @Walter

Thx guys. I didnt even see the suggested command options

--single-transaction --skip-lock-tables

But why is this lock process happening only for xenforo DB?

 

[Chromaniac]

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.

 

[Rhody]

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.

 

[Mouth]

But why is this lock process happening only for xenforo DB?

I use below, and don't have any lock issues when dumping the DB

 --skip-opt --disable-keys --create-options --extended-insert --single-transaction

 

[Mr. Jinx]

The backup script probably locks the database or similar while doing the dump.

You can try adding

--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.

 

[Mouth]

--skip-lock-tables can affect MyISAM tables, where locking the tables is a good idea.

Not if you're taking a backup of a production DB, thus the usage of --skip-lock-tables

 

[Paul B]

I have used --single-transaction --skip-lock-tables for years without issue.

 

[xlegends]

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]

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.

 

[Sunka]

Anything wrong with this?
/usr/bin/mysqldump --opt xenforo --default-character-set=utf8mb4 >