国产数据库_AntDB数据库执行计划

参数管理相关命令

param 表用于管理存放 AntDB 集群中所有节点的 postgresql.conf 文件中的参数，当参数某个被修改后，该参数就会被添加到此表中，用来标识。对于修改配置参数的查询，可以通过 list param 命令。

参数设置 set param

命令功能：

更改 postgresql.conf 节点配置文件中的参数，如果该参数有效，则系统内部会执行相关的操作，使更改生效，此操作只适用于那些不需要重启集群的参数类型（如 sighup，user，superuser），而对于修改其它类型的参数，则会给出相应的提示。如果在命令尾部加 force，则不会检查参数的有效性，而强制写入文件中，系统不执行任何操作，只起到记录作用；

命令格式：

SET COORDINATOR [ MASTER | SLAVE ] ALL ( { parameter = value } [, ...] ) [ FORCE ] 
SET COORDINATOR { MASTER | SLAVE} node_name ( { parameter = value } [, ...] ) [ FORCE ] 
SET DATANODE [ MASTER | SLAVE ] ALL ( { parameter = value } [, ...] ) [ FORCE ] 
SET DATANODE { MASTER | SLAVE } node_name ( { parameter = value } [, ...] ) [ FORCE ] 
SET GTMCOORD ALL ( { parameter = value } [, ...] ) [ FORCE ] 
SET GTMCOORD { MASTER | SLAVE } node_name ( { parameter = value } [, ...] ) [ FORCE ]

命令举例：

-- 修改coord1上的死锁时间 
SET  COORDINATOR  MASTER coord1（deadlock_timeout = '1000ms'）; 
-- 修改所有的datanode上配置文件中的checkpoint_timeout的参数 
SET  DATANODE  all（checkpoint_timeout = '1000s'）; 
-- 修改所有的datanode上配置文件中的一个不存在的参数 
SET  DATANODE  all（checkpoint = '10s'）  FORCE;

参数重置 reset param

命令功能：

把 postgresql.conf 文件中的参数变为默认值。

命令格式：

RESET COORDINATOR [ MASTER | SLAVE ] ALL ( parameter [, ...] ) [ FORCE ] 
RESET COORDINATOR { MASTER | SLAVE } node_name ( parameter [, ...] ) [ FORCE ] 
RESET DATANODE [ MASTER | SLAVE ] ALL ( parameter [, ...] ) [ FORCE ]  
RESET DATANODE { MASTER | SLAVE } node_name ( parameter [, ...] ) [ FORCE ] 
RESET GTMCOORD ALL ( parameter [, ...] ) [ FORCE ]  
RESET GTMCOORD { MASTER | SLAVE } node_name ( parameter [, ...] ) [ FORCE ]

命令举例：

-- 把datanode master db1的配置参数checkpoint_timeout变为默认值。其中查询结果中的*号是适配符，表示所有满足条件的节点名。 
RESET  DATANODE  MASTER  db1 (checkpoint_timeout); 
-- 把datanode中所有的配置参数checkpoint_timeout变为默认值 
RESET  DATANODE  all (checkpoint_timeout);

参数查询 list param

命令功能：

查询节点的 postgresql.conf 配置文件中修改过的参数列表。

命令格式：

LIST PARAM 
LIST PARAM node_type node_name [ sub_like_string ] 
LIST PARAM cluster_type ALL [ sub_like_string ] 
 
where node_type can be one of: 
 
    GTMCOORD MASTER 
    GTMCOORD SLAVE 
    COORDINATOR MASTER 
    COORDINATOR SLAVE 
    DATANODE MASTER 
    DATANODE SLAVE 
 
where cluster_type can be one of: 
 
    GTMCOORD 
    COORDINATOR 
    DATANODE 
    DATANODE MASTER 
    DATANODE SLAVE

命令举例：

--查询节点类型为datanode master ，节点名为db1配置文件中修改后的参数 
LIST  param  DATANODE  MASTER  db1; 
--查询节点类型为coordinator的所有节点中配置文件中修改后的参数 
LIST  param  COORDINATOR  all;

参数模糊查询 show

命令功能：

显示集群节点实际的参数信息，支持模糊查询。

命令格式：

SHOW PARAM node_name parameter

命令举例：

-- 模糊查询节点db1的配置文件中有wal的参数 
  SHOW PARAM db1  wal; 
-- 查询节点db1的配置文件中checkponit_timeout的参数的内容 
  SHOW PARAM db1  checkpoint_timeout;

参数配置相关特性

屏蔽关键字特性

描述

AntDB 增加会话级别 GUC 参数 adb_exclude_keywords 屏蔽关键字功能。默认值为空。

一些表用到了保留关键字作为字段名称，会导致建表失败，通过设置参数 adb_exclude_keywords，能屏蔽某些关键字，满足一些使用关键字建表的需求。

设置方法

session 级别，set 命令设置，如 set adb_exclude_keywords to ‘keyword1,keyword2,keyword3....’ ;
通过在 url 中添加选项如：

url = "jdbc:postgresql://127.0.0.1:49000/postgres?options=-c%20adb_exclude_keywords=kw1,kw2";
查看值：show adb_exclude_keywords;

设置方法示例：
# freeze为关键字，创建表格时将freeze作为列名，失败：
antdb=# CREATE TABLE t1 (freeze int);
ERROR:  syntax error at or near "freeze"
LINE 1: CREATE TABLE t1 (freeze int);
                           ^
# 使用参数adb_exclude_keywords屏蔽关键字freeze，创建表格成功：
antdb=# SET adb_exclude_keywords = 'freeze';
SET
antdb=# SHOW adb_exclude_keywords;
 adb_exclude_keywords
----------------------
 freeze
(1 row)
antdb=# CREATE TABLE t1 (freeze int); 
CREATE TABLE

注意事项

通过指定会话级的 GUC 参数来进行关键字屏蔽，但不能屏蔽如下关键字：

"database","schema","table","view","index",
"create","insert","delete","truncate","drop","update","select","alter",
"where","into","from","order","join","limit","group","exists",
"set","show"

设置的值的总长度不能超过 256Bytes；
设置的值，都会转会成小写，不支持设置大写；
支持 oracle 关键字屏蔽和 pg 关键字的屏蔽, 设置的值必须是对应语法的关键字，否则无法达到屏蔽效果；
设置的关键字重复时，会自动去重，如 ‘freeze,freeze,freeze’ -----> 'freeze'

设置屏蔽字后添加数据，取消屏蔽字后，需要对值加引号才能正确取出，如下屏蔽‘between’

SET adb_exclude_keywords to 'between';
CREATE TABLE test_issue2136(between int); -- ok
SET adb_exclude_keywords to '';
SELECT * FROM test_issue2136 WHERE "between" BETWEEN 1 AND 2; -- ok

集群参数设置参考

ADBMGR 参数设置参考

数据库参数

 
cat  >> ${mgrdata}/postgresql.conf << EOF 
port = 18610 
listen_addresses = '*' 
log_directory = 'pg_log' 
log_destination ='csvlog' 
logging_collector = on 
log_min_messages = error 
max_wal_senders = 3 
hot_standby = on 
wal_level = replica 
EOF

HBA 参数

cat  >> ${mgrdata}/pg_hba.conf << EOF 
host    replication     all        10.0.0.0/8                 trust 
host    all             all          10.0.0.0/8               trust 
EOF

其中的10.0.0.0/8 需要根据实际情况修改。

节点内存配置建议

说明：节点参数的设置方法请参看以上的【参数管理相关命令】的【参数设置 set param 】章节。

shared_buffers

shared_buffers 是 AntDB 用于共享缓冲区的内存，是由 8kb 大小的块所形成的数组。AntDB 在进行更新、查询等操作时，首先从磁盘把数据读取到内存，之后进行更新，最后将数据写回磁盘。shared_buffers 可以暂时存放从磁盘读取的数据，能够让用户下次访问不需要去磁盘直接从里面读取出来，增加查询效率。shared_buffers 的系统默认值通常为 128MB。但是当 AntDB 服务器的内存大于 1G 时，则 shared_buffers 的合理起始值为系统内存的 25%，如果由于业务原因可以将 shared_buffers 设置的更大，但是 AntDB 依赖于操作系统的缓存，因此建议分配不要超过系统内存的 40%。建议设置系统内存的 25%-40%。

在 AntDB 中，shared_buffers 可以通过 explain (analyze,buffers) 来查看出多少数据块来源磁盘，多少来源 shared_buffers，即内存的。

--sample
--shared read(共享读取)指的是其取自磁盘且不被缓存。再次执行该查询计划后，结果以shared hit的形式展现，hit就是读取shared_buffers的

benchmarksql=# explain (analyze,buffers) select * from bmsql_district where d_w_id=30;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Cluster Gather  (cost=0.16..37.77 rows=3 width=89) (actual time=0.418..0.489 rows=10 loops=1)
   Buffers: shared hit=2
   ->  Index Scan using bmsql_district_pkey on bmsql_district  (cost=0.16..36.87 rows=3 width=89) (actual time=0.005..0.005 rows=0 loops=1)
         Index Cond: (d_w_id = 30)
         Buffers: shared hit=2
 Planning:
   Buffers: shared hit=19 read=1
 Planning Time: 0.286 ms
 Execution Time: 0.691 ms
(9 rows)

benchmarksql=# explain (analyze,buffers) select * from bmsql_district where d_w_id=30;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Cluster Gather  (cost=0.16..37.77 rows=3 width=89) (actual time=0.078..0.146 rows=10 loops=1)
   Buffers: shared hit=2
   ->  Index Scan using bmsql_district_pkey on bmsql_district  (cost=0.16..36.87 rows=3 width=89) (actual time=0.009..0.010 rows=0 loops=1)
         Index Cond: (d_w_id = 30)
         Buffers: shared hit=2
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.134 ms
 Execution Time: 0.346 ms
(9 rows)

work_mem

work_mem 是 AntDB 在写入临时磁盘文件之前，进行内部 sort(order by) 和 hash(join) 操作需要使用的内存量。 work_mem 需要通过 explain analyze 分析语句来确定合适的值。实际生产环境中，work_mem 值需要经过多次测试才能设定比较合理合适的值。需要注意的是 work_mem 是每单个连接用户使用的内存，也就是实际需要的内存为 max_connections * work_mem，必须保证max_connections*work_mem 的值不要超过实际可用的内存。

--sample
benchmarksql=# set work_mem='1MB';
SET
benchmarksql=# explain (analyze) select * from bmsql_customer where c_d_id=1 order by c_discount;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Cluster Merge Gather  (cost=2941033.78..3040263.14 rows=2551434 width=0) (actual time=26750.623..35037.215 rows=3000000 loops=1)
   Sort Key: c_discount
   ->  Gather Merge  (cost=2890005.10..2989234.46 rows=850478 width=558) (actual time=0.010..0.011 rows=0 loops=1)
         Workers Planned: 2
         Workers Launched: 0
         ->  Sort  (cost=2889005.08..2890068.18 rows=425239 width=558) (actual time=0.009..0.009 rows=0 loops=1)
               Sort Key: c_discount
               Sort Method: quicksort  Memory: 25kB
               ->  Parallel Seq Scan on bmsql_customer  (cost=0.00..2424839.67 rows=425239 width=558) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: (c_d_id = 1)
 Planning Time: 0.163 ms
 Execution Time: 35235.062 ms
(12 rows)

benchmarksql=# set work_mem='10MB';
SET
benchmarksql=# explain (analyze) select * from bmsql_customer where c_d_id=1 order by c_discount;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Cluster Merge Gather  (cost=2622726.28..2721955.64 rows=2551434 width=0) (actual time=5838.424..14739.394 rows=3000000 loops=1)
   Sort Key: c_discount
   ->  Gather Merge  (cost=2571697.60..2670926.96 rows=850478 width=558) (actual time=0.011..0.012 rows=0 loops=1)
         Workers Planned: 2
         Workers Launched: 0
         ->  Sort  (cost=2570697.58..2571760.68 rows=425239 width=558) (actual time=0.009..0.010 rows=0 loops=1)
               Sort Key: c_discount
               Sort Method: quicksort  Memory: 25kB
               ->  Parallel Seq Scan on bmsql_customer  (cost=0.00..2424839.67 rows=425239 width=558) (actual time=0.005..0.005 rows=0 loops=1)
                     Filter: (c_d_id = 1)
 Planning Time: 0.164 ms
 Execution Time: 14906.677 ms
(12 rows)

maintenance_work_mem

maintenance_work_mem 定义的内存主要影响 vacuum，analyze，create index，reindex 等操作，这些命令用到的频率不高，但是会消耗较多的资源，系统默认值通常为 64MB，可以分配高一点的内存，让上述命令进行地快速一点。当系统内存为 32G 时，可以分配512MB-1024MB 的内存。

effective_cache_size

effective_cache_size 是优化器假设查询可以使用的最大内存（包括 AntDB 和系统缓存），和 shared_buffer 内存无关，只是给优化器生产计划使用的一个假设值。该值建议设置为可用空闲内存的 25%-50%，设置值大点，可以让优化器更倾向使用索引扫描而不是顺序扫描。

wal_buffers

wal_buffers 是用于还没写入磁盘的 WAL（预写入日志）数据的共享内存量，主要影响数据库的写入性能。该参数的默认值为 -1。AntDB 会自动选择一个合理的值。但是一般情况给的值都不超过 1MB，wal_buffers 的内容是在每次事务提交时写入磁盘，因此设置太大的值也没有用处。但是将此值设置为至少几兆可以提高许多客户端一次提交的写入性能。

COORDINATOR 节点参数设置参考

--coord:
--Modify according to actual situation/请用户根据主机环境信息，适当调整
SET COORDINATOR ALL (shared_buffers = '48GB' );
SET COORDINATOR ALL (effective_cache_size = '96GB' );
SET COORDINATOR ALL (waitglobaltransaction = '300s');
SET COORDINATOR ALL (maintenance_work_mem = '1024MB');
SET COORDINATOR ALL (work_mem = '8MB' );
SET COORDINATOR ALL (max_connections = 2000 );
SET COORDINATOR ALL (max_prepared_transactions = 2000 );
--设置为cpu cores
SET COORDINATOR ALL (max_parallel_workers = 12 );
--antdb使用reduce需要增大mwp参数设置
SET COORDINATOR ALL (max_worker_processes = 2000 );
SET COORDINATOR ALL (max_parallel_workers_per_gather = 4 );
SET COORDINATOR ALL (log_destination = 'csvlog');
SET COORDINATOR ALL (log_truncate_on_rotation = on);
SET COORDINATOR ALL (log_rotation_age = '7d');
SET COORDINATOR ALL (log_rotation_size = '100MB');
SET COORDINATOR ALL (log_min_messages = error );
--设置执行最大耗时记录语句ms
SET COORDINATOR ALL (log_min_duration_statement = 50000 );
SET COORDINATOR ALL (log_duration = off);
SET COORDINATOR ALL (log_statement = 'ddl' );
SET COORDINATOR ALL (log_lock_waits = on );
SET COORDINATOR ALL (unix_socket_permissions =0700);
SET COORDINATOR ALL (listen_addresses = '*' );
SET COORDINATOR ALL (superuser_reserved_connections = 13);
SET COORDINATOR ALL (tcp_keepalives_idle = 180);
SET COORDINATOR ALL (tcp_keepalives_interval = 10 );
SET COORDINATOR ALL (tcp_keepalives_count = 3 );
--开启表索引统计信息收集
SET COORDINATOR ALL (track_counts = on);
--设置pg_stat_activity.query长度
SET COORDINATOR ALL (track_activity_query_size = 2048 );
--默认64，当查询中有大量子表的时候分区表可以调大，从库设置大于等于主库
SET COORDINATOR ALL (max_locks_per_transaction = 128);
--通常用于pg父子表查询条件优化，使用约束条件
SET COORDINATOR ALL (constraint_exclusion = on);
SET COORDINATOR ALL (wal_level='replica');
SET COORDINATOR ALL (max_wal_senders = 3);
SET COORDINATOR ALL (hot_standby = off);
SET COORDINATOR ALL (autovacuum = on );
SET COORDINATOR ALL (autovacuum_max_workers = 5 );
SET COORDINATOR ALL (autovacuum_naptime = '60min');
SET COORDINATOR ALL (autovacuum_vacuum_threshold = 500);
SET COORDINATOR ALL (autovacuum_analyze_threshold = 500 );
SET COORDINATOR ALL (autovacuum_vacuum_scale_factor = 0.5 );
SET COORDINATOR ALL (autovacuum_vacuum_cost_limit = -1);
SET COORDINATOR ALL (autovacuum_vacuum_cost_delay = '30ms');
SET COORDINATOR ALL (statement_timeout = 86400000 );
SET COORDINATOR ALL (lock_timeout = '30s');
SET COORDINATOR ALL (idle_in_transaction_session_timeout = '3000s');
SET COORDINATOR ALL (fsync = on);
--设置主从数据同步一致性(性能测试可以关闭)
SET COORDINATOR ALL (synchronous_commit = on );
--open_datasync直接io,受fsync参数影响
SET COORDINATOR ALL (wal_sync_method = open_datasync);
SET COORDINATOR all (wal_log_hints = on);
--用于故障恢复建议打开
SET COORDINATOR ALL (full_page_writes = on );
SET COORDINATOR ALL (commit_delay = 10);
SET COORDINATOR ALL (commit_siblings = 10 );
SET COORDINATOR ALL (checkpoint_timeout = '15min'); 
SET COORDINATOR ALL (checkpoint_completion_target=0.9 );
SET COORDINATOR ALL (max_wal_size = 10240);
SET COORDINATOR ALL (archive_mode = off);
SET COORDINATOR ALL (archive_command = '/bin/date');
--设置bgwriter写脏页的周期默认200ms，建议设置10的倍数
SET COORDINATOR ALL (bgwriter_delay = '10ms');
--设置bgwriter写脏页的数量默认100，写密集系统可以调大，考虑IO写入能力不能设置太大
SET COORDINATOR ALL (bgwriter_lru_maxpages = 1000 );
--设置每轮最大写入脏页比例默认1，写入bgwriter_lru_maxpages*bgwriter_lru_multiplier
SET COORDINATOR ALL (bgwriter_lru_multiplier = 10.0 );
SET COORDINATOR ALL (pool_time_out = 300);
SET COORDINATOR ALL (enable_pushdown_art = on );

DATANODE 节点参数设置参考

--datanode:
--Modify according to actual situation/请用户根据主机环境信息，适当调整
SET DATANODE ALL (shared_buffers = '48GB' );
SET DATANODE ALL (waitglobaltransaction = '300s');
SET DATANODE ALL (maintenance_work_mem = '1024MB');
SET DATANODE ALL (work_mem = '8MB' );
--dn节点最大连接数最少为cn配置参数的cn节点倍数
SET DATANODE ALL (max_connections = 4000 );
SET DATANODE ALL (max_prepared_transactions = 4000 );
SET DATANODE ALL (wal_keep_size = 102400 );
--建议设置和shared_buffers一致
SET DATANODE ALL (effective_cache_size = '96GB' );
SET DATANODE ALL (max_parallel_workers = 12 );
--reduce特性需要增大mwp参数设置不小于max_connections
SET DATANODE ALL (max_worker_processes = 2000 );
SET DATANODE ALL (max_parallel_workers_per_gather = 4 );
SET DATANODE ALL (log_destination = 'csvlog');
SET DATANODE ALL (log_truncate_on_rotation = on);
SET DATANODE ALL (log_rotation_age = '7d' );
SET DATANODE ALL (log_rotation_size = '100MB' );
SET DATANODE ALL (log_min_messages = error);
SET DATANODE ALL (log_min_error_statement = error );
SET DATANODE ALL (log_duration = off);
SET DATANODE ALL (log_statement = 'ddl' );
SET DATANODE ALL (log_lock_waits = on );
SET DATANODE ALL (unix_socket_permissions = '0700' );
SET DATANODE ALL (listen_addresses = '*');
SET DATANODE ALL (superuser_reserved_connections = 13 );
SET DATANODE ALL (track_counts = on );
SET DATANODE ALL (track_activity_query_size = 2048);
SET DATANODE ALL (max_locks_per_transaction = 64);
SET DATANODE ALL (constraint_exclusion = on );
SET DATANODE ALL (wal_level='replica');
SET DATANODE ALL (max_wal_senders = 5 );
--用于数据安全建议打开
SET DATANODE all (wal_log_hints = on);
--用于异常恢复加快速度，影响性能较大
SET DATANODE ALL (full_page_writes = on);
SET DATANODE ALL (autovacuum = on );
SET DATANODE ALL (statement_timeout = 86400000 );
SET DATANODE ALL (lock_timeout = '30s' );
SET DATANODE ALL (idle_in_transaction_session_timeout = '3000s');
SET DATANODE ALL (fsync = on);
--开启读写分离建议设置remote_apply否则设置为on
SET DATANODE ALL (synchronous_commit = on);
SET DATANODE ALL (wal_sync_method = open_datasync );
SET DATANODE ALL (wal_writer_delay = '200ms');
SET DATANODE ALL (wal_compression = on);
SET DATANODE ALL (commit_delay = 10 );
SET DATANODE ALL (commit_siblings = 10);
SET DATANODE ALL (checkpoint_timeout = '15min' );
SET DATANODE ALL (checkpoint_completion_target = 0.9);
SET DATANODE ALL (max_wal_size = 30960);
SET DATANODE ALL (min_wal_size = 2048);
SET DATANODE ALL (archive_mode = off );
SET DATANODE ALL (archive_command = '/bin/date' );
--设置值不小于max_connections
SET DATANODE ALL (max_prepared_transactions = 4000);
SET DATANODE ALL (bgwriter_delay = '10ms' );
SET DATANODE ALL (bgwriter_lru_maxpages = 1000);
SET DATANODE ALL (bgwriter_lru_multiplier = 10.0);
SET DATANODE ALL (rep_max_avail_flag = on );
set GTMCOORD all (enable_fast_query_shipping = off);
set GTMCOORD all (pgxc_enable_remote_query = off);
set COORDINATOR all (enable_fast_query_shipping = off);
set COORDINATOR all (pgxc_enable_remote_query = off);
set DATANODE all (enable_fast_query_shipping = off);
set DATANODE all (pgxc_enable_remote_query = off);

GTMCOORD 节点参数设置参考

--gtmcoord:
SET GTMCOORD ALL (shared_buffers = '8GB' );
SET GTMCOORD ALL (work_mem = '4MB' );
SET GTMCOORD ALL (maintenance_work_mem = '1024MB');
--设置为cn设置值*cn节点数
SET GTMCOORD ALL(max_connections = 2000);
SET GTMCOORD ALL (max_worker_processes = 2000 );
SET GTMCOORD ALL(max_prepared_transactions = 2000);
SET GTMCOORD ALL (log_destination = 'csvlog');
SET GTMCOORD ALL (log_truncate_on_rotation = on);
SET GTMCOORD ALL (log_rotation_age = '7d');
SET GTMCOORD ALL (log_rotation_size = '100MB');
SET GTMCOORD ALL (log_min_messages = error );
--关闭fsync提升性能可能丢失数据系统故障电源故障
SET GTMCOORD ALL (fsync = on);
SET GTMCOORD all (wal_log_hints = on);
--用于异常恢复加快速度，影响性能较大
SET GTMCOORD ALL (full_page_writes = on);
set GTMCOORD ALL (wal_level='replica');
SET GTMCOORD ALL (max_wal_senders = 5 );
SET GTMCOORD ALL (lock_timeout = '30s');
SET GTMCOORD ALL (idle_in_transaction_session_timeout = '3000s');
SET GTMCOORD ALL (waitglobaltransaction = '300s');
set GTMCOORD all (autovacuum =on);
SET GTMCOORD ALL (rep_max_avail_flag = on );
SET GTMCOORD ALL (synchronous_commit = on);

数据库参数设置

参数管理相关命令

参数设置 set param

参数重置 reset param

参数查询 list param

参数模糊查询 show

参数配置相关特性

屏蔽关键字特性

集群参数设置参考

ADBMGR 参数设置参考

节点内存配置建议

shared_buffers

work_mem

maintenance_work_mem

effective_cache_size

wal_buffers

COORDINATOR 节点参数设置参考

DATANODE 节点参数设置参考

GTMCOORD 节点参数设置参考