Hi Jan,

Thanks for sharing these information.

Do you think it is worth waiting that replication could catch up properly.

Yesterday while this issue was occuring


- I stopped replication on all nodes and
- I restarted the PostgreSQL server on the master
- I restaerted Slony processes on all nodes.

Then I guess that my issue is related to "A replica that is far behind."
Indeed Slony processes are redirected localy on the disk (/ partition) with log_level = 1. I don't used any log rotate mechanism


There is free disk space (50GB) if need on /var/database partition or do you 
advise me to stop replication and rebuidl replication for scratch??

This is the last solution I have but I wanted to know if it is worth waiting. 



________________________________
 De : Jan Wieck <JanWieck at Yahoo.com>
À : David TECHER <davidtecher at yahoo.fr> 
Cc : Slony Hackers <slony1-hackers at lists.slony.info> 
Envoyé le : Jeudi 26 juillet 2012 14h22
Objet : Re: [Slony1-hackers] sl_log_1 doesn't stop to grow
 
On 7/26/2012 7:54 AM, David TECHER wrote:
> Hi
> 
> Yesterday on our production server, the / partition was fully populated.
> 
> - Slony 1.2.22 is running on /
> - our databases files are locate at /var/database
> - PostgreSQL binaries are located into /opt/PostgresPlus/8.3R2AS/ inside
> of / partition
> 
> df -h / /var/database/
> Filesystem            Size  Used Avail Use% Mounted on
> /dev/mapper/VG1-root   18G  2.1G   16G  12% /
> /dev/sdf1             253G  176G   78G  70% /var/database
> 
> Quick workaround was to clean up /tmp (inside of /)
> 
> However a couple hours ago, while I was trying to build a new set
> replication hangs.
> 
> I discovered that sl_log_1 table doesn't stop to growing. (more than
> 30GB size).

That can have two possible reasons.

1) A long running transaction.

2) A replica that is far behind.

Both situations prevent the log switching from sl_log_2 to sl_log_1 to complete. The long running transaction may still write log rows to sl_log_2 and the far behind replica would still need rows that are in sl_log_2. In both cases Slony cannot truncate sl_log_2 at this moment and therefore cannot initiate another log switch.

Note that under 1.2.x this is a serious issue. 1.2.x misses an optimization in the log select query which causes the query planner to not generate a lower index scan key. As your replicas advance to the end of those 30 GB with their SYNC processing, they will scan the whole thing over and over.

> 
> I would like to know if there is any temporary files generated by Slony
> which may explains why this table doesn't stop to grow.

Slony does not create any temporary files. Unless you are using archive shipping, the only thing that comes to mind is the stdout, eventually on an unreasonably high debug level, redirected into some file without any log rotate mechanism.

> 
> I don't know if the two issues are linked.

Probably not.


Jan

-- Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-hackers/attachments/20120726/5e255524/attachment.htm