Thread: 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
Attachment
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.