Thread: Meaning of vacuum output
I have no idea what the output of
vacuum verbose means.
rupert=# vacuum verbose resp_header;
NOTICE: --Relation resp_header--
NOTICE: Pages 28276: Changed 4, Empty 0; Tup 2437108: Vac 0, Keep
2295773, UnUsed 96275.
Total CPU 1.79s/0.38u sec elapsed 2.72 sec.
VACUUM
rupert=# select count(*) from resp_header;
count
--------
141357
(1 row)
There's nowhere near 2.437e6 tuples in the table, but the difference
between Tup and Keep seems to be the number of tuples in the table. But
what does this mean? Are there 2.295e6 deleted tuples hanging around in
the table? That doesn't make sense because vacuum is supposed to toss
deleted tuples (right?). What's unused? What is Vac and why is it
*always* 0? I don't want unused tuples in my tables!
Can you understand my confusion?
Regards,
Jeffrey Baker
"Jeffrey W. Baker" <jwbaker@acm.org> writes:
> rupert=# vacuum verbose resp_header;
> NOTICE: --Relation resp_header--
> NOTICE: Pages 28276: Changed 4, Empty 0; Tup 2437108: Vac 0, Keep
> 2295773, UnUsed 96275.
> Total CPU 1.79s/0.38u sec elapsed 2.72 sec.
> VACUUM
> rupert=# select count(*) from resp_header;
> count
> --------
> 141357
> (1 row)
> There's nowhere near 2.437e6 tuples in the table, but the difference
> between Tup and Keep seems to be the number of tuples in the table. But
> what does this mean? Are there 2.295e6 deleted tuples hanging around in
> the table?
Kinda looks that way. Do you have open transactions hanging around that
might still be able to see those tuples?
regards, tom lane
"Jeffrey W. Baker" <jwbaker@acm.org> writes:
> I would be very surprised but it isn't impossible. Is there a way to
> query the transaction counter for an open connection?
No easy way I can think of, short of gdb'ing each backend. But you
could use "ps" and look at the start time of each backend process
that isn't showing status "idle".
regards, tom lane
On Wed, 2002-01-16 at 10:08, Tom Lane wrote:
> "Jeffrey W. Baker" <jwbaker@acm.org> writes:
> > rupert=# vacuum verbose resp_header;
> > NOTICE: --Relation resp_header--
> > NOTICE: Pages 28276: Changed 4, Empty 0; Tup 2437108: Vac 0, Keep
> > 2295773, UnUsed 96275.
> > Total CPU 1.79s/0.38u sec elapsed 2.72 sec.
> > VACUUM
> > rupert=# select count(*) from resp_header;
> > count
> > --------
> > 141357
> > (1 row)
>
> > There's nowhere near 2.437e6 tuples in the table, but the difference
> > between Tup and Keep seems to be the number of tuples in the table. But
> > what does this mean? Are there 2.295e6 deleted tuples hanging around in
> > the table?
>
> Kinda looks that way. Do you have open transactions hanging around that
> might still be able to see those tuples?
I shut down all connections, and vacuum has a different output:
NOTICE: Pages 33639: Changed 226, Empty 0; Tup 128819: Vac 2840812,
Keep 0, UnUsed 96275.
Total CPU 14.29s/28.69u sec elapsed 233.02 sec.
So I conclude from these two data points that Keep means tuples that are
still visible to a transaction, Vac means tuples that are not visible to
any transaction. But, what is UnUsed?
-jwb
"Jeffrey W. Baker" <jwbaker@acm.org> writes:
> I shut down all connections, and vacuum has a different output:
> NOTICE: Pages 33639: Changed 226, Empty 0; Tup 128819: Vac 2840812,
> Keep 0, UnUsed 96275.
> Total CPU 14.29s/28.69u sec elapsed 233.02 sec.
Ah-hah, you did have open transactions.
> So I conclude from these two data points that Keep means tuples that are
> still visible to a transaction, Vac means tuples that are not visible to
> any transaction. But, what is UnUsed?
Looking at the code:
Tup # tuples remaining after vacuum
Vac # tuples removed by vacuum
Keep # dead tuples kept because some xact can still see 'em
Unused # unused item pointers
"Keep" is included in the "Tup" total.
The unused item pointers may get recycled later; it looks like that
hasn't been determined yet at the point where these stats are printed.
regards, tom lane
On Wed, 2002-01-16 at 10:08, Tom Lane wrote: > "Jeffrey W. Baker" <jwbaker@acm.org> writes: > > rupert=# vacuum verbose resp_header; > > NOTICE: --Relation resp_header-- > > NOTICE: Pages 28276: Changed 4, Empty 0; Tup 2437108: Vac 0, Keep > > 2295773, UnUsed 96275. > > Total CPU 1.79s/0.38u sec elapsed 2.72 sec. > > VACUUM > > rupert=# select count(*) from resp_header; > > count > > -------- > > 141357 > > (1 row) > > > There's nowhere near 2.437e6 tuples in the table, but the difference > > between Tup and Keep seems to be the number of tuples in the table. But > > what does this mean? Are there 2.295e6 deleted tuples hanging around in > > the table? > > Kinda looks that way. Do you have open transactions hanging around that > might still be able to see those tuples? I would be very surprised but it isn't impossible. Is there a way to query the transaction counter for an open connection? -jwb
Tom Lane wrote: > > "Jeffrey W. Baker" <jwbaker@acm.org> writes: > > I shut down all connections, and vacuum has a different output: > > > NOTICE: Pages 33639: Changed 226, Empty 0; Tup 128819: Vac 2840812, > > Keep 0, UnUsed 96275. > > Total CPU 14.29s/28.69u sec elapsed 233.02 sec. > > Ah-hah, you did have open transactions. > > > So I conclude from these two data points that Keep means tuples that are > > still visible to a transaction, Vac means tuples that are not visible to > > any transaction. But, what is UnUsed? > > Looking at the code: > > Tup # tuples remaining after vacuum > Vac # tuples removed by vacuum > Keep # dead tuples kept because some xact can still see 'em > Unused # unused item pointers > > "Keep" is included in the "Tup" total. > > The unused item pointers may get recycled later; it looks like that > hasn't been determined yet at the point where these stats are printed. I think this explanation would be worth mentioning in the documentation for the VACUUM command (maybe as OUTPUT?). ??? Regards and best wishes, Justin Clift > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi