Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with explain analyze finally)
What platform is this on? It seems very strange/fishy that all the actual-time values are exact integral milliseconds. My machine is WinXP professional, athon xp 2100, but I get similar results on my Intel P4 3.0Ghz as well (which is also running WinXP). Why do you ask? Well, what it suggests is that gettimeofday() is only returning a result good to the nearest millisecond. (Win32 hackers, does that sound right?) Yes. The gettimeofday() implementation (in src/backend/port/gettimeofday.c). Actually, in reality you don't even get millisecond resolution it seems (after some reading up). More along the line of 10-millisecond-resolution. See for example http://msdn.microsoft.com/msdnmag/issues/04/03/HighResolutionTimer/. Most modern machines seem to have clocks that can count elapsed time down to near the microsecond level. Anyone know if it's possible to get such numbers out of Windows, or are we stuck with milliseconds? There are, see link above. But it's definitly not easy. I don't think we can just take the complete code from their exmaple (due to licensing). We could go with the middle way, but it has a couple of pitfalls. Do we need actual high precision time, or do we just need to be able to get high precision differences? Getting the differences is fairly easy, but if you need to sync up any drif then it becomes a bit more difficult. //Magnus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with explain analyze finally)
Do we need actual high precision time, or do we just need to be able to get high precision differences? Getting the differences is fairly easy, but if you need to sync up any drif then it becomes a bit more difficult. You're right, we only care about differences not absolute time. If there's something like a microseconds-since-bootup counter, it would be fine. There is. I beleive QueryPerformanceCounter has sub-mirosecond resolution. Can we just replace gettimeofday() with a version that's basically: if (never_run_before) GetSystemTime() and get current timer for baseline. now = baseline + current timer - baseline timer; return now; Or do we need to make changes at the points where the function is actually called? //Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with explain analyze finally)
Magnus Hagander [EMAIL PROTECTED] writes: There is. I beleive QueryPerformanceCounter has sub-mirosecond resolution. Can we just replace gettimeofday() with a version that's basically: No, because it's also used for actual time-of-day calls. It'd be necessary to hack executor/instrument.c in particular. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
Tom Lane wrote:
Magnus Hagander [EMAIL PROTECTED] writes:
There is. I beleive QueryPerformanceCounter has sub-mirosecond
resolution.
Can we just replace gettimeofday() with a version that's basically:
No, because it's also used for actual time-of-day calls. It'd be
necessary to hack executor/instrument.c in particular.
regards, tom lane
It seems that there are 2 possibilities. Leave gettimeofday as it is,
and then change code that calls it for deltas with a
pg_get_high_res_delta_time(), which on most platforms is just
gettimeofday, but on win32 is a wrapper for QueryPerformanceCounter().
Or we modify the win32 gettimeofday call to something like:
gettimeofday(struct timeval *tv, struct timezone *tz)
{
static int initialized = 0;
static LARGE_INTEGER freq = {0};
static LARGE_INTEGER base = {0};
static struct time_t base_tm = {0};
LARGE_INTEGER now = {0};
int64_t delta_secs = 0;
if(!initialized) {
QueryPerformanceFrequency(freq);
base_tm = time(NULL); // This can be any moderately accurate time
function, maybe getlocaltime if it exists
QueryPerformanceCounter(base);
}
QueryPerformanceCounter(now);
delta_secs = now.QuadPart - base.QuadPart;
tv-tv_sec = delta_secs / freq.QuadPart;
delta_secs -= *tv.tv_sec * freq.QuadPart;
tv-tv_usec = delta_secs * 100 / freq.QuadPart
tv-tv_sec += base_tm;
return 0;
}
signature.asc
Description: OpenPGP digital signature
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with explain analyze finally)
John A Meinel [EMAIL PROTECTED] writes: Can we just replace gettimeofday() with a version that's basically: No, because it's also used for actual time-of-day calls. It'd be necessary to hack executor/instrument.c in particular. Or we modify the win32 gettimeofday call to something like: That's what Magnus was talking about, but it's really no good because it would cause Postgres' now() function to fail to track post-boot-time changes in the system date setting. Which I think would rightly be considered a bug. The EXPLAIN ANALYZE instrumentation code will really be happier with a straight time-since-bootup counter; by using gettimeofday, it is vulnerable to giving wrong answers if someone changes the date setting while the EXPLAIN is running. But there is (AFAIK) no such call among the portable Unix syscalls. It seems reasonable to me to #ifdef that code to make use of QueryPerformanceCounter on Windows. This does not mean we want to alter the behavior of gettimeofday() where it's being used to find out the time of day. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
John A Meinel [EMAIL PROTECTED] writes: Dave Held wrote: There is always clock(). My experience with clock() on win32 is that CLOCKS_PER_SEC was 1000, and it had a resolution of 55clocks / s. When I just did this: The other problem is it measures process CPU time, not elapsed time which is probably more significant for our purposes. Which brings up a question: just what does QueryPerformanceCounter measure? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
Dave Held [EMAIL PROTECTED] writes: What would be really neato would be to use the rtdsc (sp?) or equivalent assembly instruction where available. Most processors provide such a thing and it would give much lower overhead and much more accurate answers. The main problem I see with this would be on multi-processor machines. (QueryPerformanceCounter does work properly on multi-processor machines, right?) I believe QueryPerformanceCounter() already does this. This would be a good example of why selectively quoting the part of the message to which you're responding to is more useful than just blindly echoing my message back to me. Already does what? Use rtdsc? In which case using it would be a mistake. Since rtdsc doesn't work across processors. And using it via QueryPerformanceCounter would be a non-portable approach to using rtdsc. Much better to devise a portable approach that works on any architecture where something equivalent is available. Or already works on multi-processor machines? In which case, uh, ok. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
-Original Message- From: Greg Stark [mailto:[EMAIL PROTECTED] Sent: Monday, March 07, 2005 5:15 PM To: Dave Held Cc: Greg Stark; John A Meinel; Tom Lane; Magnus Hagander; Ken Egervari; pgsql-performance@postgresql.org; [EMAIL PROTECTED] Subject: Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with Dave Held [EMAIL PROTECTED] writes: What would be really neato would be to use the rtdsc (sp?) or equivalent assembly instruction where available. Most processors provide such a thing and it would give much lower overhead and much more accurate answers. The main problem I see with this would be on multi-processor machines. (QueryPerformanceCounter does work properly on multi-processor machines, right?) I believe QueryPerformanceCounter() already does this. [...] Already does what? Use rtdsc? Yes. In which case using it would be a mistake. Since rtdsc doesn't work across processors. It doesn't always use RDTSC. I can't find anything authoritative on when it does. I would assume that it would use RDTSC when available and something else otherwise. And using it via QueryPerformanceCounter would be a non-portable approach to using rtdsc. Much better to devise a portable approach that works on any architecture where something equivalent is available. How do you know that QueryPerformanceCounter doesn't use RDTSC where available, and something appropriate otherwise? I don't see how any strategy that explicitly executes RDTSC can be called portable. Or already works on multi-processor machines? In which case, uh, ok. According to MSDN it does work on MP systems, and they say that it doesn't matter which CPU gets called. __ David B. Held Software Engineer/Array Services Group 200 14th Ave. East, Sartell, MN 56377 320.534.3637 320.253.7800 800.752.8129 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, March 07, 2005 4:57 PM To: John A Meinel Cc: Dave Held; pgsql-performance@postgresql.org; [EMAIL PROTECTED] Subject: Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with John A Meinel [EMAIL PROTECTED] writes: Dave Held wrote: There is always clock(). My experience with clock() on win32 is that CLOCKS_PER_SEC was 1000, and it had a resolution of 55clocks / s. Which is why I suggested QueryPerformanceCounter for Win32. I only suggested clock() for *nix. The other problem is it measures process CPU time, not elapsed time which is probably more significant for our purposes. Actually, the bigger problem is that a quick test of clock() on Linux shows that it only has a maximum resolution of 10ms on my hardware. Looks like gettimeofday() is the best choice. Which brings up a question: just what does QueryPerformanceCounter measure? I think it measures raw CPU cycles, roughly, which seems like it would more or less correspond to wall time. __ David B. Held Software Engineer/Array Services Group 200 14th Ave. East, Sartell, MN 56377 320.534.3637 320.253.7800 800.752.8129 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
On Mon, Mar 07, 2005 at 06:11:34PM -0600, Dave Held wrote: In which case using it would be a mistake. Since rtdsc doesn't work across processors. It doesn't always use RDTSC. I can't find anything authoritative on when it does. I would assume that it would use RDTSC when available and something else otherwise. RDTSC is a bad source of information for this kind of thing, as the CPU frequency might vary. Check your QueryPerformanceFrequency() -- most likely it will not match your clock speed. I haven't tested on a lot of machines, but I've never seen QueryPerformanceFrequency() ever match the clock speed, which it most probably would if it was using RDTSC. (I've been told it uses some other kind of timer available on most motherboards, but I don't know the details.) /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
Steinar H. Gunderson [EMAIL PROTECTED] writes: RDTSC is a bad source of information for this kind of thing, as the CPU frequency might vary. One thought that was bothering me was that if the CPU goes idle while waiting for disk I/O, its clock might stop or slow down dramatically. If we believed such a counter for EXPLAIN, we'd severely understate the cost of disk I/O. I dunno if that is the case on any Windows hardware or not, but none of this thread is making me feel confident that we know what QueryPerformanceCounter does measure. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
From the Linux Kernel (make menuconfig) there seem to be two new reliable sources for timing information. Note the remark about Time Stamp Counter below. Question is, which one of these (or others) are your API functions using ? I have absolutely no idea ! CONFIG_HPET_TIMER: This enables the use of the HPET for the kernel's internal timer. HPET is the next generation timer replacing legacy 8254s. You can safely choose Y here. However, HPET will only be activated if the platform and the BIOS support this feature. Otherwise the 8254 will be used for timing services. Choose N to continue using the legacy 8254 timer. Symbol: HPET_TIMER [=y] Prompt: HPET Timer Support Defined at arch/i386/Kconfig:440 Location: - Processor type and features CONFIG_X86_PM_TIMER: The Power Management Timer is available on all ACPI-capable, in most cases even if ACPI is unusable or blacklisted. This timing source is not affected by powermanagement features like aggressive processor idling, throttling, frequency and/or voltage scaling, unlike the commonly used Time Stamp Counter (TSC) timing source. So, if you see messages like 'Losing too many ticks!' in the kernel logs, and/or you are using this on a notebook which does not yet have an HPET, you should say Y here. Symbol: X86_PM_TIMER [=y] Prompt: Power Management Timer Support Defined at drivers/acpi/Kconfig:319 Depends on: !X86_VOYAGER !X86_VISWS !IA64_HP_SIM (IA64 || X86) X86 ACPI ACPI_ Location: - Power management options (ACPI, APM)- ACPI (Advanced Configuration and Power Interface) Support - ACPI Support (ACPI [=y]) On Tue, 08 Mar 2005 03:06:24 +0100, Steinar H. Gunderson [EMAIL PROTECTED] wrote: On Mon, Mar 07, 2005 at 09:02:38PM -0500, Tom Lane wrote: One thought that was bothering me was that if the CPU goes idle while waiting for disk I/O, its clock might stop or slow down dramatically. If we believed such a counter for EXPLAIN, we'd severely understate the cost of disk I/O. I dunno if that is the case on any Windows hardware or not, but none of this thread is making me feel confident that we know what QueryPerformanceCounter does measure. I believe the counter is actually good in such a situation -- I'm not a Win32 guru, but I believe it is by far the best timer for measuring, well, performance of a process like this. After all, it's what it was designed to be :-) OBTW, I think I can name something like 15 or 20 different function calls to measure time in the Win32 API (all of them in use); it really is a giant mess. /* Steinar */ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
Tom Lane wrote: John A Meinel [EMAIL PROTECTED] writes: Dave Held wrote: There is always clock(). My experience with clock() on win32 is that CLOCKS_PER_SEC was 1000, and it had a resolution of 55clocks / s. When I just did this: The other problem is it measures process CPU time, not elapsed time which is probably more significant for our purposes. Which brings up a question: just what does QueryPerformanceCounter measure? regards, tom lane clock() according to the Visual Studio Help measures wall clock time. But you're right, POSIX says it is approximation of processor time. The docs don't say specifically what QueryPerformanceCounter() measures, but states The *QueryPerformanceCounter* function retrieves the current value of the high-resolution performance counter. It also states: Remarks On a multiprocessor machine, it should not matter which processor is called. However, you can get different results on different processors due to bugs in the BIOS or the HAL. To specify processor affinity for a thread, use the *SetThreadAffinityMask* function. So it sounds like it is actually querying some counter independent of processing. In fact, there is also this statement: *QueryPerformanceFrequency* The QueryPerformanceFrequency function retrieves the frequency of the high-resolution performance counter, if one exists. The frequency cannot change while the system is running. If that is accurate, it would make QueryPerformanceCounter independent of things like speed stepping, etc. So again, it sounds independent of processing. John =:- signature.asc Description: OpenPGP digital signature
