Why not have sl_event_[1,2] and sl_confirm_[1,2]?  They seem
to have similar levels of churn.

SkyTools PGQ rotates their queues between three tables every 5 minutes.
See do_maintenance function:
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/skytools/skytools/python/pgq/maint.py?rev=1.2&content-type=text/x-cvsweb-markup

The do_maintenance function calls these plpgsql functions (maint_rotate_tables_step1, maint_rotate_tables_step2, maint_tables_to_vacuum) defined here:
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/skytools/skytools/sql/pgq/functions/pgq.maint_rotate_tables.sql?rev=1.3&content-type=text/x-cvsweb-markup

They're design was to "We use table rotation to decrease hard disk io."


On Feb 8, 2008, at 9:00 AM, Christopher Browne wrote:

"Jan Wieck" <[EMAIL PROTECTED]> writes:
It not only can, it definitely will hurt performance. A Slony
replica experiences the same updates, row per row, as the origin
did. This means that the need for vacuuming is exactly the same.

Well, there are *some* differences.

Any transactions that roll back on the origin won't be reflected at
all on subscribers, so if a system sees a lot of transaction
ROLLBACKs, that will a point of non-correspondance.

But Christian pointed out three specific Slony-I-managed tables that
are worth at least a quick look...

- sl_log_2 consisted 91% of dead tuples
- sl_event had 57% dead tuples
- sl_confirm was composed of 86% dead tuples

In the 1.2 branch, we do periodically rotate between sl_log_1 and
sl_log_2 so that *that* space will eventually get reclaimed via a
TRUNCATE.  In the 2.0 branch, we'll be moving to a strategy of solely
running TRUNCATE against sl_log_{*}, so there should never be any dead
tuples except insofar as some replicable activity got recently rolled
back.

The same is not true for the other two tables.

It seems to me that sl_confirm and sl_confirm could really use a
CLUSTER (or similar), here, as the tables are mostly comprised of
"dead space."

Mind you:

- They are regularly vacuumed by the cleanup loop, probably every 10
  minutes

- Access usually offers reasonable possibilities for index usage,
  which should allow access to be reasonably efficient even if they
  have some "bloat."

The existing structure of Slony-I tries to vacuum them immediately
after purging data from them; I can't see autovacuum doing a better
job than that.
--
(format nil "[EMAIL PROTECTED]" "cbbrowne" "linuxfinances.info")
http://cbbrowne.com/info/multiplexor.html
"Nondeterminism means never having to say you're wrong."  -- Unknown

_______________________________________________
Slony1-general mailing list
[email protected]
http://lists.slony.info/mailman/listinfo/slony1-general

Reply via email to