First, I recommend to you read about the differences between the history and trends table.
Each item on Zabbix can have your own housekeeper value. But this feature turns housekeeping process a performance killer, because delete_history() function executes a DELETE per item every turn. If you have 100k items, 100k DELETE queries will be executed.
This can be a big problem using InnoDB engine on MySQL, because DELETE is extremelly slow on large tables and deleted rows doesn’t release space on disk.
My proposed and tested solution is disable housekeeping and partitioning some tables.
To know more about it. read my post Partitioning Tables.
do you have an tutorial for PostgreSQL 8.4?
Hi Colttt,
No, I suggest you to watch this post http://www.zabbix.com/forum/showthread.php?t=14208&page=2
http://www.zabbix.com/wiki/howto/db/postgres/partition
do you have an query to run Housekeeping manually via cron? (for postgres?)
Coltt,
I have a purge query for this – as noted, very slow, but you can run when/how you want – this is for one table and has some min/max to control speed; not great, but usable if you don’t want to run real housekeeper:
DELETE trends t
FROM trends t JOIN items i ON i.itemid = t.itemid
JOIN hosts h ON i.itemid = h.hostid
WHERE i.itemid < 22250 /* To do only some */
AND i.status = 0
AND h.status = 0
AND clock 0 /* Needed to get DB to use index on trends */
See also these pages (in Russian): http://www.zabbix.com/wiki/non-english/ru/partitioning_in_postgresql
http://www.zabbix.com/forum/showthread.php?p=88879
Great job Sergey
Hi
Thanks for your good recommendation.
If I disable “Housekeeping” feature, how can I remove garbage data from database?
Does Housekeeping delete garbage data from database ? or only marks those rows ?
If it does not remove, how can I reduce size of mt tables?
If you disable “Housekeeping”, you need create partitions on tables (http://zabbixzone.com/zabbix/partitioning-tables/). So, when you “DROP PARTITION” it erases files from your disk.
[…] des tables énormes et il deviens impossible de faire le ménage dans l’historique (Housekeeper) ou des sauvegarde, d’ou le partitionnement de table en […]
Hi, Ricardo!
I have to keep data for 5 years (legal requirements) for around 1000 devices. Making some math gives me something like 500GB data… Then partitioning is a real solution, but how to disable housekeeper in version 2.0? Any idea/hint?
Hi Edson,
in the same way: DisableHousekeeping=1 on zabbix_server.conf
http://git.zabbixzone.com/trunk/.git/blob/HEAD:/conf/zabbix_server.conf#l258
ps: saudações brasileiras
tá legal… nessa vc me pegou… hehehehe
valeu a dica! T+
[…] History Tables – Housekeeper | Zabbix ZoneMay 12, 2011 … 9 Responses to “ “History Tables – Housekeeper”. Colttt says: May 24, 2011 at 11 :32 am. do you have an tutorial for PostgreSQL 8.4? Reply … […]
Hi Ricardo,
I have 2 hosts that for some strange reason have a gigantic number of items: one with 11.000 and another with 15.000. I checked the items table and several items were created based on discovery for disks. How can I remove this items? Via the GUI, I get time out.
Thanks in advance.
Hi João,
you can remove items directly from database. There is some examples in this link: http://pastebin.com/ce1Rnkid
After remove Triggers, Functions and Items, try to remove the host using GUI