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.