Recommended Parameter Settings

#!/bin/bash

source ~/.bashrc

##Connection and access related parameters
##listen_addresses : IP address for a database server to listen the local server. * indicates that all IP addresses are listened. This setting takes effect after the server is restarted. 
##remote_read_mode : The remote read function is enabled. non_authentication indicates that certificate authentication is not needed. This setting takes effect after the server is restarted.
##password_encryption_type : Database password encryption type. 1 indicates that passwords are encrypted using SHA256 and MD5 respectively.
##password_reuse_time : indicates the number of days for the password reused. 0 indicates that no check is performed. 
##password_lock_time : indicates the account lock time. 0 indicates that the account is not locked. 
##password_effect_time : indicates the password validity time. 0 indicates that the password is valid permanently. 
##session_timeout : indicates the session timeout when no any operation is performed. 0 indicates that the session is not disconnected permanently. 
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-related parameters
##wal_level : indicates the level of details for writting into logs. Logical WAL logs support logical parsing and take effect after restart.
##full_page_writes : In full checkpoint mode, the content of each page is recorded to WAL logs after the first checkpoint. You are advised to set the value to off and enable incremental checkpoint and dual-write.
##wal_log_hints : The meaning is the same as that of full_page_writes, which contains non-critical information about the prompt bit. The information takes effect after restart
##xloginsert_locks : indicates the number of concurrent prewrite log locks, which improves the prewrite log efficiency and takes effect after restart.
##advance_xlog_file_num : initializes the number of Xlog files in advance to reduce the performance impact of creating Xlog files in the case of high concurrency, which takes effect after restart.
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"
}


##Replication-related parameters
##wal_keep_segments : indicates the number of reserved wal files. Each file is 16 MB. If the value is increased, the error that wal is removed can be avoided during standby database rebuilt.
##max_wal_senders : indicates the total number of sender threads of the upstream node, including stream replication and logical replication. This parameter takes effect after restart.
##most_available_sync : indicates the maximum available mode. If the synchronous standby database fails, the standby database automatically changes to asynchronous mode. The mode does not block data changes in the primary database and takes effect after restart.
##catchup2normal_wait_time : indicates the time for preventing the primary database data change when asynchronous standby changes to synchronous standby. 0 indicates that data changes in the primary database are not blocked and take effect after restart.
##enable_slot_log : synchronizes the replication slot information of the primary node to other nodes in the cluster to avoid slot inconsistency among nodes in the cluster.
##max_replication_slots : indicates the maximum number of replication slots, including physical replication slots and logical replication slots. The parameter takes effect after restart.
##wal_receiver_timeout : indicates the Wal receiver thread timeout duration. Increasing this value reduces the number of replication relationship reestablishment times due to network jitter.
##sync_config_strategy : This parameter is used to set the synchronization scheme of database configuration information between the primary and standby nodes. In common environments, the configuration of the standby database server is not 1:1 with that of the primary database. Therefore, some parameters may be too large for the hardware resources of the standby database during synchronous parameter configuration. none_node indicates non-synchronization. This parameter takes effect after restart.
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-related parameters
##log_duration    : determines whether to record the execution time of completed SQL statements in logs. This parameter is together used with log_statement. 
##log_line_prefix : idindicates the prefix information, timestamp, user name, database name, client IP address and port, thread ID, and session ID of each log.
##log_checkpoints : records checkpoint information to logs.
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-related parameters
##vacuum_cost_limit :  indicates the vacuum thread overhead limit. It will hibernate when this value is reached.
##autovacuum_max_workers : indicates the maximum number of concurrent autovacuums. Each worker consumes maintenance_work_mem of memory.
##autovacuum_naptime : indicates the autovacuum sleep duration. Reducing this value to increase the frequency of autovacuum makes the statistics more accurate, but the disk is busier.
##autovacuum_vacuum_cost_delay : indicates the overhead delay of autovacuum. Reducing this value increases the frequency of autovacuum.
##autovacuum_io_limits : indicates the maximum number of IOs triggered by the autovacuum thread per second.

##scale_factor is used with threshold. The trigger condition is data volumne of a table * scale_factor + threshold
##autovacuum_vacuum_scale_factor : triggers the vacuum scaling factor.
##autovacuum_analyze_scale_factor : triggers the analyze scaling factor.
##autovacuum_vacuum_threshold : triggers the vacuum threshold.
##autovacuum_analyze_threshold : triggers the analyze threshold.
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"
}

##Performance statistics related parameters
##instr_unique_sql_count : indicates the number of records in the dbe_perf.statement table.
##enable_wdr_snapshot : indicates Whether to enable the WDR snapshot function. By default, the WDR snapshot function is enabled every one hour and reserved for eight days, similar to the AWR function of Oracle.
##log_min_duration_statement : indicates the slow SQL threshold. SQL that takes longer than this value is recorded in the statement_history table.
##track_activity_query_size : indicates the number of text bytes in the current query execution. This parameter takes effect after restart.
##enable_instr_rt_percentile : Specifies whether to enable the function of calculating the response time of 80% and 95% SQL statements in the system. Off indicates that the function of calculating the response time of 80% and 95% SQL statements is disabled.
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"
}


##Other parameters
##cstore_buffers : indicates the size of the column-store shared cache area. If no column storage is used, set this parameter to the minimum to save memory. The value takes effect after a restart.
##local_syscache_threshold : indicates the maximum cache size for each session. If the maximum cache size is set too high, dynamic memory will be out of use (the database OOM level)
##standby_shared_buffers_fraction : indicates the proportion of shared buffers used by the standby node
##checkpoint_segments : indicates the number of wals that trigger full checkpoint. This parameter is valid even if incremental checkpoint is enabled.
##checkpoint_completion_target : indicates the time required to complete a full checkpoint within the checkpoint interval
##max_files_per_process : indicates the maximum number of files that a process can open.
##behavior_compat_options : indicates the database compatibility configuration options. The value of valueDisplay_leading_zero shows the 0 before the decimal point.
##lc_messages : indicates the information language display format.
##lc_monetary : indicates the display format of currency.
##lc_numeric : indicates the display format of values.
##lc_time : indicates the display format of the time and time zone. 
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'"
}


##Client whitelist
##For details, see the pg_hba.conf file.
function setHba(){
    gs_guc set -I all -N all -h "host     all      all  0.0.0.0/0    md5"
}


##max_process_memory : indicates the maximum memory allowed to be used for an instance. This parameter needs to be dynamically adjusted based on the RAM size because a part of memory needs to be reserved for the operating system. The adjustment rule is as follows: RAM x N % / Number of database instances. The adjustment takes effect after restart.
##shared_buffers : indicates the size of row-store shared cache area. The recommended value is 40% of max_PROCESS_memory, which takes effect after restart.
##max_connections : indicates the maximum number of connections allowed by the database instance. If the value is too high, the dynamic memory is insufficient. You need to set this parameter based on service usage and restart it to take effect.
##work_mem : indicates the memory used for sorting, hash, or join. For complex queries, increasing this memory reduces the use of temporary files.
##maintenance_work_mem : indicates the memory used by VACUUM or Create Index. Increasing this parameter improves efficiency.
##wal_buffers : indicates the wal cache size. Adjusting this parameter has some impact on database performance.
##max_prepared_transactions : indicates the number of precompiled transactions. It is recommended to be consistent with the value of max_connections.

## To determine the size of RAM, divide the database memory size into 4 levels, which are 0 to 4 GB, 4 to 8 GB, 8 to 64 GB, and memory size greater than 64 GB.
## If the RAM is less than or equal to 4 GB, you are advised to use the default database parameters.
## The other three levels have suggested parameter values.
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;