自助健康检查常用命令

实例在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;