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

host.get api method SQL improvement

XMLWordPrintable

    • Icon: Problem report Problem report
    • Resolution: Won't fix
    • Icon: Major Major
    • None
    • 3.4.6
    • API (A)
    • None
    • Team B
    • Sprint 26

      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.

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

              Created:
              Updated:
              Resolved: