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

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

#!/bin/bash
source ~/.bashrc
memory=`free|awk '{print $2}' |sed -n 2p`
max_process_memory=$((memory*8/10/1024/1024))
shared_buffers=$((memory*5/10/1024/1024))
##修改数据库参数
gs_guc set -I all -Nall  -c "max_process_memory=${max_process_memory}GB"
gs_guc set -I all -Nall  -c "shared_buffers=${shared_buffers}GB"
gs_guc set -I all -N all -h "host     all      all  0.0.0.0/0    md5"
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 "cstore_buffers=16MB"
gs_guc set -I all -N all -c "wal_buffers=1GB"
gs_guc set -I all -N all -c "max_connections=3000"
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 "synchronous_commit=on"
gs_guc set -I all -N all -c "wal_keep_segments=1024"
gs_guc set -I all -N all -c "xloginsert_locks=48"
gs_guc set -I all -N all -c "advance_xlog_file_num=10"
gs_guc set -I all -N all -c "archive_mode=on"
gs_guc set -I all -N all -c "archive_dest='/ogarchive'"
gs_guc set -I all -N all -c "logging_collector=on"
gs_guc set -I all -N all -c "log_duration=on"
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"
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 "max_wal_senders=16"
gs_guc set -I all -N all -c "recovery_max_workers=4"
gs_guc set -I all -N all -c "most_available_sync=on"
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 "password_encryption_type=1"
gs_guc set -I all -N all -c "session_timeout=0"
gs_guc set -I all -N all -c "enable_alarm=off"
gs_guc set -I all -N all -c "enable_codegen=off"
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_wdr_snapshot=on"
gs_guc set -I all -N all -c "sync_config_strategy=none_node"
gs_guc set -I all -N all -c "audit_enabled=off"
gs_guc set -I all -N all -c "wal_receiver_timeout=60s"
gs_guc set -I all -N all -c "plog_merge_age=0"
gs_guc set -I all -N all -c "autovacuum_io_limits=104857600"
gs_guc set -I all -N all -c "update_lockwait_timeout=1min"
gs_guc set -I all -N all -c "lockwait_timeout=1min"
gs_guc set -I all -N all -c "max_prepared_transactions=3000"
gs_guc set -I all -N all -c "instr_unique_sql_count=20000"
gs_guc set -I all -N all -c "enable_save_datachanged_timestamp=off"
gs_guc set -I all -N all -c "track_sql_count=off"
gs_guc set -I all -N all -c "enable_instr_rt_percentile=off"
gs_guc set -I all -N all -c "enable_instance_metric_persistent=off"
gs_guc set -I all -N all -c "enable_logical_io_statistics=off"
gs_guc set -I all -N all -c "enable_user_metric_persistent=off"
gs_guc set -I all -N all -c "enable_mergejoin=on"
gs_guc set -I all -N all -c "enable_nestloop=on"
gs_guc set -I all -N all -c "enable_pbe_optimization=off"
gs_guc set -I all -N all -c "max_replication_slots=32"
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 "max_files_per_process=100000"
gs_guc set -I all -N all -c "pagewriter_sleep=200"
gs_guc set -I all -N all -c "max_size_for_xlog_prune=104857600"
gs_guc set -I all -N all -c "enable_resource_track=on"
gs_guc set -I all -N all -c "enable_asp=off"
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 "behavior_compat_options='display_leading_zero'"

重启数据库，使参数生效

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;