Postgresql 9.4.1 stuck all queries when making multi updates

Question

Before i used 9.3.5 and servers with ubuntu 12 with 32GB memory.

After upgrade to 9.4.1, with more power server 60GB memory on each in wall replication and ubuntu 14, started get db stucks when run multi update from resque/sidekiq background workers. resque/sidekiq work via pgpool 3.3

Queries can be as simple, update column where primary id = id. and complex, doesn't metter. usually average connections about less 300, but when queries stucks, count connections grow to maximum and i get

LOG:  process 16121 still waiting for ShareLock on transaction 2448707428 after 1000.121 ms
DETAIL:  Process holding the lock: 16139. Wait queue: 16121.
LOG:  process 16146 still waiting for ExclusiveLock on tuple (665346,11) of relation 997395 of database 16455 after 1000.102 ms
DETAIL:  Process holding the lock: 16121. Wait queue: 16146.
ERROR:  canceling statement due to lock timeout

then

FATAL:  sorry, too many clients already

then only restart help, when run restart got this

WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.

Current settings,

Database size 190GB
max_connections = 400
shared_buffers = 13GB
work_mem = 19660kB
maintenance_work_mem = 2GB
effective_cache_size = 40GB

But i played with different, didn't help. On 9.3 all was fine. Nothing more in logs.

Also in one day i did pg_repack, on next day db was down with usual queries.

P.S.

I found problem, issue was in

commit_delay = 50

After set to 0, issue was solved

Answer 1

There was a bug with commit_delay in PostgreSQL 9.4.X. It was just fixed, and the fix will appear in version 9.4.5. See the thread on the PostgreSQL mailing list: http://www.postgresql.org/message-id/CA[email protected]

Answer 2

The typical cause for this situation is a query that locks one or more tables and is stalled somehow (or just takes for ever). Other requests from separate sessions keep piling up and have to wait for the lock to be released - until you hit the connection maximum.

Generally, connection pooling can prevent this situation from stalling your whole server. If a certain app only gets 10 connections it cannot exhaust the maximum.

To debug and find the source study the Postgres log files. Go back to the first messages of the type you posted and find the table, and ultimately the query that is stalled.

For instance, to resolve relation 997395 in this message:

LOG: process 16146 still waiting for ExclusiveLock on tuple (665346,11) of relation 997395 of database 16455 after 1000.102 ms

use the convenience cast to regclass that looks up the oid in the system table pg_class automatically:

SELECT 997395::regclass::text;

To avoid a server restart, identify the zombi process and selectively kill it with pg_terminate_backend(pid):

• Force drop db while others may be connected

Once you identified the root cause, fix it, of course.