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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
