Re: RFC: replace pg_stat_activity.waiting with something more descriptive

> On Jul 14, 2015, at 5:25 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I really think we should do the simple thing first. If we make this
>> complicated and add lots of bells and whistles, it is going to be much
>> harder to get anything committed, because there will be more things
>> for somebody to object to. If we start with something simple, we can
>> always improve it later, if we are confident that the design for
>> improving it is good. The hardest thing about a proposal like this is
>> going to be getting down the overhead to a level that is acceptable,
>> and every expansion of the basic design that has been proposed -
>> gathering more than one byte of information, or gathering times, or
>> having the backend update a tracking hash - adds *significant*
>> overhead to the design I proposed.
>
> FWIW, I entirely share Robert's opinion that adding gettimeofday()
> overhead in routinely-taken paths is likely not to be acceptable.
> But there's no need to base this solely on opinions. I suggest somebody
> try instrumenting just one hotspot in the various ways that are being
> proposed, and then we can actually measure what it costs, instead
> of guessing about that.
>
> regards, tom lane
>

I made benchmark of gettimeofday(). I believe it is certainly usable for monitoring.
Testing configuration:
24 cores, Intel Xeon CPU X5675(at)3(dot)07Ghz
RAM 24 GB

54179703 - microseconds total
2147483647 - (INT_MAX), the number of gettimeofday() calls

>>> 54179703 / 2147483647.0
0.025229390256679331

Here we have the average duration of one gettimeofday in microseconds.

Now we get the count of all waits in one minute (pgbench -i -s 500,
2 GB shared buffers, started with -c 96 -j 4, it is almost 100% cpu load).

b1=# select sum(wait_count) from pg_stat_wait_profile;
sum
---------
2113608

So, we can estimate the time of gtd in one minute (it multiplies by two,
because we are going to call it twice for each wait)

>>> 2113608 * 0.025229390256679331 * 2
106650.08216327897

This is time in microseconds that we spend on gtd in one minute.
Calculation of overhead (in percents):

>>> 106650 / 60000000.0 * 100
0.17775