Usingperf on Linux is a great way to understand your CPU consumption. It is mostly harmless on modern kernels, especially when running for a short time, but try it on a test environment first if you can.

Here is an example where I gather 99 samples per second, during 10 seconds, for PostgreSQL and YugabyteDB processes and threads:

ssh my.server'sudo bash -c "perf record -o - --call-graph fp -F99 -e cpu-cycles \ -p $(pgrep -d, postgres\|yb-tserver\|yb-master) sleep 10 | perf script -F +pid"'>$(date +%Y%m%d%H%M%S |tee /dev/stderr).perf

This generates a file that can be read by the Firefox Profiler.
Go tohttps://profiler.firefox.com/ and load the generated file.

You will get something that looks like this:

On the upper right, you can upload it to share it. I did this, which means that you can see mine here:https://share.firefox.dev/4576kKY

I gathered the perf statistics during 10 seconds while running this on YugabyteDB:

droptableifexistsdemo;createtabledemo(idbigint);insertintodemoselectgenerate_series(1,300000);\watch

Maybe you already see what's wrong here: I didn't define a primary key. But how does that impact my CPU usage?

I filter on thepostgres process:

In YugabyteDB, thepostgres process is running the query layer, with additional threads (likepggate to talk to the storage layer):

This shows the call tree I can navigate, similar toperf top orperf report, but with a graphical interface.

One interesting view is theStack Chart, which follows the timeline:

My query was not active during the whole 10 seconds of sampling. It has been running a few seconds and then\watch was waiting.

A view made popular by Brenda Gregg is theFlame Graph:

Here, for example, I see that I spend some time reading from a virtual filesystem. I focus on this part:

The names of the YugabyteDB functions give a clue. TheCall Tree shows the full names (right-click to copy it as text):

The stack gives a lot of information:

_extract_crngurandom_read_nowarn.isra.34urandom_read__vfs_readvfs_readksys_read__arm64_sys_readel0_svc_commonel0_svc_handlerel0_svc__readboost::uuids::detail::random_provider_base::get_random_bytesyb::GenerateObjectIdyb::pggate::PgApiImpl::TupleIdBuilder::BuildYBCPgBuildYBTupleIdYBCGetYBTupleIdFromTuple

Of course, I can guess from the function names, butYugabyteDB is open-source, and I can search for them. What happens here is that I didn't declare a Primary Key for my table, and then an internal one (ybctid) is generated because secondary indexes need a key to address the table row. This ID generation calls/dev/urandom. I made this simple example to show that low-level traces can give a clue about high-level data model problems.