Slow process on mass host creation/import

[xsbr]

Version: 1.8.4 stable using MySQL

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 present on 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` )

Now I can insert 100 hosts per second.

I think this issue affects all host creation methods: FrontEnd, API and XML Import

[xsbr]

Bug reported

https://support.zabbix.com/browse/ZBX-3439

[jean]

Hello,

I have applied your "solution" and now the import operation works as never !!!

Thank you so much !