History Tables – Housekeeper

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.

14 Responses to “ “History Tables – Housekeeper”

  1. Colttt says:

    do you have an tutorial for PostgreSQL 8.4?

  2. Colttt says:

    do you have an query to run Housekeeping manually via cron? (for postgres?)

  3. 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 */

  4. nima010 says:

    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?

  5. […] 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 […]

  6. Edson says:

    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?

  7. […] 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 … […]

  8. João Lopes says:

    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.

Leave a Reply