I'm experiencing intermittent slowness when (simple) queries are executed (be it SELECT or UPDATE, I haven't checked inserts/deletes).

For example, to execute the query below, usually, it takes less than 1ms, while sometimes it can take around 300ms to execute when the server is more or less running in idle (that is DB time only - per ORM profiler, when the connection is already established - which is also fast).
Note, that is only a random example, it happens every now and then on random queries, not just this specific one.

UPDATE "public"."tableA"
SET    "field" = 'xxxxx' 
WHERE  ((("public"."tableA"."ColumnA" = '<guid>'
      AND "public"."tableA"."ColumnB" = '<guid>')))

Table A has composite PK on these two columns, thus it has a backing index - explain analyze confirms it is using index.

For the record, currently, that table has ~3000 records.

I'm suspecting that I'm running into some configuration issue, and below are details regarding machine and configuration.

PostgreSQL 12.4 is running on a CentOS 7 machine (24 core, 64GB RAM, running on server-grade SSDs).

Let me know if you need any additional params.

max_connections = 4000 (~running at around 1500)
shared_buffers = 16G
work_mem = 100MB (I know this is too much given the number of connections and server memory, this is just dev machine)
maintenance_work_mem = 6GB
vacuum_cost_delay = 10                  
vacuum_cost_page_hit = 1              
vacuum_cost_page_miss = 10              
vacuum_cost_page_dirty = 20             
vacuum_cost_limit = 2000
effective_io_concurrency = 200         
max_worker_processes = 24               
max_parallel_maintenance_workers = 4   
max_parallel_workers_per_gather = 4    
max_parallel_workers = 24
random_page_cost=1.1 (tried 1.0, no noticeable difference)
effective_cache_size = 48GB
autovacuum=on (with custom params per table where we have a lot of inserts/updates/deletes - not mentioned table above)

I just can't get on top of that, and I'm slowly going crazy as it seems that I'm chasing the ghosts - any help is appreciated - thanks.

  • much more interesting is the CREATE TABLE with all indexes and also chekc out dba.meta.stackexchange.com/questions/3034/…
    – nbk
    Jul 4 at 22:53
  • Are the victims always DML statements? Maybe fsync problem?
    – jjanes
    Jul 5 at 1:18
  • @nbk thanks for the tip and link, I'll provide requested info a bit later and edit the post. Jul 5 at 7:51
  • @jjanes SELECTs are affected as well, and that is what is bugging me. I understand in what direction nbks question leads to (index update etc), but I'm not sure what could impact reads. Jul 5 at 7:53
  • 1
    Turn on track_io_timing and set up auto_explain to capture slow queries, with analyze and buffers, to see if you can catch it in the act. If your system has fast clock access (test that with command line tool pg_test_fsync), then also have auto_explain capture timings as well
    – jjanes
    Jul 5 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.