Thread: interpret vacuum verbose output
hi all,
I'm trying to evaluate the frequecy to run vacuum
analyze on key tables. so if anyone could help me to
interpret the output of vacuum analyze verbose output
that would be great. below is the output of one of our
major indexes.
INFO: --Relation public.accounts--
INFO: Index accounts_u1: Pages 1503; Tuples 231:
Deleted 55448.
CPU 0.01s/0.14u sec elapsed 0.15 sec.
INFO: Index account_u1: Pages 1997; Tuples 231:
Deleted 55448.
CPU 0.01s/0.13u sec elapsed 0.15 sec.
INFO: Index account_u2: Pages 2415; Tuples 231:
Deleted 55448.
CPU 0.00s/0.14u sec elapsed 0.30 sec.
INFO: Removed 55448 tuples in 2367 pages.
CPU 0.04s/0.11u sec elapsed 0.41 sec.
INFO: Pages 11266: Changed 4, Empty 0; Tup 231: Vac
55448, Keep 0, UnUsed 205434.
Total CPU 0.12s/0.56u sec elapsed 1.08 sec.
thanks
Shankar
__________________________________
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
On Wed, 4 Jun 2003, Shankar K wrote:
> hi all,
>
> I'm trying to evaluate the frequecy to run vacuum
> analyze on key tables. so if anyone could help me to
> interpret the output of vacuum analyze verbose output
> that would be great. below is the output of one of our
> major indexes.
>
> INFO: --Relation public.accounts--
> INFO: Index accounts_u1: Pages 1503; Tuples 231:
> Deleted 55448.
> CPU 0.01s/0.14u sec elapsed 0.15 sec.
> INFO: Index account_u1: Pages 1997; Tuples 231:
> Deleted 55448.
> CPU 0.01s/0.13u sec elapsed 0.15 sec.
> INFO: Index account_u2: Pages 2415; Tuples 231:
> Deleted 55448.
> CPU 0.00s/0.14u sec elapsed 0.30 sec.
> INFO: Removed 55448 tuples in 2367 pages.
> CPU 0.04s/0.11u sec elapsed 0.41 sec.
> INFO: Pages 11266: Changed 4, Empty 0; Tup 231: Vac
> 55448, Keep 0, UnUsed 205434.
> Total CPU 0.12s/0.56u sec elapsed 1.08 sec.
>
> thanks
> Shankar
>
Its actually quite simple from what I under stand.... (Not that I
know for certain someone will correct me if I'm wrong and I'm not an
expert)
Pages: Number of Pages used by Index (or table)
Tuples: Number of Records In table in use
Deleted: Number of Records just deleted in table
UnUsed: Number of Records In Table not in use currently. (I think vacuum
full should remove these)
Changed: Number of Records Changed (Not sure)
Keep: Number of Records to Keep (Not Sure)
CPU Time taken to complete vacuum.
So Tuples + Unused = Total Tuples used in table.
Hence (Tuples + Unused)/Pages = Number of Records per page hence record
size
If deleted gets too big you should be vacuuming more often
If unused gets too big try and increase your fsm size.
Or that is the way I under stand it. I'm now waiting for somone to
correct me.
Peter Childs
Shankar K <shan0075@yahoo.com> writes:
> I'm trying to evaluate the frequecy to run vacuum
> analyze on key tables. so if anyone could help me to
> interpret the output of vacuum analyze verbose output
> that would be great.
> INFO: Removed 55448 tuples in 2367 pages.
> CPU 0.04s/0.11u sec elapsed 0.41 sec.
> INFO: Pages 11266: Changed 4, Empty 0; Tup 231: Vac
> 55448, Keep 0, UnUsed 205434.
This says that you have 231 live tuples versus 55448 that were removed ---
in other words, less than one-half of one percent of your table was
current data. You need to vacuum this table a LOT more often than
you are doing.
You will need a VACUUM FULL and a REINDEX to get the table and index
physical sizes down to something reasonable, after which a more rigorous
schedule of plain vacuums ought to keep things in shape.
regards, tom lane
Thanks tom that was very useful. just wondering what could be "Keep 0, UnUsed 205434" refer here. does that any of it impact in evaluvating the vaccum frequecy. thanks, Shankar --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Shankar K <shan0075@yahoo.com> writes: > > I'm trying to evaluate the frequecy to run vacuum > > analyze on key tables. so if anyone could help me > to > > interpret the output of vacuum analyze verbose > output > > that would be great. > > > INFO: Removed 55448 tuples in 2367 pages. > > CPU 0.04s/0.11u sec elapsed 0.41 sec. > > INFO: Pages 11266: Changed 4, Empty 0; Tup 231: > Vac > > 55448, Keep 0, UnUsed 205434. > > This says that you have 231 live tuples versus 55448 > that were removed --- > in other words, less than one-half of one percent of > your table was > current data. You need to vacuum this table a LOT > more often than > you are doing. > > You will need a VACUUM FULL and a REINDEX to get the > table and index > physical sizes down to something reasonable, after > which a more rigorous > schedule of plain vacuums ought to keep things in > shape. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to majordomo@postgresql.org > so that your > message can get through to the mailing list cleanly __________________________________ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com
On Thu, 2003-06-05 at 04:54, Peter Childs wrote:
> On Wed, 4 Jun 2003, Shankar K wrote:
>
> > hi all,
> >
> > I'm trying to evaluate the frequecy to run vacuum
> > analyze on key tables. so if anyone could help me to
> > interpret the output of vacuum analyze verbose output
> > that would be great. below is the output of one of our
> > major indexes.
> >
> UnUsed: Number of Records In Table not in use currently. (I think vacuum
> full should remove these)
yes
> Changed: Number of Records Changed (Not sure)
Number of Pages Changed
> Keep: Number of Records to Keep (Not Sure)
Correct. Usually these are tuples that are currently being accessed
within a transaction.
> If deleted gets too big you should be vacuuming more often
>
not really, unused is far more significant for that. You might say if
deleted is too small, you might want to vacuum less frequently.
> If unused gets too big try and increase your fsm size.
>
if it gets too big, it's a sign you're not vacuuming enough.
if it gets way too big, you need to do a vacuum full.
note recommendations on deleted and unused both assume you have
increased your fsm settings appropriately.
also note that in pre-7.4 releases, vacuum cannot remove dead pages
within indexes, so if your indexes grow to large you will need to
REINDEX
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL