Hi,
I'm running pre-1.6.1 on MySQL.
> SELECT COUNT(*) FROM zabbix.graphs;
+----------+
| COUNT(*) |
+----------+
| 4952 |
+----------+
> SELECT COUNT(*) FROM zabbix.graphs_items;
+----------+
| COUNT(*) |
+----------+
| 12244 |
+----------+
I've got 200 active hosts.
The "Graph" section (monitoring or configuration) is really slow. It takes around 6-7 seconds to display the Monitoring/Graph page or when selecting a graph to show.
We recently moved from zabbix 1.4 and I don't remember it being so slow.
I've taken a look at frontend/php/charts.php .
I've removed all the security checks (it's a bit "free for all" here... so graph access can be quite relaxed):
- removed all $available_(hosts|graphs|groups) variables and calls to get_accessible_(hosts|groups|graphs).
- removed those filters from SQL queries.
It helped a bit but it still took something like 5 secs for the page to display.
I then rewrote the SQL queries to something like:
Well, just plain standard JOINs with no sub-SELECT/EXISTS. And now things are pretty fast. When run from the mysql client, some of the queries went from 2.5sec to a few milliseconds.
I've also added an index on graphs_items ( graphid ) as suggested on the forum (and now included in 1.6.2).
I guess I could probably restore the security checks ($available_*) as the performance penalty seems "acceptable". But I don't have time right now to get back to the code and change my SQL queries agains.
Anyway, am I the only one experiencing this ?
A quick diff between 1.6.1 and 1.6.2 shows no change on this (apart from the index).
I'm running pre-1.6.1 on MySQL.
> SELECT COUNT(*) FROM zabbix.graphs;
+----------+
| COUNT(*) |
+----------+
| 4952 |
+----------+
> SELECT COUNT(*) FROM zabbix.graphs_items;
+----------+
| COUNT(*) |
+----------+
| 12244 |
+----------+
I've got 200 active hosts.
The "Graph" section (monitoring or configuration) is really slow. It takes around 6-7 seconds to display the Monitoring/Graph page or when selecting a graph to show.
We recently moved from zabbix 1.4 and I don't remember it being so slow.
I've taken a look at frontend/php/charts.php .
I've removed all the security checks (it's a bit "free for all" here... so graph access can be quite relaxed):
- removed all $available_(hosts|graphs|groups) variables and calls to get_accessible_(hosts|groups|graphs).
- removed those filters from SQL queries.
It helped a bit but it still took something like 5 secs for the page to display.
I then rewrote the SQL queries to something like:
Code:
- $sql = 'SELECT COUNT(DISTINCT g.groupid) as grpcount, MAX(g.groupid) as groupid'.
- ' FROM groups g, hosts_groups hg, hosts h '.
- ' WHERE '.DBcondition('g.groupid',$available_groups).
- ' AND hg.groupid=g.groupid '.
- ' AND h.hostid=hg.hostid '.
- ' AND h.status='.HOST_STATUS_MONITORED.
- ' AND EXISTS(SELECT DISTINCT i.itemid FROM items i, graphs_items gi WHERE i.hostid=h.hostid AND i.itemid=gi.itemid) ';
+ $sql = 'SELECT COUNT(DISTINCT g.groupid) as grpcount, MAX(g.groupid) as groupid '.
+ 'FROM groups AS g '.
+ 'JOIN hosts_groups AS hg ON g.groupid = hg.groupid '.
+ 'JOIN hosts AS h ON h.hostid = hg.hostid AND h.status = ' . HOST_STATUS_MONITORED . ' '.
+ 'JOIN items AS i ON i.hostid = h.hostid AND i.status = ' . ITEM_STATUS_ACTIVE . ' '.
+ 'JOIN graphs_items AS gi ON gi.itemid = i.itemid';
I've also added an index on graphs_items ( graphid ) as suggested on the forum (and now included in 1.6.2).
I guess I could probably restore the security checks ($available_*) as the performance penalty seems "acceptable". But I don't have time right now to get back to the code and change my SQL queries agains.
Anyway, am I the only one experiencing this ?
A quick diff between 1.6.1 and 1.6.2 shows no change on this (apart from the index).