Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  2. ZBX-3877

Slow query on housekeeper with PostgreSQL


    • Icon: Incident report Incident report
    • Resolution: Fixed
    • Icon: Major Major
    • 1.8.6, 1.9.5 (alpha)
    • 1.8.5
    • Server (S)
    • None
    • Ubuntu Lucid 64 bits. DB PostgreSQL 9.0.3. ~24GB RAM, RAID10 array. Shared database (other apps uses this DB).


      First: thanks for this great monitoring system. We use it with PostgreSQL on several production servers, and will be able to test and give feedback / patches for PostgreSQL issues / improvements we may find for Zabbix.

      I got that query an slow one (with more than 80 seconds query duration):

      delete from history where itemid=22330 and oid in (select oid from history where itemid=22330 limit 500;

      After taking a look at the code, it is from housekeeper.c , around line 93 (on 1.8.5 source).

      Anyway, I just added that index:

      create index idx_oid on history (oid);

      Please note the index is not unique. And now the query takes only 1.5 seconds.

      This is a Major issue because it was actually slowing-down the whole server (there are other apps using this DB server).

      I think you should include that index into the DB schema.

      On other issue (related): that delete query looks a little scary to me, because it doesn't precisely identify which rows it have to delete.... I'd feel more safe if you were to use a more specific query, something like:

      delete from history where itemid=22330 and oid in (select oid from history where itemid=22330 order by clock asc limit 500);

      That will sort using clock, and delete 500 oldest items, this query currently takes around 2 seconds to run.

      I hope this helps to improve this already good monitoring system. I'll take a closer look at the DB usage, and report other findings I may come up.


      Ildefonso Camargo

            dimir dimir
            ildefonso Ildefonso Camargo
            0 Vote for this issue
            2 Start watching this issue