21

My application is using Postgres as DBMS, the version of Postgres that i'm using is 10.3 with the extension Postgis installed.

Occasionally i noticed that in random interval of times the dbms become slow and get stuck on a few SELECT queries.

From pg_stat_activity i noticed that the wait_event_type and wait_event of these queries is as follows: 

 select wait_event_type, wait_event from pg_stat_activity where state='active'; 
 wait_event_type |  wait_event  
-----------------+--------------
 IO              | DataFileRead
 IO              | DataFileRead
 IO              | DataFileRead
 IO              | DataFileRead
 LWLock          | buffer_io
 LWLock          | buffer_io
 IO              | DataFileRead
 LWLock          | buffer_io
 LWLock          | buffer_io
 IO              | DataFileRead
 IO              | DataFileRead
 LWLock          | buffer_io
 LWLock          | buffer_io
 IO              | DataFileRead
 LWLock          | buffer_io
 IO              | DataFileRead
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 IO              | DataFileRead
 IO              | DataFileRead
                 | 
 IO              | DataFileRead
 LWLock          | buffer_io
 LWLock          | buffer_io
(33 rows)

My assumption, after checking the docs, is that the hardware underneath has some issues and then the problem i'm facing is not related to the application, or the type of query, but to the hardware itself.

Anybody ever faced this kind of issue?

Riccardo
  • 1,490
  • 2
  • 12
  • 22
  • I agree. What file system are you using? – Laurenz Albe Dec 15 '18 at 15:47
  • The dbms is installed on a Kubernetes pod, the volume is mounted on a Network storage. . – Riccardo Dec 15 '18 at 16:57
  • 5
    In that case it might well be a network problem. – Laurenz Albe Dec 15 '18 at 22:08
  • 1
    That still could be problem with the application/query. You mention to be using PostGIS, are you correctly indexing the columns to prevent sequential reads for example? When observing those wait events DataFileRead for long periods of time, it could mean that's the case. My suggestion is to capture one of those long running queries and run 'EXPLAIN (ANALYZE,BUFFERS,TIMING) query '. The parameters log_min_duration_statement and the extension auto_explain are your friends. – Vinnix Apr 22 '21 at 10:12
  • 1
    The intermittency observed, could be related by the fact that sometimes you are using a predicate/filter that needs to touch much more rows than you usually do during other periods. Of course, that's could still be a resource constraint problem like network or storage, memory or even CPU. – Vinnix Apr 22 '21 at 10:14
  • What have you tried to debug the problem? In the current state, this does not look related to programming to me – Nico Haase Apr 28 '21 at 11:29

1 Answers1

1

generic troubleshooting suggestions:

  • start gathering runtime statistics of the server - there's wide choice of tools - https://munin-monitoring.org/, https://grafana.com/ + influx db + telegraf, many more. regardless of the solution you should keep historical statistics of:

    • amount of disk operations done per second
    • latency of the disk storage [ regardless if it's spinning rust, ssd, nvme or network-attached ]
    • server CPU usage, load, memory usage

  • get also statistics about postgresql - https://www.percona.com/downloads/pmm2 might be helpful here

based on those stats - see if there's any build-up before problematic query happens.

occasional slow down might be caused by:

  • uneven performance of the storage subsystem [ ssd at the end of its life, patrol-read on the RAID array, hdd reallocating data due to bad sectors ]
  • incorrect index statistics leading to suboptimal query plan
  • overload of the system by incoming queries
  • overload of the system by other workloads running on the same hardware, noisy neighbors if you're running in a virtualized environment
pQd
  • 116
  • 4
  • 20