From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of TonyS Sent: Wednesday, April 01, 2015 12:15 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Would like to know how analyze works technically
On Wed, April 1, 2015 9:35 am, Kevin Grittner-5 [via PostgreSQL] wrote: > > > TonyS <[hidden email]</user/SendEmail.jtp?type=node&node=5844292&i=0>> wrote: > > >> The postgresql log has these entries at the crash point: >> 2015-04-01 06:24:37 EDT LOG: server process (PID 1384) was terminated >> by signal 9: Killed 2015-04-01 06:24:38 EDT DETAIL: Failed process was >> running: analyze verbose; >> > > That was almost certainly the action of the OS's Out Of Memory > Killer process. > > >> Is there anything else that would be helpful? >> > > Unfortunately, leaving the OOM killer enabled causes the best > evidence to be destroyed. If you disable the OOM killer and run this > again, when memory is exhausted the database process attempting to > allocate memory will dump a map of where its memory was allocated. That > should give us something to work with regarding the cause. Try: > > vm.overcommit_memory = 2 vm.overcommit_ratio = 80 > > Also, it would be useful to see the output of this: > > > SELECT name, current_setting(name), source > FROM pg_settings > WHERE source NOT IN ('default', 'override'); > > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > Thanks for the follow up Kevin. I made the changes to overcommit and started running the analyze operation again about 2 hours ago, so if it stays true to form, it should be crashing in about 5-1/2 hours. The output from the query you suggested is: name,current_setting,source autovacuum,off,configuration file checkpoint_completion_target,0.9,configuration file checkpoint_segments,16,configuration file checkpoint_timeout,1h,configuration file client_encoding,WIN1252,session default_statistics_target,10,configuration file default_text_search_config,pg_catalog.english,configuration file effective_cache_size,5132MB,configuration file external_pid_file,/var/run/postgresql/9.3-main.pid,configuration file fsync,off,configuration file lc_messages,en_US.UTF-8,configuration file lc_monetary,en_US.UTF-8,configuration file lc_numeric,en_US.UTF-8,configuration file lc_time,en_US.UTF-8,configuration file listen_addresses,*,configuration file log_line_prefix,%t ,configuration file log_timezone,localtime,configuration file maintenance_work_mem,480MB,configuration file max_connections,5,configuration file max_locks_per_transaction,512,configuration file max_stack_depth,2MB,environment variable port,5432,configuration file shared_buffers,1920MB,configuration file ssl,on,configuration file ssl_cert_file,/etc/ssl/certs/ssl-cert-snakeoil.pem,configuration file ssl_key_file,/etc/ssl/private/ssl-cert-snakeoil.key,configuration file synchronous_commit,off,configuration file TimeZone,localtime,configuration file unix_socket_directories,/var/run/postgresql,configuration file wal_buffers,8MB,configuration file work_mem,1536MB,configuration file --- ? work_mem,1536MB,configuration file IIRC, your RAM is 8GB. Your work_mem is too high. Actual memory used for sorting, etc... could be multiples of work_mem setting. That could be the reason for your memory problems. I'd suggest to set it to 16MB, and see if you can avoid "on disk" sorting. If not - gradually increase work_mem. Regards, Igor Neyman