Hi

We have

207 devices (network, servers, ups etc.)

6709 ports

2841 sensors

The problem is still not fixed.

Best,

Thomas

Am 26.07.2022 um 23:49 schrieb Adam Armstrong via observium:

You might want to provide information on how big the installation is and what sort of devices are involved.

But I'd try upgrading to a newer distribution. Newer MySQL/MariaDB may behave better.

Adam.

-----Original Message----- From: observium observium-bounces@observium.org On Behalf Of Thomas Stather via observium Sent: 26 July 2022 08:42 To: 'Observium' observium@observium.org Cc: Thomas Stather tstather@mr.mpg.de Subject: [Observium] Lots of errors "Deadlock found when trying to get lock; try restarting transaction"

Hi

We are running Observium Professional r12071

OS: Ubuntu 18.04 LTS

MySQL: 5.7.38-0ubuntu0.18.04.1

In our /opt/observium/logs/db log, we are seeing a lot of these messages (approx. every minute).

---

[2022/07/26 09:23:54 +0200] poller.php(3889): Failed dbQuery (#1213 - Deadlock found when trying to get lock; try restarting transaction), Query: INSERT INTO `vlans_fdb` (`fdb_id`,`port_id`,`fdb_status`,`fdb_port`,`fdb_last_change`,`deleted`) VALUES ('2022403206','19917','learned','1','1658820234','0'),('2022403209','19917','learned','1','1658820234','0'),('2022374672','19917','learned','1','1658820234','0'),('2022406153','19917','learned','1','1658820234','0'),('2022438526','19917','learned','1','1658820234','0'),('2022403210','19917','learned','1','1658820234','0'),('2022403211','19917','learned','1','1658820234','0'),('2022424430','19917','learned','1','1658820234','0'),('2022389346','19917','learned','1','1658820234','0'),('2022417515','19917','learned','1','1658820234','0'),('2022403215','19917','learned','1','1658820234','0'),('2022424431','19917','learned','1','1658820234','0'),('2022374680','19917','learned','1','1658820234','0'),('2022428820','19917','learned','1','1658820234','0'),('2022417516','19917','learned','1','1658820234','0'),('2022626809','19917','learned','1','1658820234','0'),('2022403218','19917','learned','1','1658820234','0'),('2022013327','19917','learned','1','1658820234','0'),('2022440623','19917','learned','1','1658820234','0'),('2023195038','19917','learned','1','1658820234','0'),('2022417520','19917','learned','1','1658820234','0'),('2022417521','19917','learned','1','1658820234','0'),('2022417522','19917','learned','1','1658820234','0'),('2022417525','19917','learned','1','1658820234','0'),('2022389354','19917','learned','1','1658820234','0'),('2022634466','19917','learned','1','1658820234','0'),('2022389355','19917','learned','1','1658820234','0'),('2022374686','19917','learned','1','1658820234','0'),('2022318658','19917','learned','1','1658820234','0'),('2022403222','19917','learned','1','1658820234','0'),('2022389360','19917','learned','1','1658820234','0'),('2022334581','19917','learned','1','1658820234','0'),('2022318665','19917','learned','1','1658820234','0'),('2022535557','19917','learned','1','1658820234','0'),('2022633094','19917','learned','1','1658820234','0'),('2022403228','19917','learned','1','1658820234','0'),('2022507536','19917','learned','1','1658820234','0'),('2023391470','19917','learned','1','1658820234','0'),('2022440625','19917','learned','1','1658820234','0'),('2022442062','19917','learned','1','1658820234','0'),('2022318672','19917','learned','1','1658820234','0'),('2022352825','19917','learned','1','1658820234','0'),('2022448395','19917','learned','1','1658820234','0'),('1781846826','19917','learned','1','1658820234','0'),('2022440626','19917','learned','1','1658820234','0'),('2021364663','19917','learned','1','1658820234','0'),('2022621595','19917','learned','1','1658820234','0'),('2021996873','19917','learned','1','1658820234','0'),('1930092608','19917','learned','1','1658820234','0'),('2022403237','19917','learned','1','1658820234','0'),('2022389374','19917','learned','1','1658820234','0'),('2021923896','19917','learned','1','1658820234','0'),('2022406174','19917','learned','1','1658820234','0'),('2022281507','19917','learned','1','1658820234','0'),('2022370404','19917','learned','1','1658820234','0'),('2022132020','19917','learned','1','1658820234','0'),('2022370406','19917','learned','1','1658820234','0'),('2022403242','19917','learned','1','1658820234','0'),('2022442064','19917','learned','1','1658820234','0'),('2022160398','19917','learned','1','1658820234','0'),('2021963914','19917','learned','1','1658820234','0'),('2022370416','19917','learned','1','1658820234','0'),('2022370417','19917','learned','1','1658820234','0'),('2022428831','19917','learned','1','1658820234','0'),('2022352835','19917','learned','1','1658820234','0'),('2022417541','19917','learned','1','1658820234','0'),('2022403248','19917','learned','1','1658820234','0'),('2022406184','19917','learned','1','1658820234','0'),('2022403251','19917','learned','1','1658820234','0'),('2022403252','19917','learned','1','1658820234','0'),('2022248989','19917','learned','1','1658820234','0'),('2022220229','19917','learned','1','1658820234','0'),('2022438537','19917','learned','1','1658820234','0'),('2022417547','19917','learned','1','1658820234','0'),('2022403257','19917','learned','1','1658820234','0'),('2022403260','19917','learned','1','1658820234','0'),('2022403261','19917','learned','1','1658820234','0'),('2022403262','19917','learned','1','1658820234','0'),('2022403263','19917','learned','1','1658820234','0'),('2022403265','19917','learned','1','1658820234','0'),('2022374716','19917','learned','1','1658820234','0'),('2022344523','19917','learned','1','1658820234','0'),('2022403276','19917','learned','1','1658820234','0'),('2024336889','19917','learned','1','1658820234','0'),('2022036744','19917','learned','1','1658820234','0'),('2022352850','19917','learned','1','1658820234','0'),('2022463833','19917','learned','1','1658820234','0'),('2022389400','19917','learned','1','1658820234','0'),('2022535560','19917','learned','1','1658820234','0'),('2022406201','19917','learned','1','1658820234','0'),('2022403291','19917','learned','1','1658820234','0'),('2022374736','19917','learned','1','1658820234','0'),('2022295503','19917','learned','1','1658820234','0'),('2022295504','19917','learned','1','1658820234','0'),('2022885411','19917','learned','1','1658820234','0'),('2022417565','19917','learned','1','1658820234','0'),('2022403294','19917','learned','1','1658820234','0'),('2022389405','19917','learned','1','1658820234','0'),('2022403295','19917','learned','1','1658820234','0'),('2022352861','19917','learned','1','1658820234','0'),('2022389406','19917','learned','1','1658820234','0'),('2022403296','19917','learned','1','1658820234','0'),('2022220258','19917','learned','1','1658820234','0'),('2022403297','19917','learned','1','1658820234','0'),('2022318712','19917','learned','1','1658820234','0'),('2022185898','19917','learned','1','1658820234','0'),('2022417566','19917','learned','1','1658820234','0'),('2022403299','19917','learned','1','1658820234','0'),('2022431524','19917','learned','1','1658820234','0'),('2022318715','19917','learned','1','1658820234','0'),('2022403303','19917','learned','1','1658820234','0'),('2022389409','19917','learned','1','1658820234','0'),('2022406211','19917','learned','1','1658820234','0'),('2022056535','19917','learned','1','1658820234','0'),('2022389412','19917','learned','1','1658820234','0'),('2022417570','19917','learned','1','1658820234','0'),('2022443725','19917','learned','1','1658820234','0'),('2021925685','19917','learned','1','1658820234','0'),('2022440635','19917','learned','1','1658820234','0'),('2023222617','19917','learned','1','1658820234','0'),('2022428840','19917','learned','1','1658820234','0'),('2022403307','19917','learned','1','1658820234','0'),('2022406215','19917','learned','1','1658820234','0'),('2022458785','19917','learned','1','1658820234','0'),('2022406216','19917','learned','1','1658820234','0'),('2022449600','19917','learned','1','1658820234','0'),('2022445047','19917','learned','1','1658820234','0'),('2023938925','19917','learned','1','1658820234','0'),('2022389418','19917','learned','1','1658820234','0'),('2022451483','19917','learned','1','1658820234','0'),('2022417575','19917','learned','1','1658820234','0'),('2022440636','19917','learned','1','1658820234','0'),('2022370471','19917','learned','1','1658820234','0'),('2022417583','19917','learned','1','1658820234','0'),('2022464789','19917','learned','1','1658820234','0'),('2022287606','19917','learned','1','1658820234','0'),('2022352884','19917','learned','1','1658820234','0'),('2022424457','19917','learned','1','1658820234','0'),('2022417585','19917','learned','1','1658820234','0'),('2022424462','19917','learned','1','1658820234','0'),('2022403324','19917','learned','1','1658820234','0'),('2022406247','19917','learned','1','1658820234','0'),('2022117804','19917','learned','1','1658820234','0'),('2022464791','19917','learned','1','1658820234','0'),('2022403327','19917','learned','1','1658820234','0'),('2022406249','19917','learned','1','1658820234','0'),('2022403329','19917','learned','1','1658820234','0'),('2022448403','19917','learned','1','1658820234','0'),('2022417595','19917','learned','1','1658820234','0'),('2022403333','19917','learned','1','1658820234','0'),('2022445053','19917','learned','1','1658820234','0'),('2022389440','19917','learned','1','1658820234','0'),('2024143217','19917','learned','1','1658820234','0') ON DUPLICATE KEY UPDATE `fdb_id`=VALUES(`fdb_id`),`port_id`=VALUES(`port_id`),`fdb_status`=VALUES(`fdb_status`),`fdb_port`=VALUES(`fdb_port`),`fdb_last_change`=VALUES(`fdb_last_change`),`deleted`=VALUES(`deleted`)

---

Does somebody have a clue how this can be fixed?

Best,

Thomas

-- Thomas Stather IT Services

VCP4, VCP6-DCV, VCP6.5-DCV

Tel: +49 6221 486 628 Fax: +49 6221 486 561

Bevor Sie diese Mail ausdrucken, prüfen Sie bitte ob dies wirklich nötig ist. Umweltschutz geht uns alle an!

---

observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium

---

observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium

This seems to be an issue with the number of updates we do on large vlan installations. It's not entirely clear to me why it's happening, so not really sure how to fix it.

MySQL behaves oddly at times. We do an insert with a clause to update when primary key exists, which might well be why it's behaving oddly. I think if we tried to remove that and do it another way, it would be much slower.

I saw some solutions involving query ordering, but I couldn't see how that would affect us.

I wonder if this happens only when both an insert and an update occur on the same query?

adam.

Milton Ngan via observium wrote on 2022-10-28 10:03:

Try out some of the diagnostic steps here. Is it always the vlans_fdb table? You can probably turn off polling that data for now so polling keeps working.

https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlocks-handling.html

• At any time, issue |SHOW ENGINE INNODB STATUS| https://dev.mysql.com/doc/refman/5.6/en/show-engine.html to determine the cause of the most recent deadlock. That can help you to tune your application to avoid deadlocks.

• If frequent deadlock warnings cause concern, collect more extensive debugging information by enabling the |innodb_print_all_deadlocks| https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks variable. Information about each deadlock, not just the latest one, is recorded in the MySQL error log https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_error_log. Disable this option when you are finished debugging.

---

*From:* Thomas Stather via observium observium@lists.observium.org *Sent:* Friday, October 28, 2022 12:53:01 AM *To:* observium@observium.org *Cc:* Adam Armstrong; Thomas Stather *Subject:* [Observium] Re: Lots of errors "Deadlock found when trying to get Hi

We have

207 devices (network, servers, ups etc.)

6709 ports

2841 sensors

The problem is still not fixed.

Best,

Thomas

Am 26.07.2022 um 23:49 schrieb Adam Armstrong via observium:

...

You might want to provide information on how big the installation is and what sort of devices are involved.

But I'd try upgrading to a newer distribution. Newer MySQL/MariaDB may behave better.

Adam.

-----Original Message----- From: observium observium-bounces@observium.org On Behalf Of Thomas Stather via observium Sent: 26 July 2022 08:42 To: 'Observium' observium@observium.org Cc: Thomas Stather tstather@mr.mpg.de Subject: [Observium] Lots of errors "Deadlock found when trying to get lock; try restarting transaction"

Hi

We are running Observium Professional r12071

OS: Ubuntu 18.04 LTS

MySQL: 5.7.38-0ubuntu0.18.04.1

In our /opt/observium/logs/db log, we are seeing a lot of these messages (approx. every minute).

---

[2022/07/26 09:23:54 +0200] poller.php(3889): Failed dbQuery (#1213 - Deadlock found when trying to get lock; try restarting transaction), Query: INSERT INTO `vlans_fdb` (`fdb_id`,`port_id`,`fdb_status`,`fdb_port`,`fdb_last_change`,`deleted`) VALUES ('2022403206','19917','learned','1','1658820234','0'),('2022403209','19917','learned','1','1658820234','0'),('2022374672','19917','learned','1','1658820234','0'),('2022406153','19917','learned','1','1658820234','0'),('2022438526','19917','learned','1','1658820234','0'),('2022403210','19917','learned','1','1658820234','0'),('2022403211','19917','learned','1','1658820234','0'),('2022424430','19917','learned','1','1658820234','0'),('2022389346','19917','learned','1','1658820234','0'),('2022417515','19917','learned','1','1658820234','0'),('2022403215','19917','learned','1','1658820234','0'),('2022424431','19917','learned','1','1658820234','0'),('2022374680','19917','learned','1','1658820234','0'),('2022428820','19917','learned','1','1658820234','0'),('2022417516','19917','learned','1','1658820234','0'),('2022626809','19917','learned','1','1658820234','0'),('2022403218','19917','learned','1','1658820234','0'),('2022013327','19917','learned','1','1658820234','0'),('2022440623','19917','learned','1','1658820234','0'),('2023195038','19917','learned','1','1658820234','0'),('2022417520','19917','learned','1','1658820234','0'),('2022417521','19917','learned','1','1658820234','0'),('2022417522','19917','learned','1','1658820234','0'),('2022417525','19917','learned','1','1658820234','0'),('2022389354','19917','learned','1','1658820234','0'),('2022634466','19917','learned','1','1658820234','0'),('2022389355','19917','learned','1','1658820234','0'),('2022374686','19917','learned','1','1658820234','0'),('2022318658','19917','learned','1','1658820234','0'),('2022403222','19917','learned','1','1658820234','0'),('2022389360','19917','learned','1','1658820234','0'),('2022334581','19917','learned','1','1658820234','0'),('2022318665','19917','learned','1','1658820234','0'),('2022535557','19917','learned','1','1658820234','0'),('2022633094','19917','learned','1','1658820234','0'),('2022403228','19917','learned','1','1658820234','0'),('2022507536','19917','learned','1','1658820234','0'),('2023391470','19917','learned','1','1658820234','0'),('2022440625','19917','learned','1','1658820234','0'),('2022442062','19917','learned','1','1658820234','0'),('2022318672','19917','learned','1','1658820234','0'),('2022352825','19917','learned','1','1658820234','0'),('2022448395','19917','learned','1','1658820234','0'),('1781846826','19917','learned','1','1658820234','0'),('2022440626','19917','learned','1','1658820234','0'),('2021364663','19917','learned','1','1658820234','0'),('2022621595','19917','learned','1','1658820234','0'),('2021996873','19917','learned','1','1658820234','0'),('1930092608','19917','learned','1','1658820234','0'),('2022403237','19917','learned','1','1658820234','0'),('2022389374','19917','learned','1','1658820234','0'),('2021923896','19917','learned','1','1658820234','0'),('2022406174','19917','learned','1','1658820234','0'),('2022281507','19917','learned','1','1658820234','0'),('2022370404','19917','learned','1','1658820234','0'),('2022132020','19917','learned','1','1658820234','0'),('2022370406','19917','learned','1','1658820234','0'),('2022403242','19917','learned','1','1658820234','0'),('2022442064','19917','learned','1','1658820234','0'),('2022160398','19917','learned','1','1658820234','0'),('2021963914','19917','learned','1','1658820234','0'),('2022370416','19917','learned','1','1658820234','0'),('2022370417','19917','learned','1','1658820234','0'),('2022428831','19917','learned','1','1658820234','0'),('2022352835','19917','learned','1','1658820234','0'),('2022417541','19917','learned','1','1658820234','0'),('2022403248','19917','learned','1','1658820234','0'),('2022406184','19917','learned','1','1658820234','0'),('2022403251','19917','learned','1','1658820234','0'),('2022403252','19917','learned','1','1658820234','0'),('2022248989','19917','learned','1','1658820234','0'),('2022220229','19917','learned','1','1658820234','0'),('2022438537','19917','learned','1','1658820234','0'),('2022417547','19917','learned','1','1658820234','0'),('2022403257','19917','learned','1','1658820234','0'),('2022403260','19917','learned','1','1658820234','0'),('2022403261','19917','learned','1','1658820234','0'),('2022403262','19917','learned','1','1658820234','0'),('2022403263','19917','learned','1','1658820234','0'),('2022403265','19917','learned','1','1658820234','0'),('2022374716','19917','learned','1','1658820234','0'),('2022344523','19917','learned','1','1658820234','0'),('2022403276','19917','learned','1','1658820234','0'),('2024336889','19917','learned','1','1658820234','0'),('2022036744','19917','learned','1','1658820234','0'),('2022352850','19917','learned','1','1658820234','0'),('2022463833','19917','learned','1','1658820234','0'),('2022389400','19917','learned','1','1658820234','0'),('2022535560','19917','learned','1','1658820234','0'),('2022406201','19917','learned','1','1658820234','0'),('2022403291','19917','learned','1','1658820234','0'),('2022374736','19917','learned','1','1658820234','0'),('2022295503','19917','learned','1','1658820234','0'),('2022295504','19917','learned','1','1658820234','0'),('2022885411','19917','learned','1','1658820234','0'),('2022417565','19917','learned','1','1658820234','0'),('2022403294','19917','learned','1','1658820234','0'),('2022389405','19917','learned','1','1658820234','0'),('2022403295','19917','learned','1','1658820234','0'),('2022352861','19917','learned','1','1658820234','0'),('2022389406','19917','learned','1','1658820234','0'),('2022403296','19917','learned','1','1658820234','0'),('2022220258','19917','learned','1','1658820234','0'),('2022403297','19917','learned','1','1658820234','0'),('2022318712','19917','learned','1','1658820234','0'),('2022185898','19917','learned','1','1658820234','0'),('2022417566','19917','learned','1','1658820234','0'),('2022403299','19917','learned','1','1658820234','0'),('2022431524','19917','learned','1','1658820234','0'),('2022318715','19917','learned','1','1658820234','0'),('2022403303','19917','learned','1','1658820234','0'),('2022389409','19917','learned','1','1658820234','0'),('2022406211','19917','learned','1','1658820234','0'),('2022056535','19917','learned','1','1658820234','0'),('2022389412','19917','learned','1','1658820234','0'),('2022417570','19917','learned','1','1658820234','0'),('2022443725','19917','learned','1','1658820234','0'),('2021925685','19917','learned','1','1658820234','0'),('2022440635','19917','learned','1','1658820234','0'),('2023222617','19917','learned','1','1658820234','0'),('2022428840','19917','learned','1','1658820234','0'),('2022403307','19917','learned','1','1658820234','0'),('2022406215','19917','learned','1','1658820234','0'),('2022458785','19917','learned','1','1658820234','0'),('2022406216','19917','learned','1','1658820234','0'),('2022449600','19917','learned','1','1658820234','0'),('2022445047','19917','learned','1','1658820234','0'),('2023938925','19917','learned','1','1658820234','0'),('2022389418','19917','learned','1','1658820234','0'),('2022451483','19917','learned','1','1658820234','0'),('2022417575','19917','learned','1','1658820234','0'),('2022440636','19917','learned','1','1658820234','0'),('2022370471','19917','learned','1','1658820234','0'),('2022417583','19917','learned','1','1658820234','0'),('2022464789','19917','learned','1','1658820234','0'),('2022287606','19917','learned','1','1658820234','0'),('2022352884','19917','learned','1','1658820234','0'),('2022424457','19917','learned','1','1658820234','0'),('2022417585','19917','learned','1','1658820234','0'),('2022424462','19917','learned','1','1658820234','0'),('2022403324','19917','learned','1','1658820234','0'),('2022406247','19917','learned','1','1658820234','0'),('2022117804','19917','learned','1','1658820234','0'),('2022464791','19917','learned','1','1658820234','0'),('2022403327','19917','learned','1','1658820234','0'),('2022406249','19917','learned','1','1658820234','0'),('2022403329','19917','learned','1','1658820234','0'),('2022448403','19917','learned','1','1658820234','0'),('2022417595','19917','learned','1','1658820234','0'),('2022403333','19917','learned','1','1658820234','0'),('2022445053','19917','learned','1','1658820234','0'),('2022389440','19917','learned','1','1658820234','0'),('2024143217','19917','learned','1','1658820234','0') ON DUPLICATE KEY UPDATE `fdb_id`=VALUES(`fdb_id`),`port_id`=VALUES(`port_id`),`fdb_status`=VALUES(`fdb_status`),`fdb_port`=VALUES(`fdb_port`),`fdb_last_change`=VALUES(`fdb_last_change`),`deleted`=VALUES(`deleted`)

---

Does somebody have a clue how this can be fixed?

Best,

Thomas

-- Thomas Stather IT Services

VCP4, VCP6-DCV, VCP6.5-DCV

Tel: +49 6221 486 628 Fax: +49 6221 486 561

Bevor Sie diese Mail ausdrucken, prüfen Sie bitte ob dies wirklich nötig ist. Umweltschutz geht uns alle an!

---

observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium

---

observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium

-- Thomas Stather IT Services

VCP4, VCP6-DCV, VCP6.5-DCV

Room: U1.509 Tel: +49 6221 486 628 Fax: +49 6221 486 561

Bevor Sie diese Mail ausdrucken, prüfen Sie bitte ob dies wirklich nötig ist. Umweltschutz geht uns alle an!

---

observium mailing list -- observium@lists.observium.org To unsubscribe send an email to observium-leave@lists.observium.org

---

observium mailing list -- observium@lists.observium.org To unsubscribe send an email to observium-leave@lists.observium.org

Do you have the output of “SHOW ENGINE INNODB STATUS”?

The docs say you can also get more information if you enable innodb print all deadlocks and then look in the mysql.err after a deadlock has occurred.

Adam.

From: Thomas Stather via observium observium@lists.observium.org Sent: 04 November 2022 11:19 To: observium@lists.observium.org Cc: Thomas Stather tstather@mr.mpg.de Subject: [Observium] Re: Lots of errors "Deadlock found when trying to get

Hi

This happens on INSERT and UPDATE queries since today i had

[2022/11/04 12:05:53 +0100] poller.php(3430): Failed dbQuery (#1205 - Lock wait timeout exceeded; try restarting transaction), Query: UPDATE `vlans_fdb` set `fdb_last_change` ='1667559900',`deleted` ='1' WHERE `fdb_id` IN ('20...... <lots of output in the log>

I just migrated from MySQL 5.7 to MariaDB 10.9 but this didn't solve the issue

Best,

Thomas

Am 04.11.2022 um 02:20 schrieb Adam Armstrong via observium:

This seems to be an issue with the number of updates we do on large vlan installations. It's not entirely clear to me why it's happening, so not really sure how to fix it.

MySQL behaves oddly at times. We do an insert with a clause to update when primary key exists, which might well be why it's behaving oddly. I think if we tried to remove that and do it another way, it would be much slower.

I saw some solutions involving query ordering, but I couldn't see how that would affect us.

I wonder if this happens only when both an insert and an update occur on the same query?

adam.

Milton Ngan via observium wrote on 2022-10-28 10:03:

Try out some of the diagnostic steps here. Is it always the vlans_fdb table? You can probably turn off polling that data for now so polling keeps working.

https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlocks-handling.html

* At any time, issue https://dev.mysql.com/doc/refman/5.6/en/show-engine.html SHOW ENGINE INNODB STATUS to determine the cause of the most recent deadlock. That can help you to tune your application to avoid deadlocks.

* If frequent deadlock warnings cause concern, collect more extensive debugging information by enabling the https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks innodb_print_all_deadlocks variable. Information about each deadlock, not just the latest one, is recorded in the MySQL https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_error_log error log. Disable this option when you are finished debugging.

_____

From: Thomas Stather via observium mailto:observium@lists.observium.org observium@lists.observium.org Sent: Friday, October 28, 2022 12:53:01 AM To: observium@observium.org mailto:observium@observium.org Cc: Adam Armstrong; Thomas Stather Subject: [Observium] Re: Lots of errors "Deadlock found when trying to get

Hi

We have

207 devices (network, servers, ups etc.)

6709 ports

2841 sensors

The problem is still not fixed.

Best,

Thomas

Am 26.07.2022 um 23:49 schrieb Adam Armstrong via observium:

You might want to provide information on how big the installation is and what sort of devices are involved.

But I'd try upgrading to a newer distribution. Newer MySQL/MariaDB may behave better.

Adam.

-----Original Message----- From: observium mailto:observium-bounces@observium.org observium-bounces@observium.org On Behalf Of Thomas Stather via observium Sent: 26 July 2022 08:42 To: 'Observium' mailto:observium@observium.org observium@observium.org Cc: Thomas Stather mailto:tstather@mr.mpg.de tstather@mr.mpg.de Subject: [Observium] Lots of errors "Deadlock found when trying to get lock; try restarting transaction"

Hi

We are running Observium Professional r12071

OS: Ubuntu 18.04 LTS

MySQL: 5.7.38-0ubuntu0.18.04.1

In our /opt/observium/logs/db log, we are seeing a lot of these messages (approx. every minute).

---

[2022/07/26 09:23:54 +0200] poller.php(3889): Failed dbQuery (#1213 - Deadlock found when trying to get lock; try restarting transaction), Query: INSERT INTO `vlans_fdb` (`fdb_id`,`port_id`,`fdb_status`,`fdb_port`,`fdb_last_change`,`deleted`) VALUES ('2022403206','19917','learned','1','1658820234','0'),('2022403209','19917','learned','1','1658820234','0'),('2022374672','19917','learned','1','1658820234','0'),('2022406153','19917','learned','1','1658820234','0'),('2022438526','19917','learned','1','1658820234','0'),('2022403210','19917','learned','1','1658820234','0'),('2022403211','19917','learned','1','1658820234','0'),('2022424430','19917','learned','1','1658820234','0'),('2022389346','19917','learned','1','1658820234','0'),('2022417515','19917','learned','1','1658820234','0'),('2022403215','19917','learned','1','1658820234','0'),('2022424431','19917','learned','1','1658820234','0'),('2022374680','19917','learned','1','1658820234','0'),('2022428820','19917','learned','1','1658820234','0'),('2022417516','19917','learned','1','1658820234','0'),('2022626809','19917','learned','1','1658820234','0'),('2022403218','19917','learned','1','1658820234','0'),('2022013327','19917','learned','1','1658820234','0'),('2022440623','19917','learned','1','1658820234','0'),('2023195038','19917','learned','1','1658820234','0'),('2022417520','19917','learned','1','1658820234','0'),('2022417521','19917','learned','1','1658820234','0'),('2022417522','19917','learned','1','1658820234','0'),('2022417525','19917','learned','1','1658820234','0'),('2022389354','19917','learned','1','1658820234','0'),('2022634466','19917','learned','1','1658820234','0'),('2022389355','19917','learned','1','1658820234','0'),('2022374686','19917','learned','1','1658820234','0'),('2022318658','19917','learned','1','1658820234','0'),('2022403222','19917','learned','1','1658820234','0'),('2022389360','19917','learned','1','1658820234','0'),('2022334581','19917','learned','1','1658820234','0'),('2022318665','19917','learned','1','1658820234','0'),('2022535557','19917','learned','1','1658820234','0'),('2022633094','19917','learned','1','1658820234','0'),('2022403228','19917','learned','1','1658820234','0'),('2022507536','19917','learned','1','1658820234','0'),('2023391470','19917','learned','1','1658820234','0'),('2022440625','19917','learned','1','1658820234','0'),('2022442062','19917','learned','1','1658820234','0'),('2022318672','19917','learned','1','1658820234','0'),('2022352825','19917','learned','1','1658820234','0'),('2022448395','19917','learned','1','1658820234','0'),('1781846826','19917','learned','1','1658820234','0'),('2022440626','19917','learned','1','1658820234','0'),('2021364663','19917','learned','1','1658820234','0'),('2022621595','19917','learned','1','1658820234','0'),('2021996873','19917','learned','1','1658820234','0'),('1930092608','19917','learned','1','1658820234','0'),('2022403237','19917','learned','1','1658820234','0'),('2022389374','19917','learned','1','1658820234','0'),('2021923896','19917','learned','1','1658820234','0'),('2022406174','19917','learned','1','1658820234','0'),('2022281507','19917','learned','1','1658820234','0'),('2022370404','19917','learned','1','1658820234','0'),('2022132020','19917','learned','1','1658820234','0'),('2022370406','19917','learned','1','1658820234','0'),('2022403242','19917','learned','1','1658820234','0'),('2022442064','19917','learned','1','1658820234','0'),('2022160398','19917','learned','1','1658820234','0'),('2021963914','19917','learned','1','1658820234','0'),('2022370416','19917','learned','1','1658820234','0'),('2022370417','19917','learned','1','1658820234','0'),('2022428831','19917','learned','1','1658820234','0'),('2022352835','19917','learned','1','1658820234','0'),('2022417541','19917','learned','1','1658820234','0'),('2022403248','19917','learned','1','1658820234','0'),('2022406184','19917','learned','1','1658820234','0'),('2022403251','19917','learned','1','1658820234','0'),('2022403252','19917','learned','1','1658820234','0'),('2022248989','19917','learned','1','1658820234','0'),('2022220229','19917','learned','1','1658820234','0'),('2022438537','19917','learned','1','1658820234','0'),('2022417547','19917','learned','1','1658820234','0'),('2022403257','19917','learned','1','1658820234','0'),('2022403260','19917','learned','1','1658820234','0'),('2022403261','19917','learned','1','1658820234','0'),('2022403262','19917','learned','1','1658820234','0'),('2022403263','19917','learned','1','1658820234','0'),('2022403265','19917','learned','1','1658820234','0'),('2022374716','19917','learned','1','1658820234','0'),('2022344523','19917','learned','1','1658820234','0'),('2022403276','19917','learned','1','1658820234','0'),('2024336889','19917','learned','1','1658820234','0'),('2022036744','19917','learned','1','1658820234','0'),('2022352850','19917','learned','1','1658820234','0'),('2022463833','19917','learned','1','1658820234','0'),('2022389400','19917','learned','1','1658820234','0'),('2022535560','19917','learned','1','1658820234','0'),('2022406201','19917','learned','1','1658820234','0'),('2022403291','19917','learned','1','1658820234','0'),('2022374736','19917','learned','1','1658820234','0'),('2022295503','19917','learned','1','1658820234','0'),('2022295504','19917','learned','1','1658820234','0'),('2022885411','19917','learned','1','1658820234','0'),('2022417565','19917','learned','1','1658820234','0'),('2022403294','19917','learned','1','1658820234','0'),('2022389405','19917','learned','1','1658820234','0'),('2022403295','19917','learned','1','1658820234','0'),('2022352861','19917','learned','1','1658820234','0'),('2022389406','19917','learned','1','1658820234','0'),('2022403296','19917','learned','1','1658820234','0'),('2022220258','19917','learned','1','1658820234','0'),('2022403297','19917','learned','1','1658820234','0'),('2022318712','19917','learned','1','1658820234','0'),('2022185898','19917','learned','1','1658820234','0'),('2022417566','19917','learned','1','1658820234','0'),('2022403299','19917','learned','1','1658820234','0'),('2022431524','19917','learned','1','1658820234','0'),('2022318715','19917','learned','1','1658820234','0'),('2022403303','19917','learned','1','1658820234','0'),('2022389409','19917','learned','1','1658820234','0'),('2022406211','19917','learned','1','1658820234','0'),('2022056535','19917','learned','1','1658820234','0'),('2022389412','19917','learned','1','1658820234','0'),('2022417570','19917','learned','1','1658820234','0'),('2022443725','19917','learned','1','1658820234','0'),('2021925685','19917','learned','1','1658820234','0'),('2022440635','19917','learned','1','1658820234','0'),('2023222617','19917','learned','1','1658820234','0'),('2022428840','19917','learned','1','1658820234','0'),('2022403307','19917','learned','1','1658820234','0'),('2022406215','19917','learned','1','1658820234','0'),('2022458785','19917','learned','1','1658820234','0'),('2022406216','19917','learned','1','1658820234','0'),('2022449600','19917','learned','1','1658820234','0'),('2022445047','19917','learned','1','1658820234','0'),('2023938925','19917','learned','1','1658820234','0'),('2022389418','19917','learned','1','1658820234','0'),('2022451483','19917','learned','1','1658820234','0'),('2022417575','19917','learned','1','1658820234','0'),('2022440636','19917','learned','1','1658820234','0'),('2022370471','19917','learned','1','1658820234','0'),('2022417583','19917','learned','1','1658820234','0'),('2022464789','19917','learned','1','1658820234','0'),('2022287606','19917','learned','1','1658820234','0'),('2022352884','19917','learned','1','1658820234','0'),('2022424457','19917','learned','1','1658820234','0'),('2022417585','19917','learned','1','1658820234','0'),('2022424462','19917','learned','1','1658820234','0'),('2022403324','19917','learned','1','1658820234','0'),('2022406247','19917','learned','1','1658820234','0'),('2022117804','19917','learned','1','1658820234','0'),('2022464791','19917','learned','1','1658820234','0'),('2022403327','19917','learned','1','1658820234','0'),('2022406249','19917','learned','1','1658820234','0'),('2022403329','19917','learned','1','1658820234','0'),('2022448403','19917','learned','1','1658820234','0'),('2022417595','19917','learned','1','1658820234','0'),('2022403333','19917','learned','1','1658820234','0'),('2022445053','19917','learned','1','1658820234','0'),('2022389440','19917','learned','1','1658820234','0'),('2024143217','19917','learned','1','1658820234','0') ON DUPLICATE KEY UPDATE `fdb_id`=VALUES(`fdb_id`),`port_id`=VALUES(`port_id`),`fdb_status`=VALUES(`fdb_status`),`fdb_port`=VALUES(`fdb_port`),`fdb_last_change`=VALUES(`fdb_last_change`),`deleted`=VALUES(`deleted`)

---

Does somebody have a clue how this can be fixed?

Best,

Thomas

-- Thomas Stather IT Services

VCP4, VCP6-DCV, VCP6.5-DCV

Tel: +49 6221 486 628 Fax: +49 6221 486 561

Bevor Sie diese Mail ausdrucken, prüfen Sie bitte ob dies wirklich nötig ist. Umweltschutz geht uns alle an!

---

observium mailing list observium@observium.org mailto:observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium

---

observium mailing list observium@observium.org mailto:observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium

You should probably just disable the fdb module. It's a very heavy module, and it seems recent changes have made it behave badly with multiple pollers trying to update the fdb table.

adam.

Thomas Stather via observium wrote on 2022-11-07 09:10:

Hi

I had to revert back to MySQL 5.7 because after the migration to MariaDB 10.9 there were gaps in all graphs and the db.log grew even faster.

The output of SHOW ENGINE INNODB STATUS is

---

| InnoDB |      |

2022-11-07 10:09:12 0x7f4f36c15700 INNODB MONITOR OUTPUT

Per second averages calculated from the last 46 seconds

BACKGROUND THREAD

srv_master_thread loops: 124709 srv_active, 0 srv_shutdown, 113 srv_idle srv_master_thread log flush and writes: 124822

---

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 47713509 OS WAIT ARRAY INFO: signal count 28518767 RW-shared spins 0, rounds 42974568, OS waits 15164860 RW-excl spins 0, rounds 772630515, OS waits 25383658 RW-sx spins 145522, rounds 3008088, OS waits 73966 Spin rounds per wait: 42974568.00 RW-shared, 772630515.00 RW-excl, 20.67 RW-sx

---

LATEST DETECTED DEADLOCK

2022-11-07 10:02:02 0x7f4f36ffd700 *** (1) TRANSACTION: TRANSACTION 10874120207, ACTIVE 2 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2 MySQL thread id 182879, OS thread handle 139978201470720, query id 12635343 localhost observium update INSERT INTO `vlans_fdb` (`fdb_id`,`port_id`,`fdb_status`,`fdb_port`,`fdb_last_change`,`deleted`) VALUES ('2025238686','20594','learned','1','1667811720','0'),('2025237413','20594','learned','1','1667811720','0'),('2013955508','20594','learned','1','1667811720','0'),('2013923178','20594','learned','1','1667811720','0'),('2013955509','20594','learned','1','1667811720','0'),('2025274310','20594','learned','1','1667811720','0'),('2025238687','20594','learned','1','1667811720','0'),('2025237415','20594','learned','1','1667811720','0'),('2013955513','20594','learned','1','1667811720','0'),('2025237417','20594','learned','1','1667811720','0'),('2025237810','20594','learned','1','1667811720','0'),('2025237811','20594','learned','1','1667811720','0'),('2025366168','20594','learned','1','1667811720','0'),('2013946482','20594','learned','1','1667811720','0'),('2025237555','20594','learned','1','1667811720','0'),('201 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 445 page no 444 n bits 232 index PRIMARY of table `observium`.`vlans_fdb` trx id 10874120207 lock_mode X locks rec but not gap waiting Record lock, heap no 119 *** (2) TRANSACTION: TRANSACTION 10874117802, ACTIVE 50 sec fetching rows mysql tables in use 1, locked 1 13881 lock struct(s), heap size 1564880, 2997140 row lock(s), undo log entries 26489 MySQL thread id 182763, OS thread handle 139978201880320, query id 12628154 localhost observium updating UPDATE `vlans_fdb` set `fdb_last_change` ='1667811670',`deleted` ='1' WHERE  `fdb_id` IN ('1132864756','1132865043','1132865062','1631027935','1631028133','1631028144','1888392483','1915282828','1923857809','1930092869','1961020401','1967185692','1973487378','1979780642','1983023977','1985730343','1996508399','1998944659','2002767001','2010955906','2011067506','2011299640','2012864129','2017460185','2020769901','2021848541','2021886167','2021902147','2021975479','2021975515','2021986152','2021986155','2021986156','2022000628','2022000657','2022000680','2022028046','2022028049','2022028079','2022051129','2022051139','2022064486','2022140079','2022171797','2022201998','2022217108','2022217117','2022217120','2022235472','2022235512','2022248245','2022248259','2022276664','2022288724','2022288751','2022299179','2022299181','2022307491','2022307511','2022319006','2022329260','2022329268','2022329300','20223293 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 445 page no 444 n bits 232 index PRIMARY of table `observium`.`vlans_fdb` trx id 10874117802 lock_mode X Record lock, heap no 1 Record lock, heap no 2 Record lock, heap no 6 Record lock, heap no 7 Record lock, heap no 8 Record lock, heap no 9 Record lock, heap no 10 Record lock, heap no 11 Record lock, heap no 12 Record lock, heap no 13 Record lock, heap no 14 Record lock, heap no 15 Record lock, heap no 16 Record lock, heap no 17 Record lock, heap no 18 Record lock, heap no 19 Record lock, heap no 21 Record lock, heap no 22 Record lock, heap no 27 Record lock, heap no 28 Record lock, heap no 29 Record lock, heap no 31 Record lock, heap no 33 Record lock, heap no 34 Record lock, heap no 37 Record lock, heap no 39 Record lock, heap no 40 Record lock, heap no 41 Record lock, heap no 42 Record lock, heap no 43 Record lock, heap no 44 Record lock, heap no 45 Record lock, heap no 46 Record lock, heap no 47 Record lock, heap no 48 Record lock, heap no 49 Record lock, heap no 50 Record lock, heap no 51 Record lock, heap no 52 Record lock, heap no 53 Record lock, heap no 54 Record lock, heap no 55 Record lock, heap no 56 Record lock, heap no 57 Record lock, heap no 58 Record lock, heap no 59 Record lock, heap no 60 Record lock, heap no 62 Record lock, heap no 67 Record lock, heap no 68 Record lock, heap no 69 Record lock, heap no 70 Record lock, heap no 72 Record lock, heap no 73 Record lock, heap no 75 Record lock, heap no 76 Record lock, heap no 77 Record lock, heap no 79 Record lock, heap no 81 Record lock, heap no 82 Record lock, heap no 84 Record lock, heap no 86 Record lock, heap no 88 Record lock, heap no 91 Record lock, heap no 92 Record lock, heap no 93 Record lock, heap no 94 Record lock, heap no 95 Record lock, heap no 96 Record lock, heap no 97 Record lock, heap no 98 Record lock, heap no 99 Record lock, heap no 100 Record lock, heap no 101 Record lock, heap no 102 Record lock, heap no 103 Record lock, heap no 104 Record lock, heap no 105 Record lock, heap no 106 Record lock, heap no 107 Record lock, heap no 108 Record lock, heap no 109 Record lock, heap no 110 Record lock, heap no 111 Record lock, heap no 112 Record lock, heap no 113 Record lock, heap no 114 Record lock, heap no 116 Record lock, heap no 117 Record lock, heap no 118 Record lock, heap no 119 Record lock, heap no 122 Record lock, heap no 123 Record lock, heap no 125 Record lock, heap no 126 Record lock, heap no 127 Record lock, heap no 128 Record lock, heap no 129 Record lock, heap no 130 Record lock, heap no 132 Record lock, heap no 134 Record lock, heap no 135 Record lock, heap no 136 Record lock, heap no 137 Record lock, heap no 138 Record lock, heap no 139 Record lock, heap no 140 Record lock, heap no 141 Record lock, heap no 143 Record lock, heap no 144 Record lock, heap no 146 Record lock, heap no 148 Record lock, heap no 149 Record lock, heap no 150 Record lock, heap no 151 Record lock, heap no 152 Record lock, heap no 154 Record lock, heap no 155 Record lock, heap no 156 Record lock, heap no 157 Record lock, heap no 158 Record lock, heap no 159 Record lock, heap no 160 Record lock, heap no 161 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 445 page no 37010 n bits 304 index PRIMARY of table `observium`.`vlans_fdb` trx id 10874117802 lock_mode X waiting Record lock, heap no 228 PHYSICAL RECORD: n_fields 11; compact format; info bits 0  0: len 8; hex 0000000078b6aba5; asc     x   ;;  1: len 6; hex 00028825e80f; asc    %  ;;  2: len 7; hex 4b0001802f0168; asc K   / h;;  3: len 4; hex 800001d3; asc     ;;  4: len 4; hex 80000001; asc     ;;  5: len 4; hex 80005072; asc   Pr;;  6: len 12; hex 303035303536386233326366; asc 0050568b32cf;;  7: len 7; hex 6c6561726e6564; asc learned;;  8: len 1; hex 31; asc 1;;  9: len 4; hex 6368c988; asc ch  ;;  10: len 1; hex 80; asc  ;;

*** WE ROLL BACK TRANSACTION (1)

TRANSACTIONS

Trx id counter 10874142190 Purge done for trx's n:o < 10874142190 undo n:o < 0 state: running but idle History list length 24 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421454604985288, not started 0 lock struct(s), heap size 1136, 0 row lock(s)

---

FILE I/O

I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,  ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 606216106 OS file reads, 233093249 OS file writes, 12204091 OS fsyncs 2.87 reads/s, 16384 avg bytes/read, 7.83 writes/s, 1.35 fsyncs/s

---

INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: size 1, free list len 2049, seg size 2051, 1245230 merges merged operations:  insert 2651601, delete mark 2727628, delete 1803645 discarded operations:  insert 0, delete mark 0, delete 0 Hash table size 34673, node heap has 7 buffer(s) Hash table size 34673, node heap has 5 buffer(s) Hash table size 34673, node heap has 8 buffer(s) Hash table size 34673, node heap has 7 buffer(s) Hash table size 34673, node heap has 589 buffer(s) Hash table size 34673, node heap has 1 buffer(s) Hash table size 34673, node heap has 1 buffer(s) Hash table size 34673, node heap has 12 buffer(s) 111.89 hash searches/s, 379.14 non-hash searches/s

---

LOG

Log sequence number 16121562031966 Log flushed up to   16121562031966 Pages flushed up to 16121562031966 Last checkpoint at  16121562031957 0 pending log flushes, 0 pending chkp writes 2322618 log i/o's done, 0.80 log i/o's/second

---

BUFFER POOL AND MEMORY

Total large memory allocated 137428992 Dictionary memory allocated 926486 Buffer pool size   8191 Free buffers       1024 Database pages     6537 Old database pages 2393 Modified db pages  0 Pending reads      0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 75025021, not young 63229816352 0.00 youngs/s, 0.00 non-youngs/s Pages read 606227143, created 2007805, written 224193059 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 998 / 1000, young-making rate 0 / 1000 not 9 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 6537, unzip_LRU len: 0 I/O sum[414]:cur[0], unzip sum[0]:cur[0]

---

ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=27475, Main thread ID=139979239032576, state: sleeping Number of rows inserted 198328, updated 761940298, deleted 192787, read 87443142380

1.11 inserts/s, 2.67 updates/s, 0.09 deletes/s, 1361.95 reads/s

END OF INNODB MONITOR OUTPUT

---

Am 04.11.2022 um 12:52 schrieb Adam Armstrong via observium:

...

Do you have the output of “SHOW ENGINE INNODB STATUS”?

The docs say you can also get more information if you enable innodb print all deadlocks and then look in the mysql.err after a deadlock has occurred.

Adam.

*From:*Thomas Stather via observium observium@lists.observium.org *Sent:* 04 November 2022 11:19 *To:* observium@lists.observium.org *Cc:* Thomas Stather tstather@mr.mpg.de *Subject:* [Observium] Re: Lots of errors "Deadlock found when trying to get

Hi

This happens on INSERT and UPDATE queries since today i had

[2022/11/04 12:05:53 +0100] poller.php(3430): Failed dbQuery (#1205 - Lock wait timeout exceeded; try restarting transaction), Query: UPDATE `vlans_fdb` set `fdb_last_change` ='1667559900',`deleted` ='1' WHERE  `fdb_id` IN ('20...... <lots of output in the log>

I just migrated from MySQL 5.7 to MariaDB 10.9 but this didn't solve the issue

Best,

Thomas

Am 04.11.2022 um 02:20 schrieb Adam Armstrong via observium:

This seems to be an issue with the number of updates we do on
large vlan installations. It's not entirely clear to me why it's
happening, so not really sure how to fix it.

MySQL behaves oddly at times. We do an insert with a clause to
update when primary key exists, which might well be why it's
behaving oddly. I think if we tried to remove that and do it
another way, it would be much slower.

I saw some solutions involving query ordering, but I couldn't see
how that would affect us.

I wonder if this happens only when both an insert and an update
occur on the same query?

adam.

Milton Ngan via observium wrote on 2022-10-28 10:03:

    Try out some of the diagnostic steps here. Is it always the
    vlans_fdb table? You can probably turn off polling that data
    for now so polling keeps working.

    https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlocks-handling.html

    ·At any time, issue |SHOW ENGINE INNODB STATUS|
    <https://dev.mysql.com/doc/refman/5.6/en/show-engine.html> to
    determine the cause of the most recent deadlock. That can
    help you to tune your application to avoid deadlocks.

    ·If frequent deadlock warnings cause concern, collect more
    extensive debugging information by enabling the
    |innodb_print_all_deadlocks|
    <https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks> variable.
    Information about each deadlock, not just the latest one, is
    recorded in the MySQL error log
    <https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_error_log>.
    Disable this option when you are finished debugging.

    ------------------------------------------------------------------------

    *From:*Thomas Stather via observium
    <observium@lists.observium.org>
    <mailto:observium@lists.observium.org>
    *Sent:* Friday, October 28, 2022 12:53:01 AM
    *To:* observium@observium.org
    *Cc:* Adam Armstrong; Thomas Stather
    *Subject:* [Observium] Re: Lots of errors "Deadlock found
    when trying to get

    Hi


    We have


    207 devices (network, servers, ups etc.)

    6709 ports

    2841 sensors


    The problem is still not fixed.


    Best,

    Thomas

    Am 26.07.2022 um 23:49 schrieb Adam Armstrong via observium:

    > You might want to provide information on how big the
    installation is and what sort of devices are involved.
    >
    > But I'd try upgrading to a newer distribution. Newer
    MySQL/MariaDB may behave better.
    >
    > Adam.
    >
    > -----Original Message-----
    > From: observium <observium-bounces@observium.org>
    <mailto:observium-bounces@observium.org> On Behalf Of Thomas
    Stather via observium
    > Sent: 26 July 2022 08:42
    > To: 'Observium' <observium@observium.org>
    <mailto:observium@observium.org>
    > Cc: Thomas Stather <tstather@mr.mpg.de>
    <mailto:tstather@mr.mpg.de>
    > Subject: [Observium] Lots of errors "Deadlock found when
    trying to get lock; try restarting transaction"
    >
    > Hi
    >
    > We are running Observium Professional r12071
    >
    > OS: Ubuntu 18.04 LTS
    >
    > MySQL: 5.7.38-0ubuntu0.18.04.1
    >
    >
    > In our /opt/observium/logs/db log, we are seeing a lot of
    these messages (approx. every minute).
    >
    > ---
    >
    > [2022/07/26 09:23:54 +0200] poller.php(3889): Failed
    dbQuery (#1213 - Deadlock found when trying to get lock; try
    restarting transaction),
    > Query: INSERT INTO `vlans_fdb`
    >
    (`fdb_id`,`port_id`,`fdb_status`,`fdb_port`,`fdb_last_change`,`deleted`)
    > VALUES
    >
    ('2022403206','19917','learned','1','1658820234','0'),('2022403209','19917','learned','1','1658820234','0'),('2022374672','19917','learned','1','1658820234','0'),('2022406153','19917','learned','1','1658820234','0'),('2022438526','19917','learned','1','1658820234','0'),('2022403210','19917','learned','1','1658820234','0'),('2022403211','19917','learned','1','1658820234','0'),('2022424430','19917','learned','1','1658820234','0'),('2022389346','19917','learned','1','1658820234','0'),('2022417515','19917','learned','1','1658820234','0'),('2022403215','19917','learned','1','1658820234','0'),('2022424431','19917','learned','1','1658820234','0'),('2022374680','19917','learned','1','1658820234','0'),('2022428820','19917','learned','1','1658820234','0'),('2022417516','19917','learned','1','1658820234','0'),('2022626809','19917','learned','1','1658820234','0'),('2022403218','19917','learned','1','1658820234','0'),('2022013327','19917','learned','1','1658820234','0'),('2022440623','19917','learned','1','1658820234','0'),('2023195038','19917','learned','1','1658820234','0'),('2022417520','19917','learned','1','1658820234','0'),('2022417521','19917','learned','1','1658820234','0'),('2022417522','19917','learned','1','1658820234','0'),('2022417525','19917','learned','1','1658820234','0'),('2022389354','19917','learned','1','1658820234','0'),('2022634466','19917','learned','1','1658820234','0'),('2022389355','19917','learned','1','1658820234','0'),('2022374686','19917','learned','1','1658820234','0'),('2022318658','19917','learned','1','1658820234','0'),('2022403222','19917','learned','1','1658820234','0'),('2022389360','19917','learned','1','1658820234','0'),('2022334581','19917','learned','1','1658820234','0'),('2022318665','19917','learned','1','1658820234','0'),('2022535557','19917','learned','1','1658820234','0'),('2022633094','19917','learned','1','1658820234','0'),('2022403228','19917','learned','1','1658820234','0'),('2022507536','19917','learned','1','1658820234','0'),('2023391470','19917','learned','1','1658820234','0'),('2022440625','19917','learned','1','1658820234','0'),('2022442062','19917','learned','1','1658820234','0'),('2022318672','19917','learned','1','1658820234','0'),('2022352825','19917','learned','1','1658820234','0'),('2022448395','19917','learned','1','1658820234','0'),('1781846826','19917','learned','1','1658820234','0'),('2022440626','19917','learned','1','1658820234','0'),('2021364663','19917','learned','1','1658820234','0'),('2022621595','19917','learned','1','1658820234','0'),('2021996873','19917','learned','1','1658820234','0'),('1930092608','19917','learned','1','1658820234','0'),('2022403237','19917','learned','1','1658820234','0'),('2022389374','19917','learned','1','1658820234','0'),('2021923896','19917','learned','1','1658820234','0'),('2022406174','19917','learned','1','1658820234','0'),('2022281507','19917','learned','1','1658820234','0'),('2022370404','19917','learned','1','1658820234','0'),('2022132020','19917','learned','1','1658820234','0'),('2022370406','19917','learned','1','1658820234','0'),('2022403242','19917','learned','1','1658820234','0'),('2022442064','19917','learned','1','1658820234','0'),('2022160398','19917','learned','1','1658820234','0'),('2021963914','19917','learned','1','1658820234','0'),('2022370416','19917','learned','1','1658820234','0'),('2022370417','19917','learned','1','1658820234','0'),('2022428831','19917','learned','1','1658820234','0'),('2022352835','19917','learned','1','1658820234','0'),('2022417541','19917','learned','1','1658820234','0'),('2022403248','19917','learned','1','1658820234','0'),('2022406184','19917','learned','1','1658820234','0'),('2022403251','19917','learned','1','1658820234','0'),('2022403252','19917','learned','1','1658820234','0'),('2022248989','19917','learned','1','1658820234','0'),('2022220229','19917','learned','1','1658820234','0'),('2022438537','19917','learned','1','1658820234','0'),('2022417547','19917','learned','1','1658820234','0'),('2022403257','19917','learned','1','1658820234','0'),('2022403260','19917','learned','1','1658820234','0'),('2022403261','19917','learned','1','1658820234','0'),('2022403262','19917','learned','1','1658820234','0'),('2022403263','19917','learned','1','1658820234','0'),('2022403265','19917','learned','1','1658820234','0'),('2022374716','19917','learned','1','1658820234','0'),('2022344523','19917','learned','1','1658820234','0'),('2022403276','19917','learned','1','1658820234','0'),('2024336889','19917','learned','1','1658820234','0'),('2022036744','19917','learned','1','1658820234','0'),('2022352850','19917','learned','1','1658820234','0'),('2022463833','19917','learned','1','1658820234','0'),('2022389400','19917','learned','1','1658820234','0'),('2022535560','19917','learned','1','1658820234','0'),('2022406201','19917','learned','1','1658820234','0'),('2022403291','19917','learned','1','1658820234','0'),('2022374736','19917','learned','1','1658820234','0'),('2022295503','19917','learned','1','1658820234','0'),('2022295504','19917','learned','1','1658820234','0'),('2022885411','19917','learned','1','1658820234','0'),('2022417565','19917','learned','1','1658820234','0'),('2022403294','19917','learned','1','1658820234','0'),('2022389405','19917','learned','1','1658820234','0'),('2022403295','19917','learned','1','1658820234','0'),('2022352861','19917','learned','1','1658820234','0'),('2022389406','19917','learned','1','1658820234','0'),('2022403296','19917','learned','1','1658820234','0'),('2022220258','19917','learned','1','1658820234','0'),('2022403297','19917','learned','1','1658820234','0'),('2022318712','19917','learned','1','1658820234','0'),('2022185898','19917','learned','1','1658820234','0'),('2022417566','19917','learned','1','1658820234','0'),('2022403299','19917','learned','1','1658820234','0'),('2022431524','19917','learned','1','1658820234','0'),('2022318715','19917','learned','1','1658820234','0'),('2022403303','19917','learned','1','1658820234','0'),('2022389409','19917','learned','1','1658820234','0'),('2022406211','19917','learned','1','1658820234','0'),('2022056535','19917','learned','1','1658820234','0'),('2022389412','19917','learned','1','1658820234','0'),('2022417570','19917','learned','1','1658820234','0'),('2022443725','19917','learned','1','1658820234','0'),('2021925685','19917','learned','1','1658820234','0'),('2022440635','19917','learned','1','1658820234','0'),('2023222617','19917','learned','1','1658820234','0'),('2022428840','19917','learned','1','1658820234','0'),('2022403307','19917','learned','1','1658820234','0'),('2022406215','19917','learned','1','1658820234','0'),('2022458785','19917','learned','1','1658820234','0'),('2022406216','19917','learned','1','1658820234','0'),('2022449600','19917','learned','1','1658820234','0'),('2022445047','19917','learned','1','1658820234','0'),('2023938925','19917','learned','1','1658820234','0'),('2022389418','19917','learned','1','1658820234','0'),('2022451483','19917','learned','1','1658820234','0'),('2022417575','19917','learned','1','1658820234','0'),('2022440636','19917','learned','1','1658820234','0'),('2022370471','19917','learned','1','1658820234','0'),('2022417583','19917','learned','1','1658820234','0'),('2022464789','19917','learned','1','1658820234','0'),('2022287606','19917','learned','1','1658820234','0'),('2022352884','19917','learned','1','1658820234','0'),('2022424457','19917','learned','1','1658820234','0'),('2022417585','19917','learned','1','1658820234','0'),('2022424462','19917','learned','1','1658820234','0'),('2022403324','19917','learned','1','1658820234','0'),('2022406247','19917','learned','1','1658820234','0'),('2022117804','19917','learned','1','1658820234','0'),('2022464791','19917','learned','1','1658820234','0'),('2022403327','19917','learned','1','1658820234','0'),('2022406249','19917','learned','1','1658820234','0'),('2022403329','19917','learned','1','1658820234','0'),('2022448403','19917','learned','1','1658820234','0'),('2022417595','19917','learned','1','1658820234','0'),('2022403333','19917','learned','1','1658820234','0'),('2022445053','19917','learned','1','1658820234','0'),('2022389440','19917','learned','1','1658820234','0'),('2024143217','19917','learned','1','1658820234','0')
    > ON DUPLICATE KEY UPDATE
    >
    `fdb_id`=VALUES(`fdb_id`),`port_id`=VALUES(`port_id`),`fdb_status`=VALUES(`fdb_status`),`fdb_port`=VALUES(`fdb_port`),`fdb_last_change`=VALUES(`fdb_last_change`),`deleted`=VALUES(`deleted`)
    >
    > ---
    >
    >
    > Does somebody have a clue how this can be fixed?
    >
    > Best,
    >
    > Thomas
    >
    > --
    > Thomas Stather
    > IT Services
    >
    > VCP4, VCP6-DCV, VCP6.5-DCV
    >
    > Tel: +49 6221 486 628
    > Fax: +49 6221 486 561
    >
    > Bevor Sie diese Mail ausdrucken, prüfen Sie bitte ob dies
    wirklich nötig ist. Umweltschutz geht uns alle an!
    >
    > _______________________________________________
    > observium mailing list
    > observium@observium.org
    > http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
    >
    > _______________________________________________
    > observium mailing list
    > observium@observium.org
    > http://postman.memetic.org/cgi-bin/mailman/listinfo/observium

    -- 
    Thomas Stather
    IT Services

    VCP4, VCP6-DCV, VCP6.5-DCV

    Room: U1.509
    Tel: +49 6221 486 628
    Fax: +49 6221 486 561

    Bevor Sie diese Mail ausdrucken, prüfen Sie bitte ob dies
    wirklich nötig ist. Umweltschutz geht uns alle an!

    _______________________________________________
    observium mailing list -- observium@lists.observium.org
    To unsubscribe send an email to
    observium-leave@lists.observium.org




    _______________________________________________

    observium mailing list --observium@lists.observium.org

    To unsubscribe send an email toobservium-leave@lists.observium.org

-- 

Sent from Postbox
<https://www.postbox-inc.com/?utm_source=email&utm_medium=siglink&utm_campaign=reach>



_______________________________________________

observium mailing list --observium@lists.observium.org

To unsubscribe send an email toobservium-leave@lists.observium.org

-- Thomas Stather IT Services VCP4, VCP6-DCV, VCP6.5-DCV Room: U1.509 Tel: +49 6221 486 628 Fax: +49 6221 486 561 Bevor Sie diese Mail ausdrucken, prüfen Sie bitte ob dies wirklich nötig ist. Umweltschutz geht uns alle an!

---

observium mailing list --observium@lists.observium.org To unsubscribe send an email toobservium-leave@lists.observium.org

-- Thomas Stather IT Services

VCP4, VCP6-DCV, VCP6.5-DCV

Room: U1.509 Tel: +49 6221 486 628 Fax: +49 6221 486 561

Bevor Sie diese Mail ausdrucken, prüfen Sie bitte ob dies wirklich nötig ist. Umweltschutz geht uns alle an!

---

observium mailing list -- observium@lists.observium.org To unsubscribe send an email to observium-leave@lists.observium.org