Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

Try to change to :validate => true but beware there might be dragons. I do not remember why we set this. LZ On Thu, Oct 26, 2017 at 6:20 PM, 'Konstantin Orekhov' via Foreman users < foreman-users@googlegroups.com> wrote: > Ok. Is there anything I could do now to workaround this? The only thing

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

Ok. Is there anything I could do now to workaround this? The only thing worked for me so far was to periodically go through discovered hosts and remove duplicate entries. Thanks! Konstantin. > On Oct 26, 2017, at 07:18, Lukas Zapletal wrote: > > Ok this confirms it.

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

Ok this confirms it. http://projects.theforeman.org/issues/21479 we will fix later. We don't have an unique index on DB level, just in Rails level and a second NIC with same MAC can sneak in. The relevant code in core is: validate :mac_uniqueness, :if => Proc.new { |nic| nic.managed? &&

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

> > Please use foreman-rake (I assume this is a packaged .deb install). > > This is CentOS7 install and foreman-rake did work. Here's the result: [root@spc01 ~]# cd ~foreman [root@spc01 foreman]# foreman-rake console Successfully encrypted field for Setting::Auth oauth_consumer_key

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

On Mon, Oct 23, 2017 at 05:50:52PM -0700, 'Konstantin Orekhov' via Foreman users wrote: > [root@spc02 foreman]# rake --trace console Please use foreman-rake (I assume this is a packaged .deb install). -- Michael Moll -- You received this message because you are subscribed to the Google

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

Hmm is this Debian? Should work. LLZ On Tue, Oct 24, 2017 at 2:50 AM, 'Konstantin Orekhov' via Foreman users wrote: > >> OK, thanks, Lukas! As soon as I get that duplicate entries show up again, >> I'll run above and provide a result here. After patching for

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

> In the console with both records presnet do something like: > > ::Nic::Managed.where(:mac => "MA:CA:DDRESS::", :primary => true) > > OK, thanks, Lukas! As soon as I get that duplicate entries show up again, I'll run above and provide a result here. After patching for MySQL query issue, I

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

Ideally I would like to see DEBUG and SQL logs for that transactions, but thats flooding your production server. In the console with both records presnet do something like: ::Nic::Managed.where(:mac => "MA:CA:DDRESS::", :primary => true) On Wed, Oct 11, 2017 at 9:08 PM, 'Konstantin Orekhov' via

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

Oh now I understand, looks like a new bug. I am not able to reproduce with FDI 3.4.1 and develop Foreman. The code responsible for finding existing record is here: https://github.com/theforeman/foreman_discovery/blob/develop/app/models/host/discovered.rb#L53-L62 Theoretically if you changed

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

> > Name has already been taken - this usually means that host (either > discovered or managed or unmanaged) of that name "macX" already > exist. Same mac address? You can change easily how discovered hosts are > being named, by default it is "mac" + MAC address, you can change that

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

Hey I did not see any 422 error before this transaction so I think this is it. > Although I did not see any long MySQL queries, the whole transaction still > took ~11 seconds to complete for some reason: > Name has already been taken - this usually means that host (either discovered or managed

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

> please use git to find out which branches it landed in, I believe the > MySQL facter patch is 1.15+ only. > Yes, I already found that and am planning on an upgrade in our lab instance. BTW, even after applying a patch (on 1.14), which helped tremendously, from time to time I still get some

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

Hello, please use git to find out which branches it landed in, I believe the MySQL facter patch is 1.15+ only. For 1.15.5 you need to talk with release engineer of this version which is Daniel, if the changes are small enough I see no reason not to include them. I think it's too late for 1.15.5

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

I've not seen that, no - I've CC'd someone who might know ;) Greg On Thu, 2017-10-05 at 18:35 -0700, 'Konstantin Orekhov' via Foreman users wrote: > > Let us know next week if this helped. I highly suggest upgrade to > > 1.15, it is a very solid release. > > > Are there any performance

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

> Let us know next week if this helped. I highly suggest upgrade to > 1.15, it is a very solid release. > Are there any performance improvements for Smart-Proxy in 1.15, BTW? Lately, in one of my busiest locations, we've started seeing a strange issue when SmP stops responding on 8443 for

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

> > Agreed, a proper place to hook it would be ideal, I'm just throwing > ideas out that might help in the short term. > Sure, would be a nice thing to have to start from. -- You received this message because you are subscribed to the Google Groups "Foreman users" group. To unsubscribe

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

> Let us know next week if this helped. I highly suggest upgrade to > 1.15, it is a very solid release. > > Is this patch for MySQL a part of 1.15? As you suggested, I've taken it from develop branch, so assumed it is not released yet. Plus, there are other 2 things that worry me: -

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

Let us know next week if this helped. I highly suggest upgrade to 1.15, it is a very solid release. LZ On Wed, Oct 4, 2017 at 11:04 PM, 'Konstantin Orekhov' via Foreman users wrote: > >> See the comment there, do you have this in your instance? If not git >>

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

On Tue, 2017-10-03 at 16:12 -0700, 'Konstantin Orekhov' via Foreman users wrote: > > As Lukas says, a full refactor may well happen, and we'd love input > > on that as we go forward. > > Any of you, guys, going to PuppetConf this year? If so, can we meet > and have a discussion on this maybe? I

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

> See the comment there, do you have this in your instance? If not git > blame the commit and apply it. You have some older version I assume. > Yes, I'm running several 1.14.1 and 1.14.3 instances/clusters. Both had the same issue with deadlocks. I've updated 2 of them with above patch and

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

Ok I can see there is a subselect, these are sometimes painful particularly for MySQL. We fixed that already, see in fact_importer.rb (this is develop branch): def delete_removed_facts ActiveSupport::Notifications.instrument "fact_importer_deleted.foreman", :host_id => host.id, :host_name

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

> This is absolutely true. We had, at one time, considered adding a state > machine (or similar) to Foreman, so that such things (as well as boot > loops in Kickstart, and so forth) could be detected, but it was never > completed. > State machine would be nice as it allows for more actions

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

A few extra thoughts on this, since a lot of it is still based on my design from nearly 5 years ago ;) On Wed, 2017-09-20 at 17:27 -0700, 'Konstantin Orekhov' via Foreman users wrote: > > Hmm, one generic question on this - according to above logic, if my > managed host had crashed, say because

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

