Re: Postgresql 9.4.1 stuck all queries when making multi updates

nothing in pgpool. Pgpool use only resque/sidekiq. Rest use unicorn directly to master.
1. 45 connections
2. 45 connections
3. 30 connections
4. 10 connections

When connections stucks, growth count connections from unicorn only. Seems it not granted locks, but weird that i didn’t have it on 9.3.

In general didn’t change except new pg servers.

I added log for not granted locks, and in 12 hours got only one, and it was short and on another table, which never presented in pg logs. will see more.

lock_timeout: 30000
deadlock_timeout: 1s
statement_timeout: 0

I know that set statement_timeout isn’t recommended, but what’s better solution, don’t get stuck db?

> 4 апр. 2015 г., в 3:10, Venkata Balaji N <nag1010(at)gmail(dot)com> написал(а):
>
>
> On Fri, Apr 3, 2015 at 10:14 PM, Ilya Bazylchuk <ilya(dot)bazylchuk(at)gmail(dot)com <mailto: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