Understanding Postgresql CPU Utilization

[mamiyaakira]

TPROC-C benchmarks are measured in Postgresql.
If you look at the CPU utilization during the benchmarks, it looks like the utilization is going up and down. Is it possible to keep using the CPU all the time?

[sm-shaw]

Of course, but your question is more of a beginners one about how to analyze database performance and identify bottlenecks.

So to start with, CPU utilization - you will likely have multiple cores and CPU utilization will be at the high level user and system - to begin to look into this run the HammerDB CPU metrics and observe the utilization across all cores - is it evenly balanced across cores or for example do you see some cores entirely utilized and others id.e

Also is the CPU utilization productive? This is a very common misunderstanding, you have can 100% CPU utilization not doing productive work. For example one modified database benchmark observed was based on deletes. Analyzing the benchmark showed that that all the data was soon exhausted after the first run and then the benchmark ran at 100% CPU utilization running just 1 select returning "no rows found" and aborting the rest of the transaction - so this is 100% CPU utilization but is it productive? Of course not. So CPU utilization in isolation is not a useful measure.

So then you need to ask how is the CPU being used and if utilization is not consistent then what is the database waiting on? i.e. bottlenecks - To do this run the HammerDB PostgreSQL metrics viewer - In this you will see CPU used in green and other wait events in different colours - so in this example we can see the top wait event is WALwrite - so we need to improve the WAL log performance to use more CPU. WAL/redo/tranaction log waits is the most common beginner bottleneck.

also run the extended HammerDB time profiling https://www.hammerdb.com/blog/uncategorized/hammerdb-v4-1-extended-time-profiling/ to see which stored procedures consume the most time.

for advanced analysis, you should also look at the OS statistics beyond the database, a tool I recommend is LinuxKI here https://github.com/HewlettPackard/LinuxKI - this will show you what the OS is waiting on as well.

But don't forget there are architectural considerations as well, so you should run a performance profile as the open source databases tend to be limited in being able to use all the available CPU due to lock, latch contention. The commercial databases do scale better in particular across multiple cores and sockets. With PostgreSQL as shown when you reach higher levels of utilization after repeated runs you will see the impact of the autovaccum process that makes results less efficient until you vaccuum full the tables.

So now you need to look beyond "CPU utilization" and start to learn how to analyze your database performance - when you have done this you will be able to understand where you will see the maximum possible CPU utilization and know whether it is productive or not.