请教POSTGRESQL 9.1.6 执行大量inesrt的时候内存溢出的问题

[letheanwater]

请教一下，postgresql在执行大量的空间查询和insert的时候，内存溢出导致server progress crash。
尝试加大share buffer时，超过1G，就会无法启动postgresql

抛错日志如下。。
SPI TupTable: 8192 total in 1 blocks; 6824 free (0 chunks); 1368 used
SPI TupTable: 8192 total in 1 blocks; 6824 free (0 chunks); 1368 used
SPI TupTable: 8192 total in 1 blocks; 6824 free (0 chunks); 1368 used
MessageContext: 24576 total in 2 blocks; 19128 free (6 chunks); 5448 used
Operator class cache: 8192 total in 1 blocks; 4872 free (0 chunks); 3320 used
smgr relation table: 8192 total in 1 blocks; 760 free (0 chunks); 7432 used
TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0 chunks); 16 used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 8192 total in 1 blocks; 7768 free (1 chunks); 424 used
PortalHeapMemory: 7168 total in 3 blocks; 1640 free (0 chunks); 5528 used
ExecutorState: 32832 total in 3 blocks; 5384 free (1 chunks); 27448 used
LOG: server process (PID 11452) was terminated by exception 0xC0000005
HINT: See C include file "ntstatus.h" for a description of the hexadecimal value.
LOG: terminating any other active server processes
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 e
xit, 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 e
xit, 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.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2014-07-17 13:46:05 HKT
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 1/D104A18
LOG: record with zero length at 1/D10C980
LOG: redo done at 1/D10C940
LOG: last completed transaction was at log time 2014-07-17 13:57:03.486+08
LOG: database system is ready to accept connections
LOG: autovacuum launcher started

还有一种日志是在sql查询窗口会出现out of memory的错误。

还请牛人不吝指教。

[小笨和漂向北方]

From the log you posted, I can't tell there was a OOM or stack overflow. server process (PID 11452) was terminated by exception 0xC0000005 Is your PG on a Windows machine ( "ntstatus.h")? 0xC0000005 is segmentation fault. There was a memory access violation. What are the version number of ur PG and PostGIS? if you were mainly doing insert, shared_buffer may not help too much since it's for caching data. shared_buffer along with other param, such as work_mem, however, could impact other db operations. Since you were doing spatial queries, the implementation of Postgis should be examined as well. Why PG can't do buffer over 1GB, I have no idea. How much ram do you have on that machine? Speak of speeding up insert, there are just too many factors that could potentially affect the performance of insertion remove index, drop FK, prefer copy over insert, use transaction to delay fsync. Of course, it's also related to hardware, such as iops, amount of ram, One more thing: don't run postgresql against windows...

[iihero]

加大share buffer时，超过1G，就会无法启动postgresql? 你的pg跑在哪个操作系统下边? 物理配置 是多少?