Обсуждение: Out of memory...
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 restartingautomatically 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
"Cassiano, Marco" <mcassiano@manord.com> wrote: > There was an autovaccum running on a big table saying it was "to > avoid xid wraparound" > 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 Are those the only settings changed from their defaults? If not, or you're not sure, please post the output from running the query on this page: http://wiki.postgresql.org/wiki/Server_Configuration -Kevin
Thank you Kevin, here is the result of the query : "version";"PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit" "archive_command";"/usr/local/bin/walscript.sh %p %f" "archive_mode";"on" "autovacuum";"on" "autovacuum_max_workers";"7" "autovacuum_naptime";"10min" "autovacuum_vacuum_cost_delay";"20ms" "autovacuum_vacuum_cost_limit";"200" "bytea_output";"escape" "checkpoint_completion_target";"0.8" "checkpoint_segments";"32" "checkpoint_timeout";"10min" "client_encoding";"UNICODE" "client_min_messages";"warning" "debug_pretty_print";"off" "debug_print_parse";"off" "debug_print_plan";"off" "debug_print_rewritten";"off" "default_statistics_target";"200" "effective_cache_size";"5870MB" "fsync";"off" "full_page_writes";"off" "lc_collate";"en_US.LATIN1" "lc_ctype";"en_US.LATIN1" "listen_addresses";"*" "log_autovacuum_min_duration";"1s" "log_checkpoints";"on" "log_connections";"off" "log_destination";"stderr,syslog" "log_directory";"/var/logs" "log_disconnections";"off" "log_duration";"off" "log_error_verbosity";"default" "log_filename";"postgresql-%Y-%m-%d_%H%M%S.log" "log_line_prefix";"%t %r %u %d %x %c - " "log_min_duration_statement";"1min" "log_min_error_statement";"error" "log_min_messages";"error" "log_rotation_age";"1d" "log_rotation_size";"0" "log_statement";"none" "log_truncate_on_rotation";"off" "logging_collector";"on" "maintenance_work_mem";"300MB" "max_connections";"250" "max_stack_depth";"2MB" "max_wal_senders";"5" "port";"5432" "random_page_cost";"2" "server_encoding";"LATIN1" "shared_buffers";"1GB" "silent_mode";"on" "superuser_reserved_connections";"6" "synchronous_commit";"off" "syslog_facility";"local2" "syslog_ident";"postgres" "temp_buffers";"8MB" "TimeZone";"Europe/Rome" "track_activities";"on" "track_counts";"on" "vacuum_cost_delay";"0" "vacuum_cost_limit";"200" "wal_buffers";"16MB" "wal_keep_segments";"10" "wal_level";"hot_standby" "work_mem";"12MB ________________________________ Da: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Inviato: ven 17/02/2012 22.00 A: Cassiano, Marco; pgsql-admin@postgresql.org Oggetto: Re: [ADMIN] Out of memory... "Cassiano, Marco" <mcassiano@manord.com> wrote: > There was an autovaccum running on a big table saying it was "to > avoid xid wraparound" > 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 Are those the only settings changed from their defaults? If not, or you're not sure, please post the output from running the query on this page: http://wiki.postgresql.org/wiki/Server_Configuration -Kevin
"Cassiano, Marco" <mcassiano@manord.com> wrote: > "autovacuum_max_workers";"7" > "autovacuum_naptime";"10min" > "autovacuum_vacuum_cost_delay";"20ms" > "autovacuum_vacuum_cost_limit";"200" You've made autovacuum a little less aggressive for small, heavily-updated tables with the 10min naptime, even with 7 workers. That's some cause for concern, but probably not the immediate problem. > "fsync";"off" > "full_page_writes";"off" With those settings, be prepared to go to your last known good backup if the OS or hardware crashes. > "maintenance_work_mem";"300MB" Each of your seven autovacuum processes can be using that, for 2.1GB in autovacuum work space. > "max_connections";"250" > "temp_buffers";"8MB" > "work_mem";"12MB If you had all connections busy, using temp tables and doing sorts or hash tables, you could hit 5GB or more in space for that. On top of the shared_buffers and maintenance work memory, you could hit 8GB or more in RAM usage pretty easily. You might want to consider a connection pool to allow the 250 client connections to be limited to a pool of 20 or 30 database connections on the server side. Are you using the INET data type? There is a bug introduced in 9.1.2 which can leak memory when using that type. If that's your problem, you might want to fall back to 9.1.1 and/or install 9.1.3 as soon as it becomes available. -Kevin