文档中心MogDBMogDB StackUqbar
v3.0

文档:v3.0

支持的版本:

其他版本:

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

在安装完数据库之后,推荐执行以下shell脚本来进行初始化参数设置。

注意:

执行脚本前,请确保脚本中提到的所有相关文件目录已存在。如没有,需要执行mkdir -p <文件目录>命令提前创建。

例如:执行gs_guc set -I all -N all -c "archive_dest='/ogarchive'"前,可先执行ls -l查看是否存在该目录,若不存在,需执行mkdir -p ogarchive创建此目录。

#!/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

在数据库init完成之后,如果需要进行测试,推荐新建数据库和新用户来进行测试。比如如果需要创建一个用于TPCC测试的数据库和用户,则执行以下命令。

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;
Copyright © 2011-2024 www.enmotech.com All rights reserved.