Why and what application is holding locks on the Slony log tables?

Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info
On Jul 8, 2014 11:06 AM, "Soni M" <diptatapa at gmail.com> wrote:

> Sorry for late response,
> currently :
> NOTICE:  Slony-I: could not lock sl_log_1 - sl_log_1 not truncated
> dbname=# select last_value from sl_log_status;
>  last_value
> ------------
>           3
> (1 row)
>
> That's what it should be, right?
>
> But now, i see application transaction holding locks on sl_log_1, some
> select, insert and update transaction and also the <IDLE> in transaction
> just like before. But the row numbers of sl_log_1 not increasing anyway.
> The row num is increasing in sl_log_2. Is that normal?
>
> The bug on 2.0.x as You said does happen in our environment, and it
> happens when the slave falls further behind the master. I should plan to
> move to 2.1 or 2.2.
>
>
>
> On Wed, Jul 2, 2014 at 11:02 PM, Steve Singer <ssinger at ca.afilias.info>
> wrote:
>
>> On 07/01/2014 08:24 PM, Soni M wrote:
>>
>>>
>>> Master : Ubuntu 10.04 LTS, Postgre 9.1.13, Slony 2.0.7 from Ubuntu
>>> Package
>>> Slave : Ubuntu 10.04 LTS, Postgres 9.1.13, Slony 2.0.7 seems build from
>>>
>>
>>
>> I should also point out that slony 2.0.x does not properly support PG 9.1
>> or higher.  You should use slony 2.1.x or 2.2.x with PG 9.1+.
>>
>> If you are seeing a lot of transactions being aborted due to
>> serialization conflicts then this is because of the issues with PG 9.1 and
>> slony 2.0 (though it doesn't sound like that is causing this particular
>> issue)
>>
>>
>>
>>  source.
>>> DB size 1.5 TB, Master utilize relative high number of temp tables and
>>> relative High number of locks on busy time
>>>
>>> DB=# select mode, count(*) from pg_locks group by mode;
>>>             mode           | count
>>> --------------------------+--------
>>>   ExclusiveLock            |    112
>>>   RowShareLock             |     37
>>>   AccessExclusiveLock      | 208577
>>>   RowExclusiveLock         |  33087
>>>   ShareUpdateExclusiveLock |      5
>>>   ShareLock                |  54906
>>>   AccessShareLock          |  93607
>>> (7 rows)
>>> but the slave is relative low on load.
>>>
>>> this is the connection from slave that open from May 25th
>>> postgres 14090  0.1  1.1 4512644 230760 ?      Ss   May25  59:08
>>> postgres: slony_user dbname master_ip_address(58554) idle
>>>
>>> On slave, this connection seems always keep on idle status, but on
>>> master, this connection often in "<IDLE> in transaction" status for some
>>> minutes and hold lock on sl_log_2 while transaction are filling up
>>> sl_log_1.
>>>
>>> Such condition usually happen on server high load time, but on low load
>>> it sometimes happen, but not many and does not affect on replication lag.
>>>
>>> On Wed, Jul 2, 2014 at 12:37 AM, Steve Singer <ssinger at ca.afilias.info
>>> <mailto:ssinger at ca.afilias.info>> wrote:
>>>
>>>     On 06/30/2014 01:05 PM, Soni M wrote:
>>>
>>>         it seems a slony process that has <IDLE> in transaction for many
>>>         times.
>>>         the client address and the user are identical to slony slave.
>>>
>>>
>>>
>>>     Which version of slony are you on?
>>>
>>>
>>>
>>>
>>>
>>> --
>>> Regards,
>>>
>>> Soni Maula Harriz
>>>
>>
>>
>
>
> --
> Regards,
>
> Soni Maula Harriz
>
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20140708/52223a67/attachment.htm