Question InnoDB backup configuration ok?

[Amauri]

Server operating system version

AlmaLinux 8.10

Plesk version and microupdate number

18.0.69

Hi

I have 40 seconds of downtime each day at the exact moment Plesk generate a backup.

The database is heavy and a 500 error is triggered.

I asked ChatGTP and he suggest adding this configuration file.

/etc/psa/dump.d/01-custom-my.cnf

[mysqldump]
single-transaction
quick
lock-tables = false

All the tables of the big website are InnoDB.
Not all of the others websites.

Is it safe ? I'm not expert on this point and I prefer human review

Thanks!

 

[Bitpalast]

A web server 500 error that is caused by a database dump is quite unlikely. What is the exact error message that is logged along with the 500 error code? Also, MySQL dumps should not impact regular database operations at all, because they are only read operations. Is it really a 500 error? Or might it be a 504?

 

[Amauri]

Right, I assumed it was a 500, but it's not, sorry.

It's because it takes "more than 10 seconds to load« . Not sure if it's a 504 or a timeout of the monitoring system, but there is something on the backup process that slows down the database.

I have delayed the backup by an hour for today to confirm if it's 100% related.

 

[Bitpalast]

Yep, let's check that first. It's likely a load issue as you already figured out. But it might not be the dumps. The dump process could drive CPU usage up by about one single core. It could be a a disk I/O issue that slows the system down. But it could also be data compression, which has a significantly higher impact on speed. What compression level is used in the backup? Maybe less compression could help. Also the disk I/O priority and CPU process priority should be lowered. Try I/O priority 7 (highest digit 7 = lowest I/O priority) and process priority 14 or higher to make the backup process significantly less important compared to other things going on.

 

[Amauri]

It was set to Priority 10/IO priority 7, I switched to Priority 16/IO priority 7.

[edit] compression level from fast to fastest


I keep you updated tomorrow.

Thanks!