[Partly solved] Really slow display of graphs / screens

[Gwenaelle]

Hello,
I'm using Zabbix 1.4.4 with a Postgres 7.4 database, I'm doing only Web Monitoring.
I had some problems with the display of graphs.
A graph for a 1h period with 5 items took 20-25 seconds to display, 4 graphs in a screen took 40 seconds.
I saw that the slowdown came from the Postgres database. As I haven't got that much different items on the history table (~700) Postgres don't use the Index on (itemid, clock). I added an Index on the clock column (alone) of the history table and that did the job. (I added that same Index on all history_* tables)
I now have another problem also related to Postgres, it doesn't use the clock index for large periods as 12h. I had to add a hack in the graph.inc.php file to force it using the Index with a "SET enable_seqscan = off;" / "SET enable_seqscan = on;" while requesting the data from the history.

[Alexei]

As I haven't got that much different items on the history table (~700) Postgres don't use the Index on (itemid, clock).

I hardly believe PostgreSQL is not smarter than MySQL. It must use this index!

[Gwenaelle]

As I haven't got that much different items on the history table (~700) Postgres don't use the Index on (itemid, clock).

Postgres seems to feel he don't need an index for as few values as 700.

zabbix=# select count(*) from history;
  count
---------
 2250723
zabbix=# select count(distinct itemid) from history;
 count
-------
   884

(I added a few items since yesterday)

Here are the requests I analyse and the results :
I use the "EXPLAIN ANALYSE <my_request>" provided by Postgres.

For a 1h-period :

select itemid,round(449*(mod(clock+3343,3600))/(3600),0) as i, count(*) as count,avg(value) as avg,min(value) as min, max(value) as max,max(clock) as clock from history where itemid=20593 and clock>=1205503457 and clock<=1205507057 group by itemid,round(449*(mod(clock+3343,3600))/(3600),0);

12h-period :

select itemid,round(449*(mod(clock+9550,43200))/(43200),0) as i, count(*) as count,avg(value) as avg,min(value) as min, max(value) as max,max(clock) as clock from history where itemid=20593 and clock>=1205702450 and clock<=1205745650 group by itemid,round(449*(mod(clock+9550,43200))/(43200),0);

With the original index : history_1 on history using btree(itemid, clock)

                                                       QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=56573.80..56573.99 rows=4 width=22) (actual time=1327.503..1327.541 rows=7 loops=1)
   ->  Sort  (cost=56573.80..56573.81 rows=4 width=22) (actual time=1327.469..1327.475 rows=7 loops=1)
         Sort Key: itemid, round((((449 * mod((clock + 3343), 3600)) / 3600))::numeric, 0)
         ->  Seq Scan on history  (cost=0.00..56573.76 rows=4 width=22) (actual time=147.281..1327.422 rows=7 loops=1)
               Filter: ((itemid = 20593) AND (clock >= 1205503457) AND (clock <= 1205507057))
 Total runtime: 1342.197 ms

                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=56577.59..56581.53 rows=83 width=22) (actual time=3042.589..3043.360 rows=144 loops=1)
   ->  Sort  (cost=56577.59..56577.80 rows=83 width=22) (actual time=3042.546..3042.572 rows=144 loops=1)
         Sort Key: itemid, round((((449 * mod((clock + 9550), 43200)) / 43200))::numeric, 0)
         ->  Seq Scan on history  (cost=0.00..56574.94 rows=83 width=22) (actual time=604.385..3042.319 rows=144 loops=1)
               Filter: ((itemid = 20593) AND (clock >= 1205702450) AND (clock <= 1205745650))
 Total runtime: 3043.473 ms

With a "reverse" index : history_1 on history using btree(clock, itemid)

                                                              QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=12879.67..12879.91 rows=5 width=22) (actual time=22.854..22.889 rows=7 loops=1)
   ->  Sort  (cost=12879.67..12879.69 rows=5 width=22) (actual time=22.824..22.826 rows=7 loops=1)
         Sort Key: itemid, round((((449 * mod((clock + 3343), 3600)) / 3600))::numeric, 0)
         ->  Index Scan using history_1 on history  (cost=0.00..12879.61 rows=5 width=22) (actual time=22.335..22.797 rows=7 loops=1)
               Index Cond: ((clock >= 1205503457) AND (clock <= 1205507057))
               Filter: (itemid = 20593)
 Total runtime: 23.201 ms

                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=56606.52..56609.37 rows=60 width=22) (actual time=2538.760..2539.550 rows=144 loops=1)
   ->  Sort  (cost=56606.52..56606.67 rows=60 width=22) (actual time=2538.727..2538.756 rows=144 loops=1)
         Sort Key: itemid, round((((449 * mod((clock + 9550), 43200)) / 43200))::numeric, 0)
         ->  Seq Scan on history  (cost=0.00..56604.75 rows=60 width=22) (actual time=661.385..2538.490 rows=144 loops=1)
               Filter: ((itemid = 20593) AND (clock >= 1205702450) AND (clock <= 1205745650))
 Total runtime: 2539.687 ms

With two indexes : history_1 on history using btree(itemid, clock) and history_2 on history using btree(clock)

                                                               QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=11961.41..11961.60 rows=4 width=22) (actual time=13.149..13.185 rows=7 loops=1)
   ->  Sort  (cost=11961.41..11961.42 rows=4 width=22) (actual time=13.122..13.125 rows=7 loops=1)
         Sort Key: itemid, round((((449 * mod((clock + 3343), 3600)) / 3600))::numeric, 0)
         ->  Index Scan using history_2 on history  (cost=0.00..11961.37 rows=4 width=22) (actual time=12.925..13.099 rows=7 loops=1)
               Index Cond: ((clock >= 1205503457) AND (clock <= 1205507057))
               Filter: (itemid = 20593)
 Total runtime: 13.507 ms

                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=56647.91..56651.14 rows=68 width=22) (actual time=3024.807..3025.571 rows=144 loops=1)
   ->  Sort  (cost=56647.91..56648.08 rows=68 width=22) (actual time=3024.766..3024.795 rows=144 loops=1)
         Sort Key: itemid, round((((449 * mod((clock + 9550), 43200)) / 43200))::numeric, 0)
         ->  Seq Scan on history  (cost=0.00..56645.84 rows=68 width=22) (actual time=511.461..3024.537 rows=144 loops=1)
               Filter: ((itemid = 20593) AND (clock >= 1205702450) AND (clock <= 1205745650))
 Total runtime: 3025.715 ms

So for the last one i forced the sequential analyse off (SET enable_seqscan = off) so that it use the index and the display is indeed faster with it

                                                                QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=212813.68..212816.91 rows=68 width=22) (actual time=36.988..37.768 rows=144 loops=1)
   ->  Sort  (cost=212813.68..212813.85 rows=68 width=22) (actual time=36.960..36.995 rows=144 loops=1)
         Sort Key: itemid, round((((449 * mod((clock + 9550), 43200)) / 43200))::numeric, 0)
         ->  Index Scan using history_2 on history  (cost=0.00..212811.62 rows=68 width=22) (actual time=9.889..36.627 rows=144 loops=1)
               Index Cond: ((clock >= 1205702450) AND (clock <= 1205745650))
               Filter: (itemid = 20593)
 Total runtime: 37.907 ms

I don't know how to understand this last one, it seems to be more a Postgres problem.

[Gwenaelle]

I added an index on column 'clock' of table 'trends', that speeds up a bit the display of large period graphs.