Calculation and Interpretation of Net/Delay*

[AnatraGorda]

First of all: Thanks for providing this great way to get further insights into Postgres Network analysis! We were so interested in this approach in my group, that I worked on my own version of this. This is due to the fact, that I we cannot construct extra tables in the public schema of a customer, so I just perform the necessary SELECTs into pg_stat_activity and aggregate them via a Python script.

I have one last open point concerning the Net/Delay* value. The central statement for this, seems to me this (in gather_report.sql):

CASE WHEN (itr_max - itr_min)::float/itr.gitr_max2000 - pidwcnt > 0 THEN', Net/Delay: ' || round(((itr_max - itr_min)::float/itr.gitr_max2000 - pidwcnt)::numeric100/2000,2) || '%'

From my results, I suspect that itr.gitr_max will mostly equal wait_events such as 'WalWriterMain', which you "always see".

Looking at my evaluations, I suspect hat Net/Delay* may include sometimes "counts" from idle connections. In another statement, we exclude those (in gather.sql):

SELECT pid || E'\t' || COALESCE(wait_event,'\N') FROM pg_stat_get_activity(NULLIF(pg_sleep(0.01)::text,'')::INT) WHERE (state != 'idle' OR state IS NULL) AND pid != pg_backend_pid();

Is this an invalid concern?

Thanks a lot again,

Thomas

[jobinau]

@AnatraGorda , Welcome your feedback.
As you pointed out the lines :

   CASE WHEN (itr_max - itr_min)::float/itr.gitr_max*2000 - pidwcnt > 0 THEN
    ', Net/Delay*: ' || round(((itr_max - itr_min)::float/itr.gitr_max*2000 - pidwcnt)::numeric*100/2000,2) || '%'

Does the Net/Delay calculation.
Regarding,

Looking at my evaluations, I suspect hat Net/Delay* may include sometimes "counts" from idle connections. In another statement, we exclude those (in gather.sql):

Wait events of sessions that are completely idle are removed during the data collection. However, sessions that had to go idle between two statements need to be counted towards Delay in communication because the server session was waiting for the next statement.

Regarding,

This is due to the fact, that I we cannot construct extra tables in the public schema of a customer,

That is one main reason for separating data collection and analysis. Data collection (gather.sql) doesn't need any schema/table, so no table needs to be created in customer/critical environments.
The analysis can be done on a separate system. Most of the pg_gather users do that on a docker container (Please Refer: generate_report.sh)

[AnatraGorda]

Thanks a lot for the reference to the generate_report.sh! It really slipped my attention. Now, I may have two ways.

I have one follow-up question, if possible: How do you differentiate with sql code sessions, that are "completely idle" from those "that go idle between two statements". Is this implemented somewhere and I oversaw it?

Thanks again,

Thomas

[jobinau]

Hi @AnatraGorda ,

that are "completely idle" from those "that go idle between two statements"

The trick is to consider the sessions with missing pieces in wait events. I mean, If some expected wait events mysteriously missing, then that is considered a Net/Delay.
It is the same line which you identified, calculation might be confusing.

pg_gather/gather_report.sql

Line 287 in a1f2ca9

CASE WHEN (itr_max - itr_min)::float/itr.gitr_max*2000 - pidwcnt > 0 THEN