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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

> 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
>
>
>



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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/


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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)



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match