Monitoring system metrics

Pg_proctab is a set of stored functions installed as a Postgres extension that will let you access operating system statistics, like I/O, processor load, and memory usage. You can enable pg_proctab for Crunchy Bridge by running:

CREATE EXTENSION pg_proctab;

Once you’ve enable the extension you have access to new functions to monitor system metrics.

System load

To query load you can run:

SELECT * FROM pg_loadavg()

This will provide you with:

  • load1 – load average of last minute
  • load5 – load average of last 5 minutes
  • load15 – load average of last 15 minutes
  • last_pid – last PID running

Load is going to give you a number relative to the number of CPUs you have, so you’ll need to know the total number of cores/virtual cpus available. So as the load numbers get closer to the number of CPUs you have, you are running on the high end of load. On a single core machine, you want the number below 1.0. Or for example, if you have 4 cores, you just want the number to be way below 4.0.

The below query will give you your percentage of load if you have a 4 core set up.

SELECT load15/4*100 
FROM pg_loadavg();

Memory usage

To query memory usage you can run:

SELECT * 
FROM pg_memusage();

The above will return:

  • memused
  • memfree
  • memshared
  • membuffers
  • memcached
  • swapused
  • swapfree
  • swapcached

Memory usage will show memory used, free memory, memory shared, swap used and free. If you start regularly seeing swap used, it’s time to look at getting a provision with additional memory.

Here’s an easy one to check the free memory

SELECT pg_size_pretty(memfree*1024)
FROM pg_memusage();

CPU time and I/O

To monitor CPU and I/O you can run:

SELECT * 
FROM pg_cputime();

This is going to get you:

  • user nice
  • system idle
  • iowait

User will give you all the non-system programs using cpu, system is the other system/kernel cpu process, and idle is the idle/free processes. So you can get pretty close to getting CPU usage by totaling idle, user, and system and diving by idle. The below example gives a sample of that:

SELECT idle/(idle + "user" + system)*100 
FROM pg_cputime();