Hey, you are absolutely right that this is huge design gap in discovery, we are tracking a refactor ticket to redesign how discovered hosts are stored, but this is complete change of how discovery hosts are being provisioned (you would not be able to use New Hosts screen for example). I think this

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

On Wednesday, September 20, 2017 at 3:55:43 AM UTC-7, Lukas Zapletal wrote: > > A MAC address can only exist once, if you already have a > (managed/unmanaged) host and you try to discover a host with same MAC, > you will get error. Depending on Foreman discovery it is either 422 or > "Host

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

A MAC address can only exist once, if you already have a (managed/unmanaged) host and you try to discover a host with same MAC, you will get error. Depending on Foreman discovery it is either 422 or "Host already exists":

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

Well no, the biggest update was for 1.14 there: http://projects.theforeman.org/issues/9016 That focused on memory consumption tho, there was a little speedup but nothing big. On Tue, Sep 19, 2017 at 10:05 PM, 'Konstantin Orekhov' via Foreman users wrote: > BTW,

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

BTW, Lukas, you mentioned that some improvements were made in 1.14. I am running 1.14.1 and 1.14.3. Did you mean 1.15 maybe? Should I even consider an upgrade to help resolve this situation? -- You received this message because you are subscribed to the Google Groups "Foreman users" group. To

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

After I got the debug output, I've deleted this host from Foreman and on its next attempt it got registered perfectly fine - no issues with interfaces or anything anymore: (on a client side) Discovered by URL: https://spc.vip Registering host with Foreman (https://spc.vip) Response from

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

Here you go, Lukas (just one host that can't register and keeps on retrying): 2017-09-19 11:45:55 5de80a14 [audit] [I] [mac3cfdfe52252c] deleted 0 (1898.9ms) 2017-09-19 11:45:56 5de80a14 [audit] [I] [mac3cfdfe52252c] updated 0 (575.8ms) 2017-09-19 11:45:56 5de80a14 [audit] [I]

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

I would rather fix importing code to be faster than doing async, that is the last resort. Konstantin, thanks for analysis. Our import code is slow indeed, we improved it a bit in 1.14. Note we mostly test this on PostgreSQL. For each import, there is a log in INFO level about how much time was

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

On Fri, Sep 15, 2017 at 8:56 PM, 'Konstantin Orekhov' via Foreman users < foreman-users@googlegroups.com> wrote: > > >> what kind of load do you have? Puppet? Facter? Is that ENC? Something >> else? >> >> Can you tell which requests are slow from logs or monitoring? >> >> > Yes, I should have

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

This is how it looks in WebUI: Empties: And most of those empties would also have duplicates in this form: -- 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

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

> > what kind of load do you have? Puppet? Facter? Is that ENC? Something > else? > > Can you tell which requests are slow from logs or monitoring? > > Yes, I should have mentioned that - there's very little puppet and ENC work done by this cluster at this point (more is coming soon though).

Re: [foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

Hey, what kind of load do you have? Puppet? Facter? Is that ENC? Something else? Can you tell which requests are slow from logs or monitoring? LZ On Fri, Sep 15, 2017 at 3:35 AM, 'Konstantin Orekhov' via Foreman users wrote: > > Hi, all! > > Under increased

[foreman-users] Lots of "Mysql2::Error: Deadlock found when trying to get lock" under increased load

Hi, all! Under increased load (which comes in spikes), I noticed lots of mysql deadlock errors resulting in failed transactions and incorrectly discovered systems (duplicate and/or empty entries in discovered_hosts I reported in this group some time ago, just can't find those posts for some