Re: RFC: replace pg_stat_activity.waiting with something more descriptive - Mailing list pgsql-hackers

16 сент. 2015 г., в 20:52, Robert Haas <robertmhaas@gmail.com> написал(а):

On Wed, Sep 16, 2015 at 12:29 PM, Alexander Korotkov
<aekorotkov@gmail.com> wrote:

Yes, the major question is cost. But I think we should validate our thoughts
by experiments assuming there are more possible synchronization protocols.
Ildus posted implemention of double buffering approach that showed quite low
cost.

I'm not sure exactly which email you are referring to, but I don't
believe that anyone has done experiments that are anywhere near
comprehensive enough to convince ourselves that this won't be a
problem.  If a particular benchmark doesn't show an issue, that can
just mean that the benchmark isn't hitting the case where there is a
problem.  For example, EDB has had customers who have severe
contention apparently on the buffer content lwlocks, resulting in big
slowdowns.  You don't see that in, say, a pgbench run.  But for people
who have certain kinds of queries, it's really bad.  Those sort of
loads, where the lwlock system really gets stressed, are cases where
adding overhead seems likely to pinch.

[Thread debugging using libthread_db enabled]
0x00007f10e01d4f27 in semop () from /lib64/libc.so.6
#0  0x00007f10e01d4f27 in semop () from /lib64/libc.so.6
#1  0x000000000061fe27 in PGSemaphoreLock (sema=0x7f11f2d4f430, interruptOK=0 '\000') at pg_sema.c:421
#2  0x00000000006769ba in LWLockAcquireCommon (l=0x7f10e1e00120, mode=LW_EXCLUSIVE) at lwlock.c:626
#3  LWLockAcquire (l=0x7f10e1e00120, mode=LW_EXCLUSIVE) at lwlock.c:467
#4  0x0000000000667862 in ProcArrayEndTransaction (proc=0x7f11f2d4f420, latestXid=182562881) at procarray.c:404
#5  0x00000000004b579b in CommitTransaction () at xact.c:1957
#6  0x00000000004b6ae5 in CommitTransactionCommand () at xact.c:2727
#7  0x00000000006819d9 in finish_xact_command () at postgres.c:2437
#8  0x0000000000684f05 in PostgresMain (argc=<value optimized out>, argv=<value optimized out>, dbname=0x21e1a70 "xivadb", username=<value optimized out>) at postgres.c:4270
#9  0x0000000000632d7d in BackendRun (argc=<value optimized out>, argv=<value optimized out>) at postmaster.c:4155
#10 BackendStartup (argc=<value optimized out>, argv=<value optimized out>) at postmaster.c:3829
#11 ServerLoop (argc=<value optimized out>, argv=<value optimized out>) at postmaster.c:1597
#12 PostmasterMain (argc=<value optimized out>, argv=<value optimized out>) at postmaster.c:1244
#13 0x00000000005cadb8 in main (argc=3, argv=0x21e0aa0) at main.c:228

root@pgload01g ~ # grep '^#4 ' /tmp/bt | awk '{print $2, $4, $NF}' | sort | uniq -c | sort -rn    126 0x000000000065db61 BufferAlloc bufmgr.c:591     67 0x000000000065e03a BufferAlloc bufmgr.c:760     43 0x00000000005c8c3b pq_getbyte pqcomm.c:899     39 0x000000000065dd93 BufferAlloc bufmgr.c:765      6 0x00000000004b52bb RecordTransactionCommit xact.c:1194      4 0x000000000065da0e ReadBuffer_common bufmgr.c:476      1 ReadBuffer_common relpersistence=112 bufmgr.c:340      1 exec_eval_expr expr=0x166e908, pl_exec.c:4796      1 0x00007f78b8cb217b ?? /usr/pgsql-9.4/lib/pg_stat_statements.so      1 0x00000000005d4cbb _copyList copyfuncs.c:3849
root@pgload01g ~ #

Yes, but some competing products also provides comprehensive waits
monitoring too. That makes me think it should be possible for us too.

I agree, but keep in mind that some of those products may use
techniques to reduce the overhead that we don't have available.  I
have a strong suspicion that one of those products in particular has
done something clever to make measuring the time cheap on all
platforms.  Whatever that clever thing is, we haven't done it.  So
that matters.

I think the reason for hooks could be not only disagreements about design,
but platform dependent issues too.
Next step after we have view with current wait events will be gathering some
statistics of them. We can oppose at least two approaches here:
1) Periodical sampling of current wait events.
2) Measure each wait event duration. We could collect statistics for short
period locally and update shared memory structure periodically (using some
synchronization protocol).

In the previous attempt to gather lwlocks statistics, you predict that
sampling could have a significant overhead [1]. In contrast, on many systems
time measurements are cheap. We have implemented both approaches and it
shows that sampling every 1 milliseconds produce higher overhead than
individual duration measurements for each wait event. We can share another
version of waits monitoring based on sampling to make these results
reproducible for everybody. However, cheap time measurements are available
not for each platform. For instance, ISTM that on Windows time measurements
are too expensive [2].

That makes me think that we need pluggable solution, at least for
statistics: direct measuring of events durations for majority of systems and
sampling for others as the least harm.

To me, those seem like arguments for making it configurable, but not
necessarily for having hooks.

I think it's reasonable to consider reporting this data in the PGPROC
using a 4-byte integer rather than reporting it through a singe byte
in the backend status structure.  I believe that addresses the
concerns about reporting from auxiliary processes, and it also allows
a little more data to be reported.  For anything in excess of that, I
think we should think rather harder.  Most likely, such addition
detail should be reported only for certain types of wait events, or on
a delay, or something like that, so that the core mechanism remains
really, really fast.

That sounds reasonable. There are many pending questions, but it seems like
step forward to me.

Great, let's do it.  I think we should probably do the work to
separate the non-individual lwlocks into tranches first, though.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers