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

slow query in portgres on users that not an Super-Admin

    XMLWordPrintable

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Major
    • Resolution: Won't fix
    • Affects Version/s: 2.2.2, 2.2.3
    • Fix Version/s: None
    • Component/s: Frontend (F)
    • Environment:
      OS CentOS6.4 Storage: 4x320GB RAID10, RAM: 8GB, CPU: 8 cores. VMware, Portgres 9.3 partition.
      Number of hosts: 4462, Number of items: 241610, Number of triggers: 66727, Number of users:66, Required server performance, new values per second: 1528.56

      Description

      Hello, we have a slow query in portgres on users that not an Super-Admin.

      pg_log:
      < 2014-06-03 15:49:42.660 MSK >???????: ?????????????????: 290868.729 ??, ????????: SELECT DISTINCT g.graphid,g.name FROM graphs g,graphs_items gi,items i,hosts_groups hg,hosts h WHERE NOT EXISTS (SELECT NULL FROM graphs_items gi,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid='19' WHERE g.graphid=gi.graphid AND gi.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND NOT EXISTS (SELECT NULL FROM items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid='19' WHERE g.ymin_type=2 AND g.ymin_itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND NOT EXISTS (SELECT NULL FROM items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid='19' WHERE g.ymax_type=2 AND g.ymax_itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND hg.groupid='66' AND hg.hostid=i.hostid AND gi.graphid=g.graphid AND i.itemid=gi.itemid AND g.graphid=gi.graphid AND h.hostid=i.hostid AND h.status<>3 AND g.flags IN ('0','4')

        Attachments

        1. explain.txt
          9 kB
        2. explainSuper-Admin
          4 kB
        3. schema.dump
          324 kB
        4. ticket.dump
          15 kB
        5. zbx_export_templates.xml
          52 kB

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            ulypka Artur
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: