Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27983

InnoDB hangs on multiple concurrent requests of a cold ROW_FORMAT=COMPRESSED page

    XMLWordPrintable

Details

    Description

      Updated a week ago from 10.6.5 to 10.6.7
      Since then alt least every 2 days the database hangs um in a dead-lock.

      And either an rollback on transaction-level nor killing the specific process in MariadDB helps (the status in processlist just switches to 'KILLED' but does not disappear).
      Have to kill the mariadbd on the system to force a restart of the service.

      error-log is mostly empty, only suspicious is:

      2022-03-01 16:10:33 0 [Warning] /usr/sbin/mariadbd: Thread 13235574 (user : 'fccUser') did not exit
      

      INNODB MONITOR OUTPUT:

      =====================================
      2022-03-02 16:48:36 0x7ef19c0d2700 INNODB MONITOR OUTPUT
      =====================================
      Per second averages calculated from the last 4 seconds
      -----------------
      BACKGROUND THREAD
      -----------------
      srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 88453 srv_idle
      srv_master_thread log flush and writes: 88451
      ----------
      SEMAPHORES
      ----------
      ------------
      TRANSACTIONS
      ------------
      Trx id counter 499373011
      Purge done for trx's n:o < 498830860 undo n:o < 0 state: running
      History list length 270960
      LIST OF TRANSACTIONS FOR EACH SESSION:
      ---TRANSACTION 499373008, ACTIVE 0 sec
      9 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 32
      MariaDB thread id 1051177, OS thread handle 139854290118400, query id 112011648 10.254.28.151 fccUser 
      ---TRANSACTION 499373006, ACTIVE 0 sec
      32 lock struct(s), heap size 3488, 1 row lock(s), undo log entries 463
      MariaDB thread id 1051178, OS thread handle 139854282340096, query id 112011651 10.254.28.151 fccUser 
      ---TRANSACTION 499373004, ACTIVE 0 sec
      10 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 143
      MariaDB thread id 1051179, OS thread handle 139854296569600, query id 112011652 10.254.28.151 fccUser 
      ---TRANSACTION 499372994, ACTIVE 1 sec
      9 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 77
      MariaDB thread id 1051172, OS thread handle 139854293497600, query id 112011649 10.254.28.151 fccUser 
      ---TRANSACTION 499372984, ACTIVE 1 sec
      15 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 661
      MariaDB thread id 1051168, OS thread handle 139854291961600, query id 112011653 10.254.28.151 fccUser 
      ---TRANSACTION 499372979, ACTIVE 1 sec
      8 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 187
      MariaDB thread id 1051164, OS thread handle 139854277019392, query id 112011645 10.254.28.151 fccUser 
      ---TRANSACTION 499372976, ACTIVE 2 sec
      9 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1150
      MariaDB thread id 1051162, OS thread handle 139573419251456, query id 112011647 10.254.28.151 fccUser 
      ---TRANSACTION 499372961, ACTIVE 2 sec
      32 lock struct(s), heap size 3488, 1 row lock(s), undo log entries 1097
      MariaDB thread id 1051154, OS thread handle 139854281725696, query id 112011654 10.254.28.151 fccUser 
      ---TRANSACTION (0x7f325f42cc30), not started
      0 lock struct(s), heap size 1128, 0 row lock(s)
      ---TRANSACTION (0x7f325f43ba30), not started
      0 lock struct(s), heap size 1128, 0 row lock(s)
      ---TRANSACTION (0x7f325f437630), ACTIVE 6253 sec starting index read
      mysql tables in use 7, locked 0
      0 lock struct(s), heap size 1128, 0 row lock(s)
      MariaDB thread id 863232, OS thread handle 139573419558656, query id 91757471 10.254.28.149 fccUser Sending data
      SET STATEMENT max_statement_time=180 FOR WITH invCTE AS (SELECT JSON_OBJECT(	'ObjectId', invData.objectId,	'eepromName', invData.eepromName,	'COUNT', COUNT(invData.mechanicsRelease),	'fckm', releaseInfo(invData.mechanicsRelease),	'fcke', releaseInfo(invData.electronicsRelease),	'bauz', invData.constructionLevelFru,	'matNr', invData.tcnFru) AS invData, DATE_FORMAT(fr.date,'%x/%v') AS TimeSpan FROM Device AS d JOIN Flightrecord AS fr USING(deviceId) JOIN rm3flightrecord AS rm3Fr USING(flightRecordId) JOIN inventorydata AS invData USING(flightRecordId) WHERE NOT d.isInternal AND d.deviceType  = '
      Trx read view will not see trx with id >= 499127988, sees < 498865549
      ---TRANSACTION (0x7f325f42bb30), ACTIVE 8280 sec starting index read
      mysql tables in use 8, locked 0
      0 lock struct(s), heap size 1128, 0 row lock(s)
      MariaDB thread id 853090, OS thread handle 139854292883200, query id 84760567 10.254.28.149 fccUser Sending data
      SET STATEMENT max_statement_time=180 FOR SELECT GROUP_CONCAT(DISTINCT d.customer ORDER BY customer ASC) AS customers, GROUP_CONCAT(DISTINCT d.country ORDER BY country ASC) AS countrys, GROUP_CONCAT(DISTINCT d.deviceType ORDER BY deviceType ASC) AS deviceTypes, COUNT(DISTINCT deviceId) AS  sumDevices, GROUP_CONCAT(DISTINCT calendarWeek ORDER BY calendarWeek DESC) AS weeks, COUNT(DISTINCT flightRecordId) AS sumFlightRecords, GROUP_CONCAT(DISTINCT  CONCAT_WS(' ',inv.objectId, inv.eepromName) ORDER BY inv.objectId ASC) AS inventoryItems, GROUP_CONCAT(DISTINCT releaseInfo(inv.mechanicsRelease) ORDE
      Trx read view will not see trx with id >= 499027698, sees < 498865549
      ---TRANSACTION (0x7f325f428830), ACTIVE 8782 sec starting index read
      mysql tables in use 5, locked 0
      0 lock struct(s), heap size 1128, 0 row lock(s)
      MariaDB thread id 851032, OS thread handle 139854291347200, query id 82854933 10.254.28.149 fccUser Sending data
      SET STATEMENT max_statement_time=180 FOR SELECT d.Country, d.Customer, d.AtmType, d.DeviceType, d.System, GROUP_CONCAT(DISTINCT mfr.configInfo) AS configInfo, GROUP_CONCAT(DISTINCT fr.systemSerialNumber ORDER BY fr.systemSerialNumber) AS systemSerialNumber, fr.date, CONCAT('[', GROUP_CONCAT(DISTINCT JSON_OBJECT(	'ObjectId', invData.objectId,	'eepromName', invData.eepromName,	'fckm', releaseInfo(invData.mechanicsRelease),	'fcke', releaseInfo(invData.electronicsRelease),	'bauz', invData.constructionLevelFru,	'matNr', invData.tcnFru,	'snoFru', invData.snoFru)),']') AS jsonData FROM Device AS d JO
      Trx read view will not see trx with id >= 499003501, sees < 498865549
      ---TRANSACTION (0x7f325f434330), ACTIVE 8889 sec starting index read
      mysql tables in use 5, locked 0
      0 lock struct(s), heap size 1128, 0 row lock(s)
      MariaDB thread id 833319, OS thread handle 139854281111296, query id 82518141 10.254.28.149 fccUser Sending data
      SET STATEMENT max_statement_time=180 FOR SELECT d.Country, d.Customer, d.AtmType, d.DeviceType, d.System, GROUP_CONCAT(DISTINCT mfr.configInfo) AS configInfo, GROUP_CONCAT(DISTINCT fr.systemSerialNumber ORDER BY fr.systemSerialNumber) AS systemSerialNumber, fr.date, CONCAT('[', GROUP_CONCAT(DISTINCT JSON_OBJECT(	'ObjectId', invData.objectId,	'eepromName', invData.eepromName,	'fckm', releaseInfo(invData.mechanicsRelease),	'fcke', releaseInfo(invData.electronicsRelease),	'bauz', invData.constructionLevelFru,	'matNr', invData.tcnFru,	'snoFru', invData.snoFru)),']') AS jsonData FROM Device AS d JO
      Trx read view will not see trx with id >= 498999973, sees < 498865549
      ---TRANSACTION (0x7f325f433230), ACTIVE 9237 sec starting index read
      mysql tables in use 7, locked 0
      0 lock struct(s), heap size 1128, 0 row lock(s)
      MariaDB thread id 820829, OS thread handle 139854292576000, query id 81471517 10.254.28.149 fccUser Sending data
      SET STATEMENT max_statement_time=180 FOR WITH invCTE AS (SELECT JSON_OBJECT(	'ObjectId', invData.objectId,	'eepromName', invData.eepromName,	'COUNT', COUNT(invData.mechanicsRelease),	'fckm', releaseInfo(invData.mechanicsRelease),	'fcke', releaseInfo(invData.electronicsRelease),	'bauz', invData.constructionLevelFru,	'matNr', invData.tcnFru) AS invData, DATE_FORMAT(fr.date,'%x/%v') AS TimeSpan FROM Device AS d JOIN Flightrecord AS fr USING(deviceId) JOIN rm3flightrecord AS rm3Fr USING(flightRecordId) JOIN inventorydata AS invData USING(flightRecordId) WHERE NOT d.isInternal AND d.deviceType  = '
      Trx read view will not see trx with id >= 498987846, sees < 498865549
      ---TRANSACTION 498869350, ACTIVE 12264 sec inserting
      mysql tables in use 1, locked 1
      12 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 28
      MariaDB thread id 779625, OS thread handle 139854295033600, query id 71573797 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498869286, ACTIVE 12267 sec inserting
      mysql tables in use 1, locked 1
      13 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 59
      MariaDB thread id 779575, OS thread handle 139854294112000, query id 71569211 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498869030, ACTIVE 12275 sec inserting
      mysql tables in use 1, locked 1
      7 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 3
      MariaDB thread id 779394, OS thread handle 139854294726400, query id 71547016 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498869028, ACTIVE 12275 sec inserting
      mysql tables in use 1, locked 1
      14 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 141
      MariaDB thread id 779393, OS thread handle 139854296876800, query id 71547276 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498868954, ACTIVE 12277 sec inserting
      mysql tables in use 1, locked 1
      13 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 48
      MariaDB thread id 779333, OS thread handle 139854295648000, query id 71541649 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498868756, ACTIVE 12282 sec inserting
      mysql tables in use 1, locked 1
      14 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 18
      MariaDB thread id 779193, OS thread handle 139854296262400, query id 71527471 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498868644, ACTIVE 12285 sec inserting
      mysql tables in use 1, locked 1
      13 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 108
      MariaDB thread id 779104, OS thread handle 139854275176192, query id 71524084 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498868593, ACTIVE 12286 sec inserting
      mysql tables in use 1, locked 1
      13 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 45
      MariaDB thread id 779068, OS thread handle 139576170018560, query id 71521522 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498868539, ACTIVE 12288 sec inserting
      mysql tables in use 1, locked 1
      12 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 46
      MariaDB thread id 779029, OS thread handle 139854274868992, query id 71518003 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498868492, ACTIVE 12290 sec updating or deleting
      mysql tables in use 48, locked 48
      6 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
      MariaDB thread id 778998, OS thread handle 139854278862592, query id 71514723 10.254.28.150 fccUser Updating
      DELETE FROM flightRecord WHERE flightRecordId = 42493133
      ---TRANSACTION 498868336, ACTIVE 12294 sec inserting
      mysql tables in use 1, locked 1
      7 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 3
      MariaDB thread id 778886, OS thread handle 139854274561792, query id 71501944 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498868269, ACTIVE 12297 sec inserting
      mysql tables in use 1, locked 1
      15 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 102
      MariaDB thread id 778842, OS thread handle 139854279784192, query id 71494737 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498868191, ACTIVE 12299 sec inserting
      mysql tables in use 1, locked 1
      14 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 153
      MariaDB thread id 778787, OS thread handle 139854277940992, query id 71490051 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498868021, ACTIVE 12302 sec inserting
      mysql tables in use 1, locked 1
      13 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 88
      MariaDB thread id 778675, OS thread handle 139854276712192, query id 71481832 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498867961, ACTIVE 12305 sec inserting
      mysql tables in use 1, locked 1
      14 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 25
      MariaDB thread id 778639, OS thread handle 139854275790592, query id 71476103 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498867803, ACTIVE 12309 sec inserting
      mysql tables in use 1, locked 1
      12 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 49
      MariaDB thread id 778533, OS thread handle 139854276404992, query id 71465661 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498867792, ACTIVE 12310 sec inserting
      mysql tables in use 1, locked 1
      11 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 8
      MariaDB thread id 778524, OS thread handle 139854298412800, query id 71464578 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498867480, ACTIVE 12318 sec inserting
      mysql tables in use 1, locked 1
      8 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 4
      MariaDB thread id 778326, OS thread handle 139854275483392, query id 71444665 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498867479, ACTIVE 12318 sec inserting
      mysql tables in use 1, locked 1
      12 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 30
      MariaDB thread id 778325, OS thread handle 139854313137920, query id 71444513 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498867209, ACTIVE 12324 sec inserting
      mysql tables in use 1, locked 1
      14 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 193
      MariaDB thread id 778143, OS thread handle 139576170632960, query id 71425116 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498867208, ACTIVE 12324 sec inserting
      mysql tables in use 1, locked 1
      7 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 3
      MariaDB thread id 778140, OS thread handle 139854276097792, query id 71424368 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498867109, ACTIVE 12327 sec inserting
      mysql tables in use 1, locked 1
      13 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 71
      MariaDB thread id 778071, OS thread handle 139854298105600, query id 71415221 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498866841, ACTIVE 12334 sec inserting
      mysql tables in use 1, locked 1
      8 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 7
      MariaDB thread id 777883, OS thread handle 139854279476992, query id 71392001 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498866760, ACTIVE 12337 sec inserting
      mysql tables in use 1, locked 1
      11 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 8
      MariaDB thread id 777828, OS thread handle 139854299027200, query id 71386843 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498866645, ACTIVE 12341 sec inserting
      mysql tables in use 1, locked 1
      8 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 4
      MariaDB thread id 777749, OS thread handle 139854297798400, query id 71378387 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498866544, ACTIVE 12344 sec inserting
      mysql tables in use 1, locked 1
      7 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 3
      MariaDB thread id 777678, OS thread handle 139854278248192, query id 71372223 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498866420, ACTIVE 12347 sec inserting
      mysql tables in use 1, locked 1
      7 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 3
      MariaDB thread id 777590, OS thread handle 139854269036288, query id 71364798 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498866342, ACTIVE 12349 sec inserting
      mysql tables in use 1, locked 1
      7 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 3
      MariaDB thread id 777544, OS thread handle 139854269343488, query id 71359580 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498866316, ACTIVE 12350 sec inserting
      mysql tables in use 1, locked 1
      14 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 268
      MariaDB thread id 777524, OS thread handle 139854299334400, query id 71357180 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498866154, ACTIVE 12353 sec inserting
      mysql tables in use 1, locked 1
      12 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 24
      MariaDB thread id 777412, OS thread handle 139854269650688, query id 71343956 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498866047, ACTIVE 12356 sec inserting
      mysql tables in use 1, locked 1
      8 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 4
      MariaDB thread id 777341, OS thread handle 139854277326592, query id 71336090 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498865939, ACTIVE 12359 sec inserting
      mysql tables in use 1, locked 1
      12 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 13
      MariaDB thread id 777269, OS thread handle 139854277633792, query id 71326942 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498865827, ACTIVE 12361 sec inserting
      mysql tables in use 1, locked 1
      7 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 3
      MariaDB thread id 777198, OS thread handle 139854280398592, query id 71318911 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498865765, ACTIVE 12363 sec inserting
      mysql tables in use 1, locked 1
      7 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 3
      MariaDB thread id 777158, OS thread handle 139854279169792, query id 71314671 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498865618, ACTIVE 12367 sec inserting
      mysql tables in use 1, locked 1
      7 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 3
      MariaDB thread id 777058, OS thread handle 139854313752320, query id 71303094 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION 498865549, ACTIVE 12368 sec inserting
      mysql tables in use 1, locked 1
      7 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 9
      MariaDB thread id 777020, OS thread handle 139854278555392, query id 71298302 10.254.28.150 fccUser Update
      insert into InventoryData (constructionLevelFru, eepromName, electronicsRelease, inventoryType, mechanicsRelease, objectId, sdateFru, slaveFirmwareRelease, snoElectronic, snoFru, tcnFru, traceIndex, flightRecordId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ---TRANSACTION (0x7f325f409b30), ACTIVE 13353 sec fetching rows
      mysql tables in use 8, locked 0
      0 lock struct(s), heap size 1128, 0 row lock(s)
      MariaDB thread id 761164, OS thread handle 139854313445120, query id 68837725 10.254.28.149 fccUser Sending data
      SET STATEMENT max_statement_time=180 FOR SELECT DISTINCT CONCAT(r.denomination, ' ', r.currency) AS Banknote FROM Device AS d JOIN Flightrecord AS fr USING(deviceId) JOIN rm4flightrecord AS rm4Fr USING(flightRecordId) JOIN (SELECT flightRecordId FROM Device AS d JOIN Flightrecord AS fr USING(deviceId) JOIN rm4flightrecord AS rm4Fr USING(flightRecordId) JOIN inventorydata AS inv USING(flightRecordId) WHERE NOT d.isInternal AND d.deviceType IN ("RM4H") AND d.country IN ("DE_Germany") AND fr.type = 'RM4' AND fr.calendarWeek >= 202125 AND (inv.objectId = -233828863) AND inv.tcnFru IN ("01750357308
      Trx read view will not see trx with id >= 498830871, sees < 498830841
      ---TRANSACTION (0x7f325f405730), not started
      0 lock struct(s), heap size 1128, 0 row lock(s)
      ---TRANSACTION (0x7f325f403530), not started
      0 lock struct(s), heap size 1128, 0 row lock(s)
      ---TRANSACTION (0x7f325f400230), ACTIVE 13353 sec fetching rows
      mysql tables in use 9, locked 0
      0 lock struct(s), heap size 1128, 0 row lock(s)
      MariaDB thread id 756778, OS thread handle 139854269957888, query id 68837335 10.254.28.149 fccUser Sending data
      SET STATEMENT max_statement_time=180 FOR SELECT GROUP_CONCAT(DISTINCT d.customer ORDER BY customer ASC) AS customers, GROUP_CONCAT(DISTINCT d.country ORDER BY country ASC) AS countrys, GROUP_CONCAT(DISTINCT d.deviceType ORDER BY deviceType ASC) AS deviceTypes, COUNT(DISTINCT deviceId) AS  sumDevices, GROUP_CONCAT(DISTINCT calendarWeek ORDER BY calendarWeek DESC) AS weeks, COUNT(DISTINCT flightRecordId) AS sumFlightRecords, SUM(p.movedMedia) AS sumMovedMedia, GROUP_CONCAT(DISTINCT  CONCAT_WS(' ',inv.objectId, inv.eepromName) ORDER BY inv.objectId ASC) AS inventoryItems, GROUP_CONCAT(DISTINCT re
      Trx read view will not see trx with id >= 498830860, sees < 498830841
      ---TRANSACTION (0x7f325f40ce30), ACTIVE 13353 sec fetching rows
      mysql tables in use 9, locked 0
      0 lock struct(s), heap size 1128, 0 row lock(s)
      MariaDB thread id 756773, OS thread handle 139854312523520, query id 68837393 10.254.28.149 fccUser Sending data
      SET STATEMENT max_statement_time=180 FOR SELECT GROUP_CONCAT(DISTINCT d.customer ORDER BY customer ASC) AS customers, GROUP_CONCAT(DISTINCT d.country ORDER BY country ASC) AS countrys, GROUP_CONCAT(DISTINCT d.deviceType ORDER BY deviceType ASC) AS deviceTypes, COUNT(DISTINCT deviceId) AS  sumDevices, GROUP_CONCAT(DISTINCT calendarWeek ORDER BY calendarWeek DESC) AS weeks, COUNT(DISTINCT flightRecordId) AS sumFlightRecords, SUM(p.movedMedia) AS sumMovedMedia, GROUP_CONCAT(DISTINCT  CONCAT_WS(' ',inv.objectId, inv.eepromName) ORDER BY inv.objectId ASC) AS inventoryItems, GROUP_CONCAT(DISTINCT re
      Trx read view will not see trx with id >= 498830864, sees < 498830841
      --------
      FILE I/O
      --------
      Pending flushes (fsync) log: 0; buffer pool: 0
      10713505 OS file reads, 20686025 OS file writes, 180495 OS fsyncs
      0.50 reads/s, 16384 avg bytes/read, 1.00 writes/s, 1.00 fsyncs/s
      -------------------------------------
      INSERT BUFFER AND ADAPTIVE HASH INDEX
      -------------------------------------
      Ibuf: size 1583, free list len 379673, seg size 381257, 124 merges
      merged operations:
       insert 1991, delete mark 818, delete 818
      discarded operations:
       insert 0, delete mark 0, delete 0
      0.00 hash searches/s, 0.00 non-hash searches/s
      ---
      LOG
      ---
      Log sequence number 26253191150825
      Log flushed up to   26253188941985
      Pages flushed up to 26252931949961
      Last checkpoint at  26252931949961
      0 pending log flushes, 0 pending chkp writes
      86531 log i/o's done, 1.00 log i/o's/second
      ----------------------
      BUFFER POOL AND MEMORY
      ----------------------
      Total large memory allocated 275012124672
      Dictionary memory allocated 1700217512
      Buffer pool size   16613376
      Free buffers       1162127
      Database pages     12963548
      Old database pages 4785352
      Modified db pages  48330
      Percent of dirty pages(LRU & free pages): 0.342
      Max dirty pages percent: 90.000
      Pending reads 0
      Pending writes: LRU 0, flush list 0
      Pages made young 7489486, not young 168282843
      0.00 youngs/s, 2.50 non-youngs/s
      Pages read 10696931, created 2687512, written 20601815
      0.50 reads/s, 98.23 creates/s, 0.00 writes/s
      Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
      Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
      LRU len: 12963548, unzip_LRU len: 6123811
      I/O sum[6]:cur[2], unzip sum[207]:cur[30]
      --------------
      ROW OPERATIONS
      --------------
      8 read views open inside InnoDB
      Process ID=0, Main thread ID=0, state: sleeping
      Number of rows inserted 239506249, updated 1367100, deleted 27047407, read 12547057806
      8802.05 inserts/s, 29.49 updates/s, 0.00 deletes/s, 30.99 reads/s
      Number of system rows inserted 0, updated 0, deleted 0, read 0
      0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
      ----------------------------
      END OF INNODB MONITOR OUTPUT
      ============================
      

      my.ini

       
      # This group is read both by the client and the mariadb
      # use it for options that affect everything
      [client-mariadb]
       
      port=3306
       
      # This group is only read by MariaDB servers, not by MySQL.
      # If you use the same .cnf file for MySQL and MariaDB,
      # you can put MariaDB-only options here
      [mariadb]
       
      datadir=/data/mariadb10.6/data
      tmpdir=/data/mariadb10.6/temp
      log_error=/data/mariadb10.6/mariadbErr.log
       
       
      character_set_server=utf8
      collation-server=utf8_general_ci
       
      #no case sensitiveness...
      lower_case_table_names=1
       
      #skip_name_resolve
      performance_schema=on
       
      max_connections=500
       
      query_cache_size=16M
       
      tmp_table_size=2G
      max_heap_table_size=2G
       
      max_allowed_packet=64M
      table_definition_cache=2048
       
      sort_buffer_size=64M
      read_buffer_size=64M
       
      mrr_buffer_size=16M
       
      join_cache_level=8
      join_buffer_size=64M
      join_buffer_space_limit=128M
       
      table_open_cache_instances=16
       
      #4 MB for GROUP_CONCAT()
      group_concat_max_len=4194304
       
      #*** INNODB Specific options ***
      innodb_compression_algorithm=lzma
      innodb_read_only_compressed=OFF
       
      innodb_buffer_pool_size=256G
      innodb_sort_buffer_size=16M
       
      innodb_log_file_size=512M
      innodb_log_buffer_size=64M
       
      innodb_flush_log_at_trx_commit=0
      innodb_flush_neighbors=0
       
      innodb_read_io_threads=24
      innodb_write_io_threads=16
       
      innodb_io_capacity = 15000
      innodb_io_capacity_max = 30000
       
      innodb_doublewrite = 0
       
       
      [mysqldump]
      quick
      max_allowed_packet = 32M
      

      Anything I can do to get more information to trace down this problem?

      Attachments

        1. gdb.txt
          416 kB
          Niels Hendriks
        2. gdb2.txt
          78 kB
          Niels Hendriks
        3. innodb_status.txt
          30 kB
          Sebastian Stamm
        4. log_2022-04-08.txt
          1.05 MB
          Sebastian Stamm
        5. log.txt
          120 kB
          Sebastian Stamm
        6. log-2022-06-17.7z
          221 kB
          Sebastian Stamm
        7. log-2022-06-20.txt
          2.08 MB
          Sebastian Stamm
        8. log-2022-07-29.txt
          6.52 MB
          Sebastian Stamm
        9. log-2022-08-02.txt
          386 kB
          Sebastian Stamm
        10. log-debug.txt
          313 kB
          Sebastian Stamm
        11. log-full.txt
          745 kB
          Sebastian Stamm
        12. screenshot-1.png
          13 kB
          Sebastian Stamm
        13. stacktrace.txt
          1.77 MB
          Sebastian Stamm
        14. stacktrace-2022-04-28.txt
          2.10 MB
          Sebastian Stamm
        15. stacktrace-2022-05-06.txt
          1.68 MB
          Sebastian Stamm

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              sstamm Sebastian Stamm
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.