Zabbix busy history syncer process 100%

[judge]

Hi There

I am new to the forums and have been working with Zabbix for about a year now.

My setup:

Zabbix 2.0.1
Frontend and MYSQL on same server (small environment)
12 Core / 2GB ram server
VPS: 37.88


The problem:

Every day at exactly the same time (12am) I get an alert that the History Syncer process is at 100% and then I get false triggers for all my hosts:

"Zabbix agent on (HOSTNAME) is unreachable for 5 minutes"
This trigger uses the nodata key.

I am not sure what to do as the server does not seem busy at all and the only thing running at that time is the MYSQL Backup.

Any ideas as to why this is happening everyday at the same time and affecting false triggers?

I have attached a graph of a weekly snapshot.

Any help would be appreciated.

[f.koch]

what kind of mysql backup do you do?

[judge]

Thanks for the response.

I have cron job running at 12am using the following command:

mysqldump -u -p zabbix | gzip -9 > /Install/Backup/ZabbixData_`date +%y_%m_%d`.sql.gz

[heaje]

I assume they aren't, but are your Zabbix history tables in MySQL using the MYISAM engine? If they are, this makes sense. If I remember correctly, MYISAM will lock up a table if you have a select running. In the case of your backups, it will cause a very long-running select statement to run on all your history tables. That would prevent Zabbix from being able to insert any history data into the tables until the select queries were finished. I assume that would result in your history syncer process going to 100%.

This is a shot in the dark though. By default, Zabbix creates tables using InnoDB. That wouldn't suffer from this problem.

[judge]

I can confirm now it must be the MYSQL backup.
I moved the job to 4:30am and the Syncer process alerted that it maxed out at that time as well.

Also, all my hosts triggered that they are unavailable when they were not at that time.

Very odd that a simple mysqldump would be doing this. I'm pretty sure its InnoDB.


Any other suggestions for backing up my Zabbix database?

Thanks!

[f.koch]

Thanks for the response.

I have cron job running at 12am using the following command:

mysqldump -u -p zabbix | gzip -9 > /Install/Backup/ZabbixData_`date +%y_%m_%d`.sql.gz

ok that is your problem, with this command the tables are locked during the backup, and this brings zabbix in trouble.

you need to do a hotbackup

take a look here:

http://www.percona.com/software/percona-xtrabackup

regards flo

[judge]

Thanks very much, will look into that.

[heaje]

@jduge - Add the option "--single-transaction" to your current mysqldump command that you already have. That will tell mysqldump to do the backup in a transaction. That will prevent the tables from being locked. In fact, that's exactly how I do my backups. I have no problems during my backups at all (and my history tables have 500+ million rows).

@f.koch - Thanks for mentioning that. As I was typing my previous response I knew I was missing something....