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

From chenhj
Subject Re: OOM-killer issue when updating a inheritance table which has large number of child tables
Date
Msg-id 150d941a.5648.14c25b913fb.Coremail.chjischj@163.com
Whole thread Raw
In response to Re: OOM-killer issue when updating a inheritance table which has large number of child tables  (David Fetter <david@fetter.org>)
List pgsql-hackers

>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.
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.
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.
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.
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.
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.
Client 3 aborted in state 0. Probably the backend died while processing.
Client 6 aborted in state 0. Probably the backend died while processing.
Client 1 aborted in state 0. Probably the backend died while processing.
Client 5 aborted in state 0. Probably the backend died while processing.
Client 8 aborted in state 0. Probably the backend died while processing.
Client 9 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.
Client 7 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.
Client 4 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.
Client 2 aborted in state 0. Probably the backend died while processing.
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
duration: 2 s
number of transactions actually processed: 0
tps = 0.000000 (including connections establishing)
tps = 0.000000 (excluding connections establishing)
statement latencies in milliseconds:
0.000000 update maintb set name = 'aaaaa12345' where id=1;

Best Regards,
Chen Huajun
Attachment

pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: get_object_address support for additional object types
Next
From: Shigeru Hanada
Date:
Subject: Re: Custom/Foreign-Join-APIs (Re: [v9.5] Custom Plan API)