Manager Handbook for Distributed AntDB-T - P23–4
Published: 2023-08-25 06:22:30

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 adjust appropriately according to the 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 using reduce requires increasing the MWP parameter setting
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 );
--Set the maximum execution time to execute the logging statement ms
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 the pg_stat_activity.query length
SET COORDINATOR ALL (track_activity_query_size = 2048 );
--By default, when there are a large number of subtables in the query, the partitioned table can be increased and the slave database setting is greater than or equal to the main database
SET COORDINATOR ALL (max_locks_per_transaction = 128);
--It is usually used for PG parent-child table query condition optimization and uses constraint conditions
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 (performance testing can be turned off)
SET COORDINATOR ALL (synchronous_commit = on );
--open_datasync direct IO, affected by the fsync parameter
SET COORDINATOR ALL (wal_sync_method = open_datasync);
SET COORDINATOR all (wal_log_hints = on);
--Recommended for failure recovery is turned 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 default period of BGWRITER to write dirty pages to 200ms, and it is recommended to set a multiple of 10
SET COORDINATOR ALL (bgwriter_delay = '10ms');
--Set the default number of dirty pages written by bgwriter to 100, and the write-intensive system can be increased, considering that the IO write capacity cannot be set too large
SET COORDINATOR ALL (bgwriter_lru_maxpages = 1000 );
--Set the default value of 1 for the maximum write dirty page ratio per round, and write bgwriter_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/请Users adjust appropriately according to the host environment information
SET DATANODE ALL (shared_buffers = '48GB' );
SET DATANODE ALL (waitglobaltransaction = '300s');
SET DATANODE ALL (maintenance_work_mem = '1024MB');
SET DATANODE ALL (work_mem = '8MB' );
--The maximum number of connections to a DN node is at least a multiple of the CN node of the CN configuration parameter
SET DATANODE ALL (max_connections = 4000 );
SET DATANODE ALL (max_prepared_transactions = 4000 );
SET DATANODE ALL (wal_keep_size = 102400 );
--It is recommended that the settings be consistent with the shared_buffers
SET DATANODE ALL (effective_cache_size = '96GB' );
SET DATANODE ALL (max_parallel_workers = 12 );
--The Reduce feature needs to be increased by no less than max_connections mwp parameter
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 recommendations turned on
SET DATANODE all (wal_log_hints = on);
--It is used to speed up abnormal recovery and have a large impact on performance
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);
--Enable the Recommended setting for read/write splitting remote_apply otherwise 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 to at least 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);
--Set 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 the value of cn to *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 );
--Turning off fsync to boost performance can result in data loss, system failure, power failure
SET GTMCOORD ALL (fsync = on);
SET GTMCOORD all (wal_log_hints = on);
--It is used to speed up abnormal recovery and have a large impact on performance
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); 
```