From: | "Cassiano, Marco" <mcassiano(at)manord(dot)com> |
---|---|
To: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | Out of memory... |
Date: | 2012-02-17 15:41:52 |
Message-ID: | BC53C974C3B9E542BC0A9BD4C5B1168A0E9326E0@NEWMAIL.manord.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi all,
This morning my db experienced 10 minutes of "out of memory" condition with the log filled up of messages like :
TopMemoryContext: 90856 total in 13 blocks; 7936 free (6 chunks); 82920 used
TopTransactionContext: 24576 total in 2 blocks; 21360 free (15 chunks); 3216 used
TOAST to main relid map: 24576 total in 2 blocks; 11872 free (5 chunks); 12704 used
AV worker: 24576 total in 2 blocks; 19312 free (9 chunks); 5264 used
Autovacuum Portal: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
Vacuum: 8192 total in 1 blocks; 8080 free (0 chunks); 112 used
Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
smgr relation table: 24576 total in 2 blocks; 13920 free (4 chunks); 10656 used
TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used
Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
PortalMemory: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 24576 total in 2 blocks; 13872 free (3 chunks); 10704 used
CacheMemoryContext: 817840 total in 20 blocks; 151408 free (2 chunks); 666432 used
mmfg.cc_records_2_mmfg_nome_file: 2048 total in 1 blocks; 776 free (0 chunks); 1272 used
mmfg.cc_records_1_societa,cliente,anno,stagione,collezione,clas: 2048 total in 1 blocks; 224 free (0 chunks); 1824 used
cc_records_pkey: 2048 total in 1 blocks; 224 free (0 chunks); 1824 used
pg_index_indrelid_index: 2048 total in 1 blocks; 728 free (0 chunks); 1320 used
..
pg_authid_rolname_index: 3072 total in 2 blocks; 1768 free (4 chunks); 1304 used
MdSmgr: 8192 total in 1 blocks; 7968 free (0 chunks); 224 used
LOCALLOCK hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used
Postmaster: 57344 total in 3 blocks; 48640 free (331 chunks); 8704 used
ErrorContext: 8192 total in 1 blocks; 8160 free (4 chunks); 32 used
2012-02-17 11:08:47 CET 0 4f3e272f.65db - ERROR: out of memory
2012-02-17 11:08:47 CET 0 4f3e272f.65db - DETAIL: Failed on request of size 16731918.
2012-02-17 11:08:47 CET 0 4f3e272f.65db - CONTEXT: automatic vacuum of table "mdn.mmfg.cc_records"
2012-02-17 11:08:53 CET 0 4f3e272f.65db - LOG: automatic vacuum of table "mdn.mmfg.mo7_records": index scans: 0
OR
TopMemoryContext: 164632 total in 22 blocks; 12328 free (44 chunks); 152304 used
TopTransactionContext: 8192 total in 1 blocks; 7328 free (0 chunks); 864 used
RI compare cache: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used
RI query cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used
TableSpace cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used
Type information cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used
Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used
MessageContext: 524288 total in 7 blocks; 48672 free (6 chunks); 475616 used
Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
smgr relation table: 57344 total in 3 blocks; 17872 free (10 chunks); 39472 used
TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used
Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
PortalHeapMemory: 1024 total in 1 blocks; 824 free (0 chunks); 200 used
ExecutorState: 122880 total in 4 blocks; 8152 free (2 chunks); 114728 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 3170352 total in 10 blocks; 304 free (5 chunks); 3170048 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
..
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
AggContext: 8192 total in 1 blocks; 8016 free (0 chunks); 176 used
TupleHashTable: 253952 total in 5 blocks; 119344 free (16 chunks); 134608 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 24576 total in 2 blocks; 5552 free (3 chunks); 19024 used
CacheMemoryContext: 4618048 total in 36 blocks; 986456 free (5 chunks); 3631592 used
AND ALSO
movcor.movcor_pf_dett_1_mod,var: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
CachedPlan: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
CachedPlanSource: 3072 total in 2 blocks; 1832 free (1 chunks); 1240 used
SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used
CachedPlan: 3072 total in 2 blocks; 768 free (0 chunks); 2304 used
..
..
CachedPlanSource: 3072 total in 2 blocks; 1968 free (2 chunks); 1104 used
SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used
CachedPlan: 1024 total in 1 blocks; 176 free (0 chunks); 848 used
CachedPlanSource: 3072 total in 2 blocks; 1736 free (1 chunks); 1336 used
AND
2012-02-17 11:19:31 CET 0 4f05cb33.1c0d - LOG: could not fork new process for connection: Cannot allocate memory
There was an autovaccum running on a big table saying it was "to avoid xid wraparound"
It happened only once till this morning, and that time Postgresql finally crashed closing all the connections and restarting automatically with a recovery.
My configuration is :
Postgresql 9.1.2 compiled and running on Redhat 5 64 bit
DB Size : about 100 GB
RAM 5 GB
Shared Buffers 1 GB
temp_buffers = 8MB
work_mem = 12MB
maintenance_work_mem = 300MB
Could you help to understand
1) How can I understand what is filling up memory ?
2) How can I avoid (or limit) this kind of situations ?
Thank you very much!
Marco
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Crawford | 2012-02-17 18:00:36 | Re: Big difference in databasesize compared with disksize |
Previous Message | Jan Nielsen | 2012-02-17 15:37:22 | Monitor without superuser? |