what are the things that occupy the session memory.

Started by DBAover 1 year ago4 messagesbugs

ecountdba@ecounterp.co.kr

over 1 year ago

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 sourcearchive_command cp /data/PG15/pg_wal/%f /archive_data/%f configuration filearchive_mode on configuration fileauto_explain.log_analyze on configuration fileauto_explain.log_buffers on configuration fileautovacuum_analyze_scale_factor 0 configuration fileautovacuum_analyze_threshold 100000000 configuration fileautovacuum_freeze_max_age 500000000 configuration fileautovacuum_max_workers 3 configuration fileautovacuum_naptime 1min configuration fileautovacuum_vacuum_cost_delay 50ms configuration fileautovacuum_vacuum_insert_scale_factor 0 configuration fileautovacuum_vacuum_insert_threshold 30000 configuration fileautovacuum_vacuum_scale_factor 0 configuration fileautovacuum_vacuum_threshold 5000 configuration filecheckpoint_warning 1h configuration fileclient_encoding UTF8 clientDateStyle ISO, MDY clientdeadlock_timeout 100ms configuration filedefault_text_search_config pg_catalog.english configuration filedynamic_shared_memory_type posix configuration fileeffective_cache_size 48GB configuration fileeffective_io_concurrency 600 configuration fileenable_mergejoin off configuration fileenable_seqscan off configuration fileextra_float_digits 3 sessionjit off configuration filelc_messages en_US.UTF-8 configuration filelc_monetary en_US.UTF-8 configuration filelc_numeric en_US.UTF-8 configuration filelc_time en_US.UTF-8 configuration filelisten_addresses * configuration filelog_autovacuum_min_duration 10ms configuration filelog_checkpoints on configuration filelog_destination stderr, csvlog configuration filelog_directory /data/log configuration filelog_filename postgresql-%a.log configuration filelog_line_prefix %m [%p][%u][%h] configuration filelog_lock_waits on configuration filelog_min_duration_statement 1500ms userlog_min_messages warning configuration filelog_rotation_age 1d configuration filelog_rotation_size 0 configuration filelog_timezone Asia/Seoul configuration filelog_truncate_on_rotation on configuration filelogging_collector on configuration filemaintenance_work_mem 200MB configuration filemax_connections 300 configuration filemax_locks_per_transaction 2000 configuration filemax_parallel_workers 0 configuration filemax_parallel_workers_per_gather 0 configuration filemax_wal_size 2GB configuration filemax_worker_processes 50 configuration filemin_wal_size 1GB configuration filepassword_encryption md5 configuration fileplan_cache_mode force_custom_plan configuration fileport 45432 configuration filerestore_command mv -f /archive_data/%f /data/PG15/pg_wal/%f configuration fileshared_buffers 70GB configuration fileshared_preload_libraries pg_hint_plan, pg_cron, pg_stat_statements, auto_explain configuration filesuperuser_reserved_connections 6 configuration filetcp_keepalives_idle 30 configuration filetcp_keepalives_interval 1 configuration fileTimeZone Asia/Seoul clientvacuum_cost_limit 1000 configuration filewal_buffers 1GB configuration filewal_compression zstd configuration filewal_sender_timeout 10min configuration filework_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<div> </div><table id="ecma_tracking"><tr><td><img src='https://wmail.ecount.com/ec5/api/app.webmail/action/ReadReceiptAction:6d736769643d32303234303933303131333233362e3539333135392e31313830413626656d61696c3d706773716c2d62756773406c697374732e706f737467726573716c2e6f7267'></a></td></tr></table>

semab tariq

semabtariq1@gmail.com

over 1 year ago

In reply to: DBA (#1)

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

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:

Show quoted text

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

Tomas Vondra

tomas.vondra@2ndquadrant.com

over 1 year ago

In reply to: DBA (#1)

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

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

Andy Fan

zhihui.fan1213@gmail.com

over 1 year ago

In reply to: Tomas Vondra (#3)

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

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