Re: Patch: add timing of buffer I/O requests - Mailing list pgsql-hackers

On 11/27/2011 04:39 PM, Ants Aasma wrote:
> On the AMD I saw about 3% performance drop with timing enabled. On the
> Intel machine I couldn't measure any statistically significant change.

Oh no, it's party pooper time again.  Sorry I have to be the one to do 
it this round.  The real problem with this whole area is that we know 
there are systems floating around where the amount of time taken to grab 
timestamps like this is just terrible.  I've been annoyed enough by that 
problem to spend some time digging into why that is--seems to be a bunch 
of trivia around the multiple ways to collect time info on x86 
systems--and after this CommitFest is over I was already hoping to dig 
through my notes and start quantifying that more.  So you can't really 
prove the overhead of this approach is acceptable just by showing two 
examples; we need to find one of the really terrible clocks and test 
there to get a real feel for the worst-case.

I recall a patch similar to this one was submitted by Greg Stark some 
time ago.  It used the info for different reasons--to try and figure out 
whether reads were cached or not--but I believe it withered rather than 
being implemented mainly because it ran into the same fundamental 
roadblocks here.  My memory could be wrong here, there were also 
concerns about what the data would be used for.

I've been thinking about a few ways to try and cope with this whole 
class of timing problem:

-Document the underlying problem and known workarounds, provide a way to 
test how bad the overhead is, and just throw our hands up and say 
"sorry, you just can't instrument like this" if someone has a slow system.

-Have one of the PostgreSQL background processes keep track of a time 
estimate on its own, only periodically pausing to sync against the real 
time.  Then most calls to gettimeofday() can use that value instead.  I 
was thinking of that idea for slightly longer running things though; I 
doubt that can be made accurate enough to test instrument buffer

And while I hate to kick off massive bike-shedding in your direction, 
I'm also afraid this area--collecting stats about how long individual 
operations take--will need a much wider ranging approach than just 
looking at the buffer cache ones.  If you step back and ask "what do 
people expect here?", there's a pretty large number who really want 
something like Oracle's v$session_wait  and v$system_event interface for 
finding the underlying source of slow things.  There's enough demand for 
that that EnterpriseDB has even done some work in this area too; what 
I've been told about it suggests the code isn't a great fit for 
contribution to community PostgreSQL though.  Like I said, this area is 
really messy and hard to get right.

Something more ambitious like the v$ stuff would also take care of what 
you're doing here; I'm not sure that what you've done helps built it 
though.  Please don't take that personally.  Part of one of my own 
instrumentation patches recently was rejected out of hand for the same 
reason, just not being general enough.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us