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

Question

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?

This question is too broad and probably off topic for SO. I'd try dba.stackexchange. — Jørgen R
– Jørgen R, Commented Feb 24, 2016 at 12:10

jcaron · Accepted Answer · 2016-02-24 12:05:56Z

3

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!

answered Feb 24, 2016 at 12:05

jcaron

17.8k6 gold badges36 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alexander Over a year ago

Thank you, actually it looks like high writes doesn't correlate with high CPU. I will take a look on heavy SELECT queries.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related