瀚高数据库执行计划解读及常见性能优化点（培训交流记要）

BTREE 的索引，针对like模糊查询，只支持 like 'AAAAA%' 不支持 like '%AAAAA'





2、数据库并行-- 不超过 CPU核数的

max_worker_processes=XXX



3、每个事务的锁数量默认64 不宜过大（整个库也就1000）

max_locks_per_transaction

4、maintenance_work_mem 后台进程、临时表内存调整到2G---- 和数据库、用户数 关联不大。

5、temp_buffers 临时表内存--创建临时表、查询视图。--- 占系统内存，按session独享，占session*值

6、因以下语句中涉及的表bi_smart_dir的pk_parent列中存在长度为0的字符串（不是null值）。该功能开启时，下面的查询语句执行结果不准确，需要关闭该功能。

SELECT
*,length(pk_parent) FROM bi_smart_dir order by ts desc

SELECT
* FROM bi_smart_dir where (pk_parent = '' or
pk_parent is null or pk_parent = '~') and moduleid = 'ufoa' and pk_org =
'GLOBLE00000000000000' and ( sysinit = 'N' or sysinit is null )



关闭并生效的命令如下：

set
session_restore_keyword_list to 'system';

alter
system set emptystr_to_null = off;
select pg_reload_conf();

explain (analyze,buffers) verbose  具体语句

verbose  =详细 

Seq_scan 全表扫描

Index_scan 索引扫描

索引的建议： 单个索引列不要超过4个，超过在PG内会很难采用

表关联查询，如果有条件，先执行 条件，再关联~~

1 概述
本文档主要介绍NC Cloud适配瀚高数据库场景下的部分参数配置指南。瀚高数据库是基于PG内核的数据库，其参数设计、调优方法，可参考PG数据库调优的标准流程进行。本文重点介绍在适配过程中遇到的部分默认参数不适宜NC Cloud产品，进而推荐进行调整的情况。本指南中的参数调整是基于X86架构+40CPU+256G内存环境下的推荐值，其他架构及配置可咨询数据库提供方。

2 参数调整
2.1 主要待调整参数
max_connections = '3000'
work_mem = '256MB'
temp_buffers = '64MB'
shared_buffers = '60GB'
max_wal_size = '5GB'
max_worker_processes = '16'
max_parallel_workers_per_gather = '8'
max_locks_per_transaction = '96'
maintenance_work_mem = '2GB'
temp_buffers = '256MB'
emptystr_to_null = 'off'

2.2 参数调整方法
数据库的核心配置文件位于数据库家目录下，其中postgresql.conf为原始配置文件，不建议修改，同级目录下还有一个postgresql.auto.conf，这个配置文件中的参数会优先生效，建议修改本配置文件。

 调整方法一：直接修改配置文件
可以直接修改postgresql.auto.conf中的参数值，如果没有的参数值，可以新起一行添加即可，修改后重启数据库生效
 调整方法二：命令行在线修改
位于postgresql.auto.conf中的参数值实际上也是基于alter system set 参数=参数值命令执行后动态生成的，所以也可以在数据库控制台，登录具有sysdba权限的用户执行如下命令即可，注意根据自己的数据库规格调整参数值。执行参数调整命令后，需重启数据库生效。
ALTER SYSTEM SET max_connections = '3000';
ALTER SYSTEM SET work_mem = '256MB';
ALTER SYSTEM SET temp_buffers = '64MB';
ALTER SYSTEM SET shared_buffers = '60GB';
ALTER SYSTEM SET max_wal_size = '5GB';
ALTER SYSTEM SET max_worker_processes = '16';
ALTER SYSTEM SET max_parallel_workers_per_gather = '8';
ALTER SYSTEM SET max_locks_per_transaction = '96';
ALTER SYSTEM SET maintenance_work_mem = '2GB';
ALTER SYSTEM SET temp_buffers = '256MB';
ALTER SYSTEM SET emptystr_to_null = 'off';

2.3 参数解析
#数据库最大连接数：如设置过低会导致应用卡在获取数据库连接，过高容易导致数据库在大并发下不稳定
max_connections = '3000'
#数据库查询缓存：内部sort（排序）操作和Hash（哈希）操作使用，内存不够在大数据量下会出现物理I/O，内存过大，会导致占用数据库连接数*语句中的排序/哈希次数* work_mem的内存占用，可能有OOM风险
work_mem = '256MB'
#临时缓冲区：用于数据库会话访问临时表数据，会话级内存占用，每个会话都可使用到最大temp_buffers
temp_buffers = '64MB'
#共享缓冲区：postgresql对数据操作时都要先将数据从磁盘读取到内存中，然后进行更新，最后再将数据写回磁盘。设置范围一般在实例的25%~40%之间。
shared_buffers = '60GB'
#自动检查点之间WAL日志可以增长的最大值：高负载场景下，生成WAL的速度往往会比其归档快，并且超出标准检查点进程的速度，调大该参数，以使其至少可以保存一小时的日志。但不宜过大，过大的日志会导致数据库崩溃后的恢复耗时更久
max_wal_size = '5GB'
#最大数据库并行数：开启后，将至多允许数据库启动的并行线程，一般推荐不大于数据库CPU核数
max_worker_processes = '16'
#每个会话可使用的最大并行数：在大数据量查询场景，开启后，将由数据库优化器决定启动多少个并行，实际应用场景，数据库会将待查询数据分段、分配给各个并行线程，会额外消耗一定的处理成本，酌情配置即可
max_parallel_workers_per_gather = '8'
#每个事务所能持有的锁的最大数量：事务级的锁数量，在事务中的DDL后也会自动释放锁，故一般默认值64即可，在NC Cloud全模块初始化等重数据场景，可能存在不够的情况，不宜设置过高
max_locks_per_transaction = '96'
#维护工作内存，主要是针对数据库的维护操作或者语句。尽量的将这些操作在内存中进行。主要针对VACUUM，CREATE INDEX，REINDEX等操作。对照NC Cloud部分业务有高频次的临时表创建及索引创建场景，默认值为1G调整至2G可满足绝大多数场景
maintenance_work_mem = '2GB'
#临时缓存区：会话级临时缓冲区，每个会话会分配如下内存支持临时表的处理，一般256MB足以
temp_buffers = '256MB'
#空值与null的转换：在列中存在长度为0的字符串（不是null值）。该功能开启时，将使部分查询语句（bi_smart_dir查询pk_parent为null）执行结果不准确，需要关闭该功能。
emptystr_to_null = 'off'

瀚高数据库驱动解析：

瀚高数据库驱动一般位于 /opt/HighGo-版本号/etc/drivers/JDBC/下，例如

hgdb-6.0.1-jdbc4-SNAPSHOT.jar

hgdb-6.0.1-jdbc41-SNAPSHOT.jar

hgdb-6.0.1-jdbc42-SNAPSHOT.jar

其中 

hgdb-6.0.1-jdbc4-SNAPSHOT.jar  为适配java1.6的

hgdb-6.0.1-jdbc41-SNAPSHOT.jar 为适配java1.7的

hgdb-6.0.1-jdbc42-SNAPSHOT.jar 为适配java1.8的

瀚高数据库支持知识社区: https://support.highgo.com/#/

添加关键字:

#查询当前关键字

show flatten_keyword_list ;

#修改关键字

#如果已有关键字aaa,bbb,要增加关键字name则执行如下

alter system set flatten_keyword_list = 'aaa,bbb,name';  

#对应的配置文件位于  瀚高数据/data/postgresql.auto.conf的flatten_keyword_list

#会话级别取消关键字设置

set session_restore_keyword_list to 'type,system';