mail-alias-created-date: 1516299501000
To: pgsql-general@lists.postgresql.org
From: Roberto Nunnari <robi@nunnari.ch>
Subject: High latency and profiling
Date: Thu, 9 Oct 2025 09:03:31 +0000 (GMT)
Message-id: <cfcf774b-7373-436c-a68a-5a8d2a3a0647@me.com>
Content-Type: multipart/alternative; boundary=Apple-Webmail-42--e8f9f4f7-70a0-4b80-a6c3-7b48d7d9112e
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/cfcf774b-7373-436c-a68a-5a8d2a3a0647%40me.com>
Precedence: bulk


--Apple-Webmail-42--e8f9f4f7-70a0-4b80-a6c3-7b48d7d9112e
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
    charset=utf-8;
    format=flowed

Hello. I was notified about high latency on one of our PostgreSQL clusters=
. This latency is observed from the application and has been increasing. T=
here is no indication about the exact database as the application uses sev=
eral DBs, but the largest and most used is 'mydb'. So, a couple of months =
ago I installed pg_profile and configured it as follows: shared_preload_li=
braries =3D 'pg_stat_statements,dblink,pg_stat_kcache,pg_wait_sampling' pg=
_profile.max_sample_age =3D 90 \c postgres CREATE SCHEMA extensions; CREAT=
E EXTENSION dblink SCHEMA extensions; CREATE EXTENSION pg_profile SCHEMA e=
xtensions; \c mydb CREATE SCHEMA extensions; CREATE EXTENSION dblink SCHEM=
A extensions; CREATE EXTENSION pg_stat_statements SCHEMA extensions; CREAT=
E EXTENSION pg_stat_kcache schema extensions; CREATE EXTENSION pg_wait_sam=
pling schema extensions; CREATE EXTENSION pg_profile SCHEMA extensions; se=
lect extensions.set_server_size_sampling('local','23:00+01',interval '2 ho=
ur',interval '8 hour'); CREATE USER collector_user with password ''; GRANT=
 pg_read_all_stats TO collector_user; grant connect on database mydb to co=
llector_user; ALTER ROLE collector_user SET search_path =3D extensions,pub=
lic; The postgres user had the following crontab: 13,43 * * * * psql -d my=
db -c 'SELECT extensions.take_sample()' > /dev/null 2>&1 I made sure autov=
acuum works correctly and after that, just to make sure, I run vacuum anal=
yze on all DBs. This has not lead to any notable improvement in the latenc=
y as seen by the application. I access pg_profile data from grafana, but I=
 cannot see anything that looks wrong. Overall Hit Ratio is always between=
 99.3% and 100%. The only strange thing I see in grafana, are the followin=
g: Overall statement times sais "No data" and 'db query error: pq: column =
"blk_read_time" does not exist'. Also the graph "Statement time" says "No =
data" and "db query error: pq: column sst.blk_read_time does not exist". W=
ait event types (statements) graph: N/A was quite stable at about 0.200 s/=
s until 2nd september and then grow regularly and sharply up 4 s/s until 2=
2nd september when it dropped back to 0.7 s/s and is stable at that value =
till now. Developers report that their software is already optimized and d=
atabase size is not a problem. They suggest the issue might be inside Post=
greSQL itself. Could you help me understand what is causing this? In parti=
cular, what does the N/A data in the "Wait event types (statements)" graph=
 represent? Thank you and best regards. Robi
--Apple-Webmail-42--e8f9f4f7-70a0-4b80-a6c3-7b48d7d9112e
Content-Type: multipart/related;
    type="text/html";
    boundary=Apple-Webmail-86--e8f9f4f7-70a0-4b80-a6c3-7b48d7d9112e


--Apple-Webmail-86--e8f9f4f7-70a0-4b80-a6c3-7b48d7d9112e
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
    charset=utf-8;

<html><body><div>Hello. <br></div><div><br></div><div>I was notified about high latenc=
y on one of our PostgreSQL clusters.<br></div><div>This latency is observe=
d from the application and has been increasing. There is no indication abo=
ut the exact database as the application uses several DBs, but the largest=
 and most used is 'mydb'.<br></div><div>So, a couple of months ago I insta=
lled pg_profile and configured it as follows:<br></div><div><br></div><div=
>shared_preload_libraries =3D 'pg_stat_statements,dblink,pg_stat_kcache,pg=
_wait_sampling'<br></div><div>pg_profile.max_sample_age =3D 90<br></div><d=
iv><br></div><div>\c postgres<br></div><div>CREATE SCHEMA extensions;<br><=
/div><div>CREATE EXTENSION dblink SCHEMA extensions;<br></div><div>CREATE =
EXTENSION pg_profile SCHEMA extensions;<br></div><div><br></div><div>\c my=
db<br></div><div>CREATE SCHEMA extensions;<br></div><div>CREATE EXTENSION =
dblink SCHEMA extensions;<br></div><div>CREATE EXTENSION pg_stat_statement=
s SCHEMA extensions;<br></div><div>CREATE EXTENSION pg_stat_kcache schema =
extensions;<br></div><div>CREATE EXTENSION pg_wait_sampling schema extensi=
ons;<br></div><div>CREATE EXTENSION pg_profile SCHEMA extensions;<br></div=
><div>select extensions.set_server_size_sampling('local','23:00+01',interv=
al '2 hour',interval '8 hour');<br></div><div><br></div><div>CREATE USER c=
ollector_user with password '';<br></div><div>GRANT pg_read_all_stats TO c=
ollector_user;<br></div><div>grant connect on database mydb to collector_u=
ser;<br></div><div>ALTER ROLE collector_user SET search_path =3D extension=
s,public;<br></div><div><br></div><div>The postgres user had the following=
 crontab:<br></div><div>13,43 * * * * psql -d mydb -c 'SELECT extensions.t=
ake_sample()' &gt; /dev/null 2&gt;&amp;1<br></div><div><br></div><div>I ma=
de sure autovacuum works correctly and after that, just to make sure, I ru=
n vacuum analyze on all DBs. This has not lead to any notable improvement =
in the latency as seen by the application.<br></div><div><br></div><div>I =
access pg_profile data from grafana, but I cannot see anything that looks =
wrong.<br></div><div>Overall Hit Ratio is always between 99.3% and 100%.<b=
r></div><div>The only strange thing I see in grafana, are the following:<b=
r></div><div><br></div><div>Overall statement times sais "No data" and 'db=
 query error: pq: column "blk_read_time" does not exist'. Also the graph "=
Statement time" says "No data" and "db query error: pq: column sst.blk_rea=
d_time does not exist".<br></div><div>Wait event types (statements) graph:=
 N/A was quite stable at about 0.200 s/s until 2nd september and then grow=
 regularly and sharply up 4 s/s until 22nd september when it dropped back =
to 0.7 s/s and is stable at that value till now.<br></div><div>Developers =
report that their software is already optimized and database size is not a=
 problem. They suggest the issue might be inside PostgreSQL itself.<br></d=
iv><div><br></div><div>Could you help me understand what is causing this? =
In particular, what does the N/A data in the "Wait event types (statements=
)" graph represent?<br></div><div><br></div><div>Thank you and best regard=
s.<br></div><div>Robi<br></div></body></html>
--Apple-Webmail-86--e8f9f4f7-70a0-4b80-a6c3-7b48d7d9112e--

--Apple-Webmail-42--e8f9f4f7-70a0-4b80-a6c3-7b48d7d9112e--