Re: [HACKERS] Doubt w.r.t vacuum
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? Bye Shridhar -- system-independent, adj.: Works equally poorly on all systems. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] 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.. Bye Shridhar -- Weed's Axiom: Never ask two questions in a business letter. The reply will discuss the one in which you areleast interested and say nothing about the other. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Doubt w.r.t vacuum
Shridhar Daithankar [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Doubt w.r.t vacuum
On 28 Jul 2003 at 9:11, Doug McNaught wrote: Shridhar Daithankar [EMAIL PROTECTED] 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.. Bye Shridhar -- Mix's Law: There is nothing more permanent than a temporary building. There is nothing more permanent than a temporary tax. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Doubt w.r.t vacuum
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) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Doubt w.r.t vacuum
Shridhar Daithankar [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Doubt w.r.t vacuum
Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Doubt w.r.t vacuum
Robert Treat [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Doubt w.r.t vacuum
On Mon, 2003-07-28 at 11:04, Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 8: explain analyze is your friend
