Re: pg_stat_activity.waiting_start

Joel Jacobson <joel(at)trustly(dot)com> writes:
> On Sat, Dec 24, 2016 at 9:00 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> The difficulty with that is it'd require a gettimeofday() call for
>> every wait start.

> I don't think we need the microsecond resolution provided by
> gettimeofday() via GetCurrentTimestamp()
> It would be enough to know which second the waiting started, so we
> could use time().

For some use-cases, perhaps ...

> gettimeofday() takes 42 cycles.
> time() only takes 3 cycles. [1]

... on some platforms, perhaps, back in 2011. My own quick testing flatly
contradicts that. On Linux (RHEL6) x86_64, I see gettimeofday() taking
about 40ns (or circa 100 CPU cycles @ 2.4GHz), time() only a shade better
at 38ns. On macOS x86_64 2.7GHz, I see gettimeofday() taking about 37ns,
time() way behind at 178ns. I think we'd need at least an order of
magnitude cheaper to consider putting timing calls into spinlock or lwlock
paths, and that's just not available at all, let alone portably.

This is not an easy problem. See our most recent discussion at
https://www.postgresql.org/message-id/flat/31856.1400021891%40sss.pgh.pa.us

I'm prepared to consider an argument that wait timing might have weaker
requirements than EXPLAIN ANALYZE (which certainly needs to measure short
durations) but you didn't actually make that argument.

regards, tom lane

Linux:

$ gcc -Wall -O2 -o time-timing-calls time-timing-calls.c
$ time ./time-timing-calls

real 0m37.678s
user 0m37.685s
sys 0m0.002s
$ gcc -Wall -O2 -o time-timing-calls -DUSE_GT time-timing-calls.c
$ time ./time-timing-calls

real 0m39.964s
user 0m39.971s
sys 0m0.001s

macOS:

$ gcc -Wall -O2 -o time-timing-calls time-timing-calls.c
$ time ./time-timing-calls

real 2m58.683s
user 2m58.515s
sys 0m0.131s
$ gcc -Wall -O2 -o time-timing-calls -DUSE_GT time-timing-calls.c
$ time ./time-timing-calls

real 0m37.004s
user 0m36.993s
sys 0m0.006s