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

Deadlocks on events when table is big and housekeeper runing

    XMLWordPrintable

Details

    • Problem report
    • Status: Confirmed
    • Trivial
    • Resolution: Unresolved
    • 4.2.8
    • None
    • Server (S)
    • 1

    Description

      Mysql is backend DB. And there is two points to review:
      1. Deadlocks themselfs. Events table has more then 9 million rows, houskeeper is running and here it's an example:

      *** (1) TRANSACTION:
      TRANSACTION 208532068729, ACTIVE 1 sec inserting
      mysql tables in use 1, locked 1
      LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
      MySQL thread id 55502371, OS thread handle 48238530000640, query id 11973893554 some IP zabbix_server update
      insert into problem (eventid,source,object,objectid,clock,ns,name,severity) values (.....)
      *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 17517 page no 4384 n bits 1160 index problem_3 of table `zabbix`.`problem` trx id 208532068729 lock_mode X locks gap before rec insert intention waiting
      *** (2) TRANSACTION:
      TRANSACTION 208531990205, ACTIVE 565 sec fetching rows
      mysql tables in use 1, locked 1
      20493 lock struct(s), heap size 2203856, 3433920 row lock(s), undo log entries 76042
      MySQL thread id 56387211, OS thread handle 48238574192384, query id 11972947767 some IP zabbix_server updating
      delete from events where (eventid between ......
      *** (2) HOLDS THE LOCK(S):
      RECORD LOCKS space id 17517 page no 4384 n bits 1160 index problem_3 of table `zabbix`.`problem` trx id 208531990205 lock mode S locks gap before rec
      *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 17876 page no 23305 n bits 168 index PRIMARY of table `zabbix`.`events` trx id 208531990205 lock_mode X waiting
      *** WE ROLL BACK TRANSACTION (1)
      

      2. Storage period for internal events is configured to 1 day, but there is also alot of unsupported items. So houskeeper will not delete ones that are in problem state (still unsupported). It might make sense to review internal event storage. It is even possible to remove the generation of recovery events for internal data elements for the sake of being able to delete them after a set period (1day)?
      In case of impossibility to get rid of unsupported data elements - it is more important to be able to control the size of the event tables than to be able to notify about the item (trigger\rule) status change to supported
      At least this requires the note in the documentation.

      Attachments

        Activity

          People

            zabbix.dev Zabbix Development Team
            elina.kuzyutkina Elina Kuzyutkina
            Votes:
            4 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated: