Previously we reported slow queries in Zabbix 1.6 (http://www.zabbix.com/forum/showthread.php?t=10887). It seems some of them are still there in 1.6.4. In our case we have large amount of items so Graphs interface worked horribly slow. Here is the attached patch for include/hosts.inc.php which gave us almost 60 times faster response. Below is the output of explain analyze on PostgreSQL database of both queries.
I'll post here more patches if we find other part which can be speed up.
Cheers
emir
Original version
Modified query
I'll post here more patches if we find other part which can be speed up.
Cheers
emir
Original version
Code:
Unique (cost=9278131.32..9278138.69 rows=53 width=18) (actual time=58667.544..58669.501 rows=75 loops=1)
-> Sort (cost=9278131.32..9278133.78 rows=982 width=18) (actual time=58667.543..58668.276 rows=4385 loops=1)
Sort Key: g.groupid, g.name
Sort Method: quicksort Memory: 464kB
-> Nested Loop (cost=0.00..9278082.52 rows=982 width=18) (actual time=3265.594..58638.315 rows=4385 loops=1)
-> Nested Loop (cost=0.00..9277559.26 rows=1501 width=26) (actual time=93.063..58572.378 rows=4646 loops=1)
-> Seq Scan on groups g (cost=0.00..10.44 rows=53 width=18) (actual time=0.037..0.360 rows=83 loops=1)
Filter: (groupid = ANY ('{14,80,78,15,72,16,17,18,56,60,82,49,19,20,21,22,9,73,6,74,5,23,24,25,77,50,69,53,54,71,51,61,26,30,81,29,28,27,8,7,55,31,32,33,34,84,67,65,48,70,13,52,11,12,10,58,36,47,68,37,42,2,66,75,64,85,35,57,38,40,39,63,79,43,1,41,45,83,62,44,3,4,59}'::bigint[]))
-> Index Scan using hosts_groups_groups_1 on hosts_groups hg (cost=0.00..175047.70 rows=31 width=16) (actual time=13.508..705.621 rows=56 loops=83)
Index Cond: (hg.groupid = g.groupid)
Filter: (subplan)
SubPlan
-> Unique (cost=2822.65..2823.10 rows=90 width=8) (actual time=12.448..12.448 rows=1 loops=4699)
-> Sort (cost=2822.65..2822.88 rows=90 width=8) (actual time=12.445..12.445 rows=1 loops=4699)
Sort Key: i.itemid
Sort Method: quicksort Memory: 27kB
-> Hash Join (cost=2252.00..2819.73 rows=90 width=8) (actual time=5.220..12.414 rows=37 loops=4699)
Hash Cond: (gi.itemid = i.itemid)
-> Seq Scan on graphs_items gi (cost=0.00..482.33 rows=22533 width=8) (actual time=0.005..6.193 rows=22533 loops=4695)
-> Hash (cost=2241.05..2241.05 rows=876 width=8) (actual time=0.328..0.328 rows=98 loops=4699)
-> Bitmap Heap Scan on items i (cost=59.67..2241.05 rows=876 width=8) (actual time=0.079..0.287 rows=98 loops=4699)
Recheck Cond: (hostid = $0)
-> Bitmap Index Scan on items_1 (cost=0.00..59.45 rows=876 width=0) (actual time=0.064..0.064 rows=151 loops=4699)
Index Cond: (hostid = $0)
-> Index Scan using hosts_pkey on hosts h (cost=0.00..0.34 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=4646)
Index Cond: (h.hostid = hg.hostid)
Filter: (h.status = 0)
Total runtime: 58669.803 ms
Code:
Unique (cost=84320.23..84324.60 rows=53 width=18) (actual time=1624.686..1626.911 rows=75 loops=1)
-> Sort (cost=84320.23..84321.69 rows=583 width=18) (actual time=1624.684..1625.597 rows=4385 loops=1)
Sort Key: g.groupid, g.name
Sort Method: quicksort Memory: 464kB
-> Hash Join (cost=84108.45..84293.45 rows=583 width=18) (actual time=1607.942..1614.825 rows=4385 loops=1)
Hash Cond: (hg.groupid = g.groupid)
-> Hash Join (cost=84097.35..84273.09 rows=913 width=8) (actual time=1607.800..1611.954 rows=4385 loops=1)
Hash Cond: (hg.hostid = h.hostid)
-> Seq Scan on hosts_groups hg (cost=0.00..148.99 rows=4699 width=16) (actual time=0.013..1.657 rows=4699 loops=1)
-> Hash (cost=84095.30..84095.30 rows=164 width=16) (actual time=1607.630..1607.630 rows=550 loops=1)
-> Merge Join (cost=84090.72..84095.30 rows=164 width=16) (actual time=1606.522..1607.174 rows=550 loops=1)
Merge Cond: (i.hostid = h.hostid)
-> Sort (cost=83998.12..83998.74 rows=250 width=8) (actual time=1605.502..1605.631 rows=787 loops=1)
Sort Key: i.hostid
Sort Method: quicksort Memory: 61kB
-> HashAggregate (cost=83985.66..83988.16 rows=250 width=8) (actual time=1604.768..1605.014 rows=787 loops=1)
-> Nested Loop (cost=540.51..83938.09 rows=19030 width=8) (actual time=23.820..1592.101 rows=20795 loops=1)
-> HashAggregate (cost=538.66..728.96 rows=19030 width=8) (actual time=23.722..36.010 rows=20795 loops=1)
-> Seq Scan on graphs_items gi (cost=0.00..482.33 rows=22533 width=8) (actual time=0.033..8.359 rows=22533 loops=1)
-> Bitmap Heap Scan on items i (cost=1.85..4.36 rows=1 width=16) (actual time=0.073..0.073 rows=1 loops=20795)
Recheck Cond: (i.itemid = gi.itemid)
-> Bitmap Index Scan on items_pkey (cost=0.00..1.85 rows=1 width=0) (actual time=0.070..0.070 rows=2 loops=20795)
Index Cond: (i.itemid = gi.itemid)
-> Sort (cost=91.69..93.07 rows=552 width=8) (actual time=0.903..0.997 rows=552 loops=1)
Sort Key: h.hostid
Sort Method: quicksort Memory: 50kB
-> Seq Scan on hosts h (cost=0.00..66.55 rows=552 width=8) (actual time=0.044..0.586 rows=552 loops=1)
Filter: (status = 0)
-> Hash (cost=10.44..10.44 rows=53 width=18) (actual time=0.121..0.121 rows=83 loops=1)
-> Seq Scan on groups g (cost=0.00..10.44 rows=53 width=18) (actual time=0.017..0.091 rows=83 loops=1)
Filter: (groupid = ANY ('{14,80,78,15,72,16,17,18,56,60,82,49,19,20,21,22,9,73,6,74,5,23,24,25,77,50,69,53,54,71,51,61,26,30,81,29,28,27,8,7,55,31,32,33,34,84,67,65,48,70,13,52,11,12,10,58,36,47,68,37,42,2,66,75,64,85,35,57,38,40,39,63,79,43,1,41,45,83,62,44,3,4,59}'::bigint[]))
Total runtime: 1627.246 ms