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

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@hotdogrecords.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