Обсуждение: OOM-killer issue when updating a inheritance table which has large number of child tables

Поиск

Список

Период

Сортировка

OOM-killer issue when updating a inheritance table which has large number of child tables

От

chenhj

Дата:

12 марта 2015 г., 13:56:12

In my test(PG9.3.4), i found when update a parent table which has a large number of child tables, the execute plan will consume lots of memory. And possibly cause OOM.

For example:

create table maintb(id int,name char(10));

create table childtb_1 (CHECK ( id BETWEEN 1 AND 200)) inherits(maintb);

create table childtb_2 (CHECK ( id BETWEEN 201 AND 400)) inherits(maintb);

...

create table childtb_n ...

When there are 100 child tables,the following update statement will consume about 8MB memory when invoking pg_plan_queries()

update maintb set name = 'aaaaa12345' where id=1;

And, when there are 1000 child tables,the same update statement will consume 717MB memory when invoking pg_plan_queries().

Does this a known problem, and could that be improved in the future?

BTW:

The following comment is according my debuging when update the parent table with 1000 child tables

src/backend/optimizer/plan/planner.c

static Plan *

inheritance_planner(PlannerInfo *root)

{

...

foreach(lc, root->append_rel_list)//### loop 1001 time

{

...

subroot.parse = (Query *)

adjust_appendrel_attrs(root,

(Node *) parse,

appinfo);//### allocate about 300KB memory a time.

...

subroot.append_rel_list = (List *) copyObject(root->append_rel_list);//### allocate about 400KB memory a time.

...

}

...

}

Best Regards

Chen Huajun

Re: OOM-killer issue when updating a inheritance table which has large number of child tables

От

David Fetter

Дата:

12 марта 2015 г., 16:38:55

On Thu, Mar 12, 2015 at 06:55:48PM +0800, chenhj wrote:
> Hi
> 
> In my test(PG9.3.4), i found when update a parent table which has a
> large number of child tables, the execute plan will consume lots of
> memory. And possibly cause OOM.

At the moment, partitioning into thousands of tables is not supported.

If you can reproduce the problem in PostgreSQL 9.3.6, or whichever
happens to be the most recent minor version by the time you do the
test, that will help.

Just generally, it helps to provide a complete test case which
reproduces the problem if at all possible.

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: OOM-killer issue when updating a inheritance table which has large number of child tables

От

Tom Lane

Дата:

12 марта 2015 г., 17:00:56

chenhj <chjischj@163.com> writes:
> In my test(PG9.3.4), i found when update a parent table which has a large number of child tables, the execute plan
willconsume lots of memory. And possibly cause OOM.
 

See
file:///net/sss1/home/postgres/pgsql/doc/src/sgml/html/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS

particularly the last paragraph.
        regards, tom lane

Re: OOM-killer issue when updating a inheritance table which has large number of child tables

От

Tom Lane

Дата:

12 марта 2015 г., 23:07:47

Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> See
>> file:///net/sss1/home/postgres/pgsql/doc/src/sgml/html/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS

> Or perhaps, if you're on the Internet, this instead:
> http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS

Ooops, pasted a link to my local copy.  Sorry bout that ...
        regards, tom lane

Re: OOM-killer issue when updating a inheritance table which has large number of child tables

От

Stephen Frost

Дата:

12 марта 2015 г., 23:10:33

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> chenhj <chjischj@163.com> writes:
> > In my test(PG9.3.4), i found when update a parent table which has a large number of child tables, the execute plan
willconsume lots of memory. And possibly cause OOM. 
>
> See
> file:///net/sss1/home/postgres/pgsql/doc/src/sgml/html/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS
>
> particularly the last paragraph.

Or perhaps, if you're on the Internet, this instead:

http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS

:)

Thanks,
Stephen

Re: OOM-killer issue when updating a inheritance table which has large number of child tables

От

chenhj

Дата:

17 марта 2015 г., 06:14:14

>At the moment, partitioning into thousands of tables is not supported.

Thank you for your reply. And thanks Tom Lane and Stephen Frost！

The following(with createsql.sql and update.sql as attachment) is my complete test case. And i reproduced this problem in PostgreSQL 9.4.1 .

1)create table and data

createdb db1000

psql -q -v total=10000000 -v pnum=1000 -f createsql.sql |psql db1000

psql -c "insert into maintb values(1,'abcde12345')" db1000

2)update the parent table with one connection, 955MB memory has been used.

[chenhj@node2 part]$ pgbench -c 1 -n -T 10 -r -f update.sql db1000;

transaction type: Custom query

scaling factor: 1

query mode: simple

number of clients: 1

number of threads: 1

duration: 10 s

number of transactions actually processed: 20

tps = 1.933407 (including connections establishing)

tps = 1.934807 (excluding connections establishing)

statement latencies in milliseconds:

516.836800 update maintb set name = 'aaaaa12345' where id=1;

part of output from "top" when runing pgbench:

...

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

22537 chenhj 20 0 955m 667m 11m R 99.4 33.3 0:06.12 postgres

3)update the parent table with ten connections simultaneously, OOM ocurrs.

Now,to run pgbench 955MB * 10 memory are needed,but my machine only has 2GB physical memory and 4GB Swap.

[chenhj@node2 part]$ pgbench -c 10 -n -T 2 -r -f update.sql db1000;

Client 0 aborted in state 0. Probably the backend died while processing.

WARNING: terminating connection because of crash of another server process

DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT: In a moment you should be able to reconnect to the database and repeat your command.