-
Incident report
-
Resolution: Duplicate
-
Major
-
1.8
-
None
-
PostgreSQL 8.4.2
RHEL and CentOS 5.4 x64
8x 15k RPM SAS 2.5" disks in RAID 1+0
2 billion rows
We have an issue with the housekeeper on PostgreSQL 8.4, the query written for PostgreSQL doesn't work very well, and in a couple of years the housekeeper queue is very large (32000 items).
I do not know the history behind this statement, and I'm not a database expert, but I wonder if the current statement is written to emulate an Oracle feature, to prevent filling up the undo table space?
Below I have listed two alternative statements which I hope can be implemented for Zabbix 1.8.1, because the housekeeper does not work in its current state (it deletes data slower than it is inserted). I believe the best option would be the unlimited delete (alternative 2).
PS! The runtimes are from items with different sizes, so the variation is expected.
- Current statement:
duration: 257371.351 ms statement: delete from history where oid in (select oid from history where itemid=22442 limit 500)
duration: 261079.443 ms statement: delete from history where oid in (select oid from history where itemid=23259 limit 500)
duration: 257834.690 ms statement: delete from history where oid in (select oid from history where itemid=22460 limit 500)
duration: 257393.873 ms statement: delete from history where oid in (select oid from history where itemid=23265 limit 500)
duration: 258183.607 ms statement: delete from history where oid in (select oid from history where itemid=25988 limit 500)
- EXPLAIN delete from history where oid in (select oid from history where itemid=25988 limit 500);
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Hash Semi Join (cost=1762.48..16917543.41 rows=200 width=6)
Hash Cond: (public.history.oid = public.history.oid)
-> Seq Scan on history (cost=0.00..14693125.32 rows=846722432 width=10)
-> Hash (cost=1756.23..1756.23 rows=500 width=4)
-> Limit (cost=0.00..1751.23 rows=500 width=4)
-> Index Scan using history_1 on history (cost=0.00..613064.01 rows=175038 width=4)
Index Cond: (itemid = 25988)
(7 rows)
- Alternative statement 1 (index "hint"):
duration: 3508.723 ms statement: delete from history where itemid=29064 and oid in (select oid from history where itemid=29064 limit 500)
duration: 39967.581 ms statement: delete from history where itemid=25419 and oid in (select oid from history where itemid=25419 limit 500)
duration: 15575.868 ms statement: delete from history where itemid=29078 and oid in (select oid from history where itemid=29078 limit 500)
duration: 55610.652 ms statement: delete from history where itemid=22231 and oid in (select oid from history where itemid=22231 limit 500)
duration: 45966.948 ms statement: delete from history where itemid=29073 and oid in (select oid from history where itemid=29073 limit 500)
- EXPLAIN delete from history where itemid=25988 and oid in (select oid from history where itemid=25988 limit 500);
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Hash Semi Join (cost=1762.48..615295.19 rows=200 width=6)
Hash Cond: (public.history.oid = public.history.oid)
-> Index Scan using history_1 on history (cost=0.00..613064.01 rows=175038 width=10)
Index Cond: (itemid = 25988)
-> Hash (cost=1756.23..1756.23 rows=500 width=4)
-> Limit (cost=0.00..1751.23 rows=500 width=4)
-> Index Scan using history_1 on history (cost=0.00..613064.01 rows=175038 width=4)
Index Cond: (itemid = 25988)
(8 rows)
- Alternative statement 2 (no limit):
duration: 75994.597 ms statement: delete from history where itemid=28908
duration: 153382.502 ms statement: delete from history where itemid=22948
duration: 70893.997 ms statement: delete from history where itemid=28910
duration: 181384.096 ms statement: delete from history where itemid=23512
duration: 63091.321 ms statement: delete from history where itemid=28904
- EXPLAIN delete from history where itemid=25988;
QUERY PLAN
-----------------------------------------------------------------------------------
Index Scan using history_1 on history (cost=0.00..613064.01 rows=175038 width=6)
Index Cond: (itemid = 25988)
(2 rows)
- duplicates
-
ZBX-1949 remove housekeeper entries if < 500 records were deleted
- Closed