Re: what are the things that occupy the session memory.

Hi

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
Semab

On Mon, Sep 30, 2024 at 5:33 PM DBA <ecountdba(at)ecounterp(dot)co(dot)kr> wrote:

>
> 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
>
>