Thread: could not fork new process for connection: Resource temporarily unavailable
ello,
I have heavy transaction load production database 9.3 PPAS .Today Database is not able to give new process. I checked pg_stat_activity , there are so many transaction in waiting stage because of one procedure and lock on one table (Code inside procedure)
SELECT cnt
FROM table_name_seq_data
WHERE circle = v_cir_cd
AND type = P_NAME FOR UPDATE;
I have terminated all transaction related to that procedure , but again locks occurred and no new process allowed.3 times I have performed this thing. After that I have restated my server, then I executed this procedure it takes very less ms. I have to take lock of this table (table_name_seq_data).
Log files
could not fork new process for connection: Resource temporarily unavailable
could not fork new process for connection: Resource temporarily unavailable
could not fork autovacuum worker process: Resource temporarily unavailable
could not fork new process for connection: Resource temporarily unavailable
could not fork new process for connection: Resource temporarily unavailable
I think because of Application workload this table(table_name_seq_data) is not vacuumed . This table contains only 50 rows only updates occur on this table, And before restart I am not able select this table.
------------------------------------------------------------------------------------------
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0 # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
vacuum_cost_limit = 1000 # 1-10000 credits
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
autovacuum_max_workers = 6 # max number of autovacuum subprocesses
# (change requires restart)
#autovacuum_naptime = 1min # time between autovacuum runs
autovacuum_vacuum_threshold = 2000 # min number of row updates before
# vacuum
autovacuum_analyze_threshold = 2000 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
Re: could not fork new process for connection: Resource temporarily unavailable
From
"Joshua D. Drake"
Date:
On 07/04/2015 12:19 PM, Jimit Amin wrote: > ello, > > I have heavy transaction load production database 9.3 PPAS .Today > Database is not able to give new process. I checked pg_stat_activity , > there are so many transaction in waiting stage because of one procedure > and lock on one table (Code inside procedure) PPAS is not a postgresql project. I would suggest you contact EnterpriseDB. Sincerely, JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing "I'm offended" is basically telling the world you can't control your own emotions, so everyone else should do it for you.
Re: could not fork new process for connection: Resource temporarily unavailable
From
Albe Laurenz
Date:
Jimit Amin wrote: > I have heavy transaction load production database 9.3 PPAS .Today Database is not able to give new > process. I checked pg_stat_activity , there are so many transaction in waiting stage because of one > procedure and lock on one table (Code inside procedure) [...] > could not fork new process for connection: Resource temporarily unavailable > could not fork new process for connection: Resource temporarily unavailable > could not fork autovacuum worker process: Resource temporarily unavailable > could not fork new process for connection: Resource temporarily unavailable > could not fork new process for connection: Resource temporarily unavailable Your machine is running out of system resources needed to create new processes. This might help: http://stackoverflow.com/questions/12079087/fork-retry-resource-temporarily-unavailable Yours, Laurenz Albe