Daniel said:
>It's plausible that, with only 4GB of RAM, the table that fails
to dump has some very large rows that can't be allocated, especially
since both the backend and pg_dump need to have it simultaneously
in memory.
> >pg_dump: The command was: COPY public.docfile (docfile_pkey,
> >docfileoriginalname, ordernumber, versionnum, docfilecontents,
>> docfilepath, docfileextension, enddatetime, endby, editnum, insby,
>> insdatetime, modby, moddatetime, active, doc_fkey) TO stdout;
>The "docfilecontents" column suggests that it might contain
large contents. If its type is bytea, it's going to be expanded
to twice its size to build the hex representation.
>You may get a sense on how big is the biggest row expressed
as text with this query:
SELECT max(length(contents.*::text)) FROM public.docfile;
>If it's big enough that it might cause the OOM issue,
try to run pg_dump remotely through an SSH tunnel [1], which you
can already do in terms of network permissions since you log in with
SSH, so pg_dump itself does not use any memory on the server.
>Also, if the machine doesn't have swap space, it might be
that just adding a few GB's of swap would make the operation
succeed.
to dump has some very large rows that can't be allocated, especially
since both the backend and pg_dump need to have it simultaneously
in memory.
> >pg_dump: The command was: COPY public.docfile (docfile_pkey,
> >docfileoriginalname, ordernumber, versionnum, docfilecontents,
>> docfilepath, docfileextension, enddatetime, endby, editnum, insby,
>> insdatetime, modby, moddatetime, active, doc_fkey) TO stdout;
>The "docfilecontents" column suggests that it might contain
large contents. If its type is bytea, it's going to be expanded
to twice its size to build the hex representation.
>You may get a sense on how big is the biggest row expressed
as text with this query:
SELECT max(length(contents.*::text)) FROM public.docfile;
>If it's big enough that it might cause the OOM issue,
try to run pg_dump remotely through an SSH tunnel [1], which you
can already do in terms of network permissions since you log in with
SSH, so pg_dump itself does not use any memory on the server.
>Also, if the machine doesn't have swap space, it might be
that just adding a few GB's of swap would make the operation
succeed.
This appears to be the case. I ran:
SELECT max(length(docfilecontents::text)) FROM docfile;
and after a very long time, got:
ERROR: invalid memory alloc request size 1636085512
SQL state: XX000Adrian said:
>> Ok, thanks for explaining this. Here is the current value:
>> "shared_buffers""131072""8kB"
>It should be a single value something like this for the default:
>shared_buffers = 128MB
>> "shared_buffers""131072""8kB"
>It should be a single value something like this for the default:
>shared_buffers = 128MB
The results I pasted were from:
SELECT * FROM pg_settings
Tom said:
Maybe I didn't get it the right way.
The system has only 4GB of RAM. I read that a reasonable swap size is 1/4 of RAM, so I've created a swap file of 1GB.
Tom said:
>> Ok, thanks for explaining this. Here is the current value:
>> "shared_buffers" "131072" "8kB"
>Well, that's 1GB, which might be ambitious inside a VM with a hard
restriction to 4GB total RAM. Postgres can get by with a *lot* less.
>Try knocking it down to a tenth of that and see if it makes a difference
>> "shared_buffers" "131072" "8kB"
>Well, that's 1GB, which might be ambitious inside a VM with a hard
restriction to 4GB total RAM. Postgres can get by with a *lot* less.
>Try knocking it down to a tenth of that and see if it makes a difference
I think I also based this on a rule-of-thumb that it should be no more than 25% of RAM. Should I test pg_dump with the added VM before reducing shared_buffers?
On Mon, Nov 5, 2018 at 10:13 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 11/5/18 7:04 AM, Charles Martin wrote:
> Adrian said:
>
> >Tom was referring to this from your previous post:
> >(postmaster) total-vm:3068900kB,
> >where vm(VM) is Virtual Memory:
> >https://stackoverflow.com/questions/18845857/what-does-anon-rss-and-total-vm-mean#22326766
> >So what is your shared_buffers:
> >https://www.postgresql.org/docs/10/static/runtime-config-resource.html
> >set to?
>
> Ok, thanks for explaining this. Here is the current value:
> "shared_buffers""131072""8kB"
>
It should be a single value something like this for the default:
shared_buffers = 128MB
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
