Skip site navigation (1) Skip section navigation (2)

Re: Problem with 8.4 stats collector high load

From: Jakub Ouhrabka <jakub(dot)ouhrabka(at)comgate(dot)cz>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Jakub Ouhrabka <kuba(at)comgate(dot)cz>, Greg Smith <greg(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Problem with 8.4 stats collector high load
Date: 2010-02-16 17:51:25
Message-ID: 4B7ADB1D.9000300@comgate.cz (view raw or flat)
Thread:
Lists: pgsql-hackers
 > Maybe you should decrease naptime a bit.

That did the trick, thanks!

 > Yes.  There were some changes that needed to be done to autovacuum so
 > that it didn't read the stats file too often, but I don't recall if I
 > got around to it.

I looked at the strace output and there are *writes* to the file not 
reads. Why? Is it a consequence of this optimization?

Release notes 8.4:

Reduce I/O load of writing the statistics collection file by writing the 
file only when requested (Martin Pihlak)

Was autovacuum requesting to write this 20MB file 650x per minute?

Anyway, thank you all for the quick answer and precise answers. 
PostgreSQL is really unique in this regard!

Kuba

Dne 16.2.2010 15:10, Alvaro Herrera napsal(a):
> Jakub Ouhrabka wrote:
>>> You might want to try setting log_autovacuum_min_duration=0 in the
>>> postgresql.conf
>>
>> Thanks, tried it. There is nothing in the log - the actual
>> vacuum/analyze commands are not run (as there is no query activity).
>> I suspect that autovacuum is checking each database if it should run
>> - and decides not to run. See the randomly catch process in ps
>> output/pg_stat_activity mentioned in earlier mail. I suspect that
>> this checking generates the load. Is it possible?
>
> Yes.  There were some changes that needed to be done to autovacuum so
> that it didn't read the stats file too often, but I don't recall if I
> got around to it.
>
> Note that autovacuum_naptime=1min (default value) means that it's
> checking stats 650 times per minute (there's a throttle IIRC but still).
> Maybe you should decrease naptime a bit.
>

In response to

Responses

pgsql-hackers by date

Next:From: David E. WheelerDate: 2010-02-16 17:51:34
Subject: Re: PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
Previous:From: Matthias BrantnerDate: 2010-02-16 17:47:25
Subject: Re: XQuery support

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group