PGError: ERROR: deadlock detected - remove the host_count updates?

Support
#1

I've been googling this error and couldn't find a solution that works. I
even tried a patch without any kind of success. I'm running foreman 1.8.2
(read about this happening in 1.9 also) with postgresql. If more than 4
threads are creating/destroying hosts I encounter PGError: ERROR: deadlock
detected, mostly on updates for host_count column:

{
"error": {"message":"PGError: ERROR: deadlock detected\nDETAIL: Process 19184 waits for ShareLock on transaction 7354368; blocked by process 22793.\nProcess 22793 waits for ExclusiveLock on tuple (4,31) of relation 16525 of database 16385; blocked by process 19184.\nHINT: See server log for query details.\n: UPDATE "domains" SET "hosts_count" = COALESCE("hosts_count", 0) + 1 WHERE "domains"."id" IN (SELECT "domains"."id" FROM "domains" WHERE "domains"."id" = 3 ORDER BY domains.name)"}
}
{
"error": {"message":"PGError: ERROR: deadlock detected\nDETAIL: Process 22773 waits for ExclusiveLock on tuple (2,26) of relation 16503 of database 16385; blocked by process 22793.\nProcess 22793 waits for ShareLock on transaction 7353856; blocked by process 4354.\nProcess 4354 waits for ShareLock on transaction 7354037; blocked by process 22773.\nHINT: See server log for query details.\n: UPDATE "architectures" SET "hosts_count" = COALESCE("hosts_count", 0) - 1 WHERE "architectures"."id" = 1"}
}
I would like to continue handle this by cancelling all this updates for host_count. Do you see a problem with this?
Thanks

#2

Hello Ghidu,
There were recently a couple of threads about this issue that had to do
with an extremely large database leading to these deadlocks:
https://groups.google.com/forum/#!topic/foreman-users/0bJd3TPY5MU
https://groups.google.com/forum/#!topic/foreman-users/KHZHi8ktD0s

You could try the solutions suggested in those threads - checking table
sizes to find extremely large ones, expiring reports, verifying all indices
are present, upgrading postgres and foreman.
If they do not help, you could disable the counter_cache for the various
fields and manually update the counters periodically using
foreman-rake fix_cached_counters.
The host counters are only used for informational purposes, so there should
not be any adverse effects other then out-of-date counts.

··· On Fri, Nov 13, 2015 at 10:03 PM, ghidu wrote:

I’ve been googling this error and couldn’t find a solution that works. I
even tried a patch without any kind of success. I’m running foreman 1.8.2
(read about this happening in 1.9 also) with postgresql. If more than 4
threads are creating/destroying hosts I encounter PGError: ERROR: deadlock
detected, mostly on updates for host_count column:

{
“error”: {“message”:“PGError: ERROR: deadlock detected\nDETAIL: Process 19184 waits for ShareLock on transaction 7354368; blocked by process 22793.\nProcess 22793 waits for ExclusiveLock on tuple (4,31) of relation 16525 of database 16385; blocked by process 19184.\nHINT: See server log for query details.\n: UPDATE “domains” SET “hosts_count” = COALESCE(“hosts_count”, 0) + 1 WHERE “domains”.“id” IN (SELECT “domains”.“id” FROM “domains” WHERE “domains”.“id” = 3 ORDER BY domains.name)”}
}
{
“error”: {“message”:“PGError: ERROR: deadlock detected\nDETAIL: Process 22773 waits for ExclusiveLock on tuple (2,26) of relation 16503 of database 16385; blocked by process 22793.\nProcess 22793 waits for ShareLock on transaction 7353856; blocked by process 4354.\nProcess 4354 waits for ShareLock on transaction 7354037; blocked by process 22773.\nHINT: See server log for query details.\n: UPDATE “architectures” SET “hosts_count” = COALESCE(“hosts_count”, 0) - 1 WHERE “architectures”.“id” = 1”}
}
I would like to continue handle this by cancelling all this updates for host_count. Do you see a problem with this?
Thanks

–
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 foreman-users+unsubscribe@googlegroups.com.
To post to this group, send email to foreman-users@googlegroups.com.
Visit this group at http://groups.google.com/group/foreman-users.
For more options, visit https://groups.google.com/d/optout.

–
Have a nice day,
Tomer Brisker
Red Hat Engineering

#3

Thanks Tomer. Did some DB cleanup that seemed to help, a little

··· On Sunday, November 15, 2015 at 10:19:17 AM UTC+2, Tomer Brisker wrote: > > Hello Ghidu, > There were recently a couple of threads about this issue that had to do > with an extremely large database leading to these deadlocks: > https://groups.google.com/forum/#!topic/foreman-users/0bJd3TPY5MU > https://groups.google.com/forum/#!topic/foreman-users/KHZHi8ktD0s > > You could try the solutions suggested in those threads - checking table > sizes to find extremely large ones, expiring reports, verifying all indices > are present, upgrading postgres and foreman. > If they do not help, you could disable the counter_cache for the various > fields and manually update the counters periodically using > `foreman-rake fix_cached_counters`. > The host counters are only used for informational purposes, so there > should not be any adverse effects other then out-of-date counts. > > > On Fri, Nov 13, 2015 at 10:03 PM, ghidu <agh...@gmail.com > > wrote: > >> I've been googling this error and couldn't find a solution that works. I >> even tried a patch without any kind of success. I'm running foreman 1.8.2 >> (read about this happening in 1.9 also) with postgresql. If more than 4 >> threads are creating/destroying hosts I encounter PGError: ERROR: deadlock >> detected, mostly on updates for host_count column: >> >> { >> "error": {"message":"PGError: ERROR: deadlock detected\nDETAIL: Process 19184 waits for ShareLock on transaction 7354368; blocked by process 22793.\nProcess 22793 waits for ExclusiveLock on tuple (4,31) of relation 16525 of database 16385; blocked by process 19184.\nHINT: See server log for query details.\n: UPDATE \"domains\" SET \"hosts_count\" = COALESCE(\"hosts_count\", 0) + 1 WHERE \"domains\".\"id\" IN (SELECT \"domains\".\"id\" FROM \"domains\" WHERE \"domains\".\"id\" = 3 ORDER BY domains.name)"} >> } >> { >> "error": {"message":"PGError: ERROR: deadlock detected\nDETAIL: Process 22773 waits for ExclusiveLock on tuple (2,26) of relation 16503 of database 16385; blocked by process 22793.\nProcess 22793 waits for ShareLock on transaction 7353856; blocked by process 4354.\nProcess 4354 waits for ShareLock on transaction 7354037; blocked by process 22773.\nHINT: See server log for query details.\n: UPDATE \"architectures\" SET \"hosts_count\" = COALESCE(\"hosts_count\", 0) - 1 WHERE \"architectures\".\"id\" = 1"} >> } >> I would like to continue handle this by cancelling all this updates for host_count. Do you see a problem with this? >> Thanks >> >> >> -- >> 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 foreman-user...@googlegroups.com . >> To post to this group, send email to forema...@googlegroups.com >> . >> Visit this group at http://groups.google.com/group/foreman-users. >> For more options, visit https://groups.google.com/d/optout. >> > > > > -- > Have a nice day, > Tomer Brisker > Red Hat Engineering >