Обсуждение: stats collector spins my disk up every 500ms (8.2.3)

While looking for ways to get some more battery life from my laptop, I
noticed that one source of periodic disk writes was the postgres stats
collector process, which appears to write to pgstat.tmp every 500ms.  I
could turn it off (in fact, for the moment I have), but I rather like
having working autovacuum ...

Questions:
1) this is on a system with I believe to be quiescent - there is only
one client open which is not doing anything.  Before I get more involved
with this, can someone just confirm that pgstat will continue to update
this file even when nothing is happening?  If not, then I guess
something /is/ happening and I need to investigate what it is (advice on
how to track it down is welcome - I already have statement logging on,
and there's nothing showing in there)

2) does the pgstat.stat file need to be preserved when postgres is not
running?  i.e. could I move it to a tmpfs or something?  I can see
nothing which suggests it has particularly important long-lived data

3) can anyone suggest a way to move the said file that doesn't involve
hacking postgresql source code?  Not that I'm especially averse to using
a C compiler, but at the moment I'm using Debian packages and therefore
getting my packaging "for free", as it were.

4) if no answer to (3), what would be a sensible approach?  First I
thought it should probably be an option for postgresql.conf, but now I
wonder if it should just be hardcoded to the same directory where the
unix domain socket and the pid file live - they being similarly
transient data.

If this isn't the right mailing list for these questions (I think it
probably is for 1-3 and not for 4 ...) please feel free to move it.


Thanks

-dan

Daniel Barlow wrote:

> Questions:
> 1) this is on a system with I believe to be quiescent - there is only
> one client open which is not doing anything.  Before I get more involved
> with this, can someone just confirm that pgstat will continue to update
> this file even when nothing is happening?  If not, then I guess
> something /is/ happening and I need to investigate what it is (advice on
> how to track it down is welcome - I already have statement logging on,
> and there's nothing showing in there)

Hmm, I don't think we have an optimization to avoid writing it when the
data hasn't changed.  This seems easy to do ... see attached patch
(untested)

> 2) does the pgstat.stat file need to be preserved when postgres is not
> running?  i.e. could I move it to a tmpfs or something?  I can see
> nothing which suggests it has particularly important long-lived data

Yes, the data in the pgstat file is supposed to be kept around when the
system is restarted -- more so if you want autovacuum to work correctly!

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:
> Daniel Barlow wrote:
>> 1 battery life from my laptop, I noticed that one source of periodic disk
>> writes was the postgres stats collector process, which appears to
>> write to pgstat.tmp every 500ms)
>
> Hmm, I don't think we have an optimization to avoid writing it when the
> data hasn't changed.  This seems easy to do ... see attached patch
> (untested)

Any chance something like this can make it to the patch queues.
I had also earlier noticed disks not spinning down when running
demos on my laptop.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Daniel Barlow wrote:

>> Questions:
>> 1) this is on a system with I believe to be quiescent - there is only
>> one client open which is not doing anything.  Before I get more involved
>> with this, can someone just confirm that pgstat will continue to update
>> this file even when nothing is happening?  If not, then I guess
>> something /is/ happening and I need to investigate what it is (advice on
>> how to track it down is welcome - I already have statement logging on,
>> and there's nothing showing in there)

> Hmm, I don't think we have an optimization to avoid writing it when the
> data hasn't changed.  This seems easy to do ... see attached patch
> (untested)

Have you made any effort to confirm this is needed?  Watching the
collector with strace, I don't see it doing anything but poll() unless
new messages come in.  However, in HEAD it seems new messages come in
every 30 seconds or so --- it looks to me like the autovacuum launcher
is rather uselessly launching a new worker every few seconds, and the
worker generates stats in the process of examining a database.
Can we damp that behavior down a bit in an idle system?

I'm not sure about Daniel's report of unprovoked writes every 500ms.
It might be an artifact of the stats delay bug we fixed in 8.2.4,
but this isn't the symptom that was reported.

            regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Daniel Barlow wrote:
>
> >> Questions:
> >> 1) this is on a system with I believe to be quiescent - there is only
> >> one client open which is not doing anything.  Before I get more involved
> >> with this, can someone just confirm that pgstat will continue to update
> >> this file even when nothing is happening?  If not, then I guess
> >> something /is/ happening and I need to investigate what it is (advice on
> >> how to track it down is welcome - I already have statement logging on,
> >> and there's nothing showing in there)
>
> > Hmm, I don't think we have an optimization to avoid writing it when the
> > data hasn't changed.  This seems easy to do ... see attached patch
> > (untested)
>
> Have you made any effort to confirm this is needed?  Watching the
> collector with strace, I don't see it doing anything but poll() unless
> new messages come in.

Hmm, right, it doesn't do anything.

> However, in HEAD it seems new messages come in
> every 30 seconds or so --- it looks to me like the autovacuum launcher
> is rather uselessly launching a new worker every few seconds, and the
> worker generates stats in the process of examining a database.

Correct, it's autovacuum causing it (turning autovac off makes it go
away).

> Can we damp that behavior down a bit in an idle system?

What we could do, I think, is to keep a counter of insert/update/delete
operations per database, and have autovac skip processing one if it sees
that it hasn't had any activity.  That would remove this particular
symptom.

(Magnus wanted to add the global counters anyway for some reason.)

> I'm not sure about Daniel's report of unprovoked writes every 500ms.
> It might be an artifact of the stats delay bug we fixed in 8.2.4,
> but this isn't the symptom that was reported.

Yup.  So going back to his original question,

> >> Before I get more involved
> >> with this, can someone just confirm that pgstat will continue to update
> >> this file even when nothing is happening?  If not, then I guess
> >> something /is/ happening and I need to investigate what it is

I suggest turning log_min_messages to debug2 and seeing if there is any
activity showing up.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.