From: | chenhj <chjischj(at)163(dot)com> |
---|---|
To: | "David Fetter" <david(at)fetter(dot)org> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: OOM-killer issue when updating a inheritance table which has large number of child tables |
Date: | 2015-03-17 03:13:48 |
Message-ID: | 150d941a.5648.14c25b913fb.Coremail.chjischj@163.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)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.836800update 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(at)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.000000update maintb set name = 'aaaaa12345' where id=1;
Best Regards,
Chen Huajun
Attachment | Content-Type | Size |
---|---|---|
createsql.sql | application/octet-stream | 1.0 KB |
update.sql | application/octet-stream | 49 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Kouhei Kaigai | 2015-03-17 04:58:21 | Re: One question about security label command |
Previous Message | Alvaro Herrera | 2015-03-17 03:01:28 | Re: [HACKERS] get_object_address support for additional object types |