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

Slow query on housekeeper with PostgreSQL

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.8.5
    • Fix Version/s: 1.8.6, 1.9.5 (alpha)
    • Component/s: Server (S)
    • Labels:
      None
    • Environment:
      Ubuntu Lucid 64 bits. DB PostgreSQL 9.0.3. ~24GB RAM, RAID10 array. Shared database (other apps uses this DB).

      Description

      Greetings,

      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.

      Sincerely,

      Ildefonso Camargo

        Attachments

          Activity

            People

            • Assignee:
              dimir dimir
              Reporter:
              ildefonso Ildefonso Camargo
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: