Bug #10133: Massive db deadlocks in postgres from hosts_counter updates with counter_cache_fix.rb - Foreman

Custom queries

3.11.5 release DONE
3.11.5 release TODO
3.12.2 release DONE
3.12.2 release TODO
3.13.1 release DONE
3.13.1 release TODO
All bugs by votes
All issues closed in the last 3 weeks
Easy and Trivial Issues
Easy Issues (only)
Foreman - new open issues in last 10 days
Good first issues - Foreman core
high prio open bugs
Host registration [open]
Mine
My issues closed in the last 3 weeks
old bugs
Open with target version set (aka blockers)
registration
taken-none
Team Atlas untriaged bugs
templates
Trivial Issues
Untriaged and opened in the past two weeks

Actions

Copy link

Bug #10133

closed

Massive db deadlocks in postgres from hosts_counter updates with counter_cache_fix.rb

Added by Chuck Schweizer almost 10 years ago. Updated over 6 years ago.

Status:

Closed

Priority:

Normal

Assignee:

Tomer Brisker

Category:

Database

Target version:

1.9.1

Difficulty:

Triaged:

Bugzilla link:

Pull request:

https://github.com/theforeman/foreman/pull/2362

Fixed in Releases:

Found in Releases:

1.8.0

Red Hat JIRA:

Description

https://gist.github.com/csschwe/4cc4d9be58e1cb96ec6c

After updating the Foreman 1.8 rc3 I am seeing a massive amount of DB Deadlocks. This issue was not present in 1.7

Related issues 5 (0 open — 5 closed)

Related to Foreman - Bug #5692: Puppet environment counters not updated	Closed	Tomer Brisker	05/13/2014	Actions
Related to Foreman - Bug #12241: Counter cache update didn't pick up changes from after_commit callback	Closed	Tomer Brisker	10/21/2015	Actions
Related to Foreman - Bug #7246: Remove counter workaround for #5692 on upgrade to rails 4.x	Rejected		08/25/2014	Actions
Has duplicate Chef - Bug #11232: Occassional error in tasks when importing facts from foreman-chef	Duplicate	Tomer Brisker	07/28/2015	Actions
Has duplicate Foreman - Bug #5990: multiple calls to create or update domain throws deadlock error	Duplicate		05/29/2014	Actions

Issue # Delay: days Cancel

History
Notes
Property changes
Associated revisions

Actions

Copy link

Updated by Chuck Schweizer almost 10 years ago

This is in a 40K node environment.

Actions

Copy link

Updated by Ohad Levy almost 10 years ago

quick google shows a few things:
- http://stackoverflow.com/questions/11911087/ruby-on-rails-is-a-counter-cache-transaction-safe
- https://github.com/magnusvk/counter_culture

Actions

Copy link

Updated by Tomer Brisker almost 10 years ago

Related to Bug #5692: Puppet environment counters not updated added

Actions

Copy link

Updated by Tomer Brisker almost 10 years ago

Category set to Database

Which PostgreSQL version are you using?
This sounds like it might be related to a problem that was fixed in 9.3: http://mina.naguib.ca/blog/2010/11/22/postgresql-foreign-key-deadlocks.html

Actions

Copy link

Updated by Lukas Zapletal almost 10 years ago

There are three users in the comments complaining that 9.3 version is even worse and it was not fixed for them :-(

Alvaro Herrera describes the solution in introducing new keyword SELECT ... FOR KEY. That would mean you need both new PostgreSQL 9.3 and newer Rails which takes advantage of that approach? Or some change in Foreman would be required I assume.

Actions

Copy link

Updated by Tomer Brisker almost 10 years ago

This specific deadlock should be prevented when we upgrade to Rails 4, as it is caused by a workaround for a bug in cached counters that existed only in Rails 3

Actions

Copy link

Updated by Lukas Zapletal almost 10 years ago

Oh I see. Maybe to make this workaround optional so users with heavy load can turn it off?

Actions

Copy link

Updated by Tomer Brisker almost 10 years ago

Status changed from New to Assigned
Assignee set to Tomer Brisker

The counter_cache fix was already in 1.7, so I'm trying to understand what caused this.
Chuck, what operation causes the deadlocks? Did you upgrade anything other then the foreman?

Actions

Copy link

Updated by Tomer Brisker almost 10 years ago

Digging into the log it would seem the deadlock is caused by a race between the counter_cache_fix and rails' original update_counters trying to update the same counter at the same time. Will continue investigating.

Actions

Copy link

#10

Updated by Chuck Schweizer almost 10 years ago

My environment is a fully updated RHEL 6 install using the foreman installer.

foreman 1.8 rc3
postgres 8.4

The foreman server is only setup to receive reports and facts from the puppet masters, it is not acting as a puppet server or external node configurator.

From what I can tell the uploading of reports and facts from the 40K nodes, through the puppet masters, is causing the deadlocks. Commenting out the logic that updates the DB in counter_cache_fix.rb made the deadlocks stop.

The only thing that was change going from foreman 1.7.1 to 1.8 rc3 was foreman. Nothing else on the system was updated or changed. The foreman install was run after installing the 1.8 rc3 rpms.

Actions

Copy link

#11

Updated by The Foreman Bot over 9 years ago

Status changed from Assigned to Ready For Testing
Pull request https://github.com/theforeman/foreman/pull/2362 added
Pull request deleted ()

Actions

Copy link

#12

Updated by Daniel Lobato Garcia over 9 years ago

Hi Chuck,

Tomer has prepared a proposed fix for this issue - https://github.com/theforeman/foreman/pull/2362
Could you report if it works for your case?

Thanks!

Actions

Copy link

#13

Updated by Chuck Schweizer over 9 years ago

After reducing the number of puppet masters in my environment I have been unable to reproduce the issue.

Daniel Lobato Garcia wrote:

Hi Chuck,

Tomer has prepared a proposed fix for this issue - https://github.com/theforeman/foreman/pull/2362
Could you report if it works for your case?

Thanks!

Actions

Copy link

#14