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

Hi,
yes, sorry, somehow I forgot description of task....

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 selected the same table on remote and local which has ~200M rows and
total size ~20GB. I needed to see all differences because we get some
erratic differences... Estimation from previous limited queries was that
differences are only in approx 1 to 3% rows. So I decided to try to select
them all to look for some patterns...

Testing instance (on Google compute engine) had 4 CPUs, 26 GB of RAM, as
for OOM killer - I used default setting on Debian 8 without any changes -
so

/proc/sys/vm/overcommit_memory = 0

Monitoring done by telegraf on local + influxDB + grafana on other instance.

Nothing else running on that instance, postgresql on instance contained
only this huge table.

After ~25 minutes of run all memory was used and as I mentioned in first
case postgresql crashed and in second test (in which I lowered work_mem
from 24M to 8M and increased a shared_buffers to 8GB to see if it helps)
the whole instance crashed and did not want to start any more. SSD disk
500GB was almost empty so no problems with disk space.

Since I did not have time to fiddle with it I just dropped crashed instance
and used ansible to create a new one.

Thanks

On 6 October 2017 at 16:54, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziomello(at)gmail(dot)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
>