Re: [PATCHES] stored procedure stats in collector

[ redirecting to pghackers for wider discussion ]

Martin Pihlak <martin(dot)pihlak(at)gmail(dot)com> writes:
>> What I think we should do about that is forget tracking getrusage()'s
>> user/system/real time and just track elapsed time.

> I find the utime/stime quite useful, compared with the actual time it
> enables us to distinguish waiters (remote calls, sleeps, etc) from the
> actual CPU hogs.

Well, what it is going to cost us to have that is double the space
in the pgstats file (64 bytes per function instead of 32) --- and that
isn't a choice we can flip with a GUC. This is a hot button for a
lot of people because we know that bloat in the pgstats file translates
directly to continual I/O overhead. (Perhaps that'll be fixed by the
time this patch hits production, but I'm not holding my breath.)

The runtime overhead is pretty daunting also. It's not just twice as
many kernel calls, it's which ones you are making. On a lot of modern
machines gettimeofday() is optimized to not enter the kernel at all,
while getrusage() will hardly be. [ click click, test test ] On my
x86_64 Fedora 8 machine, it appears that gettimeofday() requires about
60 nsec per call, whereas getrusage(RUSAGE_SELF) requires 788 nsec.

One other point here is that accuracy of the results is questionable.
On Windows we will certainly find that gettimeofday is useless and
we need to use QueryPerformanceCounter instead (see the code in
instrument.h/.c). I wonder what the accuracy of GetProcessTimes
is and whether it will even deliver answers consistent with
QueryPerformanceCounter. On Unix-ish machines the corresponding
worry is that getrusage results might be tracked only to the clock
tick and not any finer grain.

Double the pgstats storage and a dozen times as much runtime overhead
in exchange for questionable numbers is a pretty hard sell. I remain
of the opinion that we should just track elapsed time.

regards, tom lane