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:

enter image description here

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?

  • We don't know what your query is ("let DB to decide that" is not very descriptive), what the plan is, or what the colors in your pretty picture mean.
    – jjanes
    Nov 14 at 17:29


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.