GreenPlum中的gpmetrics Schema下的表介绍

0    188    1

Tags:

👉 本文共约26416个字,系统预计阅读时间或需100分钟。

简介

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

User-Defined Functions

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.

ColumnTypeDescription
rule_idintegerUnique id for the rule.
rule_typeintegerReserved for future use.
rule_descriptioncharacter varying(512)Text of the rule.
rule_configjsonJSON string containing parameters for user-specified values.
ctimetimestamp(0) without time zoneTime the rule was created.
etimetimestamp(0) without time zoneTime the rule became inactive, or null if rule is active.

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:

ColumnTypeDescription
idintegerUnique ID for the alert.
rule_idintegerThe ID of the rule that triggered this alert.
transaction_timetimestamp(0) without time zoneTime the alert was raised.
contentjsonContains parameters specifying values that triggered the alert.
configjsonStores alert history from emails or notifications.

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.

ColumnTypeDescription
ctimetimestamp(0) without time zoneTime the record was created.
queries_totalintegerTotal number of queries running and queued to run.
queries_runningintegerNumber of queries currently running.
queries_queuedintegerNumber of queries queued, but not yet running.
queries_blockedintegerThe number of queries started, but blocked by other transactions.
queries_finishedintegerThe number of queries that completed since the previous sampling interval.

gpcc_disk_history

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

ColumnTypeDescription
ctimetimestamp(0) without time zoneTime the row was created.
hostnamecharacter varying(64)Name of the segment host.
filesystemtextPath to the segment’s data directory.
total_bytesbigintTotal size of the file system storage in bytes.
bytes_usedbigintNumber of storage bytes in
bytes_availablebigintNumber of storage bytes available.

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.

ColumnTypeModifiers
idintegernot null default nextval(‘gpcc_export_log_id_seq’::regclass)
ctimetimestamp(0) with time zonenot null default now()
sourcecharacter varying(64)not null
filenamecharacter varying(64)not null
rolenamenot null
req_paramsjsonnot null
etimetimestamp(0) with time zone
statuscharacter varying(64)
fail_msgtext

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.

ColumnTypeDescription
dbidOIDThe id of the table’s database; this value is related to the pg_database OID.
databasevarchar(64)The table’s database.
schemavarchar(64)The table’s schema.
table_oidOIDThe table’s OID.
relation_namevarchar(64)The table’s name.
index_oidOIDThe OID of the table’s index.
index_namevarchar(64)The name of the table’s index.
real_sizebigintThe actual size of the index.
extra_sizebigintThe estimated extra size not used or needed by the index. This extra size is composed of the fillfactor, bloat, and alignment padding spaces.
fillfactorintThe fillfactor of the index.
index_bloat_spacebigintThe estimated size of the bloat without the extra space set aside for the fillfactor.
index_bloat_ratebigintThe estimated percentage of the real size used by the index bloat space.
is_nabooleanIf true, then the index bloat recorded by that row is “not accurate” and that index will not be included in the REINDEX recommendation.
latest_scan_tstimestamp with timezoneThe time of the last scan.
last_reindexedtimestamp with timezoneThe time of the last reindexing.

gpcc_pg_log_history

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

ColumnTypeDescription
logtimetimestamp without time zoneTimestamp for this log.
logusertextName of the role executing the query.
logdatabasetextThe database accessed.
logpidtextProcess id.
logthreadtextThread number.
loghosttextHost name or IP address of the host.
logporttextPort number.
logsessiontimetimestamp without time zoneSession timestamp.
logtransactionintegerTransaction id.
logsessiontextSession id.
logcmdcounttextCommand count.
logsegmenttextSegment number.
logslicetextSlice number.
logdistxacttextDistributed transation id.
loglocalxacttextLocation transacton id.
logsubxacttextSubtransaction id.
logseveritytextLog severity.
logstatetextSQL State code associated with this log message.
logmessagetextLog or error message text.
logdetailtextDetail message text associated with an error message.
loghinttextHint message text associated with an error message.
logquerytextInternally-generated query text.
logqueryposintegerIndex into the internally-generated query text.
logcontexttextContext in which this message gets generated.
logdebugtextQuery string with full detail for debugging.
logcursorposintegerCursor index into the query string.
logfunctiontextFunction in which this message is generated.
logfiletextLog file in which this message is generated.
loglineintegerLine in the log file in which this message is generated.
logstacktextFull text of the stack trace associated with this message.

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.

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
GreenPlum中的gpmetrics Schema下的表介绍后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部