Re: Maintenance question / DB size anomaly... - Mailing list pgsql-performance

Chris,

I took your advice, and I had found that sl_log_1 seems to be causing
some of the problem.  Here's the result of a VACUUM VERBOSE

mydb # vacuum verbose _my_cluster.sl_log_1 ;
INFO:  vacuuming "_my_cluster.sl_log_1"
INFO:  index "sl_log_1_idx1" now contains 309404 row versions in
1421785 pages
DETAIL:  455001 index row versions were removed.
1419592 index pages have been deleted, 1416435 are currently reusable.
CPU 16.83s/5.07u sec elapsed 339.19 sec.
^@^@^@INFO:  index "sl_log_1_idx2" now contains 312864 row versions
in 507196 pages
DETAIL:  455001 index row versions were removed.
506295 index pages have been deleted, 504998 are currently reusable.
CPU 6.44s/2.27u sec elapsed 138.70 sec.
INFO:  "sl_log_1": removed 455001 row versions in 7567 pages
DETAIL:  CPU 0.56s/0.40u sec elapsed 6.63 sec.
INFO:  "sl_log_1": found 455001 removable, 309318 nonremovable row
versions in 13764 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 51972 unused item pointers.
0 pages are entirely empty.
CPU 24.13s/7.85u sec elapsed 486.49 sec.
INFO:  vacuuming "pg_toast.pg_toast_955960155"
INFO:  index "pg_toast_955960155_index" now contains 9 row versions
in 2 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_955960155": found 0 removable, 9 nonremovable row
versions in 3 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 3 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

...I then checked the disk and those pages are still there.  If I do a:

select count(*) from  _my_cluster.sl_log_1;
count
-------
   6366
(1 row)

Would a VACUUM FULL take care of this?  It seems to me that its not
clearing up the indexes properly.  You are correct in that
I do see things getting much bigger on the master than on the
subscriber nodes.  Could this cause my slony replication to bog down?

Also- I have a question about this comment:

>
> You don't forcibly have to take Slony-I down during this, but the
> locks taken out on tables by CLUSTER/VACUUM FULL will block slons from
> doing any work until those transactions complete.

Thats because no writing will be done to the tables, thus, no slony
triggers will get triggered, correct?  I'd rather not
shut down slony if I dont have to, but will if it "is safer/better/
more badass".

For those playing along at home,

> $ find $PGDATA/base -name "[0-9]+\.[0-9]+"
>

...I had to use:

find $PGDATA/base -name "[0-9]*\.[0-9]*"

...but the pluses should have worked too.  Still a much better way
than how I was doing it.   Thanks again for helping me with this, its
greatly appreciated!

/kurt