The strange thing is that we have autovacuum set up on this database  
and we allow Slony to vacuum as well.
It seems that this level of bloat is normal?!

I've talked to Josh about this, DB tables make bad queues.

When we use to use DBMirror we use to do this to get around this  
problem.  While in a access exclusive
lock we'd rebuild the table in place every 1/2 hour.  It appears that  
PgQ (SkyTools) is doing something similar, however,
they do it every 5 minutes.

BEGIN;

SELECT now() AS "ts", 'locking tables' AS "step";

LOCK TABLE "Pending" IN ACCESS EXCLUSIVE MODE;
LOCK TABLE "PendingData" IN ACCESS EXCLUSIVE MODE;
LOCK TABLE "MirroredTransaction" IN ACCESS EXCLUSIVE MODE;


ALTER TABLE "Pending" RENAME TO p_old;
ALTER TABLE "PendingData" RENAME TO pd_old;
ALTER TABLE "MirroredTransaction" RENAME TO mt_old;

UPDATE pg_class SET relname = 'p_old_id_seq' WHERE relname =  
'Pending_SeqId_seq';

UPDATE pg_type  SET typname = 'p_old_id_seq' WHERE typname =  
'Pending_SeqId_seq';

ALTER TABLE p_old ALTER COLUMN "SeqId" DROP DEFAULT;
ALTER TABLE p_old ALTER COLUMN "SeqId" SET DEFAULT  
NEXTVAL('p_old_id_seq'::TEXT);


ALTER TABLE pd_old DROP CONSTRAINT "$1";
ALTER TABLE mt_old DROP CONSTRAINT "$2";

ALTER TABLE p_old  DROP CONSTRAINT "Pending_pkey";
ALTER TABLE pd_old DROP CONSTRAINT "PendingData_pkey";
ALTER TABLE mt_old DROP CONSTRAINT "MirroredTransaction_pkey";

DROP INDEX "Pending_XID_Index";

CREATE TABLE "Pending" (
        "SeqId" serial,
        "TableName" varchar NOT NULL,
        "Op" character,
        "XID" int4 NOT NULL,
        PRIMARY KEY ("SeqId")
);
LOCK TABLE "Pending" IN ACCESS EXCLUSIVE MODE;

CREATE TABLE "PendingData" (
        "SeqId" int4 NOT NULL,
        "IsKey" bool NOT NULL,
        "Data" varchar,
       PRIMARY KEY ("SeqId", "IsKey") ,
       FOREIGN KEY ("SeqId") REFERENCES "Pending" ("SeqId") ON UPDATE  
CASCADE  ON DELETE CASCADE
);
LOCK TABLE "PendingData" IN ACCESS EXCLUSIVE MODE;

CREATE TABLE "MirroredTransaction" (
        "XID" int4 NOT NULL,
        "LastSeqId" int4 NOT NULL,
        "MirrorHostId" int4 NOT NULL,
       PRIMARY KEY  ("XID","MirrorHostId"),
       FOREIGN KEY ("MirrorHostId") REFERENCES  
"MirrorHost" ("MirrorHostId") ON UPDATE CASCADE ON DELETE CASCADE,
       FOREIGN KEY ("LastSeqId") REFERENCES "Pending" ("SeqId")  ON  
UPDATE CASCADE ON DELETE CASCADE
);
LOCK TABLE "MirroredTransaction" IN ACCESS EXCLUSIVE MODE;
CREATE INDEX "Pending_XID_Index" ON "Pending" ("XID");
SELECT SETVAL('"Pending_SeqId_seq"', (SELECT MAX("SeqId") FROM p_old)  
+ 1);
INSERT INTO "Pending" SELECT * FROM p_old;
INSERT INTO "PendingData" SELECT * FROM pd_old;
INSERT INTO "MirroredTransaction" SELECT * FROM mt_old;

GRANT SELECT, INSERT, UPDATE, DELETE ON "Pending" TO www_master;
GRANT SELECT, INSERT, UPDATE, DELETE ON "PendingData" TO www_master;
GRANT SELECT, INSERT, UPDATE, DELETE ON "MirroredTransaction" TO  
www_master;
GRANT ALL ON "Pending_SeqId_seq" TO www_master;

GRANT SELECT ON "Pending" TO www;
GRANT SELECT ON "PendingData" TO www;
GRANT SELECT ON "MirroredTransaction" TO www;

COMMIT;

VACUUM ANALYZE pg_class;
VACUUM ANALYZE pg_type;
VACUUM ANALYZE "Pending";
VACUUM ANALYZE "PendingData";
VACUUM ANALYZE "MirroredTransaction";



On Feb 7, 2008, at 5:26 PM, David Rees wrote:

> On Feb 7, 2008 4:16 PM, Christian Storm <storm at iparadigms.com> wrote:
>> However, on one of the slaves I saw a lot of table bloat.  Can this  
>> hurt
>> performance?
>
> It can. Do  you not vacuum the slave database? Autovacuum would help
> you out here at a minimum on the slaves if you didn't want to bother
> with doing a full vacuum analysis.
>
> -Dave