History Tables – Housekeeper

Posted by in database, mysql, performance, server, zabbix on May 12th, 2011 | 9 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.



9 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

Leave a Reply

CAPTCHA Image
*