Zabbix 2.2, PostgreSQL and partioning.

[vesal]

Hello all,

I tried to find information about if it's still recommended to partition history tables in Zabbix 2.2. I know it can give you huge performance boost in pre 2.2 versions.

What is the current recommendation for large environments, is the internal housekeeper good enough or will it slow down every hour? We are still in POC stage so I can do some experimenting. Right now we are at ~3nvps so it works quite well. I'm just wondering if it's going to slow down once we have 300 nvps.

Obviously we are going to run the zabbix database on our storage center separate SSD LUN if we decide this is the right product for us.

[Colttt]

i thinks the nvps is not the problem..
the problem is the size of the database..

[dale]

i do

Ignoring the house keeping question, I would still do partitioning because very large tables will be broken up.

This isn't a great example, but notice the difference in these 2 plans. Postgres knows it can ignore a whole swath of the history table(s).

Each table partition has its own index, and if there are more smaller indexes the relevant (or more frequently used) can fit more easily into memory.

zabbix=# explain select * from history;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Result  (cost=0.00..10899514.79 rows=662672680 width=21)
   ->  Append  (cost=0.00..10899514.79 rows=662672680 width=21)
         ->  Seq Scan on history  (cost=0.00..0.00 rows=1 width=34)
         ->  Seq Scan on history_p2014_02_01 history  (cost=0.00..809217.72 rows=49200772 width=21)
         ->  Seq Scan on history_p2014_02_02 history  (cost=0.00..809244.84 rows=49207784 width=21)
         ->  Seq Scan on history_p2014_02_03 history  (cost=0.00..809059.00 rows=49187000 width=21)
         ->  Seq Scan on history_p2014_02_04 history  (cost=0.00..808304.08 rows=49136608 width=21)
         ->  Seq Scan on history_p2014_02_05 history  (cost=0.00..809231.00 rows=49192800 width=21)
         ->  Seq Scan on history_p2014_02_06 history  (cost=0.00..808024.28 rows=49124228 width=21)
         ->  Seq Scan on history_p2014_02_07 history  (cost=0.00..816989.56 rows=49667156 width=21)
         ->  Seq Scan on history_p2014_02_08 history  (cost=0.00..839959.68 rows=51072968 width=21)
         ->  Seq Scan on history_p2014_02_09 history  (cost=0.00..839247.36 rows=51034436 width=21)
         ->  Seq Scan on history_p2014_02_10 history  (cost=0.00..839952.48 rows=51067048 width=21)
         ->  Seq Scan on history_p2014_02_11 history  (cost=0.00..838276.76 rows=50966976 width=21)
         ->  Seq Scan on history_p2014_02_12 history  (cost=0.00..842637.48 rows=51230848 width=21)
         ->  Seq Scan on history_p2014_02_13 history  (cost=0.00..846342.52 rows=51455652 width=21)
         ->  Seq Scan on history_p2014_02_14 history  (cost=0.00..183028.03 rows=11128403 width=21)
(17 rows)

zabbix=# explain select * from history where clock between 1391673600 and 1391760000;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Result  (cost=0.00..2118970.76 rows=49114728 width=21)
   ->  Append  (cost=0.00..2118970.76 rows=49114728 width=21)
         ->  Seq Scan on history  (cost=0.00..0.00 rows=1 width=34)
               Filter: ((clock >= 1391673600) AND (clock <= 1391760000))
         ->  Seq Scan on history_p2014_02_06 history  (cost=0.00..1053645.42 rows=49114726 width=21)
               Filter: ((clock >= 1391673600) AND (clock <= 1391760000))
         ->  Seq Scan on history_p2014_02_07 history  (cost=0.00..1065325.34 rows=1 width=21)
               Filter: ((clock >= 1391673600) AND (clock <= 1391760000))
(8 rows)