1. A description of what you are trying to achieve and what results you expect :
We set shared_buffer = 70GB, max_connections = 300, work_mem = 300MB.
Our setup is as above. When monitored by TOP CPU, we confirmed that RES uses up to 500MB in one postgres session.
The session memory was initially a low number and then gradually increased to 500MB.
We wonder what the contents of session memory have, and wonder if there is a way to solve this.
Also, considering our server specification, if things like shared_buffer or work_mem need to be adjusted.
I'm inquiring because I often use swap due to lack of memory due to a large amount of session memory.
2. PostgreSQL version number you are running:
PostgreSQL 15.3 (Ubuntu 15.3-1.pgdg22.04+~20230706.1614.g25624c5) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04.1) 11.3.0, 64-bit
3. How you installed PostgreSQL:
We have downloaded debs on https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-15/
and installed them.
4. Changes made to the settings in the postgresql.conf file:
name current_setting source
archive_command cp /data/PG15/pg_wal/%f /archive_data/%f configuration file
archive_mode on configuration file
auto_explain.log_analyze on configuration file
auto_explain.log_buffers on configuration file
autovacuum_analyze_scale_factor 0 configuration file
autovacuum_analyze_threshold 100000000 configuration file
autovacuum_freeze_max_age 500000000 configuration file
autovacuum_max_workers 3 configuration file
autovacuum_naptime 1min configuration file
autovacuum_vacuum_cost_delay 50ms configuration file
autovacuum_vacuum_insert_scale_factor 0 configuration file
autovacuum_vacuum_insert_threshold 30000 configuration file
autovacuum_vacuum_scale_factor 0 configuration file
autovacuum_vacuum_threshold 5000 configuration file
checkpoint_warning 1h configuration file
client_encoding UTF8 client
DateStyle ISO, MDY client
deadlock_timeout 100ms configuration file
default_text_search_config pg_catalog.english configuration file
dynamic_shared_memory_type posix configuration file
effective_cache_size 48GB configuration file
effective_io_concurrency 600 configuration file
enable_mergejoin off configuration file
enable_seqscan off configuration file
extra_float_digits 3 session
jit off configuration file
lc_messages en_US.UTF-8 configuration file
lc_monetary en_US.UTF-8 configuration file
lc_numeric en_US.UTF-8 configuration file
lc_time en_US.UTF-8 configuration file
listen_addresses * configuration file
log_autovacuum_min_duration 10ms configuration file
log_checkpoints on configuration file
log_destination stderr, csvlog configuration file
log_directory /data/log configuration file
log_filename postgresql-%a.log configuration file
log_line_prefix %m [%p][%u][%h] configuration file
log_lock_waits on configuration file
log_min_duration_statement 1500ms user
log_min_messages warning configuration file
log_rotation_age 1d configuration file
log_rotation_size 0 configuration file
log_timezone Asia/Seoul configuration file
log_truncate_on_rotation on configuration file
logging_collector on configuration file
maintenance_work_mem 200MB configuration file
max_connections
300
configuration file
max_locks_per_transaction 2000 configuration file
max_parallel_workers 0 configuration file
max_parallel_workers_per_gather 0 configuration file
max_wal_size 2GB configuration file
max_worker_processes 50 configuration file
min_wal_size 1GB configuration file
password_encryption md5 configuration file
plan_cache_mode force_custom_plan configuration file
port 45432 configuration file
restore_command mv -f /archive_data/%f /data/PG15/pg_wal/%f configuration file
shared_buffers 70GB configuration file
shared_preload_libraries pg_hint_plan, pg_cron, pg_stat_statements, auto_explain configuration file
superuser_reserved_connections 6 configuration file
tcp_keepalives_idle 30 configuration file
tcp_keepalives_interval 1 configuration file
TimeZone Asia/Seoul client
vacuum_cost_limit 1000 configuration file
wal_buffers 1GB configuration file
wal_compression zstd configuration file
wal_sender_timeout 10min configuration file
work_mem 300MB configuration file
5. Operating system and version:
Distributor ID: Ubuntu
Description: Ubuntu 22.04.2 LTS
Release: 22.04
Codename: jammy
Memory : 128GB
CPU : 16 cores
6. What program you're using to connect to PostgreSQL:
Just do on Linux kernel