Postgresql 9.4.1 stuck all queries when making multi updates

From: Ilya Bazylchuk <ilya(dot)bazylchuk(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Postgresql 9.4.1 stuck all queries when making multi updates
Date: 2015-04-03 11:14:37
Message-ID: 24169366-5D72-4363-8F60-529442AD39BF@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2015-04-03 14:08:27 Re: src/port/getopt_long.c lossy with arguments having no option characters
Previous Message Peter Slavov 2015-04-03 09:47:20 Re: BUG #12910: Memory leak with logical decoding