Viewing Historical System Metrics using SQLin Greenplum command Center

posted Nov 8, 2012, 12:47 PM by Sachi Ojha
Historical tables in the Command Center database store all system data collected by Command Center. You can analyze this data to identify trends in the performance of the Greenplum system over time.

With a default quantum setting of 15 seconds, Command Center collects very large amounts of historical system data. To view meaningful groupings of system data records, you can view the data by selected time intervals with a SQL view (as described below).

The following provides an example SQL view for viewing aggregated system metrics. In this example view named system_metrics_aggregate_1min, rows of system metrics from system_history are averaged over one-minute intervals and aggregated across all segments in the array:

DROP VIEW IF EXISTS system_metrics_aggregate_1min;

CREATE VIEW system_metrics_aggregate_1min AS (
SELECT date_trunc('minute', ctime) AS sample_time, hostname, avg(mem_total) AS mem_total,
avg(mem_used) AS mem_used,
avg(mem_actual_used) AS mem_actual_used,
avg(mem_actual_free) AS mem_actual_free,
avg(swap_total) AS swap_total, avg(swap_used) AS swap_used, avg(swap_page_in) AS swap_page_in,
avg(swap_page_out) AS swap_page_out,
avg(cpu_user) AS cpu_user, avg(cpu_sys) AS cpu_sys, avg(cpu_idle) AS cpu_idle,
avg(load0) AS load0, avg(load1) AS load1,
avg(load2) AS load2,
avg(disk_ro_rate) AS disk_ro_rate,
avg(disk_wo_rate) AS disk_wo_rate,
avg(disk_rb_rate) AS disk_rb_rate,
avg(disk_wb_rate) AS disk_wb_rate,
avg(net_rp_rate) AS net_rp_rate,
avg(net_wp_rate) AS net_wp_rate,
avg(net_rb_rate) AS net_rb_rate,
avg(net_wb_rate) AS net_wb_rate
FROM system_history
GROUP BY hostname, sample_time

To change the interval for averaging results, adjust the value specified for date_trunc. Valid values are microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century and millennium.