Re: Postgresql 9.4.1 stuck all queries when making multi updates

On Fri, Apr 3, 2015 at 10:14 PM, Ilya Bazylchuk <ilya.bazylchuk@gmail.com>
wrote:

> 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
>
Do you see any messages in pgpool logs ?


> 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.102ms 
> DETAIL:  Process holding the lock: 16121. Wait queue: 16146.
> ERROR:  canceling statement due to lock timeout
>
> Any idea, how long the locks were hanging in there for ?
Do you have any SQL timeouts configured in postgresql.conf ?


> 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
anotherserver 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.
>
> This precisely means, one of the connections got crashed/killed.

Regards,
Venkata Balaji N

Fujitsu Australia