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

Поиск
Список
Период
Сортировка
От Josef Machytka
Тема Re: [BUGS] BUG #14845: postgres_fdw and mysql_fdw can cause crash of instance
Дата
Msg-id CAGvVEFtBaaDESMoBzAzrB+b5AWVg2QJFy50-tgfRCDus+92ZKg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [BUGS] BUG #14845: postgres_fdw and mysql_fdw can cause crash of instance  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: [BUGS] BUG #14845: postgres_fdw and mysql_fdw can cause crash of instance
Список pgsql-bugs
Дерево обсуждения
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@sss.pgh.pa.us> wrote:
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

В списке pgsql-bugs по дате отправления: