> One more idea - we have seen similar (but different tables) deadlocks > when a background (cron) job we ship by default attempts to delete old > reports. Can you check if there is any cronjob or any other process > doing some management of facts? Even deleting lot of data can block > all updates for a long time (minutes to hours). Perhaps try to disable > all foreman jobs and re-test. >
I have tried this to no avail. However, I think the culprit of a problem is in very slow DELETE MySQL query, which is apparently happens even for absolutely new and freshly-discovered systems as well already discovered ones. 2017-09-28 13:09:49 c75f5c40 [sql] [D] SQL (50843.2ms) DELETE FROM `fact_values` WHERE `fact_values`.`id` IN Please see these gists I've recorded with SQL debug enabled. I have a ton of hosts doing exactly the same thing - try to register, mysql delete expires (it takes up to 50 sec as you can see), some rollback happens and expires again. And so on and so forth until systems register one by one. This results in many empty or duplicate entries even for a small batch of systems coming online at the same time. https://gist.github.com/anonymous/a721e220d82f5160450e483b8776489d The above examples are taken from a single Foreman instance running against a regular (non-Galera) MySQL DB, so at least I can say that the fact that I had several Foreman instances behind a load-balancer talking to Galera-replicated MySQL has nothing to do with this behavior. The only difference is that in Galera-enabled DB, expiration errors are replaced with deadlock error, which makes total sense - if delete operation takes almost a minute, no wonder it results in some rows being locked. As load increases (more systems register at the same time), more and more such errors are happening, so I believe a proper way to deal with this is optimize MySQL query first and the go from there. Would you agree? -- You received this message because you are subscribed to the Google Groups "Foreman users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/foreman-users. For more options, visit https://groups.google.com/d/optout.
