Database parameter setting

Cluster version

Parameter reference for adbmgr

• database parameters

 
cat  >> ${mgrdata}/postgresql.conf << EOF 
port = 18610
listen_addresses = '*'
log_directory = 'pg_log'
log_destination ='csvlog'
logging_collector = on
log_min_messages = error
max_wal_senders = 3
hot_standby = on
wal_level = replica
EOF
 

• HBA parameters

cat  >> ${mgrdata}/pg_hba.conf << EOF 
host    replication     all        10.0.0.0/8                 trust
host    all             all          10.0.0.0/8               trust
EOF

Where 10.0.0.0/8 needs to be modified according to the actual situation.

Parameter reference for other nodes

Log in to adbmgr to execute. According to the prompt of command execution, the cluster needs to be restarted.

--coord:
--Modify according to actual situation/Users are requested to make appropriate adjustments based on host environment information
SET COORDINATOR ALL (shared_buffers = '48GB' );
SET COORDINATOR ALL (effective_cache_size = '96GB' );
SET COORDINATOR ALL (waitglobaltransaction = '300s');
SET COORDINATOR ALL (maintenance_work_mem = '1024MB');
SET COORDINATOR ALL (work_mem = '8MB' );
SET COORDINATOR ALL (max_connections = 2000 );
SET COORDINATOR ALL (max_prepared_transactions = 2000 );
--Set to cpu cores
SET COORDINATOR ALL (max_parallel_workers = 12 );
--antdb needs to increase the mwp parameter settings to use reduce.
SET COORDINATOR ALL (max_worker_processes = 2000 );
SET COORDINATOR ALL (max_parallel_workers_per_gather = 4 );
SET COORDINATOR ALL (log_destination = 'csvlog');
SET COORDINATOR ALL (log_truncate_on_rotation = on);
SET COORDINATOR ALL (log_rotation_age = '7d');
SET COORDINATOR ALL (log_rotation_size = '100MB');
SET COORDINATOR ALL (log_min_messages = error );
--Setting the maximum time consuming logging statement ms for execution
SET COORDINATOR ALL (log_min_duration_statement = 50000 );
SET COORDINATOR ALL (log_duration = off);
SET COORDINATOR ALL (log_statement = 'ddl' );
SET COORDINATOR ALL (log_lock_waits = on );
SET COORDINATOR ALL (unix_socket_permissions =0700);
SET COORDINATOR ALL (listen_addresses = '*' );
SET COORDINATOR ALL (superuser_reserved_connections = 13);
SET COORDINATOR ALL (tcp_keepalives_idle = 180);
SET COORDINATOR ALL (tcp_keepalives_interval = 10 );
SET COORDINATOR ALL (tcp_keepalives_count = 3 );
--Enable table index statistics collection
SET COORDINATOR ALL (track_counts = on);
--Set pg_stat_activity.query length
SET COORDINATOR ALL (track_activity_query_size = 2048 );
--Default 64, when the query has a large number of sub-tables when the partition table can be adjusted larger, the slave library is set to be greater than or equal to the master library
SET COORDINATOR ALL (max_locks_per_transaction = 128);
--Typically used for pg parent-child table query condition optimization using constraints
SET COORDINATOR ALL (constraint_exclusion = on);
SET COORDINATOR ALL (wal_level='replica');
SET COORDINATOR ALL (max_wal_senders = 3);
SET COORDINATOR ALL (hot_standby = off);
SET COORDINATOR ALL (autovacuum = on );
SET COORDINATOR ALL (autovacuum_max_workers = 5 );
SET COORDINATOR ALL (autovacuum_naptime = '60min');
SET COORDINATOR ALL (autovacuum_vacuum_threshold = 500);
SET COORDINATOR ALL (autovacuum_analyze_threshold = 500 );
SET COORDINATOR ALL (autovacuum_vacuum_scale_factor = 0.5 );
SET COORDINATOR ALL (autovacuum_vacuum_cost_limit = -1);
SET COORDINATOR ALL (autovacuum_vacuum_cost_delay = '30ms');
SET COORDINATOR ALL (statement_timeout = 86400000 );
SET COORDINATOR ALL (lock_timeout = '30s');
SET COORDINATOR ALL (idle_in_transaction_session_timeout = '3000s');
SET COORDINATOR ALL (fsync = on);
--Set master-slave data synchronization consistency (can be turned off for performance testing)
SET COORDINATOR ALL (synchronous_commit = on );
--open_datasync direct io, affected by fsync parameter
SET COORDINATOR ALL (wal_sync_method = open_datasync);
SET COORDINATOR all (wal_log_hints = on);
--For failure recovery it is recommended to turn on
SET COORDINATOR ALL (full_page_writes = on );
SET COORDINATOR ALL (commit_delay = 10);
SET COORDINATOR ALL (commit_siblings = 10 );
SET COORDINATOR ALL (checkpoint_timeout = '15min');
SET COORDINATOR ALL (checkpoint_completion_target=0.9 );
SET COORDINATOR ALL (max_wal_size = 10240);
SET COORDINATOR ALL (archive_mode = off);
SET COORDINATOR ALL (archive_command = '/bin/date');
--Set the bgwriter write dirty page period default 200ms, it is recommended to set a multiple of 10
SET COORDINATOR ALL (bgwriter_delay = '10ms');
--Set bgwriter to write the number of dirty pages default 100, write-intensive systems can be adjusted to a larger, consider the IO write capacity can not be set too large
SET COORDINATOR ALL (bgwriter_lru_maxpages = 1000 );
--Sets the maximum percentage of dirty pages written per round default 1, writebgwriter_lru_maxpages*bgwriter_lru_multiplier
SET COORDINATOR ALL (bgwriter_lru_multiplier = 10.0 );
SET COORDINATOR ALL (pool_time_out = 300);
SET COORDINATOR ALL (enable_pushdown_art = on );
--datanode:
--Modify according to actual situation/The user adjusts the host environment information as appropriate
SET DATANODE ALL (shared_buffers = '48GB' );
SET DATANODE ALL (waitglobaltransaction = '300s');
SET DATANODE ALL (maintenance_work_mem = '1024MB');
SET DATANODE ALL (work_mem = '8MB' );
--Maximum number of connections to a dn node is at least a multiple of the cn node for the cn configuration parameter
SET DATANODE ALL (max_connections = 4000 );
SET DATANODE ALL (max_prepared_transactions = 4000 );
SET DATANODE ALL (wal_keep_size = 102400 );
--Suggested settings are consistent with shared_buffers
SET DATANODE ALL (effective_cache_size = '96GB' );
SET DATANODE ALL (max_parallel_workers = 12 );
--The reduce feature requires that the mwp parameter be set to no less than max_connections.
SET DATANODE ALL (max_worker_processes = 2000 );
SET DATANODE ALL (max_parallel_workers_per_gather = 4 );
SET DATANODE ALL (log_destination = 'csvlog');
SET DATANODE ALL (log_truncate_on_rotation = on);
SET DATANODE ALL (log_rotation_age = '7d' );
SET DATANODE ALL (log_rotation_size = '100MB' );
SET DATANODE ALL (log_min_messages = error);
SET DATANODE ALL (log_min_error_statement = error );
SET DATANODE ALL (log_duration = off);
SET DATANODE ALL (log_statement = 'ddl' );
SET DATANODE ALL (log_lock_waits = on );
SET DATANODE ALL (unix_socket_permissions = '0700' );
SET DATANODE ALL (listen_addresses = '*');
SET DATANODE ALL (superuser_reserved_connections = 13 );
SET DATANODE ALL (track_counts = on );
SET DATANODE ALL (track_activity_query_size = 2048);
SET DATANODE ALL (max_locks_per_transaction = 64);
SET DATANODE ALL (constraint_exclusion = on );
SET DATANODE ALL (wal_level='replica');
SET DATANODE ALL (max_wal_senders = 5 );
--For data security it is recommended to turn on
SET DATANODE all (wal_log_hints = on);
--Used for anomaly recovery to speed up and affect performance more
SET DATANODE ALL (full_page_writes = on);
SET DATANODE ALL (autovacuum = on );
SET DATANODE ALL (statement_timeout = 86400000 );
SET DATANODE ALL (lock_timeout = '30s' );
SET DATANODE ALL (idle_in_transaction_session_timeout = '3000s');
SET DATANODE ALL (fsync = on);
--Enabling read/write separation is recommended to set remote_apply otherwise it is set to on
SET DATANODE ALL (synchronous_commit = on);
SET DATANODE ALL (wal_sync_method = open_datasync );
SET DATANODE ALL (wal_writer_delay = '200ms');
SET DATANODE ALL (wal_compression = on);
SET DATANODE ALL (commit_delay = 10 );
SET DATANODE ALL (commit_siblings = 10);
SET DATANODE ALL (checkpoint_timeout = '15min' );
SET DATANODE ALL (checkpoint_completion_target = 0.9);
SET DATANODE ALL (max_wal_size = 30960);
SET DATANODE ALL (min_wal_size = 2048);
SET DATANODE ALL (archive_mode = off );
SET DATANODE ALL (archive_command = '/bin/date' );
--Set the value not less than max_connections:
SET DATANODE ALL (max_prepared_transactions = 4000);
SET DATANODE ALL (bgwriter_delay = '10ms' );
SET DATANODE ALL (bgwriter_lru_maxpages = 1000);
SET DATANODE ALL (bgwriter_lru_multiplier = 10.0);
SET DATANODE ALL (rep_max_avail_flag = on );
set GTMCOORD all (enable_fast_query_shipping = off);
set GTMCOORD all (pgxc_enable_remote_query = off);
set COORDINATOR all (enable_fast_query_shipping = off);
set COORDINATOR all (pgxc_enable_remote_query = off);
set DATANODE all (enable_fast_query_shipping = off);
set DATANODE all (pgxc_enable_remote_query = off);
--Setting the parameters used by dbops
set GTMCOORD all (shared_preload_libraries='pg_stat_statements,adb_stat_statements');
set COORDINATOR all (shared_preload_libraries='pg_stat_statements,adb_stat_statements');
set DATANODE all (shared_preload_libraries='pg_stat_statements,adb_stat_statements');
set GTMCOORD all (pg_stat_statements.save=on);
set GTMCOORD all (pg_stat_statements.track=top);
set GTMCOORD all (pg_stat_statements.track_utility=off);
set COORDINATOR all (pg_stat_statements.save=on);
set COORDINATOR all (pg_stat_statements.track=top);
set COORDINATOR all (pg_stat_statements.track_utility=off);
set DATANODE all (pg_stat_statements.save=on);
set DATANODE all (pg_stat_statements.track=top);
set DATANODE all (pg_stat_statements.track_utility=off);
 
