You need to run select pg_relation_size('tablename') to see how much
space vacuum full is reclaiming.
Also remember that vacuum full does not reclaim space in the indexes.
If you don't REINDEX you might end up with indexes that are larger than
the table itself.
Victoria Parsons wrote:
The problem is on pg_listener table. I found the same problem on my test
3 node set up.
I printed the table contents, then did a vacuum full. I then waited 5
minutes, print the table contents and do a vacuum full again.
The table details have not changed, all the same slony pids. We have no
listens on public tables on this particular database so its easy to see
what is going on. To the very best of my knowledge there have been no
inserts and deletes to this table since the last print, 5 minutes ago.
I paste the vacuum results below. I've never read the vacuum verbose
reports before so any guidance on what to look for would be appreciated.
The lines that appear to be of interest are
INFO: "pg_listener": found 1036 removable, 12 nonremovable row versions
in 16 pages
INFO: "pg_listener": moved 5 row versions, truncated 16 to 1 pages
I can wait 5 minutes and repeat and another 16-18 pages of data will
again be truncated into 1 page. Where is al this extra data coming from.
I don't want to jump slony but it seems much worse on the 11 node
system, which is where the problem first came to light.
Vicki
sss=# vacuum full verbose pg_listener;
INFO: vacuuming "pg_catalog.pg_listener"
INFO: "pg_listener": found 1036 removable, 12 nonremovable row versions
in 16 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 96 to 96 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 125408 bytes.
10 pages are or will become empty, including 0 at the end of the table.
16 pages containing 125408 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_listener": moved 5 row versions, truncated 16 to 1 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.07 sec.
VACUUM
-----Original Message-----
From: Christopher Browne [mailto:[EMAIL PROTECTED]
Sent: 22 March 2007 16:05
To: Victoria Parsons
Cc: slony
Subject: Re: [Slony1-general] Vacuum full required?
"Victoria Parsons" <[EMAIL PROTECTED]> writes:
Has anyone else found that they need a vacuum full to keep their
replication, and/or general postgres use up to speed? Have I
mis-understood the use of vacuum full, and does it do more than just
recover disk space? Next time it goes wrong I will do vacuum full on
a table at a time to see if I can narrow down the culprit.
We *never* run a VACUUM FULL on a whole database; just occasionally on
some very carefully selected table.
Definitely you should narrow it down, and run VACUUM FULL ANALYZE on
each table to see where you get a huge reclaiming of space. By all
means, report back on your findings, that may help us help you, and
help others, as well...
_______________________________________________
Slony1-general mailing list
[email protected]
http://lists.slony.info/mailman/listinfo/slony1-general
