CODEWITHSUNDEEP
postgresql
Alexander
Alexander

Reputation: 485

Postgres 100% CPU. High write/read ops count rate

I have some issue with a DB (hosted on Amazon RDS), when sometimes 100% CPU consumption happens and all select queries became slow. Also there is a curious detail on graphs: write operations per sec is much greater than read operations per sec but pg_stat_activity returns only select queries, no create, update or delete (CUD) queries.

The question is: what is the cause of high write operations per sec metric value? Does this metric include operations with indexes (rebuild) on CUD queries? Why I don't see CUD queries in pg_stat_activity results?

enter image description here

Upvotes: 1

Views: 1533

Answers (1)

jcaron
jcaron

Reputation: 17720

The two metrics (high CPU, much higher number of writes than reads) are probably unrelated (there doesn't seem to be any correlation between the two graphs).

High CPU utilisation can be caused by complex select queries, especially if there's a lot of in-memory sorting or filtering. Without details of the requests, it's difficult to say anything about what's going on.

More writes than reads probably just means that the dataset is effectively cached, so so there's no need for any data to be read from disk.

Note that pg_stat_activity will only give you a snapshot of activity. There may be write requests that are too short for them to be visible in a snapshot. Also, some SELECT queries may actually cause writes (the typical example is a call to nextval). There may also be logs being written to disk!

Upvotes: 3

Related Questions