--gtmcoord:
SET GTMCOORD ALL (shared_buffers = '8GB' );
SET GTMCOORD ALL (work_mem = '4MB' );
SET GTMCOORD ALL (maintenance_work_mem = '1024MB');
--Set to cn setting value * number of cn nodes
SET GTMCOORD ALL(max_connections = 2000);
SET GTMCOORD ALL (max_worker_processes = 2000 );
SET GTMCOORD ALL(max_prepared_transactions = 2000);
SET GTMCOORD ALL (log_destination = 'csvlog');
SET GTMCOORD ALL (log_truncate_on_rotation = on);
SET GTMCOORD ALL (log_rotation_age = '7d');
SET GTMCOORD ALL (log_rotation_size = '100MB');
SET GTMCOORD ALL (log_min_messages = error );
--Turn off fsync to boost performance may lose data system failure power failure
SET GTMCOORD ALL (fsync = on);
SET GTMCOORD all (wal_log_hints = on);
--Used for anomaly recovery to speed up and affect performance more
SET GTMCOORD ALL (full_page_writes = on);
set GTMCOORD ALL (wal_level='replica');
SET GTMCOORD ALL (max_wal_senders = 5 );
SET GTMCOORD ALL (lock_timeout = '30s');
SET GTMCOORD ALL (idle_in_transaction_session_timeout = '3000s');
SET GTMCOORD ALL (waitglobaltransaction = '300s');
set GTMCOORD all (autovacuum =on);
SET GTMCOORD ALL (rep_max_avail_flag = on );
SET GTMCOORD ALL (synchronous_commit = on);