Database management issue

[pt_tomcat]

I am using zabbix 1.8.5 with Mysql to monitor over 150 servers.

Currently I am facing two problems.

First problem is housekeeping : I setup housekeeping on web interface.
I set action not keep older than 30 days and event more than 180 days.
But still database is growing. Currently DB size is 80 GB.
Even I tried deleting history table form webinterface / Items / clear history for selected . still it didn’t relize any size on my DB.

I was wondering if there is any script to clear history table on mysql.

Second problem is dumping mysql for backup. I tried to use mysqldump command to get backup form mysql. Dump fails every time after copying 4 GB data.
Nothing in log file. I think it seems MySQL DB is corrupted.
do you have any idea?

Thank you in advanced for helping me.
Much appreciated.
Arash

[Zaniwoop]

you might want to try something like this...

mysqlcheck -u root -p password --auto-repair --check --optimize --all-databases

[pt_tomcat]

Thanks for your help.

I did try that command over weekend. unfortunately it filed when tried to fix History table .


task just stuck in the system after 50H I decided to kill the process since there wasn't any acitity on disk ( I checked with IOtop command)

I decided to delete History tables. is there any script for that?

I am not sure what is the best way to delete history data. shall I just wipe all history table?

[Zaniwoop]

looks like your database is FUBAR BUNDY

[heaje]

A couple of things

First, what is the file system type on the disk that you are putting your dump file on? 4GB is the limit for FAT32, and it's very interesting that your dump fails at 4GB.

Second, in MySQL, deleting data from InnoDB tables does NOT give you the space back. It simply marks the space in the files as empty and MySQL will then fill that space back in with new data before it grows the files again (a very simplistic explanation I'll admit).

Also, what is the interval at which you are collecting data? The smaller the interval, the more data you'll collect (which leads to large database sizes).

Also, to wipe your history tables, just run the SQL command "TRUNCATE <table_name>;". That completely wipes out the data in the table.

[Zaniwoop]

FAT32? Like in Windows??

ouch

[samonrichard]

Your Zabbix database keeps growing because MySQL doesn’t release space after deletes—run OPTIMIZE TABLE on large history tables or use partitioning for better housekeeping. For backups, use mysqldump --single-transaction --quick or switch to Percona XtraBackup. Regular optimization with proper database management services will keep your DB size and backups under control.