Thread: BUG #15721: FATAL: dsa_allocate could not find 97 free pages

BUG #15721: FATAL: dsa_allocate could not find 97 free pages

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      15721
Logged by:          Chris Nestrud
Email address:      ccn@chrisnestrud.com
PostgreSQL version: 11.2
Operating system:   Scientific Linux release 7.4
Description:

I have a Postgres server running version 11.2 which occasionally fails to
run a query with an error similar to:

dsa_allocate could not find 97 free pages

This seems to happen when there are several long-running queries using the
same table which contains around 60,000,000 rows and 30 partitions.

This instance constantly receives data in staging tables, inserts from
staging to partitions, finds statistics which are sent elsewhere, and drops
the partitions when they are no longer needed.

This instance is temporary and is located on a ram disk.

Configuration values changed from defaults are:

shared_buffers = 32GB
work_mem = 32MB
maintenance_work_mem = 512MB
wal_level = minimal
synchronous_commit = off
full_page_writes = off
max_wal_size = 8GB
max_wal_senders = 0
enable_partitionwise_join = on
enable_partitionwise_aggregate = on
effective_cache_size = 128GB

This is an infrequent error. The server will run for several days,
constantly running queries such as those shown below, and only occasionally
show this error.

Queries are of the form:

select a_a, count(*) as a_count
from t
where c_time >= 'yy-mm-dd hh:mm:ss'
and c_time < 'yy-mm-dd hh:mm:ss'
group by a_a
order by a_count desc;

and

select f, sum(f_count) as f_count
        from (
        select a_a as f, count(*) as f_count
        from t
        where c_time>= 'yyyy-mm-dd hh:mm:ss'
and c_time < 'yyyy-mm-dd hh:mm:ss'
        and function_with_indexed_result(a_a)
        group by a_a
        union all
        select b_b as f, count(*) as f_count
        from t
        where c_time>= 'yyyy-mm-dd hh:mm:ss'
and c_time < 'yyyy-mm-dd hh:mm:ss'
        and function_with_indexed_result(b_b)
        group by b_b
        ) t1
        group by f
        order by sum(f_count) desc;

Column a_a is inet
Column b_b is inet
Column c_time is timestamp with time zone

There are usually 3 or 4 queries of this type running simultaneously, along
with other queries.


Re: BUG #15721: FATAL: dsa_allocate could not find 97 free pages

From
Sergei Kornilov
Date:
Hello

Thank you for report. This bug should be already fixed in stable branch and will be shipped in next minor release.
Untilthe fix is released, the simplest workaround is to disable parallel query with SET max_parallel_workers_per_gather
=0 if you're affected by the bug.
 
It's a rare timing bug, but some workloads seem to be more prone to it, so unfortunately it went undetected for a long
time.

If you're interested, related discussion was here:
https://www.postgresql.org/message-id/flat/CAMAYy4%2Bw3NTBM5JLWFi8twhWK4%3Dk_5L4nV5%2BbYDSPu8r4b97Zg%40mail.gmail.com

regards, Sergei