-
Incident report
-
Resolution: Unresolved
-
Trivial
-
None
-
2.0.9
-
CentOS 6.4.
x86_64
PGSQL 8.4.13
PHP 5.3.3-23
I noticed that the graph selection popup to add a new GRAPH to a SCREEN configuration takes a very long time to appear (10 minutes on my system).
all other queries and the Zabbix server generally perform well, this is one of the rare slow query. The query also didn't take that long in the past. This is a relatively recent Zabbix server deployment from a fresh install of Zabbix 2.0.4 at the time, which eventually upgraded to 2.0.9.
According to the logs done 3 times, when trying to add a graph to a screen (or simply changing the host in the popup to find the graph on a different host), the following query is run:
duration: 1158688.092 ms statement: SELECT DISTINCT g.groupid,g.name FROM groups g,hosts_groups hg,hosts h WHERE g.groupid=hg.groupid AND h.hostid=hg.hostid AND h.status IN(0,1) AND EXISTS (SELECT NULL FROM items i,graphs_items gi WHERE hg.hostid=i.hostid AND i.itemid=gi.itemid) AND g.groupid BETWEEN 2200000000000000 AND 2299999999999999 duration: 1073954.899 ms statement: SELECT DISTINCT g.groupid,g.name FROM groups g,hosts_groups hg,hosts h WHERE g.groupid=hg.groupid AND h.hostid=hg.hostid AND h.status IN(0,1) AND EXISTS (SELECT NULL FROM items i,graphs_items gi WHERE hg.hostid=i.hostid AND i.itemid=gi.itemid) AND g.groupid BETWEEN 2200000000000000 AND 2299999999999999 duration: 1107024.389 ms statement: SELECT DISTINCT g.groupid,g.name FROM groups g,hosts_groups hg,hosts h WHERE g.groupid=hg.groupid AND h.hostid=hg.hostid AND h.status IN(0,1) AND EXISTS (SELECT NULL FROM items i,graphs_items gi WHERE hg.hostid=i.hostid AND i.itemid=gi.itemid) AND g.groupid BETWEEN 2200000000000000 AND 2299999999999999
it took 1000s per query.
I'm currently performing an ANALYSE on the query to provide more information.
Now looking back in the ChangeLog and query, this query changed between 2.0.4 and 2.0.5 with the change in ZBX-4789. For testing, I reverted that one query in the php code and the result is a lot faster for my installation (almost instantaneous).
changed api/classes/CHostGroup.php line 348 to revert the SQL query back to pre 2.0.4:
if (!is_null($options['with_graphs'])) {
$sqlParts['from']['hosts_groups'] = 'hosts_groups hg';
$sqlParts['where']['hgg'] = 'g.groupid=hg.groupid';
$sqlParts['where'][] = 'EXISTS (SELECT 1'.
// $sqlParts['where'][] = 'EXISTS ('.
// 'SELECT NULL'.
' FROM items i,graphs_items gi'.
// Restore pre 2.0.4
' WHERE i.hostid=hg.hostid'.
' AND i.itemid=gi.itemid '.zbx_limit(1).')';
// ' WHERE hg.hostid=i.hostid'.
// ' AND i.itemid=gi.itemid'.
// ')';
}
I'm wondering if I missed an index in my installation (I deployed the Official shipped zabbix.SQL schema for Postgresql or if there is an issue with the new query on postgresql.