Thread: Vacuum takes forever
Hi I am currently running a vacuum analyse through PgAdmin on a PostgreSQL 8.1.9 database that takes forever without doing anything: no (noticeable) disk activity or (noticeable) CPU activity. The mesage tab in PgAdmin says: ... Detail: 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.00u sec elapsed 568.16 sec .... and lots of entries looking just like this ( 0 % CPU, > 500 secs). There are no other connections to the database and the machine does not do anything else than me typing this e-mail and playing Metallica MP3's. Could this be because of my Cost-Based Vacuum Delay settings ? vacuum_cost_delay = 200 vacuum_cost_page_hit = 6 #vacuum_cost_page_miss = 10 # 0-10000 credits #vacuum_cost_page_dirty = 20 # 0-10000 credits vacuum_cost_limit = 100 -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl
> Could this be because of my Cost-Based Vacuum Delay settings ?
Yeah. It is supposed to slow down VACUUM so it doesn't kill your server,
but it is not aware of the load. It will also slow it down if there is no
load. That is its purpose after all ;)
If you want fast vacuum, issue SET vacuum_cost_delay = 0; before.
>
> vacuum_cost_delay = 200
> vacuum_cost_page_hit = 6
> #vacuum_cost_page_miss = 10 # 0-10000 credits
> #vacuum_cost_page_dirty = 20 # 0-10000 credits
> vacuum_cost_limit = 100
>
>
>
On Tue, 2007-05-29 at 19:16 +0200, PFC wrote: > > > Could this be because of my Cost-Based Vacuum Delay settings ? > > Yeah. It is supposed to slow down VACUUM so it doesn't kill your server, > but it is not aware of the load. It will also slow it down if there is no > load. That is its purpose after all ;) > If you want fast vacuum, issue SET vacuum_cost_delay = 0; before. Thanks, I tried it and it worked. I did not know that changing this setting would result in such a performance drop ( I just followed an advise I read on http://www.powerpostgresql.com/PerfList/) which mentioned a tripling of the the execution time. Not a change from 8201819 ms to 17729 ms. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl
Joost Kraaijeveld wrote: > Hi > > I am currently running a vacuum analyse through PgAdmin on a PostgreSQL > 8.1.9 database that takes forever without doing anything: no > (noticeable) disk activity or (noticeable) CPU activity. > > The mesage tab in PgAdmin says: > > ... > Detail: 0 index pages have been deleted, 0 are currently reusable. > CPU 0.01s/0.00u sec elapsed 568.16 sec > .... > > and lots of entries looking just like this ( 0 % CPU, > 500 secs). > > There are no other connections to the database and the machine does not > do anything else than me typing this e-mail and playing Metallica MP3's. Cliff, Jason or Rob era? Could be important... :-) Regards, Dave.
Dave Page wrote:
>> and lots of entries looking just like this ( 0 % CPU, > 500 secs).
>>
>> There are no other connections to the database and the machine does not
>> do anything else than me typing this e-mail and playing Metallica MP3's.
>
> Cliff, Jason or Rob era? Could be important...
Well Metallica is pretty heavy metal, you might be weighing the machine
down....
/me wonders how many groans were collectively heard through the internet.
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
On Tue, 2007-05-29 at 21:43 +0100, Dave Page wrote: > Cliff, Jason or Rob era? Could be important... Cliff and Jason. Rob is in my Ozzy collection ;-) -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl
Joost Kraaijeveld wrote: > On Tue, 2007-05-29 at 21:43 +0100, Dave Page wrote: >> Cliff, Jason or Rob era? Could be important... > Cliff and Jason. > > Rob is in my Ozzy collection ;-) And rightly so imho. :-) /D
On Tue, May 29, 2007 at 07:56:07PM +0200, Joost Kraaijeveld wrote:
> Thanks, I tried it and it worked. I did not know that changing this
> setting would result in such a performance drop ( I just followed an
It's not a performance drop. It's an on-purpose delay of the
functionality, introduced so that _other_ transactions don't get I/O
starved. ("Make vacuum fast" isn't in most cases an interesting
goal.)
A
--
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin
On May 29, 2007, at 12:03 PM, Joost Kraaijeveld wrote: > vacuum_cost_delay = 200 > vacuum_cost_page_hit = 6 > #vacuum_cost_page_miss = 10 # 0-10000 credits > #vacuum_cost_page_dirty = 20 # 0-10000 credits > vacuum_cost_limit = 100 I didn't see anyone else mention this, so... Those settings are *very* aggressive. I'm not sure why you upped the cost of page_hit or dropped the cost_limit, but I can tell you the effect: vacuum will sleep at least every 17 pages... even if those pages were already in shared_buffers and vacuum didn't have to dirty them. I really can't think of any reason you'd want to do that. I do find vacuum_cost_delay to be an extremely useful tool, but typically I'll set it to between 10 and 20 and leave the other parameters alone. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)