自助健康检查常用命令

实例在256Core以下规格，建议只使用默认Table Group；256Core及以上规格实例，可以定义2~3个Table Group。数据库总的Table Group数不建议超过3个。Shard数应大于Worker Node数，小于Core数的60%。如果配置了Replication，Shard数应等比例减小，或等比例增加Worker Node计算资源。检查命令如下。

-- 查询当前Table Group个数
SELECT COUNT(DISTINCT tablegroup_name)
FROM hologres.hg_table_group_properties;

-- 检查每个TG配置，一个TG不建议超过3000张表(table_num)
SELECT tablegroup_name AS table_group
        ,max(property_value) FILTER( WHERE property_key='shard_count') AS shard_count
        ,max(property_value) FILTER( WHERE property_key='replica_count') AS replica_count
        ,max(property_value) FILTER( WHERE property_key='is_default_tg') AS is_default
        ,max(property_value) FILTER( WHERE property_key='table_num') AS table_num
FROM    hologres.hg_table_group_properties
GROUP BY tablegroup_name;

如果Table Group数量超过3个，建议规划为一个核心Table Group，合并多余Table Group；或者一个主Table Group，以及一个面向维表的小Table Group，通过（Resharding）迁移表至新建Table Group操作。

过多的表，引起小文件多，导致元数据占用内存过多，检查表数量命令如下。

-- 检查不同Schema下的内部表数量
SELECT table_namespace AS schema 
        , COUNT(distinct table_name) AS total_tables
        , COUNT(distinct table_name) FILTER ( WHERE property_key='storage_format') AS inner_tables
FROM    hologres.hg_table_properties
WHERE   table_namespace NOT IN ('hologres','hologres_statistic','pg_catalog')
GROUP BY table_namespace
ORDER BY table_namespace;

如果分区表小且多，建议重新建表，将分区表合并为普通表。

检查表统计信息命令如下。

-- 检索超过一天没有更新统计信息的表
SELECT  schema_name
        ,table_name
        ,total_rows
        ,analyze_timestamp
FROM    hologres_statistic.hg_table_statistic
WHERE   analyze_timestamp < now() - interval '1 days'
ORDER BY schema_name
         ,table_name
LIMIT 200;

如果有统计信息更新不及时的表，且该表有数据更新，请执行analyze tablename命令，刷新表的统计信息。

检查资源组的命令如下。

SELECT * FROM pg_holo_resource_groups
WHERE property_key='worker_limit';

行存表使用场景相对有限，主要用在Flink关联维表场景，因此需要避免误用。列出所有行存表命令如下。

SELECT  table_namespace AS schema
        ,table_name AS tables
FROM    hologres.hg_table_properties
WHERE
        property_key = 'orientation'
AND     property_value = 'row'
AND     table_namespace NOT IN ('hologres','hologres_statistic','pg_catalog');

如果误用了行存表，请重新建表，选择列存或者行列共存表。

建议每个表都至少设置一列做Distribution Key，并且Distribution Key不建议超过2个，检查命令如下。

-- 列出所有Distribution Key超过2列的表
SELECT  table_namespace AS schema
        ,table_name AS tables
        ,property_value AS distribution_key
FROM    hologres.hg_table_properties
WHERE   property_key = 'distribution_key'
AND     table_namespace NOT IN ('hologres','hologres_statistic','pg_catalog')
AND     array_length(string_to_array(property_value,','),1) > 2;

-- 列出所有没有设置Distribution Key的表
SELECT  DISTINCT table_namespace AS schema
        ,table_name AS tables
FROM    hologres.hg_table_properties a
WHERE property_key='storage_format' AND NOT EXISTS (
                     SELECT  1
                     FROM    hologres.hg_table_properties b
                     WHERE   b.property_key = 'distribution_key'
                     AND     a.table_namespace = b.table_namespace
                     AND     a.table_name = b.table_name)
                     AND     table_namespace NOT IN ('hologres','hologres_statistic','pg_catalog'
                 )
ORDER BY schema
         ,tables;

如果Distribution Key列超过两个，请重新建表，选择一到两个列作为Distribution Key。

