Re: Maintenance question / DB size anomaly...

That's the thing thats kinda blowing my mind here, when I look at
that table:

db1=# select count(*) from _my_cluster.sl_log_1 ;
count
-------
6788
(1 row)

As far as my DB is concerned, there's only ~7000 rows (on average)
when I look
in there (it does fluctuate, I've seen it go as high as around 12k,
but then its
gone back down, so I know events are moving around in there).

So from what I can tell- from the disk point of view, there's ~11Gb
of data; from the
vacuum point of view there's 309318 rows. From the psql point of
view, there's only
around 7,000. Am I missing something? Unless there's something
going on under the
hood that I don't know about (more than likely), it seems like my
sl_log_1 table is munged or
somehow otherwise very screwed up. I fear that a re-shuffling or
dropping/recreating
the index will mess it up further. Maybe when I take my production
systems down for
maintenance, can I wait until sl_log_1 clears out, so then I can just
drop that
table altogether (and re-create it of course)?

Thanks!

/kurt

On Jun 19, 2007, at 5:33 PM, Tom Lane wrote:

> Kurt Overberg <kurt(at)hotdogrecords(dot)com> writes:
>> mydb # vacuum verbose _my_cluster.sl_log_1 ;
>> INFO: "sl_log_1": found 455001 removable, 309318 nonremovable row
>> versions in 13764 pages
>> DETAIL: 0 dead row versions cannot be removed yet.
>
> Hmm. So you don't have a long-running-transactions problem (else that
> DETAIL number would have been large). What you do have is a failure
> to vacuum sl_log_1 on a regular basis (because there are so many
> dead/removable rows). I suspect also some sort of Slony problem,
> because AFAIK a properly operating Slony system shouldn't have that
> many live rows in sl_log_1 either --- don't they all represent
> as-yet-unpropagated events? I'm no Slony expert though. You probably
> should ask about that on the Slony lists.
>
>> ...I then checked the disk and those pages are still there.
>
> Yes, regular VACUUM doesn't try very hard to shorten the disk file.
>
>> Would a VACUUM FULL take care of this?
>
> It would, but it will take an unpleasantly long time with so many live
> rows to reshuffle. I'd advise first working to see if you can get the
> table down to a few live rows. Then a VACUUM FULL will be a snap.
> Also, you might want to do REINDEX after VACUUM FULL to compress the
> indexes --- VACUUM FULL isn't good at that.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org