Re: pg_stat_lwlocks view - lwlocks statistics, round 2 - Mailing list pgsql-hackers

Thanks for the review.

2012/10/14 8:55, Michael Paquier wrote:
>
>
> On Sun, Oct 14, 2012 at 6:00 AM, Fujii Masao <masao.fujii@gmail.com
> <mailto:masao.fujii@gmail.com>> wrote:
>
>     On Sun, Oct 14, 2012 at 3:34 AM, Fujii Masao <masao.fujii@gmail.com
>     <mailto:masao.fujii@gmail.com>> wrote:
>      > On Sat, Oct 13, 2012 at 11:34 PM, Satoshi Nagayasu
>     <snaga@uptime.jp <mailto:snaga@uptime.jp>> wrote:
>      >> Hi,
>      >>
>      >> 2012/10/13 23:05, Satoshi Nagayasu wrote:
>      >>> Hi all,
>      >>>
>      >>> I have fixed my previous patch for pg_stat_lwlocks view, and
>      >>> as Josh commented, it now supports local and global (shared)
>      >>> statistics in the same system view.
>      >>
>      >> Sorry, I found my mistakes. New fixed one is attached to this mail.
>      >
>      > Thanks for revising the patch. Here are the comments:
>      >
>      > The document needs to be updated.
>      >
>      > The patch caused the following compile warnings in my machine.
>      >
>      > pgstat.c:1357: warning: no previous prototype for
>     'pgstat_report_lwlockstat'
>      > postgres.c:3922: warning: implicit declaration of function
>      > 'pgstat_report_lwlockstat'
>      > pgstatfuncs.c:1854: warning: no previous prototype for
>     'pg_stat_reset_lwlocks'

Oops. I just fixed them. Thanks.

>      > In my test, this patch caused the measurable performance overhead.
>      > I created the test database by pgbench -s10 and ran pgbench -c8
>     -j8 -T60 -S.
>      > Results are:
>      >
>      > [HEAD]
>      > number of transactions actually processed: 1401369
>      > tps = 23351.375811 (including connections establishing)
>      > tps = 23355.900043 (excluding connections establishing)
>      >
>      > [PATCH]
>      > number of transactions actually processed: 1401369
>      > tps = 23351.375811 (including connections establishing)
>      > tps = 23355.900043 (excluding connections establishing)
>
>     Oops! Obviously I copied and pasted the test result wrongly...
>     Here is the right result.
>
>     [HEAD]
>     number of transactions actually processed: 1401369
>     tps = 23351.375811 (including connections establishing)
>     tps = 23355.900043 (excluding connections establishing)
>
>     [PATCH]
>     number of transactions actually processed: 1092400
>     tps = 18179.498013 <tel:18179.498013> (including connections
>     establishing)
>     tps = 18182.450824 <tel:18182.450824> (excluding connections
>     establishing)
>
> Performance difference is due to only the mutex lock taken?

I think it is coming from high-frequent reporting through
pgstat collector process, which means calling
pgstat_report_lwlocks() at PostgresMain().

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 585db1a..5ca2c6f 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -3919,6 +3919,8 @@ PostgresMain(int argc, char *argv[], const char 
*username)                 pgstat_report_activity(STATE_IDLE, NULL);             }

+            pgstat_report_lwlockstat();
+             ReadyForQuery(whereToSendOutput);             send_ready_for_query = false;         }

When I reduced reporting (or just disabled reporting),
it shows that the performance would not be affected
by this patch.

Here are some additional results of the performance test
which is the same one Fujii-san did.

HEAD
====
number of transactions actually processed: 3439971
tps = 57331.891602 (including connections establishing)
tps = 57340.932324 (excluding connections establishing)

pg_stat_lwlocks patch (reporting enabled)
=========================================
number of transactions actually processed: 2665550
tps = 44425.038125 (including connections establishing)
tps = 44430.565651 (excluding connections establishing)

pg_stat_lwlocks patch (reporting disabled)
==========================================
number of transactions actually processed: 3429370
tps = 57155.286475 (including connections establishing)
tps = 57163.996943 (excluding connections establishing)

pg_stat_lwlocks patch (reporting reduced 1/100)
===============================================
number of transactions actually processed: 3421879
tps = 57030.660814 (including connections establishing)
tps = 57038.950498 (excluding connections establishing)

So, I think some additional hack to reduce reporting is needed.
Would it be acceptable in terms of the performance?

>     Another comment is; local_calls/waits/time_ms are really required?
>     I'm not sure how those info would help the performance debugging.

I think there are some needs to observe/determine how your test
query is affected by the other workload from the other sessions.
So, splitting local and shared statistics would be nice to have.
Just my thought though.

Regards,

>
>     Regards,
>
>     --
>     Fujii Masao
>
>
>     --
>     Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org
>     <mailto:pgsql-hackers@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-hackers
>
>
>
>
> --
> Michael Paquier
> http://michael.otacoo.com


-- 
Satoshi Nagayasu <snaga@uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp