-
Problem report
-
Resolution: Fixed
-
Critical
-
3.2.8rc1, 3.4.2rc1
-
CentOS Linux release 7.3.1611 (Core)
postgresql-9.2.18-1.el7.x86_64
-
Sprint 18, Sprint 19, Sprint 21, Sprint 22, Sprint 23, Sprint 24
-
2
Housekeeper hangs when trying to delete old events. Manually trying to delete events take long time.
Trying to delete one minute of events:
zabbix=# explain analyze delete FROM events where age(to_timestamp(events.clock)) > interval '7 days 18:59:00'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- ---------------------- Delete on events (cost=0.00..212828.92 rows=1978096 width=6) (actual time=12120.870..12120.870 rows=0 loops=1) -> Seq Scan on events (cost=0.00..212828.92 rows=1978096 width=6) (actual time=5015.858..12119.974 rows=282 loops=1) Filter: (age((('now'::cstring)::date)::timestamp with time zone, to_timestamp((clock)::double precision)) > '7 days 18:59:00'::interval) Rows Removed by Filter: 5919455 Trigger for constraint c_acknowledges_2: time=3.971 calls=282 Trigger for constraint c_alerts_2: time=3.933 calls=282 Trigger for constraint c_event_tag_1: time=3.530 calls=282 Trigger for constraint c_problem_1: time=4.266 calls=282 Trigger for constraint c_event_recovery_1: time=5.458 calls=282 Trigger for constraint c_event_recovery_2: time=4.280 calls=282 Trigger for constraint c_problem_2: time=17571.926 calls=282 Trigger for constraint c_event_recovery_3: time=12.291 calls=282 Trigger for constraint c_alerts_5: time=4.937 calls=282 Total runtime: 29736.092 ms
Added a new index, and was able to delete 1 hour of data faster than 1 minute without the index:
zabbix=# create index problem_3 on problem(r_eventid);
zabbix=# explain analyze delete FROM events where age(to_timestamp(events.clock)) > interval '7 days 18:00:00'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- ---------------------- Delete on events (cost=0.00..214479.72 rows=1993439 width=6) (actual time=12327.789..12327.789 rows=0 loops=1) -> Seq Scan on events (cost=0.00..214479.72 rows=1993439 width=6) (actual time=5173.513..12297.065 rows=26258 loops=1) Filter: (age((('now'::cstring)::date)::timestamp with time zone, to_timestamp((clock)::double precision)) > '7 days 18:00:00'::interval) Rows Removed by Filter: 5930573 Trigger for constraint c_acknowledges_2: time=166.392 calls=26258 Trigger for constraint c_alerts_2: time=183.011 calls=26258 Trigger for constraint c_event_tag_1: time=163.226 calls=26258 Trigger for constraint c_problem_1: time=184.327 calls=26258 Trigger for constraint c_event_recovery_1: time=210.511 calls=26258 Trigger for constraint c_event_recovery_2: time=200.982 calls=26258 Trigger for constraint c_problem_2: time=188.991 calls=26258 Trigger for constraint c_event_recovery_3: time=183.742 calls=26258 Trigger for constraint c_alerts_5: time=185.132 calls=26258 Total runtime: 14012.007 ms (14 rows)
- part of
-
-
- Closed
-
