During mysqldump, zabbix seems to backlog...

[tcweb]

Hello, I have a nightly cron job setup to run mysqldump.
The dump file is about 22GB, to give you an idea of the size of the environment.

I see these every night, coinciding with the backup time:



Is this normal/expected? Or does it indicate some issues with the database that may require tuning?
I understand backup/dump is an expensive operation, but I am not a DBA, so perhaps I'm missing something, either flags to use on mysqldump, or some mysql tuning.

Thank you for any ideas or suggestions.

-Tom

[PavelZ]

Of course, the DB slows down with massive operations.
If triggers irritate you, you can set up maintenance.

DBA? What would they do? They won't rewrite Zabbix

[LukeAB93UK]

If you are doing a big job like dumping the database that Zabbix is actively using, then yes things are going to get backed up. It would be better to set up database replication rather than trying to dump the database to a .sql file every night especially when it is in use.

[tcweb]

OK, OK...I was only following some advice from other forum participants who suggested a regular mysql dump.

I support large applications on Oracle RDBMS, and a nightly RMAN backup does not disrupt operations. I also have other zabbix installations (smaller than this one) that do not have this issue when doing a dump. so my first instinct is to assume that there may be a performance issue with my database.

[PavelZ]

There is a fairly simple solution: you do not backup the history tables data at all. Information important for fast restore Zabbix from backup is located in other tables.
Of course, in the event of an accident, the accumulated values will be lost, but are they really that important for your monitoring?

[tcweb]

Point taken. This may be the way forward. Until I can experiment with mysql replication...

[tim.mooney]

Hello, I have a nightly cron job setup to run mysqldump.
The dump file is about 22GB, to give you an idea of the size of the environment.

What flags are you using with mysqldump?

Have you tuned your database installation at all?

If you've set up your database correctly, all your tables should be InnoDB (or equivalent). Are you using

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

The "--skip-lock-tables" probably isn't needed, but we usually include it for InnoDB just to be certain.

You definitely want "--single-transaction".

[tcweb]

Hi Tim, Thank you for this. I am not experienced with MySQL, other than some basic tuning, I have not done much.
I'll do some research, but my mysql schema is stock, as created by Zabbix installation instructions.

The options you list above, those are meant for the mysqldump, right?

-Tom

[tim.mooney]

Yes, those are mysqldump options.

[tcweb]

What does it mean to say "all your tables shoudl be InnoDB?"

Is this how the schema is configured by default (zabbix installation)?

[tim.mooney]

What does it mean to say "all your tables shoudl be InnoDB?"

Is this how the schema is configured by default (zabbix installation)?

Yes, the default "schema.sql" sets ENGINE=InnoDB; for each table it creates.

The Zabbix installation documentation used to have a section of things they told you to be sure were set before you created your database, i.e. settings they expected to be in place for the initial database setup. Things like "file_per_table=1".

I can't find that documentation any more, so perhaps they no longer care about any of those settings, but they are setting ENGINE=InnoDB specifically when creating the tables.