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

Slow housekeeping of events due to missing index on foreign key"c_alerts_6".

    XMLWordPrintable

Details

    • Team B
    • Sprint 82 (Nov 2021)
    • 1

    Description

      1928:20211005:045440.479 housekeeper [deleted 0 hist/trends, 0 items/triggers, 18029 events, 169 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 44895.761061 sec, idle for 1 hour(s)]
      

      It took over 12 hours for the housekeeper to delete 18,029 records of events and 169 records of problems.

      I checked the "sow query" and found that it took over 12 hours to delete events.

        1928:20211004:211956.063 slow query: 17611.322038 sec, "delete from events where (eventid ...
      
        1928:20211005:002622.993 slow query: 11186.895792 sec, "delete from events where (eventid ...
      
        1928:20211005:015255.291 slow query: 5192.229874 sec, "delete from events where (eventid ...
      
        1928:20211005:045438.434 slow query: 10900.706001 sec, "delete from events where (eventid ...
      

      I checked the "EXPLAIN ANALYZE" and it seems that c_alerts_6 is taking a long time to execute.

      Exist c_alerts_6

      zabbix_db=> EXPLAIN ANALYZE delete from events where eventid=7755800;
                                                              QUERY PLAN
      --------------------------------------------------------------------------------------------------------------------------
       Delete on events  (cost=0.43..5.45 rows=1 width=6) (actual time=1.130..1.131 rows=0 loops=1)
         ->  Index Scan using events_pkey on events  (cost=0.43..5.45 rows=1 width=6) (actual time=1.098..1.100 rows=1 loops=1)
               Index Cond: (eventid = 7755800)
       Planning Time: 0.069 ms
       Trigger for constraint c_alerts_2 on events: time=1.002 calls=1
       Trigger for constraint c_alerts_5 on events: time=0.083 calls=1
       Trigger for constraint c_acknowledges_2 on events: time=0.934 calls=1
       Trigger for constraint c_event_tag_1 on events: time=1.630 calls=1
       Trigger for constraint c_problem_1 on events: time=0.075 calls=1
       Trigger for constraint c_problem_2 on events: time=0.038 calls=1
       Trigger for constraint c_event_recovery_1 on events: time=0.844 calls=1
       Trigger for constraint c_event_recovery_2 on events: time=0.425 calls=1
       Trigger for constraint c_event_recovery_3 on events: time=0.055 calls=1
       Trigger for constraint c_event_suppress_1 on events: time=0.063 calls=1
       Trigger for constraint c_alerts_6 on acknowledges: time=8467.500 calls=1  <-- here
       Execution Time: 8473.814 ms
      (16 行)
      

      Not exist c_alerts_6

      zabbix_db=> EXPLAIN ANALYZE delete from events where eventid=8331312;
                                                              QUERY PLAN
      --------------------------------------------------------------------------------------------------------------------------
       Delete on events  (cost=0.43..5.45 rows=1 width=6) (actual time=0.085..0.086 rows=0 loops=1)
         ->  Index Scan using events_pkey on events  (cost=0.43..5.45 rows=1 width=6) (actual time=0.028..0.029 rows=1 loops=1)
               Index Cond: (eventid = 8331312)
       Planning Time: 0.093 ms
       Trigger for constraint c_alerts_2: time=0.043 calls=1
       Trigger for constraint c_alerts_5: time=0.016 calls=1
       Trigger for constraint c_acknowledges_2: time=0.024 calls=1
       Trigger for constraint c_event_tag_1: time=0.018 calls=1
       Trigger for constraint c_problem_1: time=0.016 calls=1
       Trigger for constraint c_problem_2: time=0.014 calls=1
       Trigger for constraint c_event_recovery_1: time=0.018 calls=1
       Trigger for constraint c_event_recovery_2: time=0.091 calls=1
       Trigger for constraint c_event_recovery_3: time=0.017 calls=1
       Trigger for constraint c_event_suppress_1: time=0.021 calls=1
       Execution Time: 0.392 ms
      (15 行)
      

      The acknowledgeid of c_alerts_6 is not indexed.

      ALTER TABLE `alerts` ADD CONSTRAINT `c_alerts_6` FOREIGN KEY (`acknowledgeid`) REFERENCES `acknowledges` (`acknowledgeid`) ON DELETE CASCADE;
      
      zabbix=> \d alerts;
                                       Table "public.alerts"
          Column     |          Type           | Collation | Nullable |        Default        
      ---------------+-------------------------+-----------+----------+-----------------------
       alertid       | bigint                  |           | not null | 
       actionid      | bigint                  |           | not null | 
       eventid       | bigint                  |           | not null | 
       userid        | bigint                  |           |          | 
       clock         | integer                 |           | not null | 0
       mediatypeid   | bigint                  |           |          | 
       sendto        | character varying(1024) |           | not null | ''::character varying
       subject       | character varying(255)  |           | not null | ''::character varying
       message       | text                    |           | not null | ''::text
       status        | integer                 |           | not null | 0
       retries       | integer                 |           | not null | 0
       error         | character varying(2048) |           | not null | ''::character varying
       esc_step      | integer                 |           | not null | 0
       alerttype     | integer                 |           | not null | 0
       p_eventid     | bigint                  |           |          | 
       acknowledgeid | bigint                  |           |          | 
       parameters    | text                    |           | not null | '{}'::text
      Indexes:
          "alerts_pkey" PRIMARY KEY, btree (alertid)
          "alerts_1" btree (actionid)
          "alerts_2" btree (clock)
          "alerts_3" btree (eventid)
          "alerts_4" btree (status)
          "alerts_5" btree (mediatypeid)
          "alerts_6" btree (userid)
          "alerts_7" btree (p_eventid)
      Foreign-key constraints:
          "c_alerts_1" FOREIGN KEY (actionid) REFERENCES actions(actionid) ON DELETE CASCADE
          "c_alerts_2" FOREIGN KEY (eventid) REFERENCES events(eventid) ON DELETE CASCADE
          "c_alerts_3" FOREIGN KEY (userid) REFERENCES users(userid) ON DELETE CASCADE
          "c_alerts_4" FOREIGN KEY (mediatypeid) REFERENCES media_type(mediatypeid) ON DELETE CASCADE
          "c_alerts_5" FOREIGN KEY (p_eventid) REFERENCES events(eventid) ON DELETE CASCADE
          "c_alerts_6" FOREIGN KEY (acknowledgeid) REFERENCES acknowledges(acknowledgeid) ON DELETE CASCADE
      

      Attachments

        Activity

          People

            ssimonenko Sergey Simonenko
            kazuo.ito Kazuo Ito
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: