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

slow query when creating host

    XMLWordPrintable

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.8.2
    • Fix Version/s: 1.8.5
    • Component/s: Frontend (F)
    • Labels:
      None
    • Environment:
      freebsd 7.1
      mysql-server-5.0.77_1
      3000 hosts
      Zabbix Server v1.8.2 (revision 11211) (29 March 2010)

      CPU: Intel(R) Core(TM)2 CPU 6600 @ 2.40GHz
      memory = 2GB

      Description

      about 5 seconds on my test installation (3000hosts)
      about 40-60 seconds on production (15000hosts)

      explain from mytop on test install:
      EXPLAIN SELECT max(ht.hostid) as hostid, ht.templateid FROM( SELECT count(htt.templateid) as ccc, htt.hostid FROM hosts_templates htt WHERE htt.hostid NOT IN ( SELECT httt.templateid FROM hosts_templates httt ) GROUP BY htt.hostid ) ggg, hosts_templates ht WHERE ggg.ccc>1 AND ht.hostid=ggg.hostid GROUP BY ht.templateid:

          • row 1 ***
            table: <derived2>
            type: ALL
            possible_keys: NULL
            key: NULL
            key_len: NULL
            ref: NULL
            rows: 3329
            Extra: Using where; Using temporary; Using filesort
          • row 2 ***
            table: ht
            type: ref
            possible_keys: hosts_templates_1
            key: hosts_templates_1
            key_len: 8
            ref: ggg.hostid
            rows: 1
            Extra: Using index
          • row 3 ***
            table: htt
            type: index
            possible_keys: NULL
            key: hosts_templates_1
            key_len: 16
            ref: NULL
            rows: 2904
            Extra: Using where; Using index
          • row 4 ***
            table: httt
            type: index
            possible_keys: NULL
            key: hosts_templates_1
            key_len: 16
            ref: NULL
            rows: 2904
            Extra: Using where; Using index

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              ugh Konstantin Agafontsev
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: