谈谈生产环境中PostgreSQL参数的意义

archive_command

archive_command = ‘$pg_data_dir/../arch.sh %f %p’

auto_explain.log_analyze

auto_explain.log_analyze = true

auto_explain.log_min_duration = ‘300ms’

auto_explain.log_min_duration = ‘300mss’

auto_explain.log_timing = on

auto_explain.log_verbose = off

autovacuum = on

autovacuum_analyze_scale_factor = 0.01

autovacuum_freeze_max_age = 1200000000

autovacuum_max_workers = 12

autovacuum_multixact_freeze_max_age = 1250000000
autovacuum_vacuum_cost_delay = 0ms
autovacuum_vacuum_scale_factor = 0.02
checkpoint_completion_target = 0.5
checkpoint_flush_after = 0
checkpoint_timeout = 30min
checkpoint_timeout = 30min
default_text_search_config = ‘pg_catalog.english’
enable_partitionwise_aggregate = on
enable_partitionwise_join = on
full_page_writes = on
hot_standby_feedback = off
huge_pages = try
idle_in_transaction_session_timeout = ‘6h’
idle_in_transaction_session_timeout=3600000
jit=off
lc_messages = ‘C’
lc_monetary = ‘C’
lc_numeric = ‘C’
lc_time = ‘C’
log_autovacuum_min_duration = 0
log_checkpoints = on
log_destination=csvlog
log_error_verbosity = verbose
log_filename = ‘postgresql-%H.log’
log_lock_waits = on
log_min_duration_statement = 3s
log_rotation_age = 1h
log_rotation_size = 1GB
log_statement = ‘ddl’
log_temp_files = 256MB
log_truncate_on_rotation = on
maintenance_work_mem = 3GB
maintenance_work_mem = 3GB
max_connections = 5000
max_parallel_workers = 32
max_parallel_workers = 32
max_parallel_workers_per_gather = 0
max_replication_slots = 64
max_standby_archive_delay = 120s
max_standby_streaming_delay = 120s
max_sync_workers_per_subscription = 8
max_sync_workers_per_subscription = 8
max_wal_senders = 64
max_wal_size = 80GB
min_wal_size = 12GB
old_snapshot_threshold = 6h
parallel_leader_participation = on
pg_stat_statements.max = 50
pgsentinel_pgssh.enable = true
random_page_cost = 1.1
shared_buffers = 64GB
shared_preload_libraries = ‘pg_stat_statements,auto_explain,telepg_monitor’
statement_timeout=’24h’
synchronous_commit = remote_write
tcp_keepalives_count = 10
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
temp_file_limit=20240000
track_activity_query_size = 2048
track_functions = pl
track_io_timing = off
unix_socket_directories = ‘., /tmp’
unix_socket_permissions = 0700
vacuum_freeze_table_age = 200000000
vacuum_multixact_freeze_table_age = 200000000
wal_buffers = 32MB
wal_compression = on
wal_keep_segments = 5000
wal_receiver_status_interval = 1s
work_mem = 32MB
#password_encryption = scram-sha-256
#pg_stat_statements.track = none

shared_buffers = 64GB

shared_buffers (integer)

Sets the amount of memory the database server uses for shared memory buffers. The default is typically 128 megabytes (128MB), but might be less if your kernel settings will not support it (as determined during initdb). This setting must be at least 128 kilobytes. However, settings significantly higher than the minimum are usually needed for good performance. If this value is specified without units, it is taken as blocks, that is BLCKSZ bytes, typically 8kB. (Non-default values of BLCKSZ change the minimum value.) This parameter can only be set at server start.

If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system. There are some workloads where even larger settings for shared_buffers are effective, but because PostgreSQL also relies on the operating system cache, it is unlikely that an allocation of more than 40% of RAM to shared_buffers will work better than a smaller amount. Larger settings for shared_buffers usually require a corresponding increase in max_wal_size, in order to spread out the process of writing large quantities of new or changed data over a longer period of time.

On systems with less than 1GB of RAM, a smaller percentage of RAM is appropriate, so as to leave adequate space for the operating system.

max_connections (integer)

Determines the maximum number of concurrent connections to the database server. The default is typically 100 connections, but might be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at server start.

When running a standby server, you must set this parameter to the same or higher value than on the master server. Otherwise, queries will not be allowed in the standby server.