[Observium] Re: Lots of errors "Deadlock found when trying to get

Thu, 03 Nov 2022 18:22:01 -0700

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 <[email protected]>
*Sent:* Friday, October 28, 2022 12:53:01 AM
*To:* [email protected]
*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 <[email protected]> On Behalf Of Thomas Stather 
via observium
> Sent: 26 July 2022 08:42
> To: 'Observium' <[email protected]>
> Cc: Thomas Stather <[email protected]>
> 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
> [email protected]
> http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
>
> _______________________________________________
> observium mailing list
> [email protected]
> 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 -- [email protected]
To unsubscribe send an email to [email protected]


_______________________________________________
observium mailing list -- [email protected]
To unsubscribe send an email to [email protected]


--

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

_______________________________________________
observium mailing list -- [email protected]
To unsubscribe send an email to [email protected]






















					Reply via email to