仅对低基数列设置Dictionary Encoding，一般建议不超过20列。如果不确定，请选择auto encoding，避免全部列设置Dictionary Encoding。检查命令如下。

--列出所有dictionary_encoding_columns超过20列，并且没有配置为auto encoding的表
SELECT  table_namespace AS schema
        ,table_name AS tables
        ,property_value AS dictionary_encoding_columns
FROM    hologres.hg_table_properties
WHERE   property_key = 'dictionary_encoding_columns'
AND     table_namespace NOT IN ('hologres','hologres_statistic','pg_catalog')
AND     array_length(string_to_array(property_value,','),1) > 20
AND     property_value NOT LIKE '%:auto';

如果设置Dictionary Encoding列超过20列，请通过SET_TABLE_PROPERTY或者UPDATE_TABLE_PROPERTY命令更改dictionary_encoding_columns的配置，详情请参见SET_TABLE_PROPERTY或者ALTER TABLE。

仅对需要等值比较的列设置Bitmap，一个表建议不超过30列设置Bitmap，过多字符串列设置Bitmap会占用额外存储和内存开销。

-- 列出所有bitmap_columns超过30个列的表
SELECT  table_namespace AS schema
        ,table_name AS tables
        ,property_value AS bitmap_columns
FROM    hologres.hg_table_properties
WHERE   property_key = 'bitmap_columns'
AND     table_namespace NOT IN ('hologres','hologres_statistic','pg_catalog')
AND     array_length(string_to_array(property_value,','),1) > 30;

如果设置Bitmap列超过30列，请通过SET_TABLE_PROPERTY或者UPDATE_TABLE_PROPERTY命令更改bitmap_columns的配置，详情请参见SET_TABLE_PROPERTY或者ALTER TABLE。

Clustering Key具备左匹配原则，因此一般不建议超过两个列，否则适用场景减少。检查命令如下。

-- 列出所有clustering_key超过2个列的表
SELECT  table_namespace AS schema
        ,table_name AS tables
        ,property_value AS clustering_key
FROM    hologres.hg_table_properties
WHERE   property_key = 'clustering_key'
AND     table_namespace NOT IN ('hologres','hologres_statistic','pg_catalog')
AND     array_length(string_to_array(property_value,','),1) > 2;

如果Clustering Key设置超过两列，请重新建表，选择一到两个排序列作为Clustering Key。

Segment Key用于文件分块，建议最多只设置一列，且类型为时间戳或者整型。检查命令如下。

-- 列出所有Segment Key大于一列的表
SELECT  table_namespace AS schema
        ,table_name AS tables
        ,property_value AS segment_key
FROM    hologres.hg_table_properties
WHERE   property_key = 'segment_key'
AND     table_namespace NOT IN ('hologres','hologres_statistic','pg_catalog')
AND     array_length(string_to_array(property_value,','),1) > 1;

如果Segment Key设置超过一列，请重新建表，选择一个时间戳列作为Segment Key。

TTL表示一个表数据的回收时间，由于回收是异步进行，不建议TTL小于七天，否则可能会由于回收不及时，造成重复数据。检查命令如下。

-- 列出所有time_to_live_in_seconds小于7天的表
SELECT  table_namespace AS schema
        ,table_name AS tables
        ,property_value AS time_to_live_in_seconds
FROM    hologres.hg_table_properties
WHERE   property_key = 'time_to_live_in_seconds'
AND     table_namespace NOT IN ('hologres','hologres_statistic','pg_catalog')
AND     property_value::bigint < 604800;

如果表的TTL小于七天，请通过SET_TABLE_PROPERTY命令更改TTL大于七天，详情请参见SET_TABLE_PROPERTY。

Binlog能力强大，但消耗资源也更多，使用Binlog的表写入性能会受到较大影响，行存表Binlog的开销会远小于列存表，因此对于列存表，谨慎开通Binlog能力。检查命令如下。

-- 列出所有配置了Binlog的表
SELECT  table_namespace AS schema
        ,table_name AS tables
FROM    hologres.hg_table_properties
WHERE   property_key = 'binlog.level'
AND     property_value = 'replica'
AND     table_namespace NOT IN ('hologres','hologres_statistic','pg_catalog')
;

-- 列出所有Binlog TTL大于7天的表，建议缩短TTL
SELECT  table_namespace AS schema
        ,table_name AS tables
        ,property_value AS "binlog.ttl"
FROM    hologres.hg_table_properties
WHERE   property_key = 'binlog.ttl'
AND     property_value::bigint > 604800
AND     table_namespace NOT IN ('hologres','hologres_statistic','pg_catalog')
;

如果Binlog设置不合适，请通过SET_TABLE_PROPERTY命令调整Binlog级别及TTL时间，详情请参见SET_TABLE_PROPERTY。

数据分布在不同的Shard中，如果部分Shard的数据明显多于其他Shard，说明数据具有显著的倾斜性，此时应该调整Distribution Key的设计，实现更为均衡的数据分布。检查命令如下。

SELECT  hg_shard_id
        , COUNT(*)
FROM    table_name
GROUP BY hg_shard_id;

如果数据明显倾斜，需要通过调整Distribution_key重新导入数据。

不同类型Query的占比、成功率、延时、并发同比环比分析命令如下。

-- 过去7天各个数据库的DML次数(Select\Insert\Update\Delete)
SELECT datname, query_date, count(*) FROM hologres.query_log
WHERE query_date > to_char(current_date - interval'7 days','YYYYMMDD')
AND command_tag IN ('SELECT','INSERT','UPDATE','DELETE')
GROUP BY datname, query_date
ORDER BY datname, query_date DESC;

-- 过去1天各类DML的执行成功情况
SELECT datname, query_date, command_tag,
       count(*) FILTER( WHERE status='SUCCESS') AS SUCCESS,
       count(*) FILTER( WHERE status='FAILED') AS FAILED
FROM hologres.query_log
WHERE query_date > to_char(current_date - interval'1 days','YYYYMMDD')
AND command_tag IN ('SELECT','INSERT','UPDATE','DELETE')
GROUP BY datname, query_date, command_tag
ORDER BY datname, query_date DESC;

-- 最近2天成功查询的响应延时分析
SELECT datname, query_date, command_tag, count(*), AVG(duration) as duration
FROM hologres.query_log
WHERE query_date > to_char(current_date - interval'1 days','YYYYMMDD')
AND command_tag IN ('SELECT','INSERT','UPDATE','DELETE')
AND status = 'SUCCESS'
GROUP BY datname, query_date, command_tag
ORDER BY datname, query_date DESC;

过去一天耗时最长的重点慢查询检查命令如下。

-- 查询过去1天耗时最长的重点慢查询
SELECT status AS "状态",
       duration AS "耗时(ms)",
       optimization_cost AS "优化耗时(ms)",
       start_query_cost AS "启动耗时(ms)",
       get_next_cost AS "执行耗时(ms)",
       duration-optimization_cost-start_query_cost-get_next_cost AS "其他耗时(ms)",
       query_id AS "QueryID",
       query
 FROM hologres.hg_query_log
 WHERE query_start > current_date - interval '1 days'
  AND command_tag IN ('SELECT')
  AND duration > 1000
 ORDER BY duration DESC,
          start_query_cost DESC,
          optimization_cost,
          get_next_cost DESC,
          duration-optimization_cost-start_query_cost-get_next_cost DESC
 LIMIT 200;

消耗资源最多查询的检查命令如下。

-- 查询最近一天消耗比较高的Query
SELECT status AS "状态",
       duration AS "耗时(ms)",
       query_start AS "开始时间",
       (read_bytes/1048576)::text || ' MB' AS "读取量",
       (memory_bytes/1048576)::text || ' MB' AS "内存",
       (shuffle_bytes/1048576)::text || ' MB' AS "Shuffle",
       (cpu_time_ms/1000)::text || ' s' AS "CPU时间",
       physical_reads AS "读盘",
       query_id AS "QueryID",
       query
 FROM hologres.hg_query_log
 WHERE query_start > current_date - interval'1 days'
 AND command_tag IN ('SELECT','INSERT','UPDATE','DELETE')
 AND duration > 1000
 ORDER BY duration DESC,
          read_bytes DESC,
          shuffle_bytes DESC,
          memory_bytes DESC,
          cpu_time_ms DESC,
          physical_reads DESC
 LIMIT 500;