We have PostgreSQL DB with the following characteristics:
- DB size: 20Tb
- host (actually KVM guest) memory: 1Tb (250Gb in shared_buffers)
- CPUs: 100 (200 vCPUs)
- connections: 500-1000
- active connections: 80-100
- IO: something provisioned via virtualisation, I believe it is capable to provide not more than 100K IOPS
Typically, everything works "smoothly" (actually, there are some room for improvement) and normally OS indicates following metrics:
- load average: 80
- us: 20%, sys: 10%, wait 10%, idle: 60%
- IO queue length: 100
However, when someone comes with a query like "I don't really know what I want to see, let DB to decide that" we are getting the following spike in OS metrics:
- load average: 400
- us: 18%, sys: 6%, wait: 60%, idle: 16%
- IO queue length: 700-1000
Below is the graph, representing what is actually going on:
My considerations are following:
- single (or a ~couple) of bad queries should not drastically influence on DB performance - there are a hundred of competing queries (that is actually based on my previous Oracle experience)
- since the disaster is actually happening, I may conclude the root cause is the competition for some kind of a shared resource
So, I do suspect the following:
since OS file cache is not actually assigned to anyone, we are getting the following:
- any seq scan query together with "too optimistic" read ahead settings is messing up OS file cache
- benign queries, which are typically retrieve data from shared buffers or/and OS file cache, all of them are starting retrieve data from I/O subsystem just because OS file cache got drained out - that is the reason why I do observe the "unexpected" (times instead of percents) increasing of load average and IO queue size metrics
The questions is: if my investigation is correct (I was able to find something very similar in pgsql-performance mailing list: https://www.postgresql.org/message-id/flat/FC8ED065-7EE0-11D9-904E-003065D62456%40pumptheory.com), is there any option to prevent such situation?
