Backup / clean history data

[mlodikkal]

Hi,
I have problem with my zabbix 2.2.20 database (mysql). I't have now 70GB in tables, it's working slowly and housekeeping is deleting 3-4M items every 3 hours. I want to switch to postgres but first, I'm trying to decrease database size.
I tired to do it in those steps:
1. backup config data from database (I've been already been doing it by zabbix-backup script for backup).
2. import data into new schema
3. switch which schema is used by zabbix
4. migrate history data to other server and have zabbix instance, to see historic data like disk usage trends etc
5. Start thinking about switching to postgres, and zabbix update

At first it looked fine, there was no errors in server log, I've revived mails with warning, so triggers was working. But info on dashboard didn't update, and no info seams to be written into trends (no data was visible on graphs).
I switched back to old database, and I was working fine.

My main question are:
-how perform backup without hostory data? I looks that my backup method is not to0 good...
-what tables can by safely omitted when doing database dump?
In zabbix-backup script, those tables data in not backed-up:
acknowledges
alerts
auditlog
auditlog_details
events
history
history_log
history_str
history_str_sync
history_sync
history_text
history_uint
history_uint_sync
trends
trends_uint

[kaspars.mednis]

If you have really poor housekeeper performance , you can think switching to MySQL partitioning instead of moving to postgre

https://www.zabbix.org/wiki/Docs/howto/mysql_partition

+ you wil be able to drop a whole day of old data in 1-2 seconds
- you will miss fine-tuning history periods for different items, e.g. if you drop partitions older than 2 weeks for history_uint, ALL Numeric(unsigned) data will be kept 2 weeks

70Gb is not so big for MySQL DB, maybe some mysql configuration tuning can improve the situation ? i know nothing about your hardware, RAID configuration and RAM ammount on DB server

i have switched to partitioning when my DB growth beyound ~ 350Gb because of housekeeper issues

P.S
Are you using InnoDB engine for all tables ?

Regards,
Kaspars

[mlodikkal]

Host is on I think, virtual machine 4CPU 8GB RAM, 10k disk from SAN storage. It have around 900 hosts monitored history for 30 days, trends for 1 year.

I figured it out. The problem was, that i needed to do "installation" process again (choice db in configurator on web, not only in config file). There is some db connection settings for zabbix not only in zabbix_srver.conf.


I will create partitions on postgresql, on new v.10 it's quit easy.
I successfully migrate db from mysql to postgres on my test host. I will test now and after that migrate production.

[kaspars.mednis]

Ok, if you're more familiar with postgres, move on

Good Luck,
Kaspars