Re: Tracking disk writes? (again) & bgwriter

[Erik Jones <erik@xxxxxxxxxx>]

On Mar 12, 2007, at 10:57 PM, Tom Lane wrote:

Alvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx> writes:

Tom Lane wrote:

One of the reasons you don't see that is that a large fraction of the

writes are triggered in background by the "bgwriter" process, which

operates at too low a level to participate in the stats collection

mechanism.  I'm not sure what would be involved in refactoring things

sufficiently to make that workable, but it'd be nontrivial.

You mean that bgwriter cannot send stat messages?

Right.  The stats mechanism is attached to relcache entries, which the

bgwriter doesn't have.  And if it did collect stats, it would never send

them because that happens in the outer postgres.c loop (it's not totally

clear what would be a good granularity for sending them in bgwriter).

And I think it is not a backend in the stats collector's eyes, either.

Surely these things could be dealt with, but it'd take some refactoring.

Tom, 

Thanks for your insights on this.  To be honest, I was kind of expecting you or one of the other core guys to stand up and say, "bgwriter!" as I already expected that if there wasn't currently any accounting from the bgwriter this wouldn't really be feasible.  What are the odds of you guys putting this on a your TODO list for a future postgres release?    Tracking disk level io in both directions would be an invaluable tool for profiling our db over time in order to correlate different kinds of usage of our app with the numbers we get from iostat et al.  Yes, on Solaris (and soon, Linux) DTrace is available for attaching to single processes and tracking what they are doing at the moment, but that doesn't give me the ability to answer the question: "We had reports of app slowness last night, we see via iostat that there was a huge io spike at the time, was it all postgres?

Also, are there any usage scenarios where having the bgwriter on could be detrimental to system performance that we should watch for?

erik jones <erik@xxxxxxxxxx>

sofware developer

615-296-0838

emma(r)