Re: Maintenance question / DB size anomaly...

Kurt Overberg <kurt@hotdogrecords.com> wrote:
>
> 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).

This is consistent with my experience with Slony and sl_log_[12]

I'm pretty sure that the slon processes vacuum sl_log_* on a fairly
regular basis.  I'm absolutely positive that slon occasionally switches
from using sl_log_1, to sl_log_2, then truncates sl_log_1 (then, after
some time, does the same in reverse)

So, in order for you to get massive bloat of the sl_log_* tables, you
must be doing a LOT of transactions in the time before it switches
logs and truncates the unused version.  Either that, or something is
going wrong.

> 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?

Something seems wrong here.  Correct me if I'm missing something, but
you're saying the table takes up 11G on disk, but vacuum says there are
~14000 pages.  That would mean your page size is ~800K.  Doesn't seem
right.

> 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)?

Possibly drop this node from the Slony cluster and re-add it.  Unless
it's the origin node, in which case you'll have to switchover, then
redo the origin then switch back ...

>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>
>
>
>
>
>


--
Bill Moran
Collaborative Fusion Inc.

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

****************************************************************
IMPORTANT: This message contains confidential information
and is intended only for the individual named. If the reader of
this message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
****************************************************************