The following bug has been logged on the website:
Bug reference: 15749
Logged by: Torrey Johnson
Email address: torrey@interactiveshift.com
PostgreSQL version: 11.2
Operating system: Linux Ubuntu 16.04
Description:
Hi there, after upgrading our postgres version from 10.5 to 11.2, we noticed
that our parallel query was causing DynamicSharedMemoryControlLock on our
queries, and locking everything up, causing our connections to expand and
ending up killing our database. I found this thread, and it's pretty much
the exact same thing:
https://www.postgresql.org/message-id/flat/458c691a-1b2d-7a70-1c04-58361b894011%40gmail.com#bab0f00fb877880d0fbaadadd2624ce2
I've disabled 'max_parallel_workers_per_gather = 0', and it hasn't happened
since. These were the settings when this started to happening
max_parallel_workers_per_gather = 4
max_worker_processes = 8
max_parallel_workers = 8
Here's the errors from my postgres logs after seeing
DynamicSharedMemoryControlLock fill up in 'pg_stat_activity':
2019-03-28 22:24:32.728 EDT [24283] ignite@ignite_production ERROR:
dsa_area could not attach to segment
2019-03-28 22:24:32.728 EDT [24283] ignite@ignite_production STATEMENT:
SELECT "team_activity_views".* FROM "team_activity_views" WHERE
"team_activity_views"."team_id" = $1 LIMIT $2
2019-03-28 22:24:32.728 EDT [63006] FATAL: terminating connection due to
administrator command
2019-03-28 22:24:32.728 EDT [63006] STATEMENT: SELECT
"team_activity_views".* FROM "team_activity_views" WHERE
"team_activity_views"."team_id" = $1 LIMIT $2
2019-03-28 22:24:32.728 EDT [63005] FATAL: terminating connection due to
administrator command
2019-03-28 22:24:32.728 EDT [63005] STATEMENT: SELECT
"team_activity_views".* FROM "team_activity_views" WHERE
"team_activity_views"."team_id" = $1 LIMIT $2
2019-03-28 22:24:32.729 EDT [63005] FATAL: cannot unpin a segment that is
not pinned
2019-03-28 22:24:32.730 EDT [1110] LOG: background worker "parallel worker"
(PID 63006) exited with exit code 1
Any help or update on this would be great. I would love to turn this option
back on. Thanks