推荐参数设置及新建测试库

#!/bin/bash

source ~/.bashrc

##连接访问相关参数
##listen_addresses : 数据库服务器监听本地的IP地址，* 监听所有IP，重启生效
##remote_read_mode : 开启远程读功能，non_authentication指不进行证书认证，重启生效
##password_encryption_type : 数据库密码加密类型，1指采用sha256和md5两种方式分别对密码加密
##password_reuse_time : 检查密码重用的天数，0表示不检查
##password_lock_time : 账号的锁定时间，0表示不锁定
##password_effect_time : 密码有效时间，0表示长期有效
##session_timeout : 不进行任何操作的会话断开时间，0表示永不断开
function SecureAccess(){
    gs_guc set -I all -N all -c "listen_addresses = '*'"
    gs_guc set -I all -N all -c "remote_read_mode=non_authentication"
    gs_guc set -I all -N all -c "password_encryption_type=1"
    gs_guc set -I all -N all -c "password_reuse_time=0"
    gs_guc set -I all -N all -c "password_lock_time=0"
    gs_guc set -I all -N all -c "password_effect_time=0"
    gs_guc set -I all -N all -c "session_timeout=0"
}


##wal相关参数
##wal_level : 写入wal日志信息的详细程度，logical级别的wal信息支持逻辑解析，重启生效
##full_page_writes : 全量检查点情况下，第一次checkpoint后会将每个页面内容全部记录到wal日志，建议设置为off以及使用增量检查点及双写功能
##wal_log_hints : 同full_page_writes，包含提示位的非关键信息，重启生效
##xloginsert_locks : 并发写预写式日志锁的个数，提高写预写式日志的效率，重启生效
##advance_xlog_file_num : 提前初始化xlog文件的数目，在高并发情况下可减少创建xlog文件的性能影响，重启生效
function WAL(){
    gs_guc set -I all -N all -c "wal_level=logical"
    gs_guc set -I all -N all -c "full_page_writes=off"
    gs_guc set -I all -N all -c "wal_log_hints=off"
    gs_guc set -I all -N all -c "xloginsert_locks=48"
    gs_guc set -I all -N all -c "advance_xlog_file_num=10"
}


##复制相关参数
##wal_keep_segments : wal文件保留数量，每个文件16MB，增大此值，在重做备库过程中可有效避免wal被移除的错误
##max_wal_senders : 上游节点sender线程的总数量，包括流复制和逻辑复制，重启生效
##most_available_sync : 最大可用模式，当同步备库故障后自动降级为异步，不阻塞主库数据变更，重启生效
##catchup2normal_wait_time : 异步备变同步备过程中，阻塞主库数据变更的时间，0表示不阻塞，重启生效
##enable_slot_log : 将主节点的复制槽信息同步到集群内其他节点，避免集群内各节点的slot不一致
##max_replication_slots : 最大复制槽的数量，包含物理复制槽和逻辑复制槽，重启生效
##wal_receiver_timeout : wal receiver线程超时时间，增加此值可减少由于网络抖动导致的复制关系重建次数
##sync_config_strategy : 该参数用以设置主备节点间数据库参数配置信息的同步方案，由于通常环境中备库服务器配置与主库并非1:1，因此如果同步参数配置，可能导致某些参数对于备库硬件资源而言过大，设置为none_node表示不同步，重启生效
function replicationAndSlots(){
    gs_guc set -I all -N all -c "wal_keep_segments=1024"
    gs_guc set -I all -N all -c "max_wal_senders=16"
    gs_guc set -I all -N all -c "most_available_sync=on"
    gs_guc set -I all -N all -c "catchup2normal_wait_time=0"
    gs_guc set -I all -N all -c "enable_slot_log=on"
    gs_guc set -I all -N all -c "max_replication_slots=32"
    gs_guc set -I all -N all -c "wal_receiver_timeout=60s"
    gs_guc set -I all -N all -c "sync_config_strategy=none_node"
}


##日志相关参数
##log_duration    : 已完成SQL语句的执行时间是否要记录到日志中，需要结合log_statement使用
##log_line_prefix : 每条日志的前缀信息，时间戳、用户名、数据库名、客户端ip和端口、线程id、会话id
##log_checkpoints : 记录checkpoint信息到日志里
function dbLog(){
    gs_guc set -I all -N all -c "log_duration=off"
    gs_guc set -I all -N all -c "log_line_prefix='%m %u %d %r %p %S'"
    gs_guc set -I all -N all -c "log_checkpoints=on"
}

##vacuum相关参数
##vacuum_cost_limit :  vacuum线程开销限制，达到此值后开始休眠
##autovacuum_max_workers : autovacuum的最大并发数，每个worker都要消耗maintenance_work_mem大小的内存
##autovacuum_naptime : autovacuum的休眠时间，减少此值增加autovacuum的频率，统计信息更准确，但磁盘会更加繁忙
##autovacuum_vacuum_cost_delay : autovacuum的开销延迟，减少此值增加了autovacuum的频率
##autovacuum_io_limits : autovacuum线程每秒触发的io上限

##scale_factor 与 threshold 结合使用，触发条件一般是：表数据量 * scale_factor + threshold
##autovacuum_vacuum_scale_factor : 触发vacuum的缩放系数
##autovacuum_analyze_scale_factor : 触发analyze的缩放系数
##autovacuum_vacuum_threshold : 触发vacuum的阈值
##autovacuum_analyze_threshold : 触发analyze的阈值
function VACUUM(){
    gs_guc set -I all -N all -c "vacuum_cost_limit=1000"
    gs_guc set -I all -N all -c "autovacuum_max_workers=10"
    gs_guc set -I all -N all -c "autovacuum_naptime=20s"
    gs_guc set -I all -N all -c "autovacuum_vacuum_cost_delay=10"
    gs_guc set -I all -N all -c "autovacuum_vacuum_scale_factor=0.05"
    gs_guc set -I all -N all -c "autovacuum_analyze_scale_factor=0.02"
    gs_guc set -I all -N all -c "autovacuum_vacuum_threshold=200"
    gs_guc set -I all -N all -c "autovacuum_analyze_threshold=200"
    gs_guc set -I all -N all -c "autovacuum_io_limits=104857600"
}

##性能统计相关参数
##instr_unique_sql_count : dbe_perf.statement表中的记录数
##enable_wdr_snapshot : 是否开启wdr快照功能，默认每小时执行一次，保留8天时间，类似oracle的AWR
##log_min_duration_statement : 慢sql阈值，执行时间超过这个值的sql会记录到statement_history表中
##track_activity_query_size : 当前执行query的文本字节数，重启生效
##enable_instr_rt_percentile : 是否开启计算系统中80%和95%的SQL响应时间的功能，off表示关闭sql响应时间信息计算功能
function perfStats(){
    gs_guc set -I all -N all -c "instr_unique_sql_count=200000"
    gs_guc set -I all -N all -c "enable_wdr_snapshot=on"
    gs_guc set -I all -N all -c "log_min_duration_statement=200"
    gs_guc set -I all -N all -c "track_activity_query_size=2048"
    gs_guc set -I all -N all -c "enable_instr_rt_percentile=off"
}


##其他参数
##cstore_buffers : 列存共享缓存区大小，在不使用列存存储的场景可将此参数调到最低来节省内存开销，重启生效
##local_syscache_threshold : 每个会话的最大缓存大小，设置过高会引发动态内存不足，即数据库级别的OOM
##standby_shared_buffers_fraction : 备节点使用shared_buffer的比例
##checkpoint_segments : 触发全量checkpoint的wal数量，即使开启增量检查点，此参数依然有效
##checkpoint_completion_target : checkpoint间隔内完成全量checkpoint所需的时间
##max_files_per_process : 进程允许打开的最大文件数量
##behavior_compat_options : 数据库兼容性配置选项，参数值display_leading_zero会展示小数点前面的0
##lc_messages : 信息语言显示格式
##lc_monetary : 货币的显示格式
##lc_numeric : 数值的显示格式
##lc_time : 时间和时区的显示格式
##enable_opfusion : 控制是否对简单查询进行查询优化
function otherKeyParams(){
    gs_guc set -I all -N all -c "cstore_buffers=16MB"
    gs_guc set -I all -N all -c "local_syscache_threshold=32MB"
    gs_guc set -I all -N all -c "standby_shared_buffers_fraction=1"
    gs_guc set -I all -N all -c "checkpoint_segments=1024"
    gs_guc set -I all -N all -c "checkpoint_completion_target=0.8"
    gs_guc set -I all -N all -c "max_files_per_process=100000"
    gs_guc set -I all -N all -c "behavior_compat_options='display_leading_zero'"
    gs_guc set -I all -N all -c "lc_messages='en_US.UTF-8'"
    gs_guc set -I all -N all -c "lc_monetary='en_US.UTF-8'"
    gs_guc set -I all -N all -c "lc_numeric='en_US.UTF-8'"
    gs_guc set -I all -N all -c "lc_time='en_US.UTF-8'"
    gs_guc set -I all -N all -c "enable_opfusion=off"
}


##客户端白名单
##详细信息请参考pg_hba.conf文件
function setHba(){
    gs_guc set -I all -N all -h "host     all      all  0.0.0.0/0    md5"
}


##max_process_memory : 整个实例允许使用的最大内存，由于要预留一部分内存给操作系统，所以这个参数需要根据RAM大小动态调整，调整规则：RAM * n% / 数据库实例数量，重启生效
##shared_buffers : 行存共享缓存区大小，建议设置为max_process_memory的40%，重启生效
##max_connections : 数据库实例允许的最大连接数，设置过高会引起动态内存不足的错误，需结合业务使用设定，重启生效
##work_mem : 工作内存一般指排序、hash或join占用的内存，对于复杂query，增大此内存可减少临时文件的使用
##maintenance_work_mem : 维护内存一般指vacuum或create index使用的内存，增加此参数可提高效率
##wal_buffers : wal缓存大小，调整此参数对数据库性能有一定影响
##max_prepared_transactions : 预编译事务的数量，建议与max_connections保持一致

## 判断RAM的大小，将数据库内存大小分4个阶梯，分别是0~4GB、4~8GB、8~64GB以及64-nGB.
## 当RAM小于等于4G，建议使用数据库默认参数
## 其他三个阶梯有相应的建议参数值
memory=`free -g|awk '{print $2}' |sed -n 2p`
if [[ $memory -le 4 ]]
then
    echo "Defined values of specify parameters or use the default parameters"
else
    if [[ $memory -gt 4 ]] && [[ $memory -le 8 ]] 
    then
        gs_guc set -I all -N all -c "max_process_memory=$((memory*6/10))GB"
        gs_guc set -I all -N all -c "shared_buffers=$((memory*2/10))GB"
        gs_guc set -I all -N all -c "max_connections=500"
        gs_guc set -I all -N all -c "work_mem=16MB"
        gs_guc set -I all -N all -c "maintenance_work_mem=512MB"
        gs_guc set -I all -N all -c "wal_buffers=128MB"
        gs_guc set -I all -N all -c "max_prepared_transactions=500"
    elif [[ $memory -gt 8 ]] && [[ $memory -le 64 ]] 
    then
        gs_guc set -I all -N all -c "max_process_memory=$((memory*7/10))GB"
        gs_guc set -I all -N all -c "shared_buffers=$((memory*2/10))GB"
        gs_guc set -I all -N all -c "max_connections=1000"
        gs_guc set -I all -N all -c "work_mem=32MB"
        gs_guc set -I all -N all -c "maintenance_work_mem=1GB"
        gs_guc set -I all -N all -c "wal_buffers=512MB"
        gs_guc set -I all -N all -c "max_prepared_transactions=1000"
    elif [[ $memory -gt 64 ]]
    then
        gs_guc set -I all -N all -c "max_process_memory=$((memory*8/10))GB"
        gs_guc set -I all -N all -c "shared_buffers=$((memory*3/10))GB"
        gs_guc set -I all -N all -c "max_connections=3000"
        gs_guc set -I all -N all -c "work_mem=64MB"
        gs_guc set -I all -N all -c "maintenance_work_mem=2GB"
        gs_guc set -I all -N all -c "wal_buffers=1GB"
        gs_guc set -I all -N all -c "max_prepared_transactions=3000"
    else
        echo "There may be a problem with the script, please contact us for support."
    fi
    WAL
    replicationAndSlots
    dbLog
    VACUUM
    perfStats
    otherKeyParams
fi

SecureAccess
setHba

gs_om -t stop && gs_om -t start

CREATE DATABASE tpcc_db;
\c tpcc_db
CREATE USER tpcc_usr WITH PASSWORD "tpcc@1234";
alter user tpcc_usr sysadmin;
GRANT ALL ON schema public TO tpcc_usr;