On Fri, Oct 6, 2017 at 8:24 AM, <
josef.machytka@gmail.com> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 14845
> Logged by: Josef Machytka
> Email address:
josef.machytka@gmail.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