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.
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();
To query memory usage you can run:
SELECT * FROM pg_memusage();
The above will return:
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
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();