FAQ
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

Search Discussions

  • Alvaro Herrera at May 15, 2007 at 8:48 pm

    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
  • Ron Mayer at May 16, 2007 at 6:34 pm

    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.
  • Tom Lane at May 16, 2007 at 6:50 pm

    Alvaro Herrera 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
  • Alvaro Herrera at May 16, 2007 at 8:49 pm

    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedMay 15, '07 at 3:20p
activeMay 16, '07 at 8:49p
posts5
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase