Re: [SPAM] Re: Question about locking and pg_locks

On 09/12/2016 12:47 AM, Moreno Andreo wrote:

Ccing list.
> Il 08/09/2016 15:26, Adrian Klaver ha scritto:

>>> so, I should be able to manage 800*64 = 5120 locks, right?
> OMG, time to go back to school... 800*64 = 51200 ! ! !
>>>
>>> Now my pg_locks table has more than 6200 rows, but if I reorder them by
>>> pid I see that one of them has 5800 of them, and it keeps on eating
>>> locks.
>>> If I dig more and get pid info, its state is "<IDLE> in transaction"
>>
>> So some transaction is being held open and the system cannot close out
>> the locks until it is done.
> Right, but I can't figure why these transactions are not closed.
> If I'm right, when backend is dropped (i.e. the connection is closed)
> all its locks, transactions, and so on, are dropped and resources are
> released for someone else's use.
> In this case we have the backend alive, holding dozens of transaction
> IDs and some relation lock. But it's recent_query reports <IDLE> in
> transaction
> ATM there are no locks that have granted = false.
>>>
>>> Now, question time:
>>> - Is there a number of pg_locks rows to be worried about? At more than
>>> 6000 I'm still not facing out of shared memory again
>>> - Is there a way to release locks of that pid without pg_terminate() it?
>>
>> Look in pg_stat_activity:
>>
>> https://www.postgresql.org/docs/9.5/static/monitoring-stats.html
>>
>> for state 'idle in transaction' and the corresponding query. If you
>> know where that query is coming from you could manually either commit
>> it or roll it back.
> pg_stat_activity is where I got these informations, but column query
> still says <IDLE in transaction>
>
> postgres=# select * from pg_stat_activity where procpid = 31570;
>   datid  |    datname    | procpid | usesysid | usename  |
> application_name | client_addr | client_hostname | client_port |
> backend_start         |          xact_start |
> query_start          | waiting |     current_query
>
---------+---------------+---------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------+-----------------------
>
>  1067066 | dbname |   31570 |  1067065 | username | | 10.20.9.206
> |                 |       50146 | 2016-09-08 14:23:33.146383+02 |
> 2016-09-08 14:27:02.585666+02 | 2016-09-08 15:57:32.107801+02 | f
> | <IDLE> in transaction
> (1 row)


Well you do have the client host IP as well as the port it is connecting
to Postgres on. From that you may be able to use something like ps to
work out what application is sending the query.

You might want to take a look at the below also:

https://www.postgresql.org/docs/9.1/static/monitoring-stats.html

27.2.1. Statistics Collection Configuration

and see what your settings are configured for.

>
> Am I missing something?
>>
>>>
>>> I tried to give most of the details, if you need more, just ask...
>>> Thanks
>>> Moreno.-
>>>
>>>
>>>
>>
>>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com