Skip site navigation (1) Skip section navigation (2)

Re: pg_test_timing tool for EXPLAIN ANALYZE overhead

From: Greg Smith <greg(at)2ndQuadrant(dot)com>
To: Jay Levitt <jay(dot)levitt(at)gmail(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, ants(dot)aasma(at)eesti(dot)ee
Subject: Re: pg_test_timing tool for EXPLAIN ANALYZE overhead
Date: 2012-02-22 16:44:40
Message-ID: 4F451B78.1060607@2ndQuadrant.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 02/22/2012 11:10 AM, Jay Levitt wrote:
> N.B.: Windows has at least two clock APIs, timeGetTime and
> QueryPerformanceCounters (and probably more, these days). They rely on
> different hardware clocks, and can get out of sync with each other;
> meanwhile, QueryPerformanceCounters can get out of sync with itself on
> (older?) multi-CPU boards.

The PostgreSQL wrapper in src/include/portability/instr_time.h uses 
QueryPerformanceCounter and QueryPerformanceFrequency in a way that the 
result can be used similarly to how deltas in UNIX dates are returned.

As far as I've been able to tell, there aren't any issues unique to 
Windows there.  Multiple cores can have their TSC results get out of 
sync on Windows for the same reason they do on Linux systems, and 
there's also the same frequency/temperature issues.

Newer versions of Windows can use TSC, older versions only use HPET or 
ACPI on older versions, and there's some ability to force bad TSC units 
to use ACPI instead:

http://blogs.msdn.com/b/psssql/archive/2010/08/18/how-it-works-timer-outputs-in-sql-server-2008-r2-invariant-tsc.aspx
http://blogs.msdn.com/b/psssql/archive/2010/08/18/how-it-works-timer-outputs-in-sql-server-2008-r2-invariant-tsc.aspx

There is a lot of questionable behavior if you try to use the better 
timers in Windows XP; check out the obnoxious foot note about XP SP3 at 
http://en.wikipedia.org/wiki/High_Precision_Event_Timer for example.

Since there's little you can do about--it's not even possible to figure 
out which timer is being used easily--I didn't see any easy ways to 
document what Windows does here, in a way that helped anyone.  For the 
most part, you get the best timer Windows has a driver for with 
QueryPerformanceCounter, and that's what the PostgreSQL code uses.  The 
information I gave about how newer systems can have more accurate timing 
is still accurate.  Maybe it would be useful to add something pointing 
out that newer Windows versions tend to support faster timers, too. 
That's something I assume people would guess from what I already wrote, 
it may be worth stating explicitly though.


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

In response to

Responses

pgsql-hackers by date

Next:From: Noah MischDate: 2012-02-22 17:00:07
Subject: Re: foreign key locks, 2nd attempt
Previous:From: Ants AasmaDate: 2012-02-22 16:35:54
Subject: Re: Patch: add timing of buffer I/O requests

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group