Slow history housekeeping process on postgresql

[kozy]

Hi,

I have a history house keeping problem on Zabbix 1.6.5 with postgresql 8.3.7.

After I detete items, the posgresql becomes so slow bacause it delete rows from history.
I found slow query in the housekeeping process. The query is like this.

'delete from history where oid in (select oid from history where itemid=59692 limit 500)'.

Explain result shows oid column does not have index, so that query is so slow.

1. Do I miss any configuration on postgres.conf?
2. Do I need to create index on history.oid?

Thanks.

Following are the explain result.

zabbix=> explain analyze delete from history where oid in (select oid from history where itemid=59692 limit 500);
                                                                        QUERY PLAN                                  
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=1845.48..3889990.24 rows=200 width=6) (actual time=3267.019..1967420.522 rows=500 loops=1)
   Hash Cond: (public.history.oid = public.history.oid)
   ->  Seq Scan on history  (cost=0.00..3169308.46 rows=191689146 width=10) (actual time=2.619..1017835.038 rows=191638412 loops=1)
   ->  Hash  (cost=1842.98..1842.98 rows=200 width=4) (actual time=1761.001..1761.001 rows=500 loops=1)
         ->  HashAggregate  (cost=1840.98..1842.98 rows=200 width=4) (actual time=1755.972..1758.406 rows=500 loops=1)
               ->  Limit  (cost=0.00..1834.73 rows=500 width=4) (actual time=52.219..1752.270 rows=500 loops=1)
                     ->  Index Scan using history_1 on history  (cost=0.00..190478.40 rows=51909 width=4) (actual time=52.210..1747.080 rows=500 loops=1)
                           Index Cond: (itemid = 59692)
 Total runtime: 1967463.630 ms
(9 rows)

zabbix=>

Just delete all items is much faster.

zabbix=> explain analyze delete from history where itemid=59692;
                                                           QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on history  (cost=1750.53..175079.41 rows=51909 width=6) (actual time=895.067..5271.856 rows=5106 loops=1)
   Recheck Cond: (itemid = 59692)
   ->  Bitmap Index Scan on history_1  (cost=0.00..1737.55 rows=51909 width=0) (actual time=887.039..887.039 rows=5606 loops=1)
         Index Cond: (itemid = 59692)
 Total runtime: 5838.779 ms
(5 rows)

[flavioso]

Re: Slow history housekeeping process on postgresql

Hi Kozy,

I have the same problem with Zabbix 1.6.8 and PostgreSQL 8.3.7...
My housekeeping process takes more than 5 hours today...
I saw a bug report for Zabbix 1.8 version in https://support.zabbix.com/browse/ZBX-1586
This issue is very important because, sometimes, Zabbix can`t keep inserting values in the database during housekeeping...

We need a patch urgent!

Best regards,

[Murilex]

Bug not solved in 1.8.2?

Hi guys!

Was this issue really fixed on zabbix 1.8.2 as showed in ZBX-1586? I'm currently using zabbix 1.8.2 and I see several slow delete queries on postgresql (8.4.2) database created by zabbix db user. These queries has the same nature of those reported in ZBX-1586 and take a long time to complete as well. Follow one example:
delete from history where itemid=XXXXX and oid in (select oid from history where itemid=XXXXX limit 500)

These queries start to appear in the database after zabbix housekeeping process starts, whose frequency is currently set to 24h. Just to mention, in my environment the housekeeping process take several hours to complete and the the database server performance is very degraded during the process.

[flavioso]

Re: Bug not solved in 1.8.2?

Hi Murilex!

Did you try to set MaxHousekeeperDelete to 0, as is suggest in ZBX-1586?
I didn't try this, because Im not using the 1.8.2 version yet, but if works to you please let me know...

Best regards,

[Murilex]

Thanks flavioso for the reply! I've just set the MaxHousekeeperDelete to 0. I'll wait for the next housekeeping procedure to analyze the results and post them here.

[Murilex]

OK guys. After some days running zabbix server with MaxHousekeeperDelete=0, I can securely say that load of my db server was reduced considerably. Before that, zabbix housekeeping used to increase cpu utilization of my db server to 80% for several hours. Nowadays, cpu utilization of my db server remains in this state during housekeeping process for less than one hour. Thanks Flavioso!

[pietro54]

Hi,
I notice the problem exist in zabbix 2.0
Problem is that house keeping works over 10min.
Thats very big load for this machine - sometimes values are waiting in queue.
I got only 65vps
There few graphs from my zabbix.



There is my zabbix config

### Option: HousekeepingFrequency
#       Housekeeping is removing unnecessary information from history, alert, and alarms tables.
# HousekeepingFrequency=1
### Option: MaxHousekeeperDelete
#       No more than 'MaxHousekeeperDelete' rows (corresponding to [tablename], [field], [value])
MaxHousekeeperDelete=0
### Option: DisableHousekeeping
# DisableHousekeeping=0

My hardware machine config is:
intel xeon 3065 ( bit weak)
4gb of ram
Disks = 2x 250gb SOFT raid 0 ( it work bit slowly)
Database psql (8.4.11)
DBSize = 25gb, 15gb in history, 9gb in history unit.

and this is few things from pgsql.conf

max_connections = 300
shared_buffers = 1024MB
checkpoint_segments = 32
checkpoint_completion_target = 0.9
effective_cache_size = 1024MB
track_counts = on
autovacuum = on
autovacuum_max_workers = 3

As we can see disk are overloaded, but i think the housekeeping isnt working well.
Can u give me some good advice how to fight with this?

is the only rescue in partitionig the database?

Piotr