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 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 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