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

host.get api method SQL improvement

    XMLWordPrintable

    Details

    • Type: Problem report
    • Status: Closed
    • Priority: Major
    • Resolution: Won't fix
    • Affects Version/s: 3.4.6
    • Fix Version/s: None
    • Component/s: API (A)
    • Labels:
      None
    • Team:
      Team B
    • Sprint:
      Sprint 26

      Description

      While debuging our DB performance, we encountered a SQL statement that were executed 23869 times in one day. It used 44,9% of all time that all queries that took over 1 second that day.

      Turns out that this query is used for host.get method (frontends/php/include/classes/api/services/CHost.php), and is probably called for most pages that any user may call every day.

      A sample of the query is this:

      SELECT DISTINCT h.hostid,h.name,h.status FROM hosts h,hosts_groups hg WHERE h.flags IN (0,4) AND EXISTS (SELECT NULL FROM hosts_groups hgg JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('21','45') WHERE h.hostid=hgg.hostid GROUP BY hgg.hostid HAVING MIN(r.permission)>0 AND MAX(r.permission)>='2') AND hg.groupid='174' AND hg.hostid=h.hostid AND h.status=0 AND EXISTS (SELECT NULL FROM items i,functions f,triggers t WHERE h.hostid=i.hostid AND i.itemid=f.itemid AND f.triggerid=t.triggerid AND i.status=0 AND t.status=0 AND t.flags IN (0,4)) 
      

      Environment details:
      – hosts 18352
      – hosts_groups 99820
      – rights 3179
      – items 1330422
      – functions 495390
      – triggers 279145

      Suggestions and discussions in the comments.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              zabbix.dev Zabbix Development Team
              Reporter:
              ingus.vilnis Ingus Vilnis
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: