数据库参数配置
数据库参数修改
单节点环境
会话级设置
--以deadlock_timeout为列
--设置后仅在当前会话生效
SET deadlock_timeout ='2s';
全局设置
--以deadlock_timeout为列
--设置后全局生效
ALTER system SET deadlock_timeout ='2s';
SELECT pg_reload_conf();
需要重启数据库实例的参数设置
--部分参数设置是需要重启数据库才能生效的，如果在会话级进行设置，会提示有相关ERROR提示
antdb=# SET max_connections=123;
ERROR: parameter "max_connections" cannot be changed without restarting the server
--对于需要重启数据库才能生效的参数，需要修改数据库目录中postgresql.conf文件，并重启数据库
--这类参数的影响范围是全局
注意：
对于全局设置的参数，请注意主备节点要保持一致。
高可用集群环境
参数的获取有一定的优先级：edit-config 修改（adbhamgrctl -c /etc/adbhamgr.yaml edit-config） > adbhamgr 的本地配置（/etc/adbhamgr.yaml） > postgresql.auto.conf > postgresql.conf > postgresql.base.conf。
对于参数的修改，有以下几种情形。
会话级设置
--以deadlock_timeout为列
--设置后仅在当前会话生效
SET deadlock_timeout ='2s';
全局设置
通过 adbhamgrctl edit-config 命令可以更改 adbdcs 中的共享配置，全局修改参数，如果需要重启生效，则修改完成重启集群即可。
--通过edit-config命令会影响集群中所有节点
--执行如下命令进入修改：
adbhamgrctl -c /etc/adbhamgr.yml edit-config
--执行上述命令后，显示DCS中的共享配置，如下：
loop_wait: 5
master_start_timeout: 300
maximum_lag_on_failover: 54023568112
postgresql:
parameters:
hot_standby: 'on'
listen_addresses: '*'
log_destination: csvlog
log_directory: /data1/antdb/pg_log
max_connections: 100000
max_wal_senders: 10
port: 55551
wal_keep_segments: 10
wal_level: logical
use_pg_rewind: true
retry_timeout: 5
synchronous_mode: true
ttl: 15
--此时可在postgresql的parameters下进行修改或者添加相关参数(类似vim编辑器,按i进入修改)，如：max_connections: 20000
--修改完毕后，类似vim编辑器，输入:wq! 退出编辑。
--此时界面显示修改项目，根据提示，输入y保存修改：
---
+++
@@ -8,7 +8,7 @@
listen_addresses: '*'
log_destination: csvlog
log_directory: /data1/antdb/pg_log
- max_connections: 100000
+ max_connections: 20000
max_wal_senders: 10
port: 55551
wal_keep_segments: 10
Apply these changes? [y/N]:
--输入Y，会自动reload参数。
对于不需要重启生效的参数，则通过上面的修改即可生效。
对于需要重启的参数，例如 max_connections，会话级设置会提示需要重启，则还需要执行 adbhamgrctl restart 重启集群：
--部分参数设置是需要重启数据库才能生效的，如果在会话级进行设置，会提示有相关ERROR提示
antdb=# SET max_connections=123;
ERROR: parameter "max_connections" cannot be changed without restarting the server
执行 adbhamgrctl restart 命令重启集群：
adbhamgrctl -c /etc/adbhamgr.yml restart postgres-cluster --force
AntDB 集中式数据库参数列表请参考附录。
AntDB 参数设置特性
描述
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
数据库内存参数设置建议
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
-----------------------------------------------------------------------------------------------------
--------------------------------
Index Scan using bmsql_district_pkey on bmsql_district (cost=0.14..1.79 rows=1 width=91) (actual ti
me=0.061..0.063 rows=0 loops=1)
Index Cond: (d_w_id = 30)
Buffers: shared read=1
Planning:
Buffers: shared hit=94 dirtied=1
Planning Time: 1.623 ms
Execution Time: 0.388 ms
(7 rows)
benchmarksql=# EXPLAIN (analyze,buffers) SELECT * FROM bmsql_district WHERE d_w_id=30;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
--------------------------------
Index Scan using bmsql_district_pkey on bmsql_district (cost=0.14..1.79 rows=1 width=91) (actual ti
me=0.025..0.027 rows=0 loops=1)
Index Cond: (d_w_id = 30)
Buffers: shared hit=1
Planning Time: 0.257 ms
Execution Time: 0.097 ms
(5 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
-----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------
Gather Merge (cost=15950.90..18804.54 rows=24458 width=558) (actual time=99.856..144.341 rows=30000
loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=14950.88..14981.45 rows=12229 width=558) (actual time=86.578..91.024 rows=10000 lo
ops=3)
Sort Key: c_discount
Sort Method: external merge Disk: 5968kB
Worker 0: Sort Method: quicksort Memory: 9737kB
Worker 1: Sort Method: quicksort Memory: 9211kB
-> Parallel Index Scan using bmsql_customer_pkey on bmsql_customer (cost=0.42..14120.65 ro
ws=12229 width=558) (actual time=1.261..58.155 rows=10000 loops=3)
Index Cond: (c_d_id = 1)
Planning Time: 23.329 ms
Execution Time: 147.796 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
-----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------
Gather Merge (cost=15950.90..18804.54 rows=24458 width=558) (actual time=68.330..119.872 rows=30000
loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=14950.88..14981.45 rows=12229 width=558) (actual time=58.013..62.940 rows=10000 lo
ops=3)
Sort Key: c_discount
Sort Method: external merge Disk: 6032kB
Worker 0: Sort Method: quicksort Memory: 9495kB
Worker 1: Sort Method: quicksort Memory: 9378kB
-> Parallel Index Scan using bmsql_customer_pkey on bmsql_customer (cost=0.42..14120.65 ro
ws=12229 width=558) (actual time=0.077..29.323 rows=10000 loops=3)
Index Cond: (c_d_id = 1)
Planning Time: 0.383 ms
Execution Time: 123.806 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%，设置值大点，可以让优化器更倾向使用索引扫描而不是顺序扫描。