Thread: Doubt w.r.t vacuum
Hi, I was just wondering over it. This is for difference between vacuum full and vacuum analyze. Can somebody enlighten, 1. IIRC vacuum recovers/reuses dead tuples generated from update but can not do so for delete? Why? 2. Vacuum full locks entire table, is it possible that it locks a page at a time and deal with it. It will make vacuum full non-blocking at the cost of letting it run for a longer time. Or is it that the defragmentation algorithm needs more than a page? Just a thought.. ByeShridhar -- Weed's Axiom: Never ask two questions in a business letter. The reply will discuss the one in which you are least interested and say nothing about the other.
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes: > Hi, > > I was just wondering over it. This is for difference between vacuum full and > vacuum analyze. Can somebody enlighten, > > 1. IIRC vacuum recovers/reuses dead tuples generated from update but can not do > so for delete? Why? YDNRC. > 2. Vacuum full locks entire table, is it possible that it locks a > page at a time and deal with it. It will make vacuum full > non-blocking at the cost of letting it run for a longer time. Or is > it that the defragmentation algorithm needs more than a page? This I don't know, but I imagine that if what you suggest was easy to do it would have been done, and there would have been no need for two different kinds of VACUUM. -DOUG
On 28 Jul 2003 at 9:11, Doug McNaught wrote: > "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes: > > > Hi, > > > > I was just wondering over it. This is for difference between vacuum full and > > vacuum analyze. Can somebody enlighten, > > > > 1. IIRC vacuum recovers/reuses dead tuples generated from update but can not do > > so for delete? Why? > > YDNRC. You did not read... C for what? Code? > > > 2. Vacuum full locks entire table, is it possible that it locks a > > page at a time and deal with it. It will make vacuum full > > non-blocking at the cost of letting it run for a longer time. Or is > > it that the defragmentation algorithm needs more than a page? > > This I don't know, but I imagine that if what you suggest was easy to > do it would have been done, and there would have been no need for two > different kinds of VACUUM. I went thr. the code, although vbery briefly but I can imagine that code being dependent upon tons of other things. Didn't understand everything so left it as it is.. ByeShridhar -- Mix's Law: There is nothing more permanent than a temporary building. There is nothing more permanent than a temporary tax.
On Mon, Jul 28, 2003 at 02:29:36PM +0530, Shridhar Daithankar wrote:
> I was just wondering over it. This is for difference between vacuum full and
> vacuum analyze. Can somebody enlighten,
Actually, the different concepts are "lazy vacuum" (plain VACUUM
command, with or without ANALYZE) and full vacuum ("VACUUM FULL"
command, with or without ANALYZE).
Lazy vacuum works one page at a time, so it doesn't need to lock the
entire table. It is able to recover empty space from both updated and
deleted tuples -- in fact, they look the same to it. All free space on
each page is defragmented. Pages with free space are recorded in the
Free Space Map. The FSM has limited space available, so only the pages
with the most free space will be recorded.
Vacuum full locks the entire table and moves tuples between pages. It
leaves all pages full of tuples (except, obviously, the last one), so it
doesn't need to record them in the FSM. Pages that are empty at the end
of the table are truncated. This was the only version of VACUUM present
in releases previous to 7.2.
If I got something wrong, I'm sure someone will correct me.
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"I dream about dreams about dreams", sang the nightingale
under the pale moon (Sandman)
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> 1. IIRC vacuum recovers/reuses dead tuples generated from update but can not do
> so for delete? Why?
This is not correct.
> 2. Vacuum full locks entire table, is it possible that it locks a page at a
> time and deal with it.
No. You can't compact the table by moving tuples without locking the
entire table. (For example, if we move a tuple from the end down to an
earlier page, it's quite possible that a concurrently executing
sequential scan would miss that tuple entirely. Another problem is that
we cannot truncate the table to fewer pages without locking out writers;
else we may decide that there are N empty pages, then execute ftruncate()
just after someone has put a new tuple into one of those pages.)
Non-full vacuum is designed specifically to do what can be done without
an exclusive lock.
regards, tom lane
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> Vacuum full locks the entire table and moves tuples between pages. It
> leaves all pages full of tuples (except, obviously, the last one), so it
> doesn't need to record them in the FSM.
This is overoptimistic :-(. VACUUM FULL cannot necessarily compact the
table completely, and so it will record free space in FSM (if there is
any worth recording). An example situation is that page 1000 may
contain a very large tuple, which will not fit on any earlier page.
Once VACUUM FULL discovers this fact, it will not bother shuffling
tuples on earlier pages, since it's not going to be able to truncate the
table to less than 1000 pages. There may nonetheless be enough space
available in earlier pages to store thousands of smaller-sized tuples.
regards, tom lane
Robert Treat <xzilla@users.sourceforge.net> writes:
>> This is overoptimistic :-(. VACUUM FULL cannot necessarily compact the
>> table completely, and so it will record free space in FSM (if there is
>> any worth recording). An example situation is that page 1000 may
>> contain a very large tuple, which will not fit on any earlier page.
> Isn't it possible that the reshuffling of tuples before page 1000 could
> open up enough space to move the overly large tuple?
Not in the same vacuum pass. Reshuffling opens *zero* space until you
commit the shuffling transaction, because you can't destroy the old
copies until you commit the moved ones.
You could imagine making multiple passes, but at that point it's almost
certainly faster to forget the VACUUM FULL approach entirely, and do
something more like CLUSTER: copy all the live tuples into a new file.
regards, tom lane
On Mon, 2003-07-28 at 11:04, Tom Lane wrote:
> Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> > Vacuum full locks the entire table and moves tuples between pages. It
> > leaves all pages full of tuples (except, obviously, the last one), so it
> > doesn't need to record them in the FSM.
>
> This is overoptimistic :-(. VACUUM FULL cannot necessarily compact the
> table completely, and so it will record free space in FSM (if there is
> any worth recording). An example situation is that page 1000 may
> contain a very large tuple, which will not fit on any earlier page.
> Once VACUUM FULL discovers this fact, it will not bother shuffling
> tuples on earlier pages, since it's not going to be able to truncate the
> table to less than 1000 pages. There may nonetheless be enough space
> available in earlier pages to store thousands of smaller-sized tuples.
>
Isn't it possible that the reshuffling of tuples before page 1000 could
open up enough space to move the overly large tuple?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On 28 Jul 2003 at 9:56, Alvaro Herrera wrote:
> On Mon, Jul 28, 2003 at 02:29:36PM +0530, Shridhar Daithankar wrote:
>
> > I was just wondering over it. This is for difference between vacuum full and
> > vacuum analyze. Can somebody enlighten,
>
> Actually, the different concepts are "lazy vacuum" (plain VACUUM
> command, with or without ANALYZE) and full vacuum ("VACUUM FULL"
> command, with or without ANALYZE).
>
> Lazy vacuum works one page at a time, so it doesn't need to lock the
> entire table. It is able to recover empty space from both updated and
> deleted tuples -- in fact, they look the same to it. All free space on
> each page is defragmented. Pages with free space are recorded in the
> Free Space Map. The FSM has limited space available, so only the pages
> with the most free space will be recorded.
>
> Vacuum full locks the entire table and moves tuples between pages. It
> leaves all pages full of tuples (except, obviously, the last one), so it
> doesn't need to record them in the FSM. Pages that are empty at the end
> of the table are truncated. This was the only version of VACUUM present
> in releases previous to 7.2.
OK. So here is my interpretation,
Vacuum full reclaims the space that is spilled to disk due to insufficient
vacuumi analyze and/or inadequate FSM size.
So to keep your database free from fat, use adequate FSM and use a autovacuum
daemon..
Am I going overboard here?
ByeShridhar
--
system-independent, adj.: Works equally poorly on all systems.