dsa_allocate() faliure

From: Rick Otten <rottenwindfish(at)gmail(dot)com>
To: "pgsql-performa(dot)" <pgsql-performance(at)postgresql(dot)org>
Subject: dsa_allocate() faliure
Date: 2018-01-29 16:19:43
Message-ID: CAMAYy4+w3NTBM5JLWFi8twhWK4=k_5L4nV5+bYDSPu8r4b97Zg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

I'm wondering if there is anything I can tune in my PG 10.1 database to
avoid these errors:

$ psql -f failing_query.sql
psql:failing_query.sql:46: ERROR: dsa_allocate could not find 7 free pages
CONTEXT: parallel worker

I tried throttling back the number of parallel workers to just 2, that
didn't help.

The query is joining two views that each have 50 or so underlying queries,
unioned, in them. Unfortunately due to an invalid index, it is sequence
scanning some of the tables. I can't fix the indexes until a few create
materialized view commands that are currently running (and have been
running for 6 days) finish or I kill them, because they are holding a lock
that is blocking any attempt to reindex.

So that leaves me looking for some tunable (hopefully one that doesn't
require a restart) which will fix this by adding sufficient resources to
the system to allow the dsa_allocate() to find enough (contiguous?) pages.
My system seems to have plenty of extra capacity.

There was a thread on pghackers in December where someone else was seeing a
similar error, but couldn't reproduce it consistently. I've run the above
query hundreds of times over the last 24 hours, but just the one fails when
I select just the right parameters - and fails every time I run it with
those parameters.

In that thread someone speculated it had to do with running many parallel
bitmap heap scans in one query. I count 98 in the query plan.

I'm hoping there is a "magic X tunable" which I just need to bump up a
little to let queries like this run without the fatal failure.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Chapman Flack 2018-01-29 16:23:24 Re: [HACKERS] MERGE SQL Statement for PG11
Previous Message Simon Riggs 2018-01-29 16:13:38 Re: [HACKERS] MERGE SQL Statement for PG11

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2018-01-29 16:37:09 Re: dsa_allocate() faliure
Previous Message Mariel Cherkassky 2018-01-29 10:30:13 PostgreSQL 10.1 partitions and indexes