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: Using a lot of memory in each session.
Date: Tue, 24 Sep 2024 16:58:11 +0900
Message-ID: <20240924075811.BDA2C10AA13@wsmtpa1.ecounterp.com> (raw )
<p>Hello, I'm inquiring because of using a lot of memory in each session.</p><p><br></p><p><b>1. A description of what you are trying to achieve and what results you expect : </b></p><p><br></p><p>We set shared_buffer = 70GB, max_connections = 300, work_mem = 300MB.</p><p><br></p><p>We thought the session memory could be adjusted with the work_mem variable, but no. noticed that each session consumed more than 500MB.<br></p><p><br></p><p>We're wondering what are the things that are occupied in session memory and if there is a way to organize these in each session and return the memory.<br></p><p><br></p><p><b>2. PostgreSQL version number you are running:</b></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><b>3. How you installed PostgreSQL:</b></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><span></span></p><p><b>4. Changes made to the settings in the postgresql.conf file:</b></p><p><b><br></b></p><table border="0" cellpadding="0" cellspacing="0" width="735" style="font-size: calc(12 / var(--font-calc)); width: 551pt;"><colgroup><col width="259" style="width: 194pt;"><col width="359" style="width: 269pt;"><col width="117" style="width: 88pt;"></colgroup><tbody><tr height="18" style="height: 13.5pt;"><td height="18" width="259" style="height: 13.5pt; width: 194pt;">name</td><td width="359" style="width: 269pt;">current_setting</td><td width="117" style="width: 88pt;">source</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">archive_command</td><td>cp /data/PG15/pg_wal/%f /archive_data/%f</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">archive_mode</td><td>on</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">auto_explain.log_analyze</td><td>on</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">auto_explain.log_buffers</td><td>on</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_analyze_scale_factor</td><td>0</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_analyze_threshold</td><td>100000000</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_freeze_max_age</td><td>500000000</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_max_workers</td><td>3</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_naptime</td><td>1min</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_vacuum_cost_delay</td><td>50ms</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_vacuum_insert_scale_factor</td><td>0</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_vacuum_insert_threshold</td><td>30000</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_vacuum_scale_factor</td><td>0</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">autovacuum_vacuum_threshold</td><td>5000</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">checkpoint_warning</td><td>1h</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">client_encoding</td><td>UTF8</td><td>client</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">DateStyle</td><td>ISO, MDY</td><td>client</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">deadlock_timeout</td><td>100ms</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">default_text_search_config</td><td>pg_catalog.english</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">dynamic_shared_memory_type</td><td>posix</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">effective_cache_size</td><td>48GB</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">effective_io_concurrency</td><td>600</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">enable_mergejoin</td><td>off</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">enable_seqscan</td><td>off</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">extra_float_digits</td><td>3</td><td>session</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">jit</td><td>off</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">lc_messages</td><td>en_US.UTF-8</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">lc_monetary</td><td>en_US.UTF-8</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">lc_numeric</td><td>en_US.UTF-8</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">lc_time</td><td>en_US.UTF-8</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">listen_addresses</td><td>*</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_autovacuum_min_duration</td><td>10ms</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_checkpoints</td><td>on</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_destination</td><td>stderr, csvlog</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_directory</td><td>/data/log</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_filename</td><td>postgresql-%a.log</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_line_prefix</td><td>%m [%p][%u][%h]</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_lock_waits</td><td>on</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_min_duration_statement</td><td>1500ms</td><td>user</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_min_messages</td><td>warning</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_rotation_age</td><td>1d</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_rotation_size</td><td>0</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_timezone</td><td>Asia/Seoul</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">log_truncate_on_rotation</td><td>on</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">logging_collector</td><td>on</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">maintenance_work_mem</td><td>200MB</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">max_connections</td><td><p>300</p></td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">max_locks_per_transaction</td><td>2000</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">max_parallel_workers</td><td>0</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">max_parallel_workers_per_gather</td><td>0</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">max_wal_size</td><td>2GB</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">max_worker_processes</td><td>50</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">min_wal_size</td><td>1GB</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">password_encryption</td><td>md5</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">plan_cache_mode</td><td>force_custom_plan</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">port</td><td>45432</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">restore_command</td><td>mv -f /archive_data/%f /data/PG15/pg_wal/%f</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">shared_buffers</td><td>70GB</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">shared_preload_libraries</td><td>pg_hint_plan, pg_cron, pg_stat_statements, auto_explain</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">superuser_reserved_connections</td><td>6</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">tcp_keepalives_idle</td><td>30</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">tcp_keepalives_interval</td><td>1</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">TimeZone</td><td>Asia/Seoul</td><td>client</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">vacuum_cost_limit</td><td>1000</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">wal_buffers</td><td>1GB</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">wal_compression</td><td>zstd</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">wal_sender_timeout</td><td>10min</td><td>configuration file</td></tr><tr height="18" style="height: 13.5pt;"><td height="18" style="height: 13.5pt;">work_mem</td><td>200MB</td><td>configuration file</td></tr></tbody></table><p><br></p><p><b>5. Operating system and version:</b></p><p>Distributor ID: Ubuntu</p><p>Description: Ubuntu 22.04.2 LTS</p><p>Release: 22.04</p><p>Codename: jammy</p><p><br></p><p><b>6. What program you're using to connect to PostgreSQL:</b></p><p><br></p><p>Just do on Linux kernel</p><p><br></p><p><br style="font-family: Arial, "Apple SD Gothic Neo", Gulim;"></p><table id="ecma_tracking"><tr><td><img src='https://wmail.ecount.com/ec5/api/app.webmail/action/ReadReceiptAction:6d736769643d323032343039323431... ;
view thread (3+ messages) latest in thread
Message-ID: <20240924075811.BDA2C10AA13@wsmtpa1.ecounterp.com>
Permalink: ../20240924075811.BDA2C10AA13@wsmtpa1.ecounterp.com/
Also on: postgresql.org/message-id/20240924075811.BDA2C10AA13@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: Using a lot of memory in each session.
In-Reply-To: <20240924075811.BDA2C10AA13@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