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

> On Jul 14, 2015, at 5:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Robert Haas <robertmhaas@gmail.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@3.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