参数优化说明
创建数据库时，当开启优化参数开关，如果要部署实例的服务器内存大于4G时，默认会设置部分推荐的参数值来优化数据库。
注：当服务器内存小于4G 时，以下参数均不会修改
以下为优化参数列表：（和官网推荐参数一致）
第一部分：与内存大小相关的参数（假设内存大小为 N，单位GB）
- 当4 < N <= 8 时
| 参数 | 值 |
|---|---|
| max_connections | 500 |
| max_prepared_transactions | 500 |
| max_process_memory | (0.6*N)GB |
| shared_buffers | (0.2*N)GB |
| work_mem | 16MB |
| maintenance_work_mem | 512MB |
| wal_buffers | 128MB |
- 当 8 < N <= 64 时
| 参数 | 值 |
|---|---|
| max_connections | 1000 |
| max_prepared_transactions | 1000 |
| max_process_memory | (0.7*N)GB |
| shared_buffers | (0.2*N)GB |
| work_mem | 32MB |
| maintenance_work_mem | 1GB |
| wal_buffers | 512MB |
- 当 N > 64 时
| 参数 | 参数 |
|---|---|
| max_connections | 3000 |
| max_prepared_transactions | 3000 |
| max_process_memory | (0.8*N)GB |
| shared_buffers | (0.3*N)GB |
| work_mem | 64MB |
| maintenance_work_mem | 2GB |
| wal_buffers | 1GB |
第二部分：固定优化参数列表
| 参数 | 值 |
|---|---|
| remote_read_mode | non_authentication |
| password_encryption_type | 1 |
| password_reuse_time | 0 |
| password_lock_time | 0 |
| password_effect_time | 0 |
| session_timeout | 0 |
| modify_initial_password | off |
| wal_level | logical |
| full_page_writes | off |
| wal_log_hints | off |
| xloginsert_locks | 48 |
| advance_xlog_file_num | 10 |
| wal_keep_segments | 1024 |
| most_available_sync | on |
| catchup2normal_wait_time | 0 |
| enable_slot_log | on |
| max_replication_slots | 32 |
| wal_receiver_timeout | 60s |
| sync_config_strategy | none_node |
| log_line_prefix | '%m %u %d %r %p %S' |
| log_checkpoints | on |
| vacuum_cost_limit | 1000 |
| autovacuum_max_workers | 10 |
| autovacuum_naptime | 20s |
| autovacuum_vacuum_cost_delay | 10 |
| autovacuum_vacuum_scale_factor | 0.05 |
| autovacuum_analyze_scale_factor | 0.02 |
| autovacuum_vacuum_threshold | 200 |
| autovacuum_analyze_threshold | 200 |
| autovacuum_io_limits | 104857600 |
| instr_unique_sql_count | 200000 |
| enable_wdr_snapshot | on |
| log_min_duration_statement | 200 |
| track_activity_query_size | 2048 |
| enable_instr_rt_percentile | off |
| cstore_buffers | 16MB |
| local_syscache_threshold | 32MB |
| standby_shared_buffers_fraction | 1 |
| checkpoint_segments | 1024 |
| checkpoint_completion_target | 0.8 |
| max_files_per_process | 100000 |
| behavior_compat_options | display_leading_zero |
| lc_messages | en_US.UTF-8 |
| lc_monetary | en_US.UTF-8 |
| lc_numeric | en_US.UTF-8 |
| lc_time | en_US.UTF-8 |
| enable_opfusion | off |