PostgreSQL operating system accounting - CPU info

Once cgroups are in place and ready for action you can not just enjoy the kernel
side limitations which make sure that only a certain amount of hardware is used
but you can also make use of the Linux kernel's massive analytical capabilities.

One major issue with any software is that everything which is managed by the
database itself can nicely be tracked. PostgreSQL provides some nice system
views to extract a vast amount of information. However, it is usually a bit hard
to track problems down to the kernel level and see what is really going on in
the operating system.
 

cgroups offer a simple and nice to use interface to grab information from the
OS. Basically information is exposed via a special filesystem. In our sample we
have mounted cgroups to /cgroup/main:

[root@lola main]# pwd
/cgroup/main
[root@lola main]# ls -l
total 0
-r--r--r--. 1 root root 0 Apr 11 14:57 blkio.io_merged
-r--r--r--. 1 root root 0 Apr 11 14:57 blkio.io_queued
-r--r--r--. 1 root root 0 Apr 11 14:57 blkio.io_service_bytes
-r--r--r--. 1 root root 0 Apr 11 14:57 blkio.io_service_time
-r--r--r--. 1 root root 0 Apr 11 14:57 blkio.io_serviced
-r--r--r--. 1 root root 0 Apr 11 14:57 blkio.io_wait_time
--w-------. 1 root root 0 Apr 11 14:57 blkio.reset_stats
-r--r--r--. 1 root root 0 Apr 11 14:57 blkio.sectors
-r--r--r--. 1 root root 0 Apr 11 14:57 blkio.throttle.io_service_bytes
-r--r--r--. 1 root root 0 Apr 11 14:57 blkio.throttle.io_serviced
-rw-r--r--. 1 root root 0 Apr 11 14:57 blkio.throttle.read_bps_device
-rw-r--r--. 1 root root 0 Apr 11 14:57 blkio.throttle.read_iops_device
-rw-r--r--. 1 root root 0 Apr 11 14:57 blkio.throttle.write_bps_device
-rw-r--r--. 1 root root 0 Apr 11 14:57 blkio.throttle.write_iops_device
-r--r--r--. 1 root root 0 Apr 11 14:57 blkio.time
-rw-r--r--. 1 root root 0 Apr 11 14:57 blkio.weight
-rw-r--r--. 1 root root 0 Apr 11 14:57 blkio.weight_device
-r--r--r--. 1 root root 0 Apr 11 14:57 cgroup.procs
-rw-r--r--. 1 root root 0 Apr 11 14:57 cpu.cfs_period_us
-rw-r--r--. 1 root root 0 Apr 11 14:57 cpu.cfs_quota_us
-rw-r--r--. 1 root root 0 Apr 11 14:57 cpu.rt_period_us
-rw-r--r--. 1 root root 0 Apr 11 14:57 cpu.rt_runtime_us
-rw-r--r--. 1 root root 0 Apr 11 14:57 cpu.shares
-r--r--r--. 1 root root 0 Apr 11 14:57 cpu.stat
-r--r--r--. 1 root root 0 Apr 11 14:57 cpuacct.stat
-rw-r--r--. 1 root root 0 Apr 11 14:57 cpuacct.usage
-r--r--r--. 1 root root 0 Apr 11 14:57 cpuacct.usage_percpu
-rw-r--r--. 1 root root 0 Apr 11 14:57 memory.failcnt
--w-------. 1 root root 0 Apr 11 14:57 memory.force_empty
-rw-r--r--. 1 root root 0 Apr 11 14:57 memory.limit_in_bytes
-rw-r--r--. 1 root root 0 Apr 11 14:57 memory.max_usage_in_bytes
-rw-r--r--. 1 root root 0 Apr 11 14:57 memory.memsw.failcnt
-rw-r--r--. 1 root root 0 Apr 11 14:57 memory.memsw.limit_in_bytes
-rw-r--r--. 1 root root 0 Apr 11 14:57 memory.memsw.max_usage_in_bytes
-r--r--r--. 1 root root 0 Apr 11 14:57 memory.memsw.usage_in_bytes
-rw-r--r--. 1 root root 0 Apr 11 14:57 memory.move_charge_at_immigrate
-rw-r--r--. 1 root root 0 Apr 11 14:57 memory.soft_limit_in_bytes
-r--r--r--. 1 root root 0 Apr 11 14:57 memory.stat
-rw-r--r--. 1 root root 0 Apr 11 14:57 memory.swappiness
-r--r--r--. 1 root root 0 Apr 11 14:57 memory.usage_in_bytes
-rw-r--r--. 1 root root 0 Apr 11 14:57 memory.use_hierarchy
-rw-r--r--. 1 root root 0 Apr 11 14:57 notify_on_release
-rw-r--r--. 1 root root 0 Apr 11 14:57 release_agent
-rw-r--r--. 1 root root 0 Apr 11 14:57 tasks
drwxr-xr-x. 2 root root 0 Apr 20 03:25 user_0004
drwxr-xr-x. 2 root root 0 Apr 20 03:25 user_0005

We can easily extract this information using a simple 'cat':
 

[root@lola main]# cat cpuacct.usage_percpu
19745669099020 17321579548118 16193177855463 12703461924936

In this specific example we have extracted information about CPU usage. As you
can see we got 4 CPUs in our system. Each number will give us the amount of CPU
time burned by every single CPU core. To monitor stuff you can pump this
information into a monitoring software of your choice.

In our example we have created two cgroups – one for every PostgreSQL instance
on the system. In side those user_* directories you will find similar
information again:
 

[root@lola user_0004]# cat cpuacct.usage_percpu
61114810788 53897888870 54165636817 24571480812

Those numbers only represent the CPU time burned by our specific instance. It
allows us to nicely drill down to the information needed. Using cgroups you can
see which component of your system needs how many resources. Thus, excellent
information for debugging and error checking is available. From inside the DB it
is usually impossible to tell what was done by which CPU and when using how much
I/O and so on.