Thread

1. High latency and profiling

   Roberto Nunnari <robi@nunnari.ch> — 2025-10-09T17:25:34Z

   Hello.
   
   Hope this email will keep the formatting this time..
   
   I was notified about high latency on one of our PostgreSQL clusters.
   This latency is observed from the application and has been increasing. 
   There is no indication about the exact database as the application uses 
   several 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_libraries = 
   'pg_stat_statements,dblink,pg_stat_kcache,pg_wait_sampling'
   pg_profile.max_sample_age = 90
   
   \c postgres
   CREATE SCHEMA extensions;
   CREATE EXTENSION dblink SCHEMA extensions;
   CREATE EXTENSION pg_profile SCHEMA extensions;
   
   \c mydb
   CREATE SCHEMA extensions;
   CREATE EXTENSION dblink SCHEMA extensions;
   CREATE EXTENSION pg_stat_statements SCHEMA extensions;
   CREATE EXTENSION pg_stat_kcache schema extensions;
   CREATE EXTENSION pg_wait_sampling schema extensions;
   CREATE EXTENSION pg_profile SCHEMA extensions;
   select extensions.set_server_size_sampling('local','23:00+01',interval 
   '2 hour',interval '8 hour');
   
   CREATE USER collector_user with password '';
   GRANT pg_read_all_stats TO collector_user;
   grant connect on database mydb to collector_user;
   ALTER ROLE collector_user SET search_path = extensions,public;
   
   The postgres user had the following crontab:
   13,43 * * * * psql -d mydb -c 'SELECT extensions.take_sample()' > 
   /dev/null 2>&1
   
   I made sure autovacuum works correctly and after that, just to make 
   sure, I run vacuum analyze on all DBs. This has not lead to any notable 
   improvement in the latency 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 following:
   
   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".
   
   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.
   
   Developers report that their software is already optimized and database 
   size is not a problem. They suggest the issue might be inside PostgreSQL 
   itself.
   
   Could somebody help me understand what is causing the growing latency?
   
   Also, but more related to pg_profile, what does the N/A data in the 
   "Wait event types (statements)" graph represent?
   
   Thank you and best regards.
   Robi
   
   
   
   

2. Re: High latency and profiling

   Laurenz Albe <laurenz.albe@cybertec.at> — 2025-10-09T19:21:19Z

   On Thu, 2025-10-09 at 19:25 +0200, Roberto Nunnari wrote:
   > I was notified about high latency on one of our PostgreSQL clusters.
   > This latency is observed from the application and has been increasing. 
   > There is no indication about the exact database as the application uses 
   > several DBs, but the largest and most used is 'mydb'.
   >
   > Developers report that their software is already optimized and database 
   > size is not a problem. They suggest the issue might be inside PostgreSQL 
   > itself.
   
   That may be true, but it need not be true.  The developers' statement
   sounds a bit overconfident.
   
   > Could somebody help me understand what is causing the growing latency?
   
   For that, you have to identify the statements that run longer than they
   should.  Then examine them with EXPLAIN (ANALYZE, BUFFERS), figure out what
   makes them slow and improve them.
   
   Yours,
   Laurenz Albe
   
   
   
   

3. Re: High latency and profiling

   Rob Sargent <robjsargent@gmail.com> — 2025-10-09T20:54:56Z

   
   On 10/9/25 13:21, Laurenz Albe wrote:
   > On Thu, 2025-10-09 at 19:25 +0200, Roberto Nunnari wrote:
   >> I was notified about high latency on one of our PostgreSQL clusters.
   >> This latency is observed from the application and has been increasing.
   >> There is no indication about the exact database as the application uses
   >> several DBs, but the largest and most used is 'mydb'.
   >>
   >> Developers report that their software is already optimized and database
   >> size is not a problem. They suggest the issue might be inside PostgreSQL
   >> itself.
   > That may be true, but it need not be true.  The developers' statement
   > sounds a bit overconfident.
   Thankfully I checked before sending very similar remarks as Laurenz's 
   below.  I find OP's pair of sentences (borderline) self-contradictory.
   
   
   >> For that, you have to identify the statements that run longer than they
   >> should.  Then examine them with EXPLAIN (ANALYZE, BUFFERS), figure out what
   >> makes them slow and improve them.
   >>
   >> Yours,
   >> Laurenz Albe
   >>
   >>
   >>