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

Slow process on mass host creation

    XMLWordPrintable

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 1.8.4
    • Fix Version/s: None
    • Component/s: API (A)
    • Labels:
      None
    • Environment:
      Version: 1.8.4 stable using MySQL

      Description

      Problem: "host.create" API method take too long time on large enviroments (>5000)
      Solution: Columns "hostid" and "templateid" need individual INDEXES on "host_templates" table

      I'm syncing my CMDB system with Zabbix using API mode. On first charge, I need create 10000 hosts and this process was taking too long. Looking into MySQL's process list, I got this query:

      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

      This query is used on host.create - file api/classes/class.ctemplate.php in line 1651

      Running this query manually took 30 seconds (10000 hosts linked). EXPLAIN method show me that some code was running without indexes.

      The solution was create individual indexes for "hostid" and "templateid", because the original schema only offers a unique key with both columns.

      ALTER TABLE `hosts_templates` ADD INDEX ( `hostid` )
      ALTER TABLE `hosts_templates` ADD INDEX ( `templateid` )

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              xsbr Ricardo Santos
              Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: