History Tables – Housekeeper

Posted by in database, mysql, performance, server, zabbix on May 12th, 2011 | 14 comments

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:
    May 24, 2011 at 11:32 am

    do you have an tutorial for PostgreSQL 8.4?

    Reply
  2. Colttt says:
    June 8, 2011 at 11:49 am

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

    Reply
  3. Steve Mushero says:
    July 23, 2011 at 12:59 am

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

    Reply
  4. Sergey says:
    August 18, 2011 at 5:10 am

    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

    Reply
  5. nima010 says:
    September 12, 2011 at 11:09 am

    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?

    Reply
  6. Zabbix 1.8.11 sur Ubuntu 12.04 avec partitionnement sql des tables » Karlesnine says:
    April 25, 2012 at 9:13 am

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

    Reply
  7. Edson says:
    June 15, 2012 at 12:30 pm

    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?

    Reply
  8. Postgresql housekeeping | Imageflip says:
    July 12, 2012 at 5:04 pm

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

    Reply
  9. João Lopes says:
    December 19, 2012 at 8:10 am

    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.

    Reply

Leave a Reply

CAPTCHA

*