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 track_counts=on 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.