<p dir="ltr">Why and what application is holding locks on the Slony log tables?<br></p>
<p dir="ltr">Jan</p>
<p dir="ltr">--<br>
Jan Wieck<br>
Senior Software Engineer<br>
<a href="http://slony.info">http://slony.info</a></p>
<div class="gmail_quote">On Jul 8, 2014 11:06 AM, "Soni M" <<a href="mailto:diptatapa@gmail.com">diptatapa@gmail.com</a>> wrote:<br type="attribution"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">Sorry for late response,<div>currently :</div><div>NOTICE: Slony-I: could not lock sl_log_1 - sl_log_1 not truncated<br></div><div><div>dbname=# select last_value from sl_log_status;</div><div> last_value</div>
<div>------------</div><div> 3</div><div>(1 row)</div></div><div><br></div><div>That's what it should be, right?</div><div><br></div><div>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?</div>
<div><br></div><div>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.</div><div><br></div></div><div class="gmail_extra">
<br><br><div class="gmail_quote">On Wed, Jul 2, 2014 at 11:02 PM, Steve Singer <span dir="ltr"><<a href="mailto:ssinger@ca.afilias.info" target="_blank">ssinger@ca.afilias.info</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>On 07/01/2014 08:24 PM, Soni M wrote:<br>
</div><div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
Master : Ubuntu 10.04 LTS, Postgre 9.1.13, Slony 2.0.7 from Ubuntu Package<br>
Slave : Ubuntu 10.04 LTS, Postgres 9.1.13, Slony 2.0.7 seems build from<br>
</blockquote>
<br>
<br></div>
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+.<br>
<br>
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)<br>
<br>
<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div>
source.<br>
DB size 1.5 TB, Master utilize relative high number of temp tables and<br>
relative High number of locks on busy time<br>
<br>
DB=# select mode, count(*) from pg_locks group by mode;<br>
mode | count<br>
--------------------------+---<u></u>-----<br>
ExclusiveLock | 112<br>
RowShareLock | 37<br>
AccessExclusiveLock | 208577<br>
RowExclusiveLock | 33087<br>
ShareUpdateExclusiveLock | 5<br>
ShareLock | 54906<br>
AccessShareLock | 93607<br>
(7 rows)<br>
but the slave is relative low on load.<br>
<br>
this is the connection from slave that open from May 25th<br>
postgres 14090 0.1 1.1 4512644 230760 ? Ss May25 59:08<br>
postgres: slony_user dbname master_ip_address(58554) idle<br>
<br>
On slave, this connection seems always keep on idle status, but on<br>
master, this connection often in "<IDLE> in transaction" status for some<br>
minutes and hold lock on sl_log_2 while transaction are filling up sl_log_1.<br>
<br>
Such condition usually happen on server high load time, but on low load<br>
it sometimes happen, but not many and does not affect on replication lag.<br>
<br>
On Wed, Jul 2, 2014 at 12:37 AM, Steve Singer <<a href="mailto:ssinger@ca.afilias.info" target="_blank">ssinger@ca.afilias.info</a><br></div><div>
<mailto:<a href="mailto:ssinger@ca.afilias.info" target="_blank">ssinger@ca.afilias.<u></u>info</a>>> wrote:<br>
<br>
On 06/30/2014 01:05 PM, Soni M wrote:<br>
<br>
it seems a slony process that has <IDLE> in transaction for many<br>
times.<br>
the client address and the user are identical to slony slave.<br>
<br>
<br>
<br>
Which version of slony are you on?<br>
<br>
<br>
<br>
<br>
<br>
--<br>
Regards,<br>
<br>
Soni Maula Harriz<br>
</div></blockquote>
<br>
</blockquote></div><br><br clear="all"><div><br></div>-- <br><div dir="ltr"><div>Regards,</div><div><br></div>Soni Maula Harriz</div>
</div>
<br>_______________________________________________<br>
Slony1-general mailing list<br>
<a href="mailto:Slony1-general@lists.slony.info">Slony1-general@lists.slony.info</a><br>
<a href="http://lists.slony.info/mailman/listinfo/slony1-general" target="_blank">http://lists.slony.info/mailman/listinfo/slony1-general</a><br>
<br></blockquote></div>