Re: what are the things that occupy the session memory. - Mailing list pgsql-bugs
As a first step, I recommend adjusting the value of shared_buffers. The current setting of 70GB out of 128GB of total memory seems too high, leaving insufficient memory for other processes and the operating system. Consider lowering it to 32GB, which is approximately 25% of the total memory available on your system.
Additionally, work_mem is quite high, especially with max_connections set to 300. Each session can use up to 300MB, which can quickly consume a significant amount of memory. It might be better to lower work_mem i.e., if your queries require a large amount of memory, you can set work_mem to a value between 16MB and 32MB.
Thanks and Regards
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
pgsql-bugs by date: