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

inefficient frontend sql

    XMLWordPrintable

    Details

    • Type: Incident report
    • Status: Reopened
    • Priority: Trivial
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Frontend (F)
    • Labels:

      Description

      some "interesting" operations with sql while importing a template in trunk.

      target system already had such a template and a single host linked to it, xml contained some new items and two custom graphs, containing those items (in total - 26 items, 5 triggers, 2 applications, 3 custom graphs. so pretty small.). when importing, all checkboxes marked. full debug output attached, but here are some highlights.

      — 1.

      UPDATE triggers SET triggerid=6 WHERE triggerid=6
      UPDATE triggers SET triggerid=7 WHERE triggerid=7
      UPDATE triggers SET triggerid=8 WHERE triggerid=8
      UPDATE triggers SET triggerid=9 WHERE triggerid=9
      UPDATE triggers SET triggerid=10 WHERE triggerid=10

      huzzah. it made me smile, somewhat.

      — 2.

      UPDATE triggers SET triggerid=94,templateid=6 WHERE triggerid=94
      UPDATE triggers SET triggerid=95,templateid=7 WHERE triggerid=95
      UPDATE triggers SET triggerid=96,templateid=8 WHERE triggerid=96
      UPDATE triggers SET triggerid=97,templateid=9 WHERE triggerid=97
      UPDATE triggers SET triggerid=98,templateid=10 WHERE triggerid=98

      i believe these are the same triggers, just on the host. they were not changed in any way, db already contained these values.

      — 3.

      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0
      DELETE FROM items_applications WHERE 1=0

      hey, maybe it will work this time...

      — 4.

      194 occurrances of "UPDATE sessions SET lastaccess".
      accompanied with 192 "SELECT g.usrgrpid", 194 "SELECT ug.userid" and 387 "SELECT u.userid..." queries. for a single template import.

      — 5.

      graph creation is a bit revealing as well. first nextid is selected, then ids updated, graph item inserted, nextid selected, ids updated, graph item inserted...

      — 6. (from alexei)

      Try to make mass updated for an item (set Units to 'B') and you will see queries like:

      UPDATE items SET units='B',itemid=22189,key_='test',hostid=10017,interfaceid=NULL WHERE itemid=22189

      instead of expected

      UPDATE items SET units='B' WHERE itemid=22189
      or
      UPDATE items SET units='B' WHERE itemid in (22189)

      The used SQL is extremely heavy because it affects a primary key and one index.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated: