Re: Postgresql 9.4.1 stuck all queries when making multi updates

On Fri, Apr 3, 2015 at 10:14 PM, Ilya Bazylchuk <ilya(dot)bazylchuk(at)gmail(dot)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.102 ms
> 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 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.
>
> This precisely means, one of the connections got crashed/killed.

Regards,
Venkata Balaji N

Fujitsu Australia