Обсуждение: [BUGS] BUG #14845: postgres_fdw and mysql_fdw can cause crash of instance

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.

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? This behavior seems to not depend on work_mem
setting...

Thanks
Josef

(Config: postgresql 9.6.5, latest versions of both fdw, Debian 8)




--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Fabrízio de Royes Mello <fabriziomello@gmail.com> writes:
>> 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.

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

Indeed.  I can't say about mysql_fdw, but postgres_fdw is only supposed to
fetch 100 rows at a time (cf fetch_size option), so it shouldn't run out
of memory just because the amount of data to be fetched is large.

One idea is that, because the planner lacks information about the remote
table, it picks some stupid plan that ends up blowing out memory.  In that
case, perhaps turning on use_remote_estimate would help.  But we're just
guessing here due to lack of useful detail.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Josef Machytka <josef.machytka@gmail.com> writes:
> In this case which went wrong I used postgres_fdw to compare data on local
> and remote database using "select all from remote except select all from
> local".

I think my theory B probably applies then: without use_remote_estimate,
the planner would not guess that the remote table is huge, and that could
well allow it to pick a hashed EXCEPT implementation --- which will then
try to collect the entire remote table into an in-memory hash table.

I don't know whether mysql_fdw has a comparable switch, but in any case
if it wasn't turned on then the same failure mode could apply to that FDW.

I wonder if we should rethink the default setting of use_remote_estimate
being "off".  Particularly as we keep on allowing postgres_fdw to consider
more different plan types, it seems like the potential for stupid plan
choices is getting worse all the time.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs