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