Thread: Postgresql issue: FATAL: dsa_allocate could not find 7 free pages
Hello,
I’m facing an issue on a particular query.
I’m getting this error:
FATAL: dsa_allocate could not find 7 free pages
Some users have faced this issue which seems to be a bug in postgresql query planner which should be solved :
But I’m still facing this issue.
I’m using postgresql 10.5 on ubuntu 18.04
With timescaledb extension (which could be involved in this bug but I couldn’t find any related issue on their side)
This could be related to the number of parallel Bitmap Heat scan. If I reduce the amount of data to analyze, the query works.
One work around is to disable parraller workers with:
set max_parallel_workers_per_gather to 0;
With that changes, the query works as expected and I’ve got this query plan :
I’ve attached the failing query plan and working query plan (with max_parallel_workers_per_gather set to 0)
Thanks for your help
Attachment
On Fri, Oct 26, 2018 at 2:21 AM Alexandre Assouad <alexandre.assouad@gmail.com> wrote: > FATAL: dsa_allocate could not find 7 free pages > Some users have faced this issue which seems to be a bug in postgresql query planner which should be solved : > https://www.postgresql.org/message-id/CAEepm%3D1k7sYJbxoOSJcS-4ti2MHOnBXBfLf%3D-gtuFLTXPqvTDg%40mail.gmail.com Hello Alexandre, Thanks for the report. Yes, that bug does seem like a plausible explanation for that error. The fix was in commit 8ffc3be1, which will be included in 10.6 (target release date: November 8th). It's also in 11.0, out now. If you are able to reproduce the problem easily on a copy of your database, and you have the time/inclination to investigate, is there any chance you could test the query on a local build of REL_10_STABLE (the branch that will produce 10.6 soon), instructions below, or the released v11.0 (if Timescale is available for that, it doesn't look like it)? If not, no worries. > But I’m still facing this issue. > I’m using postgresql 10.5 on ubuntu 18.04 > With timescaledb extension (which could be involved in this bug but I couldn’t find any related issue on their side) It's interesting that reports came from users of Citus and Timescale. There doesn't seem to be any reason to think it's caused by anything these extension are doing, other than just having a lot of data, big queries and the right access pattern to hit that bug. === How to set up a throw-away REL_10_STABLE cluster: On an Ubuntu developer machine, check out, build, install into temporary directory: sudo apt-get install git make gcc flex bison libz-dev libreadline-dev git clone https://github.com/postgres/postgres.git cd postgres git checkout REL_10_STABLE ./configure --prefix=$HOME/tmp_install make -s -j8 && make -s install Initialise and start a database cluster: ~/tmp_install/bin/initdb -D ~/tmp_pgdata ~/tmp_install/bin/postgres -D ~/tmp_pgdata ... now postgres is running in the foreground, until you hit ^C ... do whatever you need to do to install Timescale extension, schema, data, reproduce problem To check that you can reproduce the problem in 10.5 with a server built that way, stop that and: git checkout REL_10_5 make -s clean && make -s -j8 && make -s install ~/tmp_install/bin/postgres -D ~/tmp_pgdata To install Timescale it's probably the instructions from https://github.com/timescale/timescaledb, using ./bootstrap -DPG_CONFIG=~/tmp_install/bin/pgconfig but i haven't tried that myself. (You don't have to run initdb again or reload data when switching between tags/branches in the 10.x series). -- Thomas Munro http://www.enterprisedb.com
Thanks for your response ! I’ll try next week to build a test environment and I’ll send you the results. Does it make any difference to set up a VM vs a dedicated machine ? Thanks for your help ! > Le 26 oct. 2018 à 00:58, Thomas Munro <thomas.munro@enterprisedb.com> a écrit : > > On Fri, Oct 26, 2018 at 2:21 AM Alexandre Assouad > <alexandre.assouad@gmail.com> wrote: >> FATAL: dsa_allocate could not find 7 free pages > >> Some users have faced this issue which seems to be a bug in postgresql query planner which should be solved : >> https://www.postgresql.org/message-id/CAEepm%3D1k7sYJbxoOSJcS-4ti2MHOnBXBfLf%3D-gtuFLTXPqvTDg%40mail.gmail.com > > Hello Alexandre, > > Thanks for the report. Yes, that bug does seem like a plausible > explanation for that error. The fix was in commit 8ffc3be1, which > will be included in 10.6 (target release date: November 8th). It's > also in 11.0, out now. > > If you are able to reproduce the problem easily on a copy of your > database, and you have the time/inclination to investigate, is there > any chance you could test the query on a local build of REL_10_STABLE > (the branch that will produce 10.6 soon), instructions below, or the > released v11.0 (if Timescale is available for that, it doesn't look > like it)? If not, no worries. > >> But I’m still facing this issue. >> I’m using postgresql 10.5 on ubuntu 18.04 >> With timescaledb extension (which could be involved in this bug but I couldn’t find any related issue on their side) > > It's interesting that reports came from users of Citus and Timescale. > There doesn't seem to be any reason to think it's caused by anything > these extension are doing, other than just having a lot of data, big > queries and the right access pattern to hit that bug. > > === How to set up a throw-away REL_10_STABLE cluster: > > On an Ubuntu developer machine, check out, build, install into > temporary directory: > sudo apt-get install git make gcc flex bison libz-dev libreadline-dev > git clone https://github.com/postgres/postgres.git > cd postgres > git checkout REL_10_STABLE > ./configure --prefix=$HOME/tmp_install > make -s -j8 && make -s install > > Initialise and start a database cluster: > ~/tmp_install/bin/initdb -D ~/tmp_pgdata > ~/tmp_install/bin/postgres -D ~/tmp_pgdata > ... now postgres is running in the foreground, until you hit ^C > ... do whatever you need to do to install Timescale extension, schema, > data, reproduce problem > > To check that you can reproduce the problem in 10.5 with a server > built that way, stop that and: > git checkout REL_10_5 > make -s clean && make -s -j8 && make -s install > ~/tmp_install/bin/postgres -D ~/tmp_pgdata > > To install Timescale it's probably the instructions from > https://github.com/timescale/timescaledb, using ./bootstrap > -DPG_CONFIG=~/tmp_install/bin/pgconfig but i haven't tried that > myself. > > (You don't have to run initdb again or reload data when switching > between tags/branches in the 10.x series). > > -- > Thomas Munro > http://www.enterprisedb.com
On Fri, Oct 26, 2018 at 9:17 PM Alexandre Assouad <alexandre.assouad@gmail.com> wrote: > I’ll try next week to build a test environment and I’ll send you the results. > Does it make any difference to set up a VM vs a dedicated machine ? Thanks. Probably not, the important thing is probably the same data, settings (work_mem etc) and query. Maybe start with a build of REL_10_5 and try to reproduce the problem, and if you can get that to happen reliably, then switch to a build of REL_10_STABLE to confirm that the problem goes away? -- Thomas Munro http://www.enterprisedb.com