patch: improve charts.php perfomance.

[zerg]

I'am using zabbix from 1.4, not in product.
When i moving to 1.6.1 and install to product system i see that performance of charts very poor(40s for generate Charts page).
I'am edit some SQL's and code of charts.php. Performance grather at ten time (4s)

Last week upgraded to 1.6.4 and see same things(40-45s).

I don't have a lot of time for generate pure patch for 1.6.4(code/functions/SQL's structure changed), i'am just reedit 1.6.1 version of charts.php to get fully fork.

At this page:

http://www.zabbix.org/forum/showthread.php?p=41503

You can see when i'am not alone...

[Alexei]

Last week upgraded to 1.6.4 and see same things(40-45s).

Please read release notes. I think creation of new indexes should fix this problem.

[Calimero]

I haven't moved to 1.6.4 ... I only have a 1.6.4 test install (GUI essentially) around. Though performances from the graph screens have improved compared to 1.6.1 thanks to an extensive rewrite, performance is still ... unimpressive despite the new index.

I haven't had time yet to dig into the new code but some quick checks show that the call to the 'get_viewed_*' functions is where the problem lies. Approximately 5-6 seconds are needed to populate the lists of groups/hosts/graphs.

[Alexei]

Approximately 5-6 seconds are needed to populate the lists of groups/hosts/graphs.

I believe it was fixed in 1.6.3, 1.6.4 as well.

[zerg]

Please read release notes. I think creation of new indexes should fix this problem.

I'am read it and create it.
As you can see my indexes are right:

mysql> SHOW INDEX FROM history_log;
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| history_log | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | |
| history_log | 0 | history_log_2 | 1 | itemid | A | 0 | NULL | NULL | | BTREE | |
| history_log | 0 | history_log_2 | 2 | id | A | 0 | NULL | NULL | | BTREE | |
| history_log | 1 | history_log_1 | 1 | itemid | A | 0 | NULL | NULL | | BTREE | |
| history_log | 1 | history_log_1 | 2 | clock | A | 0 | NULL | NULL | | BTREE | |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Table graphs_items and history_text have to indexes.

In mysq-slow log i'am again see "SELECT DISTINCT ***" - SQL'S.
# Time: 090420 17:26:01
# User@Host: zabbix[zabbix] @ localhost []
# Query_time: 123 Lock_time: 0 Rows_sent: 1 Rows_examined: 7918210
SELECT DISTINCT h.hostid,h.host FROM hosts h,hosts_groups hg WHERE


I'am attached two screenshot pathced and pure version charts.php.
My firefox whth extended status bar addon:

UPD:
He-he.. As you can see graph don't displayed.
In mysql-slow.log:
# Time: 090420 17:32:06
# User@Host: zabbix[zabbix] @ localhost []
# Query_time: 466 Lock_time: 0 Rows_sent: 30 Rows_examined: 8212968
SELECT DISTINCT g.groupid,g.name FROM groups g,hosts_groups hg,hosts h WHERE
# Time: 090420 17:33:17

SELECT generate about 15 minutes?? :-( I'am don't lies you...

[Alexei]

Table graphs_items and history_text have to indexes.

It must have these indexes:

CREATE INDEX graphs_items_1 on graphs_items (itemid);
CREATE INDEX graphs_items_2 on graphs_items (graphid);

Please create them and re-test your case.

[Calimero]

It must have these indexes:

CREATE INDEX graphs_items_1 on graphs_items (itemid);
CREATE INDEX graphs_items_2 on graphs_items (graphid);

Please create them and re-test your case.

The first index (graphs_items_1) isn't listed in the release notes for 1.6.4. I just created it and my 1.6.4 test GUI is indeed much faster.

You should probably update the release notes.

[zerg]

It must have these indexes:

CREATE INDEX graphs_items_1 on graphs_items (itemid);
CREATE INDEX graphs_items_2 on graphs_items (graphid);

Please create them and re-test your case.

I have only graphs_items_2
mysql> SHOW INDEX FROM graphs_items;
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| graphs_items | 0 | PRIMARY | 1 | gitemid | A | 8902 | NULL | NULL | | BTREE | |
| graphs_items | 1 | graphs_items_2 | 1 | graphid | A | 8902 | NULL | NULL | | BTREE | |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

When create graphs_items_1 - perfomance will impresive grather!!! :-0
0.5 sec...

Thanks...

[tob]

index

Hi ;-)

Alexei, I think it would be a very good idea to add this one clearly in the release notes (it's not under 1.6.3/4). We've been hitting this issue for quite a while... the solution was very simple:

create index itemid on graphs_items (itemid);

And suddenly we didn't have to wait for 45 sec on this query ;-)

Since it wasn't in the notes we didn't see this, only today my colleague found this topic.

Cheers,

Tobias