pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedwhat are the things that occupy the session memory.
4+ messages / 4 participants
[nested] [flat]
* what are the things that occupy the session memory.
@ 2024-09-30 02:32 DBA <ecountdba@ecounterp.co.kr>
2024-09-30 13:49 ` Re: what are the things that occupy the session memory. semab tariq <semabtariq1@gmail.com>
2024-10-02 08:47 ` Re: what are the things that occupy the session memory. Tomas Vondra <tomas@vondra.me>
0 siblings, 2 replies; 4+ messages in thread
From: DBA @ 2024-09-30 02:32 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
<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...;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: what are the things that occupy the session memory.
2024-09-30 02:32 what are the things that occupy the session memory. DBA <ecountdba@ecounterp.co.kr>
@ 2024-09-30 13:49 ` semab tariq <semabtariq1@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: semab tariq @ 2024-09-30 13:49 UTC (permalink / raw)
To: DBA <ecountdba@ecounterp.co.kr>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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@ecounterp.co.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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: what are the things that occupy the session memory.
2024-09-30 02:32 what are the things that occupy the session memory. DBA <ecountdba@ecounterp.co.kr>
@ 2024-10-02 08:47 ` Tomas Vondra <tomas@vondra.me>
2024-10-06 04:19 ` Re: what are the things that occupy the session memory. Andy Fan <zhihuifan1213@163.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tomas Vondra @ 2024-10-02 08:47 UTC (permalink / raw)
To: DBA <ecountdba@ecounterp.co.kr>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On 9/30/24 04:32, DBA 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.
>
I think you may be confused about what RSS means. It is not "private"
session memory, dedicated to the single backend process. For example, as
the processes "touch" shared memory, that'll be counted in RSS too.
If you want to look closer, you can look at "smaps" for each process in
/proc/$PID/smaps, which has info about all the memory. For one of "my"
backends I see this entry:
7f4fecf42000-7f50f7000000 rw-s 00000000 00:01 3126
/dev/zero (deleted)
Size: 4358904 kB
KernelPageSize: 4 kB
MMUPageSize: 4 kB
Rss: 1622120 kB
Pss: 323476 kB
Pss_Dirty: 323476 kB
Shared_Clean: 0 kB
Shared_Dirty: 1622120 kB
Private_Clean: 0 kB
Private_Dirty: 0 kB
Referenced: 1622120 kB
...
which is clearly shared memory (shared buffers, actually), but it adds
1.6GB to RSS.
It is a bit weird / surprising, but that's what Linux does.
Anyway, I agree shared buffers 70GB is way too high - I'd reduce that to
maybe 8GB and only increase that if cache hit ratio is below 0.95 or so.
Chances are this alone will fix the OOM.
If not, try reducing work_mem. If you're doing OLTP queries, those
likely don't need wm=300MB (why did you set this value?). For OLAP
queries 300MB might make sense, but then maybe you shouldn't have 300 of
them.
If this doesn't help, you need to investigate if there's one query using
too much memory, or if it's simply a the total memory usage. You can
either monitor the system, but the OOM killer should have also logged
stas about the killed process (how much memory it used etc.).
Also, check the memory overcommit setting.
regards
--
Tomas Vondra
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: what are the things that occupy the session memory.
2024-09-30 02:32 what are the things that occupy the session memory. DBA <ecountdba@ecounterp.co.kr>
2024-10-02 08:47 ` Re: what are the things that occupy the session memory. Tomas Vondra <tomas@vondra.me>
@ 2024-10-06 04:19 ` Andy Fan <zhihuifan1213@163.com>
0 siblings, 0 replies; 4+ messages in thread
From: Andy Fan @ 2024-10-06 04:19 UTC (permalink / raw)
To: Tomas Vondra <tomas@vondra.me>; +Cc: DBA <ecountdba@ecounterp.co.kr>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Tomas Vondra <tomas@vondra.me> writes:
> Anyway, I agree shared buffers 70GB is way too high - I'd reduce that to
> maybe 8GB and only increase that if cache hit ratio is below 0.95 or
> so.
I'm always confused how should shared_buffers be set initially (saying
monitor and adjust them later is not free.) Some places say 25% of
physical memory but I don't know why is that. I'm not sure my question
can have a answer, but just have a try. The known drawback of big shared
buffer from me includes: a). Seize the memory from other
component which may contribute to OOM. b). make some operation
slower. e.g. drop / truncate table.
> If not, try reducing work_mem. If you're doing OLTP queries, those
> likely don't need wm=300MB (why did you set this value?). For OLAP
> queries 300MB might make sense, but then maybe you shouldn't have 300 of
> them.
I want to have a metion of autovacuum_work_mem/maintenance_work_mem for
a OLTP workload. In OLTP workload, user probably doesn't need a bigger
work_mem but if user set it to a bigger value, it should not cause a big
issue IIUC, since it just set a upper bound. However in OLTP workload, it
probably has lots of updates/delete, and if they have lots of tables, it
probably use up to {autovacuum_work_mem} memory per vacuum worker, which
is more likely cause an issue.
--
Best Regards
Andy Fan
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-10-06 04:19 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-30 02:32 what are the things that occupy the session memory. DBA <ecountdba@ecounterp.co.kr>
2024-09-30 13:49 ` semab tariq <semabtariq1@gmail.com>
2024-10-02 08:47 ` Tomas Vondra <tomas@vondra.me>
2024-10-06 04:19 ` Andy Fan <zhihuifan1213@163.com>
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