Description
CPU utilization is not a base metric in DPA's Resource tab because the value is not available from within the database. However, by using Foreign Data Wrappers (FDW) we can make host information available to the database so that DPA can query it. If your Postgres database is running on Linux, there is a file name /proc/loadavg that provides CPU information, and /proc/meminfo that provides Memory information.
Note: if you are using Windows, Performance Monitor (perfmon) can be used to create a file and publish any metrics needed to a file. In this case, modify the FDW definitions accordingly.
Foreign Data Wrapper Definition (Linux)
The following statement creates a table within Postgres that points to the /proc/loadavg file, which contains CPU metrics and makes it available for querying:
CREATE FOREIGN TABLE loadavg (one_min text, five_min text, fifteen_min text, scheduled text, pid text) SERVER fileserver OPTIONS (filename '/proc/loadavg', format 'text', delimiter ' ');# cat /proc/loadavg
0.75 0.35 0.25 1/25 1747The following statement uses the /proc/meminfo file to make memory metrics available for querying as well:
CREATE FOREIGN TABLE mem_data (metric text, value text) SERVER fileserver OPTIONS (filename '/proc/meminfo', format 'csv', delimiter ':');
DPA Metric Queries
The queries that you can use for inclusion as metrics in DPA are as follows, and these assume you used 60 seconds as the metric collection frequency:
CPU
SELECT AVG(CAST(one_min as decimal)) FROM loadavg;
Memory
The free and total memory values appear in different lines in this file, so do a self join to get both values in the same query:
SELECT 100*(MemFree / MemTotal)
FROM (
SELECT m1.value AS MemTotal, m2.value AS MemFree
FROM mem_data m1, mem_date m2
WHERE m1.metric = 'MemTotal'
AND m2.metric = 'MemFree') mem
