Metrics with pg_proctab

pg_proctab is a set of stored functions installed as a Postgres extension that will let you access operating system statistics from the underlying server, such as I/O, processor load, and memory usage.

You can enable pg_proctab for Crunchy Bridge by running:

CREATE EXTENSION pg_proctab;
Copy

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

System load

You can query load using pg_proctab by running:

SELECT * FROM pg_loadavg()
Copy

pg_proctab 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. 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 want the number to be below 4.0.

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

SELECT load15/4*100
FROM pg_loadavg();
Copy

Memory usage

To query memory usage you can run:

SELECT *
FROM pg_memusage();
Copy

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();
Copy

CPU time and I/O

To monitor CPU and I/O you can run:

SELECT *
FROM pg_cputime();
Copy

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();
Copy