pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: too many dynamic shared memory segments
21+ messages / 7 participants
[nested] [flat]
Commits from this thread:
2018-02-02 957ff087c8 34653bc983 Be more wary about shm_toc_lookup failure.
2017-11-28 445dbd82a3 dba6e75c11 Fix ReinitializeParallelDSM to tolerate finding no error queues.
2017-11-28 c6755e233b ec7629dfb9 Teach bitmap heap scan to cope with absence of a DSA.
* ERROR: too many dynamic shared memory segments
@ 2017-11-27 09:54 Jakub Glapa <jakub.glapa@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Jakub Glapa @ 2017-11-27 09:54 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hi,
I started seeing those errors on Postgresql 10.1 running on Ubuntu 16.04.3
(64GB/8CPU) server.
The DB enters recovery mode after that.
2017-11-23 07:20:39 CET::@:[24823]: ERROR: could not attach to dynamic
shared area
2017-11-23 07:20:39 CET::@:[24822]: ERROR: could not map dynamic shared
memory segment
2017-11-23 07:20:40 CET:192.168.xx,xx(33974):u(at)db:[24209]: ERROR: too
many
dynamic shared memory segments
The errors happen when the parallel execution is enabled and multiple
queries are executed simultaneously.
If I set the max_parallel_workers_per_gather = 0 the error doesn't occur.
The queries are rather big, each executes over 10sec.
I see the error when 4 or more queries are started at the same time.
my postgresql.conf:
max_connections = 100
shared_buffers = 12GB
effective_cache_size = 51GB
work_mem = 83886kB
maintenance_work_mem = 2GB
max_wal_size = 8GB
min_wal_size = 4GB
checkpoint_completion_target = 0.9
wal_buffers = 32MB
default_statistics_target = 1000
max_locks_per_transaction = 128
#max_parallel_workers_per_gather = 0
PS. I've posted this question on pgsql-admin distro but didn't get any
feedback.
--
regards,
Jakub Glapa
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: ERROR: too many dynamic shared memory segments
@ 2017-11-27 10:47 Thomas Munro <thomas.munro@enterprisedb.com>
parent: Jakub Glapa <jakub.glapa@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Thomas Munro @ 2017-11-27 10:47 UTC (permalink / raw)
To: Jakub Glapa <jakub.glapa@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Mon, Nov 27, 2017 at 10:54 PM, Jakub Glapa <jakub.glapa@gmail.com> wrote:
> The DB enters recovery mode after that.
That's not good. So it actually crashes? Can you please show the
full error messages?
> 2017-11-23 07:20:39 CET::@:[24823]: ERROR: could not attach to dynamic
> shared area
From src/backend/utils/mmgr/dsa.c. The same message is used for
dsa_attach() and for attach_internal(), but in this case it must be
the latter because we use in-place DSA areas for parallel query. This
means that when the worker tried to attach it found that
control->refcnt == 0, meaning 'too late, every other backend including
the leader has already detached'.
> 2017-11-23 07:20:39 CET::@:[24822]: ERROR: could not map dynamic shared
> memory segment
From src/backend/access/transam/parallel.c when dsm_attach returns
null. I think this also means 'too late'.
So those are both workers that have started up and found that the
leader has abandoned the parallel query already, but they discovered
it at different stages. PID 24823 didn't manage to attach to the DSM
segment, while PID 24822 did but found that the other(s) had already
detached from the per-query DSA area inside it.
> 2017-11-23 07:20:40 CET:192.168.xx,xx(33974):u(at)db:[24209]: ERROR: too
> many
> dynamic shared memory segments
>
> The errors happen when the parallel execution is enabled and multiple
> queries are executed simultaneously.
> If I set the max_parallel_workers_per_gather = 0 the error doesn't occur.
> The queries are rather big, each executes over 10sec.
> I see the error when 4 or more queries are started at the same time.
>
> my postgresql.conf:
>
> max_connections = 100
So the two questions we need to figure out are: (1) how do we manage
to use up all 64 + 2 * 100 DSM slots (or somehow corrupt things so it
looks that way) by running only 4 queries, and (2) why would be be
crashing rather than failing and reporting an error? I'm not able to
reproduce the problem from your description running lots of parallel
queries running at the same time. Can you provide reproduction steps?
Does your query plan happen to include a Parallel Bitmap Heap Scan?
--
Thomas Munro
http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: ERROR: too many dynamic shared memory segments
@ 2017-11-27 12:13 Jakub Glapa <jakub.glapa@gmail.com>
parent: Thomas Munro <thomas.munro@enterprisedb.com>
0 siblings, 1 reply; 21+ messages in thread
From: Jakub Glapa @ 2017-11-27 12:13 UTC (permalink / raw)
To: Thomas Munro <thomas.munro@enterprisedb.com>; +Cc: pgsql-general@lists.postgresql.org
Hi Thomas,
log excerpt:
...
2017-11-27 12:21:14 CET:192.168.10.83(33424):user@db:[27291]: ERROR: too
many dynamic shared memory segments
2017-11-27 12:21:14 CET:192.168.10.83(33424):user@db:[27291]: STATEMENT:
SELECT << REMOVED>>
2017-11-27 12:21:14 CET:192.168.10.83(35182):user@db:[28281]: ERROR: too
many dynamic shared memory segments
2017-11-27 12:21:14 CET:192.168.10.83(35182):user@db:[28281]: STATEMENT:
SELECT <<REMOVED>
2017-11-27 12:21:14 CET::@:[28618]: ERROR: could not map dynamic shared
memory segment
2017-11-27 12:21:14 CET::@:[28619]: ERROR: could not map dynamic shared
memory segment
2017-11-27 12:21:14 CET::@:[25645]: LOG: worker process: parallel worker
for PID 27291 (PID 28618) exited with exit code 1
2017-11-27 12:21:14 CET::@:[25645]: LOG: worker process: parallel worker
for PID 27291 (PID 28619) exited with exit code 1
...
this time the db didn't crash but the queries failed to execute.
The queries are somehow special.
We are still using the old style partitioning (list type) but we abuse it a
bit when querying.
When querying a set of partitions instead of doing it via parent table we
stitch together the required tables with UNION ALL (this requires less
locking) and was more performant in our benchmark (the new native
partitioning might improve this but we didn't research that yet).
The queries are in form of
SELECT col1,col2,col3 FROM
(SELECT *
FROM par1
WHERE rangestart >= '2017-01-01 00:00:00.0' AND rangeend <=
'2017-11-26 23:59:59.999'
UNION ALL SELECT *
FROM par2
WHERE rangestart >= '2017-01-01 00:00:00.0' AND rangeend <=
'2017-11-26 23:59:59.999'
UNION ALL SELECT *
FROM par2
WHERE rangestart >= '2017-01-01 00:00:00.0' AND rangeend <=
'2017-11-26 23:59:59.999'
WHERE rangestart >= '2017-01-01 00:00:00.0' AND rangeend <=
'2017-11-26 23:59:59.999'
UNION ALL
...
) unionalias
WHERE ....
and indeed the query planner shows the usage of Bitmap Heap Scan
....
-> Subquery Scan on "*SELECT* 2"
(cost=3068.58..19793.94 rows=1 width=1284)
-> Gather (cost=3068.58..19793.93
rows=1 width=5014)
Workers Planned: 2
-> Parallel Bitmap Heap Scan
on par_6 (cost=2068.58..18793.83 rows=1 width=5014)
Recheck Cond:
<<CONDITION>>
Filter: <<CONDITION>>
-> BitmapAnd
(cost=2068.58..2068.58 rows=30955 width=0)
-> BitmapOr
(cost=999.30..999.30 rows=42989 width=0)
-> Bitmap
Index Scan on par_6_datasource (cost=0.00..990.21 rows=42922 width=0)
Index
Cond: ((datasource)::text = 'one'::text)
-> Bitmap
Index Scan on par_6_datasource (cost=0.00..4.30 rows=1 width=0)
Index
Cond: ((datasource)::text = 'two'::text)
-> Bitmap
Index Scan on par_6_datasource (cost=0.00..4.79 rows=67 width=0)
Index
Cond: ((datasource)::text = 'three'::text)
-> Bitmap Index
Scan on par_6_rangestart (cost=0.00..1069.02 rows=47564 width=0)
Index Cond:
(rangestart >= '2017-01-01 00:00:00+01'::timestamp with time zone)
-> Subquery Scan on "*SELECT* 3"
(cost=761.33..7944.99 rows=1 width=1086)
-> Bitmap Heap Scan on par_7
(cost=761.33..7944.98 rows=1 width=4816)
Recheck Cond:
<<CONDITION>>
Filter: <<CONDITION>>
-> BitmapAnd
(cost=761.33..761.33 rows=7045 width=0)
-> Bitmap Index Scan on
par_7_rangestart (cost=0.00..380.35 rows=14942 width=0)
Index Cond:
(rangestart >= '2017-01-01 00:00:00+01'::timestamp with time zone)
-> BitmapOr
(cost=380.72..380.72 rows=12248 width=0)
-> Bitmap Index
Scan on par_7_datasource (cost=0.00..372.00 rows=12228 width=0)
Index Cond:
((datasource)::text = 'one'::text)
-> Bitmap Index
Scan on par_7_datasource (cost=0.00..4.36 rows=10 width=0)
Index Cond:
((datasource)::text = 'two'::text)
-> Bitmap Index
Scan on par_7_datasource (cost=0.00..4.36 rows=10 width=0)
Index Cond:
((datasource)::text = 'three'::text)
....
In this particular query there were over _100_ partitions connected with
the UNION ALL operator.
--
regards,
pozdrawiam,
Jakub Glapa
On Mon, Nov 27, 2017 at 11:47 AM, Thomas Munro <
thomas.munro@enterprisedb.com> wrote:
> On Mon, Nov 27, 2017 at 10:54 PM, Jakub Glapa <jakub.glapa@gmail.com>
> wrote:
> > The DB enters recovery mode after that.
>
> That's not good. So it actually crashes? Can you please show the
> full error messages?
>
> > 2017-11-23 07:20:39 CET::@:[24823]: ERROR: could not attach to dynamic
> > shared area
>
> From src/backend/utils/mmgr/dsa.c. The same message is used for
> dsa_attach() and for attach_internal(), but in this case it must be
> the latter because we use in-place DSA areas for parallel query. This
> means that when the worker tried to attach it found that
> control->refcnt == 0, meaning 'too late, every other backend including
> the leader has already detached'.
>
> > 2017-11-23 07:20:39 CET::@:[24822]: ERROR: could not map dynamic shared
> > memory segment
>
> From src/backend/access/transam/parallel.c when dsm_attach returns
> null. I think this also means 'too late'.
>
> So those are both workers that have started up and found that the
> leader has abandoned the parallel query already, but they discovered
> it at different stages. PID 24823 didn't manage to attach to the DSM
> segment, while PID 24822 did but found that the other(s) had already
> detached from the per-query DSA area inside it.
>
> > 2017-11-23 07:20:40 CET:192.168.xx,xx(33974):u(at)db:[24209]: ERROR:
> too
> > many
> > dynamic shared memory segments
> >
> > The errors happen when the parallel execution is enabled and multiple
> > queries are executed simultaneously.
> > If I set the max_parallel_workers_per_gather = 0 the error doesn't
> occur.
> > The queries are rather big, each executes over 10sec.
> > I see the error when 4 or more queries are started at the same time.
> >
> > my postgresql.conf:
> >
> > max_connections = 100
>
> So the two questions we need to figure out are: (1) how do we manage
> to use up all 64 + 2 * 100 DSM slots (or somehow corrupt things so it
> looks that way) by running only 4 queries, and (2) why would be be
> crashing rather than failing and reporting an error? I'm not able to
> reproduce the problem from your description running lots of parallel
> queries running at the same time. Can you provide reproduction steps?
> Does your query plan happen to include a Parallel Bitmap Heap Scan?
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: ERROR: too many dynamic shared memory segments
@ 2017-11-27 18:53 Thomas Munro <thomas.munro@enterprisedb.com>
parent: Jakub Glapa <jakub.glapa@gmail.com>
0 siblings, 2 replies; 21+ messages in thread
From: Thomas Munro @ 2017-11-27 18:53 UTC (permalink / raw)
To: Jakub Glapa <jakub.glapa@gmail.com>; Forums postgresql <pgsql-general@postgresql.org>
On Tue, Nov 28, 2017 at 1:13 AM, Jakub Glapa <jakub.glapa@gmail.com> wrote:
> The queries are somehow special.
> We are still using the old style partitioning (list type) but we abuse it a
> bit when querying.
> When querying a set of partitions instead of doing it via parent table we
> stitch together the required tables with UNION ALL (this requires less
> locking) and was more performant in our benchmark (the new native
> partitioning might improve this but we didn't research that yet).
>
> The queries are in form of
> SELECT col1,col2,col3 FROM
> (SELECT *
> FROM par1
> WHERE rangestart >= '2017-01-01 00:00:00.0' AND rangeend <=
> '2017-11-26 23:59:59.999'
> UNION ALL SELECT *
> FROM par2
> WHERE rangestart >= '2017-01-01 00:00:00.0' AND rangeend <=
> '2017-11-26 23:59:59.999'
> UNION ALL SELECT *
> FROM par2
> WHERE rangestart >= '2017-01-01 00:00:00.0' AND rangeend <=
> '2017-11-26 23:59:59.999'
>
> WHERE rangestart >= '2017-01-01 00:00:00.0' AND rangeend <=
> '2017-11-26 23:59:59.999'
>
> UNION ALL
> ...
> ) unionalias
> WHERE ....
>
>
>
> and indeed the query planner shows the usage of Bitmap Heap Scan
> ....
> -> Subquery Scan on "*SELECT* 2"
> (cost=3068.58..19793.94 rows=1 width=1284)
> -> Gather (cost=3068.58..19793.93
> rows=1 width=5014)
> Workers Planned: 2
> -> Parallel Bitmap Heap Scan
> on par_6 (cost=2068.58..18793.83 rows=1 width=5014)
> Recheck Cond:
> <<CONDITION>>
> Filter: <<CONDITION>>
> -> BitmapAnd
> (cost=2068.58..2068.58 rows=30955 width=0)
> -> BitmapOr
> (cost=999.30..999.30 rows=42989 width=0)
> -> Bitmap
> Index Scan on par_6_datasource (cost=0.00..990.21 rows=42922 width=0)
> Index
> Cond: ((datasource)::text = 'one'::text)
> -> Bitmap
> Index Scan on par_6_datasource (cost=0.00..4.30 rows=1 width=0)
> Index
> Cond: ((datasource)::text = 'two'::text)
> -> Bitmap
> Index Scan on par_6_datasource (cost=0.00..4.79 rows=67 width=0)
> Index
> Cond: ((datasource)::text = 'three'::text)
> -> Bitmap Index
> Scan on par_6_rangestart (cost=0.00..1069.02 rows=47564 width=0)
> Index Cond:
> (rangestart >= '2017-01-01 00:00:00+01'::timestamp with time zone)
> -> Subquery Scan on "*SELECT* 3"
> (cost=761.33..7944.99 rows=1 width=1086)
> -> Bitmap Heap Scan on par_7
> (cost=761.33..7944.98 rows=1 width=4816)
> Recheck Cond:
> <<CONDITION>>
> Filter: <<CONDITION>>
> -> BitmapAnd
> (cost=761.33..761.33 rows=7045 width=0)
> -> Bitmap Index Scan on
> par_7_rangestart (cost=0.00..380.35 rows=14942 width=0)
> Index Cond:
> (rangestart >= '2017-01-01 00:00:00+01'::timestamp with time zone)
> -> BitmapOr
> (cost=380.72..380.72 rows=12248 width=0)
> -> Bitmap Index
> Scan on par_7_datasource (cost=0.00..372.00 rows=12228 width=0)
> Index Cond:
> ((datasource)::text = 'one'::text)
> -> Bitmap Index
> Scan on par_7_datasource (cost=0.00..4.36 rows=10 width=0)
> Index Cond:
> ((datasource)::text = 'two'::text)
> -> Bitmap Index
> Scan on par_7_datasource (cost=0.00..4.36 rows=10 width=0)
> Index Cond:
> ((datasource)::text = 'three'::text)
>
> ....
>
>
> In this particular query there were over _100_ partitions connected with the
> UNION ALL operator.
Ah, so you have many Gather nodes under Append? That's one way to eat
arbitrarily many DSM slots. We allow for 64 + 2 * max_backends. Does
it help if you increase max_connections? I am concerned about the
crash failure mode you mentioned in the first email though: we should
always be able to handle that condition gracefully.
--
Thomas Munro
http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: ERROR: too many dynamic shared memory segments
@ 2017-11-27 19:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas Munro <thomas.munro@enterprisedb.com>
1 sibling, 0 replies; 21+ messages in thread
From: Tom Lane @ 2017-11-27 19:16 UTC (permalink / raw)
To: Thomas Munro <thomas.munro@enterprisedb.com>; +Cc: Jakub Glapa <jakub.glapa@gmail.com>; Forums postgresql <pgsql-general@postgresql.org>
Thomas Munro <thomas.munro@enterprisedb.com> writes:
> Ah, so you have many Gather nodes under Append? That's one way to eat
> arbitrarily many DSM slots. We allow for 64 + 2 * max_backends. Does
> it help if you increase max_connections? I am concerned about the
> crash failure mode you mentioned in the first email though: we should
> always be able to handle that condition gracefully.
I suspect this is an instance of the issue I complained about before [1]
that parallel query is unable to cope with worker start failure.
regards, tom lane
[1] https://www.postgresql.org/message-id/4905.1492813727@sss.pgh.pa.us
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: ERROR: too many dynamic shared memory segments
@ 2017-11-27 21:05 Jakub Glapa <jakub.glapa@gmail.com>
parent: Thomas Munro <thomas.munro@enterprisedb.com>
1 sibling, 1 reply; 21+ messages in thread
From: Jakub Glapa @ 2017-11-27 21:05 UTC (permalink / raw)
To: Thomas Munro <thomas.munro@enterprisedb.com>; +Cc: Forums postgresql <pgsql-general@postgresql.org>
Hi Thomas,
doubling the max_connection has the problem gone away for now! Yay!
As for the crash. I dug up the initial log and it looks like a segmentation
fault...
2017-11-23 07:26:53 CET:192.168.10.83(35238):user@db:[30003]: ERROR: too
many dynamic shared memory segments
2017-11-23 07:26:53 CET:192.168.10.83(35238):user@db:[30003]: STATEMENT:
SELECT ....
2017-11-23 07:26:55 CET::@:[29398]: LOG: server process (PID 26992) was
terminated by signal 11: Segmentation fault
2017-11-23 07:26:55 CET::@:[29398]: DETAIL: Failed process was running:
SELECT .....
2017-11-23 07:26:55 CET::@:[29398]: LOG: terminating any other active
server processes
2017-11-23 07:26:55 CET:192.168.10.83(35242):user@db:[30009]: WARNING:
terminating connection because of crash of another server process
2017-11-23 07:26:55 CET:192.168.10.83(35242):user@db:[30009]: DETAIL: The
postmaster has commanded this server process to roll back the current
transaction and exit, because another server process exited abnormally and
possibly corrupted shared memory.
2017-11-23 07:26:55 CET:192.168.10.83(35242):user@db:[30009]: HINT: In a
moment you should be able to reconnect to the database and repeat your
command.
2017-11-23 07:26:55 CET:192.168.10.83(35268):user@db:[30097]: WARNING:
terminating connection because of crash of another server process
2017-11-23 07:26:55 CET:192.168.10.83(35268):user@db:[30097]: DETAIL: The
postmaster has commanded this server process to roll back the current
transaction and exit, because another server process exited abnormally and
possibly corrupted shared memory.
2017-11-23 07:26:55 CET:192.168.10.83(35268):user@db:[30097]: HINT: In a
moment you should be able to reconnect to the database and repeat your
command.
2017-11-23 07:26:55 CET:192.168.10.83(46614):user@db:[31080]: WARNING:
terminating connection because of crash of another server process
2017-11-23 07:26:55 CET:192.168.10.83(46614):user@db:[31080]: DETAIL: The
postmaster has commanded this server process to roll back the current
transaction and exit, because another server process exited abnormally and
possibly corrupted shared memory.
2017-11-23 07:26:55 CET:192.168.10.83(46614):user@db: HINT: In a moment
you should be able to reconnect to the database and repeat your command.
2017-11-23 07:26:55 CET:192.168.10.83(35238):user@db:[30003]: WARNING:
terminating connection because of crash of another server process
2017-11-23 07:26:55 CET:192.168.10.83(35238):user@db:[30003]: DETAIL: The
postmaster has commanded this server process to roll back the current
transaction and exit, because another server process exited abnormally and
possibly corrupted shared memory
2017-11-23 07:26:55 CET:192.168.10.83(35300):user@db:[30152]: FATAL: the
database system is in recovery mode
--
regards,
pozdrawiam,
Jakub Glapa
On Mon, Nov 27, 2017 at 7:53 PM, Thomas Munro <thomas.munro@enterprisedb.com
> wrote:
> On Tue, Nov 28, 2017 at 1:13 AM, Jakub Glapa <jakub.glapa@gmail.com>
> wrote:
> > The queries are somehow special.
> > We are still using the old style partitioning (list type) but we abuse
> it a
> > bit when querying.
> > When querying a set of partitions instead of doing it via parent table we
> > stitch together the required tables with UNION ALL (this requires less
> > locking) and was more performant in our benchmark (the new native
> > partitioning might improve this but we didn't research that yet).
> >
> > The queries are in form of
> > SELECT col1,col2,col3 FROM
> > (SELECT *
> > FROM par1
> > WHERE rangestart >= '2017-01-01 00:00:00.0' AND rangeend <=
> > '2017-11-26 23:59:59.999'
> > UNION ALL SELECT *
> > FROM par2
> > WHERE rangestart >= '2017-01-01 00:00:00.0' AND rangeend
> <=
> > '2017-11-26 23:59:59.999'
> > UNION ALL SELECT *
> > FROM par2
> > WHERE rangestart >= '2017-01-01 00:00:00.0' AND rangeend
> <=
> > '2017-11-26 23:59:59.999'
> >
> > WHERE rangestart >= '2017-01-01 00:00:00.0' AND rangeend <=
> > '2017-11-26 23:59:59.999'
> >
> > UNION ALL
> > ...
> > ) unionalias
> > WHERE ....
> >
> >
> >
> > and indeed the query planner shows the usage of Bitmap Heap Scan
> > ....
> > -> Subquery Scan on "*SELECT* 2"
> > (cost=3068.58..19793.94 rows=1 width=1284)
> > -> Gather
> (cost=3068.58..19793.93
> > rows=1 width=5014)
> > Workers Planned: 2
> > -> Parallel Bitmap Heap
> Scan
> > on par_6 (cost=2068.58..18793.83 rows=1 width=5014)
> > Recheck Cond:
> > <<CONDITION>>
> > Filter: <<CONDITION>>
> > -> BitmapAnd
> > (cost=2068.58..2068.58 rows=30955 width=0)
> > -> BitmapOr
> > (cost=999.30..999.30 rows=42989 width=0)
> > -> Bitmap
> > Index Scan on par_6_datasource (cost=0.00..990.21 rows=42922 width=0)
> >
> Index
> > Cond: ((datasource)::text = 'one'::text)
> > -> Bitmap
> > Index Scan on par_6_datasource (cost=0.00..4.30 rows=1 width=0)
> >
> Index
> > Cond: ((datasource)::text = 'two'::text)
> > -> Bitmap
> > Index Scan on par_6_datasource (cost=0.00..4.79 rows=67 width=0)
> >
> Index
> > Cond: ((datasource)::text = 'three'::text)
> > -> Bitmap Index
> > Scan on par_6_rangestart (cost=0.00..1069.02 rows=47564 width=0)
> > Index
> Cond:
> > (rangestart >= '2017-01-01 00:00:00+01'::timestamp with time zone)
> > -> Subquery Scan on "*SELECT* 3"
> > (cost=761.33..7944.99 rows=1 width=1086)
> > -> Bitmap Heap Scan on par_7
> > (cost=761.33..7944.98 rows=1 width=4816)
> > Recheck Cond:
> > <<CONDITION>>
> > Filter: <<CONDITION>>
> > -> BitmapAnd
> > (cost=761.33..761.33 rows=7045 width=0)
> > -> Bitmap Index Scan
> on
> > par_7_rangestart (cost=0.00..380.35 rows=14942 width=0)
> > Index Cond:
> > (rangestart >= '2017-01-01 00:00:00+01'::timestamp with time zone)
> > -> BitmapOr
> > (cost=380.72..380.72 rows=12248 width=0)
> > -> Bitmap Index
> > Scan on par_7_datasource (cost=0.00..372.00 rows=12228 width=0)
> > Index
> Cond:
> > ((datasource)::text = 'one'::text)
> > -> Bitmap Index
> > Scan on par_7_datasource (cost=0.00..4.36 rows=10 width=0)
> > Index
> Cond:
> > ((datasource)::text = 'two'::text)
> > -> Bitmap Index
> > Scan on par_7_datasource (cost=0.00..4.36 rows=10 width=0)
> > Index
> Cond:
> > ((datasource)::text = 'three'::text)
> >
> > ....
> >
> >
> > In this particular query there were over _100_ partitions connected with
> the
> > UNION ALL operator.
>
> Ah, so you have many Gather nodes under Append? That's one way to eat
> arbitrarily many DSM slots. We allow for 64 + 2 * max_backends. Does
> it help if you increase max_connections? I am concerned about the
> crash failure mode you mentioned in the first email though: we should
> always be able to handle that condition gracefully.
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: ERROR: too many dynamic shared memory segments
@ 2017-11-27 22:48 Thomas Munro <thomas.munro@enterprisedb.com>
parent: Jakub Glapa <jakub.glapa@gmail.com>
0 siblings, 2 replies; 21+ messages in thread
From: Thomas Munro @ 2017-11-27 22:48 UTC (permalink / raw)
To: Jakub Glapa <jakub.glapa@gmail.com>; +Cc: Forums postgresql <pgsql-general@postgresql.org>; Robert Haas <robertmhaas@gmail.com>; Dilip Kumar <dilipbalaut@gmail.com>
On Tue, Nov 28, 2017 at 10:05 AM, Jakub Glapa <jakub.glapa@gmail.com> wrote:
> As for the crash. I dug up the initial log and it looks like a segmentation
> fault...
>
> 2017-11-23 07:26:53 CET:192.168.10.83(35238):user@db:[30003]: ERROR: too
> many dynamic shared memory segments
Hmm. Well this error can only occur in dsm_create() called without
DSM_CREATE_NULL_IF_MAXSEGMENTS. parallel.c calls it with that flag
and dsa.c doesn't (perhaps it should, not sure, but that'd just change
the error message), so that means this the error arose from dsa.c
trying to get more segments. That would be when Parallel Bitmap Heap
Scan tried to allocate memory.
I hacked my copy of PostgreSQL so that it allows only 5 DSM slots and
managed to reproduce a segv crash by trying to run concurrent Parallel
Bitmap Heap Scans. The stack looks like this:
* frame #0: 0x00000001083ace29
postgres`alloc_object(area=0x0000000000000000, size_class=10) + 25 at
dsa.c:1433
frame #1: 0x00000001083acd14
postgres`dsa_allocate_extended(area=0x0000000000000000, size=72,
flags=4) + 1076 at dsa.c:785
frame #2: 0x0000000108059c33
postgres`tbm_prepare_shared_iterate(tbm=0x00007f9743027660) + 67 at
tidbitmap.c:780
frame #3: 0x0000000108000d57
postgres`BitmapHeapNext(node=0x00007f9743019c88) + 503 at
nodeBitmapHeapscan.c:156
frame #4: 0x0000000107fefc5b
postgres`ExecScanFetch(node=0x00007f9743019c88,
accessMtd=(postgres`BitmapHeapNext at nodeBitmapHeapscan.c:77),
recheckMtd=(postgres`BitmapHeapRecheck at nodeBitmapHeapscan.c:710)) +
459 at execScan.c:95
frame #5: 0x0000000107fef983
postgres`ExecScan(node=0x00007f9743019c88,
accessMtd=(postgres`BitmapHeapNext at nodeBitmapHeapscan.c:77),
recheckMtd=(postgres`BitmapHeapRecheck at nodeBitmapHeapscan.c:710)) +
147 at execScan.c:162
frame #6: 0x00000001080008d1
postgres`ExecBitmapHeapScan(pstate=0x00007f9743019c88) + 49 at
nodeBitmapHeapscan.c:735
(lldb) f 3
frame #3: 0x0000000108000d57
postgres`BitmapHeapNext(node=0x00007f9743019c88) + 503 at
nodeBitmapHeapscan.c:156
153 * dsa_pointer of the iterator state which will be used by
154 * multiple processes to iterate jointly.
155 */
-> 156 pstate->tbmiterator = tbm_prepare_shared_iterate(tbm);
157 #ifdef USE_PREFETCH
158 if (node->prefetch_maximum > 0)
159
(lldb) print tbm->dsa
(dsa_area *) $3 = 0x0000000000000000
(lldb) print node->ss.ps.state->es_query_dsa
(dsa_area *) $5 = 0x0000000000000000
(lldb) f 17
frame #17: 0x000000010800363b
postgres`ExecGather(pstate=0x00007f9743019320) + 635 at
nodeGather.c:220
217 * Get next tuple, either from one of our workers, or by running the plan
218 * ourselves.
219 */
-> 220 slot = gather_getnext(node);
221 if (TupIsNull(slot))
222 return NULL;
223
(lldb) print *node->pei
(ParallelExecutorInfo) $8 = {
planstate = 0x00007f9743019640
pcxt = 0x00007f97450001b8
buffer_usage = 0x0000000108b7e218
instrumentation = 0x0000000108b7da38
area = 0x0000000000000000
param_exec = 0
finished = '\0'
tqueue = 0x0000000000000000
reader = 0x0000000000000000
}
(lldb) print *node->pei->pcxt
warning: could not load any Objective-C class information. This will
significantly reduce the quality of type information available.
(ParallelContext) $9 = {
node = {
prev = 0x000000010855fb60
next = 0x000000010855fb60
}
subid = 1
nworkers = 0
nworkers_launched = 0
library_name = 0x00007f9745000248 "postgres"
function_name = 0x00007f9745000268 "ParallelQueryMain"
error_context_stack = 0x0000000000000000
estimator = (space_for_chunks = 180352, number_of_keys = 19)
seg = 0x0000000000000000
private_memory = 0x0000000108b53038
toc = 0x0000000108b53038
worker = 0x0000000000000000
}
I think there are two failure modes: one of your sessions showed the
"too many ..." error (that's good, ran out of slots and said so and
our error machinery worked as it should), and another crashed with a
segfault, because it tried to use a NULL "area" pointer (bad). I
think this is a degenerate case where we completely failed to launch
parallel query, but we ran the parallel query plan anyway and this
code thinks that the DSA is available. Oops.
--
Thomas Munro
http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: ERROR: too many dynamic shared memory segments
@ 2017-11-28 07:32 Dilip Kumar <dilipbalaut@gmail.com>
parent: Thomas Munro <thomas.munro@enterprisedb.com>
1 sibling, 1 reply; 21+ messages in thread
From: Dilip Kumar @ 2017-11-28 07:32 UTC (permalink / raw)
To: Thomas Munro <thomas.munro@enterprisedb.com>; +Cc: Jakub Glapa <jakub.glapa@gmail.com>; Forums postgresql <pgsql-general@postgresql.org>; Robert Haas <robertmhaas@gmail.com>; pgsql-hackers
On Tue, Nov 28, 2017 at 4:18 AM, Thomas Munro <thomas.munro@enterprisedb.com
> wrote:
> On Tue, Nov 28, 2017 at 10:05 AM, Jakub Glapa <jakub.glapa@gmail.com>
> wrote:
> > As for the crash. I dug up the initial log and it looks like a
> segmentation
> > fault...
> >
> > 2017-11-23 07:26:53 CET:192.168.10.83(35238):user@db:[30003]: ERROR:
> too
> > many dynamic shared memory segments
>
> I think there are two failure modes: one of your sessions showed the
> "too many ..." error (that's good, ran out of slots and said so and
> our error machinery worked as it should), and another crashed with a
> segfault, because it tried to use a NULL "area" pointer (bad). I
> think this is a degenerate case where we completely failed to launch
> parallel query, but we ran the parallel query plan anyway and this
> code thinks that the DSA is available. Oops.
>
I think BitmapHeapScan check whether dsa is valid or not if DSA is not
valid then it should assume it's non-parallel plan.
Attached patch should fix the issue.
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
Attachments:
bug_fix_in_pbhs_when_dsa_not_initialized.patch (application/octet-stream, 679B) download
diff --git a/src/backend/executor/nodeBitmapHeapscan.c b/src/backend/executor/nodeBitmapHeapscan.c
index b885f2a..b03e44e 100644
--- a/src/backend/executor/nodeBitmapHeapscan.c
+++ b/src/backend/executor/nodeBitmapHeapscan.c
@@ -83,9 +83,13 @@ BitmapHeapNext(BitmapHeapScanState *node)
TBMIterateResult *tbmres;
OffsetNumber targoffset;
TupleTableSlot *slot;
- ParallelBitmapHeapState *pstate = node->pstate;
+ ParallelBitmapHeapState *pstate = NULL;
dsa_area *dsa = node->ss.ps.state->es_query_dsa;
+ /* if DSA is NULL then go for non-parallel mode */
+ if (dsa != NULL)
+ pstate = node->pstate;
+
/*
* extract necessary information from index scan node
*/
* Re: ERROR: too many dynamic shared memory segments
@ 2017-11-28 13:43 Robert Haas <robertmhaas@gmail.com>
parent: Dilip Kumar <dilipbalaut@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Robert Haas @ 2017-11-28 13:43 UTC (permalink / raw)
To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: Thomas Munro <thomas.munro@enterprisedb.com>; Jakub Glapa <jakub.glapa@gmail.com>; Forums postgresql <pgsql-general@postgresql.org>; pgsql-hackers
On Tue, Nov 28, 2017 at 2:32 AM, Dilip Kumar <dilipbalaut@gmail.com> wrote:
> I think BitmapHeapScan check whether dsa is valid or not if DSA is not
> valid then it should assume it's non-parallel plan.
>
> Attached patch should fix the issue.
So, create the pstate and then pretend we didn't? Why not just avoid
creating it in the first place, like this?
I haven't checked whether this fixes the bug, but if it does, we can
avoid introducing an extra branch in BitmapHeapNext.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
no-pstate.patch (application/octet-stream, 1.2K) download
diff --git a/src/backend/executor/nodeBitmapHeapscan.c b/src/backend/executor/nodeBitmapHeapscan.c
index 221391908c..eb5bbb57ef 100644
--- a/src/backend/executor/nodeBitmapHeapscan.c
+++ b/src/backend/executor/nodeBitmapHeapscan.c
@@ -1051,6 +1051,11 @@ ExecBitmapHeapInitializeDSM(BitmapHeapScanState *node,
{
ParallelBitmapHeapState *pstate;
EState *estate = node->ss.ps.state;
+ dsa_area *dsa = node->ss.ps.state->es_query_dsa;
+
+ /* If there's no DSA, there are no workers; initialize nothing. */
+ if (dsa == NULL)
+ return;
pstate = shm_toc_allocate(pcxt->toc, node->pscan_len);
@@ -1083,6 +1088,10 @@ ExecBitmapHeapReInitializeDSM(BitmapHeapScanState *node,
ParallelBitmapHeapState *pstate = node->pstate;
dsa_area *dsa = node->ss.ps.state->es_query_dsa;
+ /* If there's no DSA, there are no workers; do nothing. */
+ if (dsa == NULL)
+ return;
+
pstate->state = BM_INITIAL;
if (DsaPointerIsValid(pstate->tbmiterator))
@@ -1108,6 +1117,8 @@ ExecBitmapHeapInitializeWorker(BitmapHeapScanState *node,
ParallelBitmapHeapState *pstate;
Snapshot snapshot;
+ Assert(node->ss.ps.state->es_query_dsa != NULL);
+
pstate = shm_toc_lookup(pwcxt->toc, node->ss.ps.plan->plan_node_id, false);
node->pstate = pstate;
* Re: ERROR: too many dynamic shared memory segments
@ 2017-11-28 14:45 Dilip Kumar <dilipbalaut@gmail.com>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Dilip Kumar @ 2017-11-28 14:45 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Thomas Munro <thomas.munro@enterprisedb.com>; Jakub Glapa <jakub.glapa@gmail.com>; Forums postgresql <pgsql-general@postgresql.org>; pgsql-hackers
On Tue, Nov 28, 2017 at 7:13 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Nov 28, 2017 at 2:32 AM, Dilip Kumar <dilipbalaut@gmail.com>
> wrote:
> > I think BitmapHeapScan check whether dsa is valid or not if DSA is not
> > valid then it should assume it's non-parallel plan.
> >
> > Attached patch should fix the issue.
>
> So, create the pstate and then pretend we didn't? Why not just avoid
> creating it in the first place, like this?
>
This is better way to fix it.
>
> I haven't checked whether this fixes the bug, but if it does, we can
> avoid introducing an extra branch in BitmapHeapNext.
With my test it's fixing the problem.
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: ERROR: too many dynamic shared memory segments
@ 2017-11-28 16:58 Robert Haas <robertmhaas@gmail.com>
parent: Dilip Kumar <dilipbalaut@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Robert Haas @ 2017-11-28 16:58 UTC (permalink / raw)
To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: Thomas Munro <thomas.munro@enterprisedb.com>; Jakub Glapa <jakub.glapa@gmail.com>; Forums postgresql <pgsql-general@postgresql.org>; pgsql-hackers
On Tue, Nov 28, 2017 at 9:45 AM, Dilip Kumar <dilipbalaut@gmail.com> wrote:
>> I haven't checked whether this fixes the bug, but if it does, we can
>> avoid introducing an extra branch in BitmapHeapNext.
>
> With my test it's fixing the problem.
I tested it some more and found that, for me, it PARTIALLY fixes the
problem. I tested like this:
inline patch 1: src/backend/access/transam/parallel.c (+1/-1 lines) download inline-1.patch
--- a/src/backend/access/transam/parallel.c
+++ b/src/backend/access/transam/parallel.c
@@ -279,7 +279,7 @@ InitializeParallelDSM(ParallelContext *pcxt)
* parallelism than to fail outright.
*/
segsize = shm_toc_estimate(&pcxt->estimator);
- if (pcxt->nworkers > 0)
+ if (pcxt->nworkers > 0 && false)
pcxt->seg = dsm_create(segsize, DSM_CREATE_NULL_IF_MAXSEGMENTS);
if (pcxt->seg != NULL)
pcxt->toc = shm_toc_create(PARALLEL_MAGIC,
inline patch 2: src/backend/access/transam/parallel.c (+2/-1 lines) download inline-2.patch
--- a/src/backend/access/transam/parallel.c
+++ b/src/backend/access/transam/parallel.c
@@ -430,7 +430,8 @@ ReinitializeParallelDSM(ParallelContext *pcxt)
/* Recreate error queues. */
error_queue_space =
- shm_toc_lookup(pcxt->toc, PARALLEL_KEY_ERROR_QUEUE, false);
+ shm_toc_lookup(pcxt->toc, PARALLEL_KEY_ERROR_QUEUE, true);
+ Assert(pcxt->nworkers == 0 || error_queue_space != NULL);
for (i = 0; i < pcxt->nworkers; ++i)
{
char *start;
* Re: ERROR: too many dynamic shared memory segments
@ 2017-12-04 12:18 Jakub Glapa <jakub.glapa@gmail.com>
parent: Thomas Munro <thomas.munro@enterprisedb.com>
1 sibling, 1 reply; 21+ messages in thread
From: Jakub Glapa @ 2017-12-04 12:18 UTC (permalink / raw)
To: Thomas Munro <thomas.munro@enterprisedb.com>; +Cc: Forums postgresql <pgsql-general@postgresql.org>
I see that the segfault is under active discussion but just wanted to ask
if increasing the max_connections to mitigate the DSM slots shortage is the
way to go?
--
regards,
Jakub Glapa
On Mon, Nov 27, 2017 at 11:48 PM, Thomas Munro <
thomas.munro@enterprisedb.com> wrote:
> On Tue, Nov 28, 2017 at 10:05 AM, Jakub Glapa <jakub.glapa@gmail.com>
> wrote:
> > As for the crash. I dug up the initial log and it looks like a
> segmentation
> > fault...
> >
> > 2017-11-23 07:26:53 CET:192.168.10.83(35238):user@db:[30003]: ERROR:
> too
> > many dynamic shared memory segments
>
> Hmm. Well this error can only occur in dsm_create() called without
> DSM_CREATE_NULL_IF_MAXSEGMENTS. parallel.c calls it with that flag
> and dsa.c doesn't (perhaps it should, not sure, but that'd just change
> the error message), so that means this the error arose from dsa.c
> trying to get more segments. That would be when Parallel Bitmap Heap
> Scan tried to allocate memory.
>
> I hacked my copy of PostgreSQL so that it allows only 5 DSM slots and
> managed to reproduce a segv crash by trying to run concurrent Parallel
> Bitmap Heap Scans. The stack looks like this:
>
> * frame #0: 0x00000001083ace29
> postgres`alloc_object(area=0x0000000000000000, size_class=10) + 25 at
> dsa.c:1433
> frame #1: 0x00000001083acd14
> postgres`dsa_allocate_extended(area=0x0000000000000000, size=72,
> flags=4) + 1076 at dsa.c:785
> frame #2: 0x0000000108059c33
> postgres`tbm_prepare_shared_iterate(tbm=0x00007f9743027660) + 67 at
> tidbitmap.c:780
> frame #3: 0x0000000108000d57
> postgres`BitmapHeapNext(node=0x00007f9743019c88) + 503 at
> nodeBitmapHeapscan.c:156
> frame #4: 0x0000000107fefc5b
> postgres`ExecScanFetch(node=0x00007f9743019c88,
> accessMtd=(postgres`BitmapHeapNext at nodeBitmapHeapscan.c:77),
> recheckMtd=(postgres`BitmapHeapRecheck at nodeBitmapHeapscan.c:710)) +
> 459 at execScan.c:95
> frame #5: 0x0000000107fef983
> postgres`ExecScan(node=0x00007f9743019c88,
> accessMtd=(postgres`BitmapHeapNext at nodeBitmapHeapscan.c:77),
> recheckMtd=(postgres`BitmapHeapRecheck at nodeBitmapHeapscan.c:710)) +
> 147 at execScan.c:162
> frame #6: 0x00000001080008d1
> postgres`ExecBitmapHeapScan(pstate=0x00007f9743019c88) + 49 at
> nodeBitmapHeapscan.c:735
>
> (lldb) f 3
> frame #3: 0x0000000108000d57
> postgres`BitmapHeapNext(node=0x00007f9743019c88) + 503 at
> nodeBitmapHeapscan.c:156
> 153 * dsa_pointer of the iterator state which will be used by
> 154 * multiple processes to iterate jointly.
> 155 */
> -> 156 pstate->tbmiterator = tbm_prepare_shared_iterate(tbm);
> 157 #ifdef USE_PREFETCH
> 158 if (node->prefetch_maximum > 0)
> 159
> (lldb) print tbm->dsa
> (dsa_area *) $3 = 0x0000000000000000
> (lldb) print node->ss.ps.state->es_query_dsa
> (dsa_area *) $5 = 0x0000000000000000
> (lldb) f 17
> frame #17: 0x000000010800363b
> postgres`ExecGather(pstate=0x00007f9743019320) + 635 at
> nodeGather.c:220
> 217 * Get next tuple, either from one of our workers, or by running the
> plan
> 218 * ourselves.
> 219 */
> -> 220 slot = gather_getnext(node);
> 221 if (TupIsNull(slot))
> 222 return NULL;
> 223
> (lldb) print *node->pei
> (ParallelExecutorInfo) $8 = {
> planstate = 0x00007f9743019640
> pcxt = 0x00007f97450001b8
> buffer_usage = 0x0000000108b7e218
> instrumentation = 0x0000000108b7da38
> area = 0x0000000000000000
> param_exec = 0
> finished = '\0'
> tqueue = 0x0000000000000000
> reader = 0x0000000000000000
> }
> (lldb) print *node->pei->pcxt
> warning: could not load any Objective-C class information. This will
> significantly reduce the quality of type information available.
> (ParallelContext) $9 = {
> node = {
> prev = 0x000000010855fb60
> next = 0x000000010855fb60
> }
> subid = 1
> nworkers = 0
> nworkers_launched = 0
> library_name = 0x00007f9745000248 "postgres"
> function_name = 0x00007f9745000268 "ParallelQueryMain"
> error_context_stack = 0x0000000000000000
> estimator = (space_for_chunks = 180352, number_of_keys = 19)
> seg = 0x0000000000000000
> private_memory = 0x0000000108b53038
> toc = 0x0000000108b53038
> worker = 0x0000000000000000
> }
>
> I think there are two failure modes: one of your sessions showed the
> "too many ..." error (that's good, ran out of slots and said so and
> our error machinery worked as it should), and another crashed with a
> segfault, because it tried to use a NULL "area" pointer (bad). I
> think this is a degenerate case where we completely failed to launch
> parallel query, but we ran the parallel query plan anyway and this
> code thinks that the DSA is available. Oops.
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: ERROR: too many dynamic shared memory segments
@ 2017-12-07 21:30 Thomas Munro <thomas.munro@enterprisedb.com>
parent: Jakub Glapa <jakub.glapa@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Thomas Munro @ 2017-12-07 21:30 UTC (permalink / raw)
To: Jakub Glapa <jakub.glapa@gmail.com>; +Cc: Forums postgresql <pgsql-general@postgresql.org>
On Tue, Dec 5, 2017 at 1:18 AM, Jakub Glapa <jakub.glapa@gmail.com> wrote:
> I see that the segfault is under active discussion but just wanted to ask if
> increasing the max_connections to mitigate the DSM slots shortage is the way
> to go?
Hi Jakub,
Yes. In future releases this situation will improve (maybe we'll
figure out how to use one DSM segment for all the gather nodes in your
query plan, and maybe it'll be moot anyway because maybe we'll be able
to use a Parallel Append for queries like yours so that it uses the
same set of workers over all the child plans instead of the
fork()-fest you're presumably seeing). For now your only choice, if
you want that plan to run, is to crank up max_connections so that the
total number of concurrently executing Gather nodes is less than about
64 + 2 * max_connections. There is also a crash bug right now in the
out-of-slots case as discussed, fixed in the next point release, but
even with that fix in place you'll still need a high enough
max_connections setting to be sure to be able to complete the query
without an error.
Thanks for the report!
--
Thomas Munro
http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: ERROR: too many dynamic shared memory segments
@ 2017-12-08 07:59 Jakub Glapa <jakub.glapa@gmail.com>
parent: Thomas Munro <thomas.munro@enterprisedb.com>
0 siblings, 0 replies; 21+ messages in thread
From: Jakub Glapa @ 2017-12-08 07:59 UTC (permalink / raw)
To: Thomas Munro <thomas.munro@enterprisedb.com>; +Cc: Forums postgresql <pgsql-general@postgresql.org>
Thank You Thomas!
--
regards,
Jakub Glapa
On Thu, Dec 7, 2017 at 10:30 PM, Thomas Munro <thomas.munro@enterprisedb.com
> wrote:
> On Tue, Dec 5, 2017 at 1:18 AM, Jakub Glapa <jakub.glapa@gmail.com> wrote:
> > I see that the segfault is under active discussion but just wanted to
> ask if
> > increasing the max_connections to mitigate the DSM slots shortage is the
> way
> > to go?
>
> Hi Jakub,
>
> Yes. In future releases this situation will improve (maybe we'll
> figure out how to use one DSM segment for all the gather nodes in your
> query plan, and maybe it'll be moot anyway because maybe we'll be able
> to use a Parallel Append for queries like yours so that it uses the
> same set of workers over all the child plans instead of the
> fork()-fest you're presumably seeing). For now your only choice, if
> you want that plan to run, is to crank up max_connections so that the
> total number of concurrently executing Gather nodes is less than about
> 64 + 2 * max_connections. There is also a crash bug right now in the
> out-of-slots case as discussed, fixed in the next point release, but
> even with that fix in place you'll still need a high enough
> max_connections setting to be sure to be able to complete the query
> without an error.
>
> Thanks for the report!
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: ERROR: too many dynamic shared memory segments
@ 2018-02-02 20:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Tom Lane @ 2018-02-02 20:01 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Dilip Kumar <dilipbalaut@gmail.com>; Thomas Munro <thomas.munro@enterprisedb.com>; Jakub Glapa <jakub.glapa@gmail.com>; pgsql-hackers
Robert Haas <robertmhaas@gmail.com> writes:
> That turned out to produce more than one problem. I find that the
> select_parallel test then fails like this:
> ERROR: could not find key 18446744073709486082 in shm TOC at 0x10be98040
> The fix for that problem seems to be:
> /* Recreate error queues. */
> error_queue_space =
> - shm_toc_lookup(pcxt->toc, PARALLEL_KEY_ERROR_QUEUE, false);
> + shm_toc_lookup(pcxt->toc, PARALLEL_KEY_ERROR_QUEUE, true);
> + Assert(pcxt->nworkers == 0 || error_queue_space != NULL);
I happened across this patch while preparing the release notes, and
I'm quite distressed by it, because it completely breaks the point
of what I'd done in commit d46633506 (to wit, to not just blindly
assume, nor just Assert, that shm_toc_lookup always succeeds).
Do you mind if I change that Assert to a run-time test?
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: ERROR: too many dynamic shared memory segments
@ 2018-02-02 20:19 Robert Haas <robertmhaas@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 21+ messages in thread
From: Robert Haas @ 2018-02-02 20:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Dilip Kumar <dilipbalaut@gmail.com>; Thomas Munro <thomas.munro@enterprisedb.com>; Jakub Glapa <jakub.glapa@gmail.com>; pgsql-hackers
On Fri, Feb 2, 2018 at 3:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> That turned out to produce more than one problem. I find that the
>> select_parallel test then fails like this:
>> ERROR: could not find key 18446744073709486082 in shm TOC at 0x10be98040
>> The fix for that problem seems to be:
>
>> /* Recreate error queues. */
>> error_queue_space =
>> - shm_toc_lookup(pcxt->toc, PARALLEL_KEY_ERROR_QUEUE, false);
>> + shm_toc_lookup(pcxt->toc, PARALLEL_KEY_ERROR_QUEUE, true);
>> + Assert(pcxt->nworkers == 0 || error_queue_space != NULL);
>
> I happened across this patch while preparing the release notes, and
> I'm quite distressed by it, because it completely breaks the point
> of what I'd done in commit d46633506 (to wit, to not just blindly
> assume, nor just Assert, that shm_toc_lookup always succeeds).
> Do you mind if I change that Assert to a run-time test?
Hrm, I guess I could have done something like
shm_toc_lookup(pcxt->toc, PARALLEL_KEY_ERROR_QUEUE, (pcxt->nworkers ==
0)).
I don't mind much if you change it, but I will note that for the
record, before d46633506, we had a theoretical source of bugs, whereas
after that commit, we had a bug. 445dbd82a fixed that; if you change
this around again, please take care not to make it buggy again.
Otherwise, I'll be the one who is quite distressed. :-)
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: ERROR: too many dynamic shared memory segments
@ 2018-02-02 20:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 21+ messages in thread
From: Tom Lane @ 2018-02-02 20:56 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Dilip Kumar <dilipbalaut@gmail.com>; Thomas Munro <thomas.munro@enterprisedb.com>; Jakub Glapa <jakub.glapa@gmail.com>; pgsql-hackers
Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Feb 2, 2018 at 3:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Do you mind if I change that Assert to a run-time test?
> Hrm, I guess I could have done something like
> shm_toc_lookup(pcxt->toc, PARALLEL_KEY_ERROR_QUEUE, (pcxt->nworkers ==
> 0)).
OK, that'd work too. I'll make it so.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* ERROR: too many dynamic shared memory segment
@ 2020-11-17 15:21 Ben Kanouse <kanobt61@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Ben Kanouse @ 2020-11-17 15:21 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org
Hello,
My database is experiencing a 'ERROR: too many dynamic shared memory
segment' error from time to time. It seems to happen most when traffic
is high, and it happens with semi-simple select statements that run a
parallel query plan with a parallel bitmap heap scan. It is currently
on version 12.4.
Here is an example query: https://explain.depesz.com/s/aatA
I did find a message in the archive on this topic and it seems the
resolution was to increase the max connection configuration. Since I
am running my database on heroku I do not have access to this
configuration. It also seems like a bit of an unrelated configuration
change in order to not run into this issue.
Link to archived discussion:
https://www.postgresql.org/message-id/CAEepm%3D2RcEWgES-f%2BHyg4931bOa0mbJ2AwrmTrabz6BKiAp%3DsQ%40ma...
I think this block of code is determining the size for the control segment:
https://github.com/postgres/postgres/blob/REL_13_1/src/backend/storage/ipc/dsm.c#L157-L159
maxitems = 64 + 2 * MaxBackends
It seems like the reason increasing the max connections helps is
because that number is part of the equation for determining the max
segment size.
I noticed in version 13.1 there is a commit that changes the
multiplier from 2 to 5:
https://github.com/postgres/postgres/commit/d061ea21fc1cc1c657bb5c742f5c4a1564e82ee2
maxitems = 64 + 5 * MaxBackends
Should this commit be back-ported to earlier versions of postgres to
prevent this error in other versions?
Thank you,
Ben
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: ERROR: too many dynamic shared memory segment
@ 2020-11-17 21:55 Thomas Munro <thomas.munro@gmail.com>
parent: Ben Kanouse <kanobt61@gmail.com>
0 siblings, 2 replies; 21+ messages in thread
From: Thomas Munro @ 2020-11-17 21:55 UTC (permalink / raw)
To: Ben Kanouse <kanobt61@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Wed, Nov 18, 2020 at 4:21 AM Ben Kanouse <kanobt61@gmail.com> wrote:
> My database is experiencing a 'ERROR: too many dynamic shared memory
> segment' error from time to time. It seems to happen most when traffic
> is high, and it happens with semi-simple select statements that run a
> parallel query plan with a parallel bitmap heap scan. It is currently
> on version 12.4.
>
> Here is an example query: https://explain.depesz.com/s/aatA
Hmm, not sure how this plan could cause the problem, even if running
many copies of it. In the past, this problem has been reported from
single queries that had a very high number of separate Gather nodes,
or very very large parallel hash joins, or, once you've hit the error
a few times those ways, also due to an ancient DSM leak that was fixed
in 93745f1e (fix present in 12.4).
> I noticed in version 13.1 there is a commit that changes the
> multiplier from 2 to 5:
> https://github.com/postgres/postgres/commit/d061ea21fc1cc1c657bb5c742f5c4a1564e82ee2
>
> maxitems = 64 + 5 * MaxBackends
>
> Should this commit be back-ported to earlier versions of postgres to
> prevent this error in other versions?
Yeah, that seems like a good idea anyway. I will do that tomorrow,
barring objections.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: ERROR: too many dynamic shared memory segment
@ 2020-11-17 22:00 Thomas Munro <thomas.munro@gmail.com>
parent: Thomas Munro <thomas.munro@gmail.com>
1 sibling, 0 replies; 21+ messages in thread
From: Thomas Munro @ 2020-11-17 22:00 UTC (permalink / raw)
To: Ben Kanouse <kanobt61@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Wed, Nov 18, 2020 at 10:55 AM Thomas Munro <thomas.munro@gmail.com> wrote:
> a few times those ways, also due to an ancient DSM leak that was fixed
> in 93745f1e (fix present in 12.4).
I take that bit back -- I misremembered -- that was a leak of the
actual memory once slots were exhausted, not a leak of the slot.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: ERROR: too many dynamic shared memory segment
@ 2020-11-19 22:00 Thomas Munro <thomas.munro@gmail.com>
parent: Thomas Munro <thomas.munro@gmail.com>
1 sibling, 0 replies; 21+ messages in thread
From: Thomas Munro @ 2020-11-19 22:00 UTC (permalink / raw)
To: Ben Kanouse <kanobt61@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Wed, Nov 18, 2020 at 10:55 AM Thomas Munro <thomas.munro@gmail.com> wrote:
> > Should this commit be back-ported to earlier versions of postgres to
> > prevent this error in other versions?
>
> Yeah, that seems like a good idea anyway. I will do that tomorrow,
> barring objections.
Done, for 10, 11 and 12.
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2020-11-19 22:00 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-11-27 09:54 ERROR: too many dynamic shared memory segments Jakub Glapa <jakub.glapa@gmail.com>
2017-11-27 10:47 ` Thomas Munro <thomas.munro@enterprisedb.com>
2017-11-27 12:13 ` Jakub Glapa <jakub.glapa@gmail.com>
2017-11-27 18:53 ` Thomas Munro <thomas.munro@enterprisedb.com>
2017-11-27 19:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-11-27 21:05 ` Jakub Glapa <jakub.glapa@gmail.com>
2017-11-27 22:48 ` Thomas Munro <thomas.munro@enterprisedb.com>
2017-11-28 07:32 ` Dilip Kumar <dilipbalaut@gmail.com>
2017-11-28 13:43 ` Robert Haas <robertmhaas@gmail.com>
2017-11-28 14:45 ` Dilip Kumar <dilipbalaut@gmail.com>
2017-11-28 16:58 ` Robert Haas <robertmhaas@gmail.com>
2018-02-02 20:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-02-02 20:19 ` Robert Haas <robertmhaas@gmail.com>
2018-02-02 20:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-12-04 12:18 ` Jakub Glapa <jakub.glapa@gmail.com>
2017-12-07 21:30 ` Thomas Munro <thomas.munro@enterprisedb.com>
2017-12-08 07:59 ` Jakub Glapa <jakub.glapa@gmail.com>
2020-11-17 15:21 ERROR: too many dynamic shared memory segment Ben Kanouse <kanobt61@gmail.com>
2020-11-17 21:55 ` Re: ERROR: too many dynamic shared memory segment Thomas Munro <thomas.munro@gmail.com>
2020-11-17 22:00 ` Re: ERROR: too many dynamic shared memory segment Thomas Munro <thomas.munro@gmail.com>
2020-11-19 22:00 ` Re: ERROR: too many dynamic shared memory segment Thomas Munro <thomas.munro@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox