背景

数据库安装后参数需要进行维护. 
主要问题是: 
1. 连接池的设置
2. 内存参数的设置
3. 其他安全等的设置

修改方式方法

GaussDB的参数修改主要有两种方式:
第一种是 
gs_guc 的命令进行设置

第二种 修改配置文件
默认配置文件 Gaussdb 进行了改名
从postgres.conf 修改成了 gaussdb.conf

连接数的修改

第一种命令方式:
gs_guc reload -Z coordinator -Z datanode -N all -I all -c  "max_connections= 30720"
gs_guc reload -I all -c "max_connections= 30720"

两种方式均可以. 

第二种 修改配置文件
登录查看文件位置
gsql -d postgres -p 30100 -U root -W Testxxxxxxxx
执行命令
show max_connections ;
查看数值
ps -ef |grep gauss 
可以看到运行效果为: 
/data/cluster/core/app/bin/gaussdb -D data/cluster/data/dn/dn_6001 -M primary

修改配置文件
vim data/cluster/data/dn/dn_6001/gaussdb.conf 
修改为:
local_bind_address = '10.xx.xx.xx'
port = '30100'                          # (change requires restart)
max_connections = 30720                 # (change requires restart)

重启服务

注意命令为:  gs_om -t restart
效果为: 
gs_om -t restart
Stopping cluster.
=========================================
the Stop cmd is source /home/omm/gauss_env_file ; cm_ctl stop.
Successfully stopped cluster.
=========================================
End stop cluster.
Starting cluster.
======================================================================
Successfully started primary instance. Wait for standby instance.
======================================================================
.
Successfully started cluster.
======================================================================
cluster_state      : Normal
redistributing     : No
node_count         : 1
Coordinator State
    normal         : 0
    abnormal       : 0
GTM State
    primary        : 0
    standby        : 0
    abnormal       : 0
    down           : 0
Datanode State
    primary        : 1
    standby        : 0
    secondary      : 0
    building       : 0
    abnormal       : 0
    down           : 0

Successfully started cluster.

内存参数的学习

最重要的参数
max_process_memory
逻辑内存管理参数为max_process_memory，主要功能是控制单个DN上可用内存的最大峰值。
执行作业最终可用的内存为：

max_process_memory – shared memory ( 包括shared_buffers ) – cstore_buffers

所以影响执行作业可用内存参数的主要两个参数为shared_buffers及cstore_buffers。
安装过程中默认大小是 总内存的三分之一

shared_buffers = '1GB'
数据库的 内存缓存区域. 理论上应该较大一些. 

work_mem 
作业内存, 每个SQL作业可以使用的内存大小. 
不建议太小, 也不能太大, 太大会导致可用内存不足. 太小会导致单条SQL需要IO降低性能. 

temp_buffers
称之为临时缓冲区，用于数据库会话访问临时表数据，系统默认值为8M。
可以在单独的session中对该参数进行设置，
尤其是需要访问比较大的临时表时，将会有显著的性能提升。

effective_cache_size
· 该effective_cache_size提供了可以用于磁盘缓存存储器的估计。
· 它只是一个指导原则，而不是确切分配的内存或缓存大小。
· 它不分配实际内存，而是告诉优化器内核中可用的缓存量。
· 如果将此值设置得太低，查询计划程序可以决定不使用某些索引，即使它们有用。
· 因此，设置较大的值总是有益的。
· 建议使用默认值。

附: gs_guc的帮助

Checking GUC parameters:
    gs_guc check [-Z NODE-TYPE] [-N NODE-NAME] {-I INSTANCE-NAME | -D DATADIR} {-c "parameter", -c "parameter", ...}
    gs_guc check [-Z NODE-TYPE] [-N NODE-NAME] {-I INSTANCE-NAME | -D DATADIR} {-c parameter, -c parameter, ...}

Configuring GUC parameters:
  Usage:
        gs_guc {set | reload} [-Z NODE-TYPE] [-N NODE-NAME] {-I INSTANCE-NAME | -D DATADIR} [--ignore-node=NODES] {-c "parameter = value" -c "parameter = value" ...}
        gs_guc {set | reload} [-Z NODE-TYPE] [-N NODE-NAME] {-I INSTANCE-NAME | -D DATADIR} [--ignore-node=NODES] {-c " parameter = value " -c " parameter = value " ...}
        gs_guc {set | reload} [-Z NODE-TYPE] [-N NODE-NAME] {-I INSTANCE-NAME | -D DATADIR} [--ignore-node=NODES] {-c "parameter = 'value'" -c "parameter = 'value'" ...}
        gs_guc {set | reload} [-Z NODE-TYPE] [-N NODE-NAME] {-I INSTANCE-NAME | -D DATADIR} [--ignore-node=NODES] {-c " parameter = 'value' " -c " parameter = 'value' " ...}
        gs_guc {set | reload} [-Z NODE-TYPE] [-N NODE-NAME] {-I INSTANCE-NAME | -D DATADIR} [--ignore-node=NODES] {-c "parameter" -c "parameter" ...}
    e.g. gs_guc set -Z datanode -N all -I all -c "program = '\"Hello\", World\!'".
    e.g. gs_guc reload -Z datanode -N all -I all -c "program = '\"Hello\", World\!'".

  If parameter value set or reload to DEFAULT OR COMMENT configuration parameter, use the form: -c "parameter"

  You can choose Usage as you like, and perhaps the first one will be more suitable for you!

Configuring Authentication Policy:
    gs_guc {set | reload} [-Z NODE-TYPE] [-N NODE-NAME] {-I INSTANCE-NAME | -D DATADIR} [--ignore-node=NODES] -h "HOSTTYPE DATABASE USERNAME IPADDR IPMASK AUTHMEHOD authentication-options"
    gs_guc {set | reload} [-Z NODE-TYPE] [-N NODE-NAME] {-I INSTANCE-NAME | -D DATADIR} [--ignore-node=NODES] -h "HOSTTYPE DATABASE USERNAME IPADDR-WITH-IPMASK AUTHMEHOD authentication-options"
    gs_guc {set | reload} [-Z NODE-TYPE] [-N NODE-NAME] {-I INSTANCE-NAME | -D DATADIR} [--ignore-node=NODES] -h "HOSTTYPE DATABASE USERNAME HOSTNAME AUTHMEHOD authentication-options"
  If authentication policy need to set/reload DEFAULT OR COMMENT then provide without authentication menthod, use the form:
    gs_guc {set | reload} [-Z NODE-TYPE] [-N NODE-NAME] {-I INSTANCE-NAME | -D DATADIR} [--ignore-node=NODES] -h "HOSTTYPE DATABASE USERNAME IPADDR IPMASK"
    gs_guc {set | reload} [-Z NODE-TYPE] [-N NODE-NAME] {-I INSTANCE-NAME | -D DATADIR} [--ignore-node=NODES] -h "HOSTTYPE DATABASE USERNAME IPADDR-WITH-IPMASK "
    gs_guc {set | reload} [-Z NODE-TYPE] [-N NODE-NAME] {-I INSTANCE-NAME | -D DATADIR} [--ignore-node=NODES] -h "HOSTTYPE DATABASE USERNAME HOSTNAME"

Common options:
  -N                      nodename in which this command need to be executed
  -I                      instance name
  -D, --pgdata=DATADIR    location of the database storage area
  -c    parameter=value   the parameter to set
  -c    parameter         the parameter value to DEFAULT (i.e comments in configuration file)
  --ignore-node=NODES     Nodes which need to ignore. It only can be used with set/reload operation,and CN/DN g_node_type
  -h    host-auth-policy  to set authentication policy in HBA conf file
  -?, --help              show this help, then exit
  -V, --version           output version information, then exit

Options for set with -c parameter:
  -Z NODE-TYPE   can be "datanode", "cmserver" or "cmagent" or "gnsserver". NODE-TYPE is used to identify configuration file (with -c parameter) in data directory
  "datanode"                                      -- gaussdb.conf
  "cmserver"                                      -- cm_server.conf
  "cmagent"                                       -- cm_agent.conf
  "gnsserver"                                     -- gns_server.conf

Options for set and reload with -h host-auth-policy:
  -Z NODE-TYPE   can be "datanode" or "gnsserver"

Options for encrypt:
  -M, --keymode=MODE     the cipher files will be applies in server, client or source,default value is server mode
  -K PASSWORD            the plain password you want to encrypt, which length should between 8~15 and at least 3 different types of characters
  -U, --keyuser=USER     if appointed, the cipher files will name with the user name
  -R RANDFILEDIR         set the dir that put the rand file
  -C CIPHERFILEDIR       set the dir that put the cipher file


Options for generate:
  -o PREFIX               the cipher files prefix. default value is obsserver
  -S CIPHERKEY            the plain password you want to encrypt, which length should between 8~16 and at least 3 different types of characters