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

From: Ildus Kurbangaliev <i(dot)kurbangaliev(at)postgrespro(dot)ru>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>, Peter Eisentraut <peter_e(at)gmx(dot)net>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, Bruce Momjian <bruce(at)momjian(dot)us>
Subject: Re: RFC: replace pg_stat_activity.waiting with something more descriptive
Date: 2015-07-16 14:28:02
Message-ID: C784BDD3-07AF-4E50-AF1D-F71D9A138117@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers


> 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

Attachment Content-Type Size
gtd_test.c application/octet-stream 573 bytes
unknown_filename text/plain 2 bytes

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Petr Jelinek 2015-07-16 14:45:58 Re: TABLESAMPLE patch is really in pretty sad shape
Previous Message Tom Lane 2015-07-16 14:22:08 Re: TABLESAMPLE doesn't actually satisfy the SQL spec, does it?