Re: [PERFORM] Why will vacuum not end?
Josh, how long should a vacuum take on a 87 GB table with a 39 GB index? I do not think that the verbose option of vacuum is verbose enough. The vacuum keeps redoing the index, but there is no indication as to why it is doing this. I see alot of activity with transaction logs being recycled (15 to 30 every 3 to 20 minutes). Is the vacuum causing this? -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Friday, April 23, 2004 2:48 PM To: Shea,Dan [CIS]; 'Christopher Kings-Lynne' Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Why will vacuum not end? Guys, Well, inserts create some locks - perhaps that's the problem... Otherwise, check the pg_locks view to see if you can figure it out. FWIW, I've had this happen a couple of times, too. Unfortunately, it's happend in the middle of the day so that I had to cancel the processes and get the system back to normal in too much of a hurry to consider documenting it. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Why will vacuum not end?
Dan, Josh, how long should a vacuum take on a 87 GB table with a 39 GB index? Depends: -- What's your disk support? -- VACUUM, VACUUM ANALYZE, or VACUUM FULL? -- What's your vacuum_mem setting? -- What are checkpoint and wal settings? I see alot of activity with transaction logs being recycled (15 to 30 every 3 to 20 minutes). Is the vacuum causing this? Probably, yes. How many checkpoint_buffers do you allow? -- Josh Berkus Aglio Database Solutions San Francisco ---(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: [PERFORM] Setting Shared Buffers , Effective Cache, Sort Mem
Tom, It's possible that Jan's recent buffer-management improvements will change the story as of 7.5. I kinda doubt it myself, but it'd be worth re-running any experiments you've done when you start working with 7.5. Yes, Jan has indicated to me that he expects to make much heavier use of shared buffers under ARC.But 7.5 still seems to be too unstable for me to test this assertion on a large database. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Why will vacuum not end?
On Sat, 24 Apr 2004 10:45:40 -0400, Shea,Dan [CIS] [EMAIL PROTECTED] wrote: [...] 87 GB table with a 39 GB index? The vacuum keeps redoing the index, but there is no indication as to why it is doing this. If VACUUM finds a dead tuple, if does not immediately remove index entries pointing to that tuple. It instead collects such tuple ids and later does a bulk delete, i.e. scans the whole index and removes all index items pointing to one of those tuples. The number of tuple ids that can be remembered is controlled by vacuum_mem: it is VacuumMem * 1024 / 6 Whenever this number of dead tuples has been found, VACUUM scans the index (which takes ca. 6 seconds, more than 16 hours), empties the list and continues to scan the heap ... From the number of dead tuples you can estimate how often your index will be scanned. If dead tuples are evenly distributed, expect there to be 15 index scans with your current vacuum_mem setting of 196608. So your VACUUM will run for 11 days :-( OTOH this would mean that there are 500 million dead tuples. Do you think this is possible? Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Why will vacuum not end?
Manfred is indicating the reason it is taking so long is due to the number of dead tuples in my index and the vacuum_mem setting. The last delete that I did before starting a vacuum had 219,177,133 deletions. Dan. Dan, Josh, how long should a vacuum take on a 87 GB table with a 39 GB index? Depends: -- What's your disk support? -- VACUUM, VACUUM ANALYZE, or VACUUM FULL? VACUUM ANALYZE -- What's your vacuum_mem setting? set vacuum_mem = '196608' #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync -- What are checkpoint and wal settings? wal_buffers = 64 checkpoint_segments = 30 checkpoint_timeout = 300 I see alot of activity with transaction logs being recycled (15 to 30 every 3 to 20 minutes). Is the vacuum causing this? Probably, yes. How many checkpoint_buffers do you allow? I am not sure what the checkpoint_buffers are, we are running 7.4.0? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Why will vacuum not end?
There were defintely 219,177,133 deletions. The deletions are most likely from the beginning, it was based on the reception_time of the data. I would rather not use re-index, unless it is faster then using vacuum. What do you think would be the best way to get around this? Increase vacuum_mem to a higher amount 1.5 to 2 GB or try a re-index (rather not re-index so that data can be queried without soing a seqscan). Once the index is cleaned up, how does vacuum handle the table? Does it take as long as the index or is it faster? -Original Message- From: Manfred Koizar [mailto:[EMAIL PROTECTED] Sent: Saturday, April 24, 2004 1:57 PM To: Shea,Dan [CIS] Cc: 'Josh Berkus'; [EMAIL PROTECTED] Subject: Re: [PERFORM] Why will vacuum not end? On Sat, 24 Apr 2004 10:45:40 -0400, Shea,Dan [CIS] [EMAIL PROTECTED] wrote: [...] 87 GB table with a 39 GB index? The vacuum keeps redoing the index, but there is no indication as to why it is doing this. If VACUUM finds a dead tuple, if does not immediately remove index entries pointing to that tuple. It instead collects such tuple ids and later does a bulk delete, i.e. scans the whole index and removes all index items pointing to one of those tuples. The number of tuple ids that can be remembered is controlled by vacuum_mem: it is VacuumMem * 1024 / 6 Whenever this number of dead tuples has been found, VACUUM scans the index (which takes ca. 6 seconds, more than 16 hours), empties the list and continues to scan the heap ... From the number of dead tuples you can estimate how often your index will be scanned. If dead tuples are evenly distributed, expect there to be 15 index scans with your current vacuum_mem setting of 196608. So your VACUUM will run for 11 days :-( OTOH this would mean that there are 500 million dead tuples. Do you think this is possible? Servus Manfred ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Why will vacuum not end?
On Sat, 24 Apr 2004 15:48:19 -0400, Shea,Dan [CIS] [EMAIL PROTECTED] wrote: Manfred is indicating the reason it is taking so long is due to the number of dead tuples in my index and the vacuum_mem setting. nitpicking Not dead tuples in the index, but dead tuples in the table. /nitpicking The last delete that I did before starting a vacuum had 219,177,133 deletions. Ok, with vacuum_mem = 196608 the bulk delete batch size is ca. 33.5 M tuple ids. 219 M dead tuples will cause 7 index scans. The time for an index scan is more or less constant, 6 seconds in your case. So yes, a larger vacuum_mem will help, but only if you really have as much *free* memory. Forcing the machine into swapping would make things worse. BTW, VACUUM frees millions of index pages, is your FSM large enough? Servus Manfred ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Why will vacuum not end?
On Sat, 24 Apr 2004 15:58:08 -0400, Shea,Dan [CIS] [EMAIL PROTECTED] wrote: There were defintely 219,177,133 deletions. The deletions are most likely from the beginning, it was based on the reception_time of the data. I would rather not use re-index, unless it is faster then using vacuum. I don't know whether it would be faster. But if you decide to reindex, make sure sort_mem is *huge*! What do you think would be the best way to get around this? Increase vacuum_mem to a higher amount 1.5 to 2 GB or try a re-index (rather not re-index so that data can be queried without soing a seqscan). Just out of curiosity: What kind of machine is this running on? And how long does a seq scan take? Once the index is cleaned up, how does vacuum handle the table? If you are lucky VACUUM frees half the index pages. And if we assume that the most time spent scanning an index goes into random page accesses, future VACUUMs will take only 3 seconds per index scan. Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Why will vacuum not end?
Dan, There were defintely 219,177,133 deletions. The deletions are most likely from the beginning, it was based on the reception_time of the data. You need to run VACUUM more often, I think.Vacuuming out 219 million dead tuples is going to take a long time no matter how you look at it. If you vacuum more often, presumably there will be less for Vacuum to do each time. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
