Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-12758

Postgresql problem table missing index on r_eventid while MySQL InnoDB automatically adds it

    Details

    • Team:
      Team A
    • Sprint:
      Sprint 18, Sprint 19, Sprint 21, Sprint 22, Sprint 23, Sprint 24
    • Story Points:
      2

      Description

      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)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                vso Vladislavs Sokurenko
                Reporter:
                hahnium105 JB
              • Votes:
                0 Vote for this issue
                Watchers:
                10 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: