Database backup and "backward" notifications.

[ptoki]

Hello.

I am running a zabbix 4.2.8 instance which database is currently about 6GB. It backs up in about 5 minutes now. Backup is using mysqldump.

During the backup zabbix is kind of frozen but after it finishes it catches up and all data is saved in the database.

The problem is that there is a trigger with nodata condition.

It detects the brief 5 minute no data and raises the problem.

The problem is kind of strange as it starts about 2:04:40 (thats correct) but finishes at 2:00:00 (and that is wrong).
The duration of the problem is also negative in webGUI.

Now, my question is three fold:
1. Is it possible to run the native database backup in a way so it does not lock the database? (no LVM available for that purpose now)
2. Is it possible to easily define a trigger which would cover all items not getting fresh data? Maybe some internal zabbix queue metric would show such case?
3. Is the negative time problem known or shall I submit a bug for it?

[tim.mooney]

Assuming all your Zabbix tables are using the InnoDB engine (they should be), then you probably just need a different set of options for your mysqldump command. I recommend you take a look at the '--opt', '--single-transaction', and '--quick' options, possibly in conjunction with whatever dump options you're already using. Keep in mind that order on the command line matters.

My Zabbix 4.4.x instance (RHEL 7.x, MariaDB 10.2.x backend on the same host, separate disks) had been negatively impacted once in a while (maybe once a month) during the mysqldump backup, and the research I did about high I/O during mysqldump found several people recommending using '--quick' as part of the backup options. I've added that to my system's backup procedure and the load average and responsiveness during backups does seem much better.

Ultimately, you need to determine which options are right for your environment, but '--quick' doesn't seem to have any negative trade-offs that I've found.

[ptoki]

Hello Tim.

Thanks for suggestions.

The --quick option is on by default so no change there. However --single-transaction is not and it helped to isolate the backup from the rest of the changes happening on DB.

Now the "nodata" events are not happening.

Once again, thank you for help!