Postgresql 9.4.1 stuck all queries when making multi updates

postgresqlpostgresql-9.4

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

Best Answer

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/CACKh8C-tDjQUceMQeG3BUSHS5AA35SjvCD6HgRm-Vj7x5rXmeQ@mail.gmail.com