pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVacuum takes forever
9+ messages / 6 participants
[nested] [flat]
* Vacuum takes forever
@ 2007-05-29 17:03 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2007-05-29 17:16 ` Re: Vacuum takes forever PFC <lists@peufeu.com>
2007-05-29 20:43 ` Re: Vacuum takes forever Dave Page <dpage@postgresql.org>
2007-06-11 02:51 ` Re: Vacuum takes forever Jim Nasby <decibel@decibel.org>
0 siblings, 3 replies; 9+ messages in thread
From: Joost Kraaijeveld @ 2007-05-29 17:03 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Vacuum takes forever
2007-05-29 17:03 Vacuum takes forever Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2007-05-29 17:16 ` PFC <lists@peufeu.com>
2007-05-29 17:56 ` Re: Vacuum takes forever Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2 siblings, 1 reply; 9+ messages in thread
From: PFC @ 2007-05-29 17:16 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>; pgsql-performance
> 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
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Vacuum takes forever
2007-05-29 17:03 Vacuum takes forever Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2007-05-29 17:16 ` Re: Vacuum takes forever PFC <lists@peufeu.com>
@ 2007-05-29 17:56 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2007-05-30 14:11 ` Re: Vacuum takes forever Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 9+ messages in thread
From: Joost Kraaijeveld @ 2007-05-29 17:56 UTC (permalink / raw)
To: PFC <lists@peufeu.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Vacuum takes forever
2007-05-29 17:03 Vacuum takes forever Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2007-05-29 17:16 ` Re: Vacuum takes forever PFC <lists@peufeu.com>
2007-05-29 17:56 ` Re: Vacuum takes forever Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2007-05-30 14:11 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 9+ messages in thread
From: Andrew Sullivan @ 2007-05-30 14:11 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Vacuum takes forever
2007-05-29 17:03 Vacuum takes forever Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2007-05-29 20:43 ` Dave Page <dpage@postgresql.org>
2007-05-29 20:49 ` Re: Vacuum takes forever Joshua D. Drake <jd@commandprompt.com>
2007-05-30 03:58 ` Re: Vacuum takes forever Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2 siblings, 2 replies; 9+ messages in thread
From: Dave Page @ 2007-05-29 20:43 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Vacuum takes forever
2007-05-29 17:03 Vacuum takes forever Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2007-05-29 20:43 ` Re: Vacuum takes forever Dave Page <dpage@postgresql.org>
@ 2007-05-29 20:49 ` Joshua D. Drake <jd@commandprompt.com>
1 sibling, 0 replies; 9+ messages in thread
From: Joshua D. Drake @ 2007-05-29 20:49 UTC (permalink / raw)
To: Dave Page <dpage@postgresql.org>; +Cc: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; pgsql-performance
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Vacuum takes forever
2007-05-29 17:03 Vacuum takes forever Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2007-05-29 20:43 ` Re: Vacuum takes forever Dave Page <dpage@postgresql.org>
@ 2007-05-30 03:58 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2007-05-30 08:21 ` Re: Vacuum takes forever Dave Page <dpage@postgresql.org>
1 sibling, 1 reply; 9+ messages in thread
From: Joost Kraaijeveld @ 2007-05-30 03:58 UTC (permalink / raw)
To: Dave Page <dpage@postgresql.org>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Vacuum takes forever
2007-05-29 17:03 Vacuum takes forever Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2007-05-29 20:43 ` Re: Vacuum takes forever Dave Page <dpage@postgresql.org>
2007-05-30 03:58 ` Re: Vacuum takes forever Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2007-05-30 08:21 ` Dave Page <dpage@postgresql.org>
0 siblings, 0 replies; 9+ messages in thread
From: Dave Page @ 2007-05-30 08:21 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Vacuum takes forever
2007-05-29 17:03 Vacuum takes forever Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2007-06-11 02:51 ` Jim Nasby <decibel@decibel.org>
2 siblings, 0 replies; 9+ messages in thread
From: Jim Nasby @ 2007-06-11 02:51 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-performance
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-06-11 02:51 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-29 17:03 Vacuum takes forever Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2007-05-29 17:16 ` PFC <lists@peufeu.com>
2007-05-29 17:56 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2007-05-30 14:11 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-05-29 20:43 ` Dave Page <dpage@postgresql.org>
2007-05-29 20:49 ` Joshua D. Drake <jd@commandprompt.com>
2007-05-30 03:58 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2007-05-30 08:21 ` Dave Page <dpage@postgresql.org>
2007-06-11 02:51 ` Jim Nasby <decibel@decibel.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox