Re: BUG #14845: postgres_fdw and mysql_fdw can cause crash of instance

On Fri, Oct 6, 2017 at 8:24 AM, <josef(dot)machytka(at)gmail(dot)com> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 14845
> Logged by: Josef Machytka
> Email address: josef(dot)machytka(at)gmail(dot)com
> PostgreSQL version: 9.6.5
> Operating system: Debian 8
> Description:
>
> Hi,
> I work from time to time with postgres_fdw and mysql_fdw and they both
seem
> to have the same problem - they use too much memory if I query huge remote
> tables.
>
> Since I have seen this behavior before on our monitoring I decided to test
> it on testing instance with really huge amount of remote data which would
> not fit into memory.
>
> Behavior was as expected - postgesql started to use more and more memory
> until the whole available memory was used.
>
> After that in first case postgresql crashed (which was more or less OK
> because it restarted) but in second case the whole Debian instance crashed
> and did not want to start again even after repeated attempts. I did not
> fiddle with it and just recreated it but I even do not want to imagine if
it
> would happen on some other hugely used instance.
>

OOMKiller?? What about your overcommit settings?

>
> This behavior limits usage of those fdw very significantly. I cannot allow
> other people to use foreign tables on really huge tables we have and I
have
> to check sizes before I use some query which is expected to be "heavy"...
Of
> course I would love to hear from someone that I should not use "heavy
> queries"... :-)
>
> So is there a way to for example force fdw to swap data into some temp
file
> and not use so much memory?

Would be nice if you can provide to us a reproducible test case, so we can
try to figure out what's happen...

>
> This behavior seems to not depend on work_mem
> setting...
>

No, work_mem is used for sorting purposes.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello