pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: DBA <ecountdba@ecounterp.co.kr>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: what are the things that occupy the session memory.
Date: Mon, 30 Sep 2024 11:32:36 +0900
Message-ID: <20240930023236.9EC5610AA47@wsmtpa1.ecounterp.com> (raw )
<p><br></p><p>1. A description of what you are trying to achieve and what results you expect : </p><p><br></p><p><span style="font-family: Arial, "Apple SD Gothic Neo", Gulim;">We set shared_buffer = 70GB, max_connections = 300, work_mem = 300MB.</span><br></p><p><br></p><p>Our setup is as above. When monitored by TOP CPU, we confirmed that RES uses up to 500MB in one postgres session. </p><p>The session memory was initially a low number and then gradually increased to 500MB.</p><p>We wonder what the contents of session memory have, and wonder if there is a way to solve this. </p><p>Also, considering our server specification, if things like shared_buffer or work_mem need to be adjusted.</p><p>I'm inquiring because I often use swap due to lack of memory due to a large amount of session memory.</p><p><br></p><p><br></p><p>2. PostgreSQL version number you are running:</p><p><br></p><p><br></p><p><br></p><p>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</p><p><br></p><p><br></p><p><br></p><p>3. How you installed PostgreSQL:</p><p><br></p><p><br></p><p><br></p><p>We have downloaded debs on https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-15/</p><p><br><... installed them.</p><p><br></p><p><br></p><p><br></p><p>4. Changes made to the settings in the postgresql.conf file:</p><p><br></p><p><br></p><p><br></p><p>name<span style="white-space:pre"> </span>current_setting<span style="white-space:pre"> </span>source</p><p>archive_command<span style="white-space:pre"> </span>cp /data/PG15/pg_wal/%f /archive_data/%f<span style="white-space:pre"> </span>configuration file</p><p>archive_mode<span style="white-space:pre"> </span>on<span style="white-space:pre"> </span>configuration file</p><p>auto_explain.log_analyze<span style="white-space:pre"> </span>on<span style="white-space:pre"> </span>configuration file</p><p>auto_explain.log_buffers<span style="white-space:pre"> </span>on<span style="white-space:pre"> </span>configuration file</p><p>autovacuum_analyze_scale_factor<span style="white-space:pre"> </span>0<span style="white-space:pre"> </span>configuration file</p><p>autovacuum_analyze_threshold<span style="white-space:pre"> </span>100000000<span style="white-space:pre"> </span>configuration file</p><p>autovacuum_freeze_max_age<span style="white-space:pre"> </span>500000000<span style="white-space:pre"> </span>configuration file</p><p>autovacuum_max_workers<span style="white-space:pre"> </span>3<span style="white-space:pre"> </span>configuration file</p><p>autovacuum_naptime<span style="white-space:pre"> </span>1min<span style="white-space:pre"> </span>configuration file</p><p>autovacuum_vacuum_cost_delay<span style="white-space:pre"> </span>50ms<span style="white-space:pre"> </span>configuration file</p><p>autovacuum_vacuum_insert_scale_factor<span style="white-space:pre"> </span>0<span style="white-space:pre"> </span>configuration file</p><p>autovacuum_vacuum_insert_threshold<span style="white-space:pre"> </span>30000<span style="white-space:pre"> </span>configuration file</p><p>autovacuum_vacuum_scale_factor<span style="white-space:pre"> </span>0<span style="white-space:pre"> </span>configuration file</p><p>autovacuum_vacuum_threshold<span style="white-space:pre"> </span>5000<span style="white-space:pre"> </span>configuration file</p><p>checkpoint_warning<span style="white-space:pre"> </span>1h<span style="white-space:pre"> </span>configuration file</p><p>client_encoding<span style="white-space:pre"> </span>UTF8<span style="white-space:pre"> </span>client</p><p>DateStyle<span style="white-space:pre"> </span>ISO, MDY<span style="white-space:pre"> </span>client</p><p>deadlock_timeout<span style="white-space:pre"> </span>100ms<span style="white-space:pre"> </span>configuration file</p><p>default_text_search_config<span style="white-space:pre"> </span>pg_catalog.english<span style="white-space:pre"> </span>configuration file</p><p>dynamic_shared_memory_type<span style="white-space:pre"> </span>posix<span style="white-space:pre"> </span>configuration file</p><p>effective_cache_size<span style="white-space:pre"> </span>48GB<span style="white-space:pre"> </span>configuration file</p><p>effective_io_concurrency<span style="white-space:pre"> </span>600<span style="white-space:pre"> </span>configuration file</p><p>enable_mergejoin<span style="white-space:pre"> </span>off<span style="white-space:pre"> </span>configuration file</p><p>enable_seqscan<span style="white-space:pre"> </span>off<span style="white-space:pre"> </span>configuration file</p><p>extra_float_digits<span style="white-space:pre"> </span>3<span style="white-space:pre"> </span>session</p><p>jit<span style="white-space:pre"> </span>off<span style="white-space:pre"> </span>configuration file</p><p>lc_messages<span style="white-space:pre"> </span>en_US.UTF-8<span style="white-space:pre"> </span>configuration file</p><p>lc_monetary<span style="white-space:pre"> </span>en_US.UTF-8<span style="white-space:pre"> </span>configuration file</p><p>lc_numeric<span style="white-space:pre"> </span>en_US.UTF-8<span style="white-space:pre"> </span>configuration file</p><p>lc_time<span style="white-space:pre"> </span>en_US.UTF-8<span style="white-space:pre"> </span>configuration file</p><p>listen_addresses<span style="white-space:pre"> </span>*<span style="white-space:pre"> </span>configuration file</p><p>log_autovacuum_min_duration<span style="white-space:pre"> </span>10ms<span style="white-space:pre"> </span>configuration file</p><p>log_checkpoints<span style="white-space:pre"> </span>on<span style="white-space:pre"> </span>configuration file</p><p>log_destination<span style="white-space:pre"> </span>stderr, csvlog<span style="white-space:pre"> </span>configuration file</p><p>log_directory<span style="white-space:pre"> </span>/data/log<span style="white-space:pre"> </span>configuration file</p><p>log_filename<span style="white-space:pre"> </span>postgresql-%a.log<span style="white-space:pre"> </span>configuration file</p><p>log_line_prefix<span style="white-space:pre"> </span>%m [%p][%u][%h]<span style="white-space:pre"> </span>configuration file</p><p>log_lock_waits<span style="white-space:pre"> </span>on<span style="white-space:pre"> </span>configuration file</p><p>log_min_duration_statement<span style="white-space:pre"> </span>1500ms<span style="white-space:pre"> </span>user</p><p>log_min_messages<span style="white-space:pre"> </span>warning<span style="white-space:pre"> </span>configuration file</p><p>log_rotation_age<span style="white-space:pre"> </span>1d<span style="white-space:pre"> </span>configuration file</p><p>log_rotation_size<span style="white-space:pre"> </span>0<span style="white-space:pre"> </span>configuration file</p><p>log_timezone<span style="white-space:pre"> </span>Asia/Seoul<span style="white-space:pre"> </span>configuration file</p><p>log_truncate_on_rotation<span style="white-space:pre"> </span>on<span style="white-space:pre"> </span>configuration file</p><p>logging_collector<span style="white-space:pre"> </span>on<span style="white-space:pre"> </span>configuration file</p><p>maintenance_work_mem<span style="white-space:pre"> </span>200MB<span style="white-space:pre"> </span>configuration file</p><p>max_connections<span style="white-space:pre"> </span></p><p>300</p><p><br></p><p>configuration file</p><p>max_locks_per_transaction<span style="white-space:pre"> </span>2000<span style="white-space:pre"> </span>configuration file</p><p>max_parallel_workers<span style="white-space:pre"> </span>0<span style="white-space:pre"> </span>configuration file</p><p>max_parallel_workers_per_gather<span style="white-space:pre"> </span>0<span style="white-space:pre"> </span>configuration file</p><p>max_wal_size<span style="white-space:pre"> </span>2GB<span style="white-space:pre"> </span>configuration file</p><p>max_worker_processes<span style="white-space:pre"> </span>50<span style="white-space:pre"> </span>configuration file</p><p>min_wal_size<span style="white-space:pre"> </span>1GB<span style="white-space:pre"> </span>configuration file</p><p>password_encryption<span style="white-space:pre"> </span>md5<span style="white-space:pre"> </span>configuration file</p><p>plan_cache_mode<span style="white-space:pre"> </span>force_custom_plan<span style="white-space:pre"> </span>configuration file</p><p>port<span style="white-space:pre"> </span>45432<span style="white-space:pre"> </span>configuration file</p><p>restore_command<span style="white-space:pre"> </span>mv -f /archive_data/%f /data/PG15/pg_wal/%f<span style="white-space:pre"> </span>configuration file</p><p>shared_buffers<span style="white-space:pre"> </span>70GB<span style="white-space:pre"> </span>configuration file</p><p>shared_preload_libraries<span style="white-space:pre"> </span>pg_hint_plan, pg_cron, pg_stat_statements, auto_explain<span style="white-space:pre"> </span>configuration file</p><p>superuser_reserved_connections<span style="white-space:pre"> </span>6<span style="white-space:pre"> </span>configuration file</p><p>tcp_keepalives_idle<span style="white-space:pre"> </span>30<span style="white-space:pre"> </span>configuration file</p><p>tcp_keepalives_interval<span style="white-space:pre"> </span>1<span style="white-space:pre"> </span>configuration file</p><p>TimeZone<span style="white-space:pre"> </span>Asia/Seoul<span style="white-space:pre"> </span>client</p><p>vacuum_cost_limit<span style="white-space:pre"> </span>1000<span style="white-space:pre"> </span>configuration file</p><p>wal_buffers<span style="white-space:pre"> </span>1GB<span style="white-space:pre"> </span>configuration file</p><p>wal_compression<span style="white-space:pre"> </span>zstd<span style="white-space:pre"> </span>configuration file</p><p>wal_sender_timeout<span style="white-space:pre"> </span>10min<span style="white-space:pre"> </span>configuration file</p><p>work_mem<span style="white-space:pre"> </span>300MB<span style="white-space:pre"> </span>configuration file</p><p><br></p><p><br></p><p>5. Operating system and version:</p><p><br></p><p>Distributor ID: Ubuntu</p><p><br></p><p>Description: Ubuntu 22.04.2 LTS</p><p><br></p><p>Release: 22.04</p><p><br></p><p>Codename: jammy</p><p><br></p><p>Memory : 128GB</p><p><br></p><p>CPU : 16 cores</p><p><br></p><p><br></p><p><br></p><p>6. What program you're using to connect to PostgreSQL:</p><p><br></p><p><br></p><p><br></p><p>Just do on Linux kernel</p><div><br></div><table id="ecma_tracking"><tr><td><img src='https://wmail.ecount.com/ec5/api/app.webmail/action/ReadReceiptAction:6d736769643d323032343039333031... ;
view thread (4+ messages) latest in thread
Message-ID: <20240930023236.9EC5610AA47@wsmtpa1.ecounterp.com>
Permalink: ../20240930023236.9EC5610AA47@wsmtpa1.ecounterp.com/
Also on: postgresql.org/message-id/20240930023236.9EC5610AA47@wsmtpa1.ecounterp.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: ecountdba@ecounterp.co.kr, pgsql-bugs@lists.postgresql.org
Subject: Re: what are the things that occupy the session memory.
In-Reply-To: <20240930023236.9EC5610AA47@wsmtpa1.ecounterp.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox