need help to clean DB

[natalia]

Hi,

I am using Zabbix 2.0.6, DB : MySQL

I see in log :
housekeeper deleted: 109039 records from history and trends, 136536 records of deleted items, 92112 events, 0 alerts, 0 sessions

but looks like nothing is deleting at all, DB is grow (table history is very large)

How can I check that housekeeper is working ? Why DB is growing with the same number of nodes and templates ? How to clean history table ?

I find a lof of info regarding partitions, is it relevant for v2.0.6 ?


Parameter Value Details
Zabbix server is running Yes 127.0.0.1:10051
Number of hosts (monitored/not monitored/templates) 150 114 / 0 / 36
Number of items (monitored/disabled/not supported) 6469 6323 / 37 / 109
Number of triggers (enabled/disabled)[problem/unknown/ok] 3323 2277 / 1046 [23 / 0 / 2254]
Number of users (online) 5 2
Required server performance, new values per second 77.08 -


Housekeeper:
Do not keep actions older than (in days)
Do not keep events older than (in days)

DB file :
-rw-rw---- 1 mysql mysql 20923285504 Oct 16 12:47 /var/lib/mysql/ibdata1

Thanks for the help !

[Pada]

Deleting the rows won't free up the space on your HDD, however, it does get cleaned up in MySQL, which will then be reallocated/reused by new data.

We've enabled the innodb_file_per_table setting on our Zabbix's MySQL DB, exported the data and then imported it again.
By doing that, we reclaimed HDD space and we now have the ability to reclaim space by "optimizing" a single table. Unfortunately the ibdata file won't shrink, but it won't grow as fast when you have the innodb_file_per_table setting enabled, because then it only contains like indexes and a few other things (and not your row data).
You can read more about that setting here: http://dev.mysql.com/doc/refman/5.5/...blespaces.html

MySQL server optimizations makes a massive difference in terms of the DB performance!

With a low "new values per second" like you have (under 100), you don't need a beefy/high performance DB, so you can still get away without doing optimizations, but it does impact the user experience when viewing graphs when the DB is slow.

[natalia]

Deleting the rows won't free up the space on your HDD, however, it does get cleaned up in MySQL, which will then be reallocated/reused by new data.

We've enabled the innodb_file_per_table setting on our Zabbix's MySQL DB, exported the data and then imported it again.
By doing that, we reclaimed HDD space and we now have the ability to reclaim space by "optimizing" a single table. Unfortunately the ibdata file won't shrink, but it won't grow as fast when you have the innodb_file_per_table setting enabled, because then it only contains like indexes and a few other things (and not your row data).
You can read more about that setting here: http://dev.mysql.com/doc/refman/5.5/...blespaces.html

MySQL server optimizations makes a massive difference in terms of the DB performance!

With a low "new values per second" like you have (under 100), you don't need a beefy/high performance DB, so you can still get away without doing optimizations, but it does impact the user experience when viewing graphs when the DB is slow.

Thanks for the reply !
Is the above changes support by Zabbix and I will able to upgrade to the new versions ? Did you config partitions as well ? which version are you using ?

do you have exctly the steps how to config this on exists DB for v2.0.6 ? ( I have info only for v1.8)

I still don't understand why housekeeper don't delete the data from the "history" table once I see in log "housekeeper deleted: 109039 records from history and trends" ?

Thanks for your help !

[steveboyson]

Data IS deleted in the tables but the tables do not shrink in size. It is a "feature" of InnoDB tables and you cannot do anything against it besides "optimize table" or backup, delete, restore.

If you have a "full" table file, it will increase in size by means of the OS filesystem. Zabbix' housekeeper will clean records out of the table giving "free" space inside the file for new table data. But it will never shrink the file on disk.

[natalia]

Data IS deleted in the tables but the tables do not shrink in size. It is a "feature" of InnoDB tables and you cannot do anything against it besides "optimize table" or backup, delete, restore.

If you have a "full" table file, it will increase in size by means of the OS filesystem. Zabbix' housekeeper will clean records out of the table giving "free" space inside the file for new table data. But it will never shrink the file on disk.

Thanks !

I am going to reconfig my DB : partition for "history" table and enabled the innodb_file_per_table

Thanks to all for help !!!

[Pada]

Just take note that when you Partition your tables, you'll lose out on Query Cache (as described in http://dev.mysql.com/doc/refman/5.5/...mitations.html). I think you'll benefit more from the performance benefit with partitioned tables than from Query Cache.

[natalia]

Just take note that when you Partition your tables, you'll lose out on Query Cache (as described in http://dev.mysql.com/doc/refman/5.5/...mitations.html). I think you'll benefit more from the performance benefit with partitioned tables than from Query Cache.

How are you handling cleaning "history" table ?

[Pada]

We're still running Zabbix 1.8.11, using MySQL and InnoDB tables. Our HouseKeeper is is left on the default of cleaning up hourly.

We've only "cleaned up" our DB once so far, and that was when we switched from the single ibdata file to file per table setting, where we
1) stopped Zabbix completely
2) performed a mysqldump of all the tables
4) stopped mysql
3) removed the whole mysql data folder
4) started mysql
5) imported Zabbix SQL file again
There should be tutorials/guides on exactly how to do this move.

We haven't configured partitioning yet, because our system is running fast enough with our small-medium sized setup (~260 new values per second). I am very tempted to enable partitioning just to see the difference in speed.

steveboyson gave a very good explanation about how the MySQL InnoDB cleanup works.

[natalia]

We're still running Zabbix 1.8.11, using MySQL and InnoDB tables. Our HouseKeeper is is left on the default of cleaning up hourly.

We've only "cleaned up" our DB once so far, and that was when we switched from the single ibdata file to file per table setting, where we
1) stopped Zabbix completely
2) performed a mysqldump of all the tables
4) stopped mysql
3) removed the whole mysql data folder
4) started mysql
5) imported Zabbix SQL file again
There should be tutorials/guides on exactly how to do this move.

We haven't configured partitioning yet, because our system is running fast enough with our small-medium sized setup (~260 new values per second). I am very tempted to enable partitioning just to see the difference in speed.

steveboyson gave a very good explanation about how the MySQL InnoDB cleanup works.

Thanks a lot for the information !

I am checking with our DB what will be best for us

Thanks for the help !!!!

[mpasha06]

mysql backlgs

Hi,

I had the same issue and I have performed the above steps.
unfortunately to complete these steps I took 2 days, and after the db steps, I have started zabbix back, and got bunch of alerts started flowing in. huge backlogs created. even after 2 days the alerts are not cleared out. I am still 1 minute back.

Could you please reply anyone, any solution...

[mpasha06]

to get rid of

Hi All,

I was facing same issue with ibdata1 db file, and I have followed mentioned steps, to add

"innodb_file_per_table" in mysql conf file.

----------------------------------------------------
mysqldump --databases test zabbix > two_databases.sql
mysql
drop database zabbix;
exit
/etc/init.d/mysqld stop
delete both files, ibdata1 file.
/etc/init.d/mysqld start
create database zabbix character set utf8 collate utf8_bin;
grant all privileges on zabbix.* to zabbix@localhost identified by 'zabbix';
mysql zabbix <two_databases.sql


and performed database restore, it took me around two days, becouse database restore itself took longer time

than I expected, around 6-8 hours.
Everything went fine, but till date all monitoring nodes/zabbix agent had collected bunch of data.

When I started back zabbix, I was seeing all the data pushing to zabbix server, and all are queued up, it

took some time to get off from "More than 10 minutes", but still data has not cleared all, all nodes running

more than 1 minute behind, I am not seeing fresh data.

I have bounced zabbix and mysql server, disabled node for some time, removed from template from node and

re-added,

my DB config as below
------------------------------
[mysqld]
innodb_file_per_table
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#log-error=/var/log/mysql/error.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


I checked the disk i/0, its huge data writing to disk,