We have migrated to new Google sites!
Post date: Nov 08, 2012 8:48:46 PM
Historical tables in the Command Center database store all query and iterator data collected by Command Center. You can analyze this data to identify trends in the performance of Greenplum system over time.
Calculating the average, minimum and maximum values for system-wide query statistics aggregated over time can yield a useful view of database utilization. The following information provides an example SQL view for viewing query statistics in this way.
In this example view named database_metrics_1hour, rows of statistics from the table database_history are averaged over hour-long intervals:
DROP VIEW if exists database_metrics_1hour;
CREATE VIEW database_metrics_1hour AS (
SELECT date_trunc('hour', ctime) AS sample_time, avg(queries_total) AS queries_total,
min(queries_total) AS queries_total_min,
max(queries_total) AS queries_total_max, avg(queries_running) AS queries_running, min(queries_running) AS queries_running_min, max(queries_running) AS queries_running_max, avg(queries_queued) AS queries_queued,
min(queries_queued) AS queries_queued_min, max(queries_queued) AS queries_queued_max
FROM database_history
GROUP BY 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.
Note: You use can also the Command Center Console to view historical query metrics. See the Command Center Console online help for more information.