http://www.slony.info/bugzilla/show_bug.cgi?id=78

           Summary: Vacuum of sl_log_2 with 42M rows is blocking replication
           Product: Slony-I
           Version: devel
          Platform: Other
        OS/Version: Linux
            Status: NEW
          Severity: critical
          Priority: high
         Component: stored procedures
        AssignedTo: slony1-bugs at lists.slony.info
        ReportedBy: gordon at collectiveintellect.com
                CC: slony1-bugs at lists.slony.info
   Estimated Hours: 0.0


We have a large transactional system running PostgreSQL 8.3.6 with Slony-I
2.0.1 on Linux Centos server with 64G memory and 2 quad-core cpus. For over 1.5
hours, the origin node's slon log has repeatedly output this every 11 or 12
minutes:

0318 11:03:34 INFO   cleanupThread:    0.258 seconds for cleanupEvent()
0318 11:04:15 ERROR  cleanupThread: "vacuum  analyze
"_slony_cluster".sl_event;" - ERROR  cleanupThread: "vacuum  analyze
"_slony_cluster".sl_confirm;" - ERROR  cleanupThread: "vacuum  analyze
"_slony_cluster".sl_setsync;" - ERROR  cleanupThread: "vacuum  analyze
"_slony_cluster".sl_log_1;" - ERROR  cleanupThread: "vacuum  analyze
"_slony_cluster".sl_log_2;" - ERROR  cleanupThread: "vacuum  analyze
"_slony_cluster".sl_seqlog;" - INFO   cleanupThread:   40.928 seconds for
vacuuming

pg_stat_activity shows a vacuum of sl_log_2 has been running for over 9 hours. 
It has 41.9 million rows in it, probably explaining why the vacuum is taking so
long. Meanwhile, sl_status shows st_lag_num_events is now over 2,500 on each
receiver node, and growing steadily.

This vacuum is coming from slony, not pg_autovacuum, as I have all slony tables
disabled from autovacuum via the pg_autovacuum table.

One thing I wonder is why doesn't cleanupEvent just truncate the table instead
of vacuum it?  (I'm new to Postgres so maybe that's a naive question.)

My receiver nodes are falling way behind.


-- 
Configure bugmail: http://www.slony.info/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are on the CC list for the bug.
You are the assignee for the bug.