合 GreenPlum中的gpmetrics Schema下的表介绍

简介

Greenplum Command Center creates the gpmetrics schema in the Vmware Greenplum gpperfmon to save alert rules and logs, and historical metrics collected by the VMware Greenplum metrics collection system. The gpmetrics schema contains the following tables and user-defined functions:

Tables

• gpcc_alert_rule — saves alert rules configured on the Command Center Admin> Alerts page.
• gpcc_alert_history — records an event when an alert rule is triggered.
• gpcc_database_history — saves summary query activity information.
• gpcc_department — saves department information.
• gpcc_disk_history — saves disk usage statistics for each VMware Greenplum host file system.
• gpcc_export_log — saves the log notifications from every “EXPORT ALL” user action.
• gpcc_gpss_job — saves information about data loading jobs.
• gpcc_gpss_log — saves logging-related information about data loading jobs.
• gpcc_index_info — saves information about table indexes, particularly their bloat size.
• gpcc_pg_log_history — saves history from the VMware Greenplum pg_log log file.
• gpcc_plannode_history — saves plan node execution statistics for completed queries.
• gpcc_queries_history — saves execution metrics for completed VMware Greenplum queries.
• gpcc_queries_now — saves real-time query metrics data.
• gpcc_role_department — saves information about roles within a department.
• gpcc_resgroup_history — saves the history of the resource consumption of each resource group on each segment.
• gpcc_scan_history — saves history for Recommendations scans.
• gpcc_schedule — saves schedule for Recommendations scans.
• gpcc_system_history — saves system metrics sampled from VMware Greenplum segments hosts.
• gpcc_table_info — saves current statistics and size information for tables.
• gpcc_table_info_history — saves daily statistics and size information for tables.
• gpcc_wlm_rule — saves workload management rules.
• gpcc_wlm_log_history — saves the log history of workload management rule actions.

User-Defined Functions

• gpcc_delete_department — deletes a department.
• gpcc_queries_per_hour — returns a variety of details about query activity per hour.
• gpcc_queries_per_user — returns, for each user, the number of queries whose runtime is longer than the input interval, per hour, in the specified time range.
• gpcc_queries_per_user_max_and_total_spill_size — returns, for each user, the total spill_size and maximum spill_size per query per hour.
• gpcc_queries_per_user_max_cpu — returns, for each user, the query with the maximum segment and coordinator cpu usage per hour, along with details about the query.
• gpcc_queries_per_user_max_run_time — returns, for each user, the longest running query per hour, along with details about the query.
• gpcc_queries_per_user_max_skew — returns, for each user, the query with the maximum amount of processing skew in the system (skew_cpu) per hour, along with details about the query.
• gpcc_queries_per_user_rows_out — returns, for each user, the query with the maximum rows_out per hour, along with details about the query.
• gpcc_queries_top_10_cpu_per_resgroup — returns by resource group, the top ten queries utilizing the most CPU.
• gpcc_queries_top_10_memory_per_resgroup — returns by resource group, the top ten queries utilizing the most memory.
• gpcc_recommendations — returns a list of tables in need of a vacuum-related action such as VACUUM, VACUUM FULL, ANALYZE, and VACUUM FREEZE, as well as tables in need of the REINDEX action.
• gpcc_system_per_hour — returns a variety of system information.
• gpcc_update_department — renames a department.
• gpcc_reindex_check — reports whether a table’s B-tree indices needs reindexing.

If you set the schema search path to include the gpmetrics schema, you do not have to qualify table and user-defined function names with the gpmetrics schema name. To set the default search path for the gpperfmon database enter this SQL command.

You must exit the current session and start a new session for the new search path to take effect.

Alert Tables

Command Center uses the gpcc_alert_rule and gpcc_alert_log tables to store the alert rules you set up in the user interface and to log messages when the alert rules are triggered.

gpcc_alert_rule

Note: Deprecated in Command Center 6.4/4.12.

The gpcc_alert_rule table records the alert rules configured in the Command Center user interface. It has the columns shown in the following table.

The gpcc_alert_rule table keeps a history of alert rule configurations. When a rule becomes active, a new row is inserted and the ctime timestamp column is set to the current time; the etime timestamp is null, indicating that the rule is still active. When a rule is either disabled or superceded by a new rule, the etime timestamp column is set to the current time. Thus, the set of currently active rules is all rows where the etime column is null. A row that has timestamps in both ctime and etime columns is an historical record of the period of time during which the rule was active.

The rule_id column, a unique integer, is the distribution key for the table and is used to identify a single alert rule configuration. This column can be joined with the rule_id column in the gpcc_alert_log table to identify the rule that triggered each recorded alert event.

The rule_description column contains a string that describes the event that matches the rule. It is the text displayed in the Command Center UI for the rule, with user-specified values inserted.

The rule_config column contains a JSON string with parameters for the values entered for each of the rule’s fields in the Command Center UI.

gpcc_alert_history

The gpcc_alert_history table (wasgpcc_alert_log before Command Center 6.4) has the following columns:

Where:

• The id column, a unique integer, is the distribution key for the table.
• The transaction_time column is set to the current time when a row is created.
• The rule_id column can be joined with the rule_id column in the gpcc_alert_rule table to access details of the rule that triggered the alert.
• The content column contains a JSON string with parameters specifying details about the event that triggered the alert. The JSON parameters vary with the type of the alert.
• The config column stores the alert history received from the notification center or from emails.

The gpcc_alert_history table is an append-only, column-oriented table, partitioned by month on the transaction_time column. Command Center creates new partitions as needed and removes partitions over 12 months old.

A row is added to the gpcc_alert_history table whenever an alert rule is matched.

VMware Greenplum Metrics History Tables

The gpmetrics query history saves information collected by the Greenplum Database metrics collection system and forwarded to Greenplum Command Center.

The distribution key for each table is a ctime timestamp column, which is the time when the row is added to the database. The tables are partitioned by year and month, except for gpcc_queries_history and gpcc_plannode_history which are partitioned by day for optimal performance. Greenplum Command Center creates new partitions automatically as needed.

The history tables use append-optimized, column-oriented storage.

Command Center only saves queries with runtimes greater than the value of the min_query_time configuration parameter, found in the $HOME/gpmetrics/gpcc.conf configuration file on the host executing Command Center. The default, 0, saves all queries in the history table. This parameter can be configured on the Command Center Admin> Settings page.

gpcc_database_history

The gpcc_database_history table saves summary query activity metrics collected by the VMware Greenplum metrics collector. This data can be used to review the VMware Greenplum query load over time.

gpcc_disk_history

The gpcc_disk_history table saves historical disk usage statistics for each VMware Greenplum segment host file system.

gpcc_export_log

The gpcc_export_log table saves the log notifications from every “EXPORT ALL” user action. Whenever the user exports search results from the History or the Table Browser pages, this table gets updated.

gpcc_index_info

Command Center uses the gpcc_index_info table to store information about table indices’ size, particularly their bloat size. This information is used to report when reindexing is recommended for a table.

gpcc_pg_log_history

The gpcc_pg_log_history table stores pg_log errors and warnings from the Greenplum Server log files.

gpcc_plannode_history

The gpcc_plannode_history table saves detailed metrics for each operation (node) in a completed query plan. Each row contains metrics for one operation that executed on one VMware Greenplum segment. This information allows reconstructing the plan and execution metrics for a completed query.

Plan node history is only saved for queries that execute for 10 seconds or more.

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！

验证码：

请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。