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

On 01/15/2012 05:14 PM, Ants Aasma wrote:
> I hope that having a tool to measure the overhead and check the sanity
> of clock sources is enough to answer the worries about the potential
> performance hit. We could also check that the clock source is fast
> enough on start-up/when the guc is changed, but that seems a bit too
> much and leaves open the question about what is fast enough.

I've been thinking along those same lines--check at startup, provide 
some guidance on the general range of what's considered fast vs. slow in 
both the code and documentation.  What I'm hoping to do here is split 
your patch in half and work on the pg_test_timing contrib utility 
first.  That part answers some overdue questions about when EXPLAIN 
ANALYZE can be expected to add a lot of overhead, which means it's 
useful all on its own.  I'd like to see that utility go into 9.2, along 
with a new documentation section covering that topic.  I'll write the 
new documentation bit.

As far as the buffer timing goes, there is a lot of low-level timing 
information I'd like to see the database collect.  To pick a second 
example with very similar mechanics, I'd like to know which queries 
spend a lot of their time waiting on locks.  The subset of time a 
statement spends waiting just for commit related things is a third.  The 
industry standard term for these is wait events, as seen in Oracle, 
MySQL, MS SQL Server. etc.  That's so standard I don't see an 
intellectual property issue with PostgreSQL using the same term.  Talk 
with a random person who is converting from Oracle to PostgreSQL, ask 
them about their performance concerns.  At least 3/4 of those 
conversations I have mention being nervous about not having wait event data.

Right now, I feel the biggest hurdle to performance tuning PostgreSQL is 
not having good enough built-in query log analysis tools.  If the 
pg_stat_statements normalization upgrade in the CF queue is commited, 
that's enough to make me bump that to "solved well enough".  After 
clearing that hurdle, figuring out how to log, analyze, and manage 
storage of wait events is the next biggest missing piece.  One of my top 
goals for 9.3 was to make sure that happened.

I don't think the long-term answer for how to manage wait event data is 
to collect it as part of pg_stat_statements though.  But I don't have a 
good alternate proposal, while you've submitted a patch that actually 
does something useful right now.  I'm going to think some more about how 
to reconcile all that.  There is an intermediate point to consider as 
well, which is just committing something that adjusts the core code to 
make the buffer wait event data available.  pg_stat_statements is easy 
enough to continue work on outside of core.  I could see a path where 
that happens in parallel with adding a better core wait event 
infrastructure, just to get the initial buffer wait info into people's 
hands earlier.

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