1

I just did a large import of renderd tiles for an osm server. I want to start my next process (running the import of nominatim) but it takes a lot of memory. The problem I have is that walwriter, background writer, checkpointer are consuming 131GB of memory. I checked pg_top and the processes are sleeping. Is there any way to clear these processes safely or just force postgres to complete the walwriter and background writer?

I am using Postgres v12, and shared_buffers is set to 128GB.

HTOP: HTOP pg_top:

last pid: 628600;  load avg  0.08,  0.03,  0.04;       up 1+00:31:38                                                                                02:22:22
5            5 sleeping
CPU states:  0.0% user,  0.0% nice,  0.0% system,  100% idle,  0.0% iowait
Memory: 487G used, 16G free, 546M buffers, 253G cached
DB activity:   0 tps,  0 rollbs/s,   0 buffer r/s, 100 hit%,     43 row r/s,    0 row w/s  -
DB I/O:     0 reads/s,     0 KB/s,     0 writes/s,     0 KB/s
DB disk: 3088.7 GB total, 2538.8 GB free (17% used)
Swap: 45M used, 8147M free, 588K cached


627692 postgres  20    0  131G 4368K sleep   0:00  0.00%  0.00% postgres: 12/main: background writer
627691 postgres  20    0  131G 6056K sleep   0:00  0.00%  0.00% postgres: 12/main: checkpointer
627693 postgres  20    0  131G 4368K sleep   0:00  0.00%  0.00% postgres: 12/main: walwriter
628601 postgres  20    0  131G   11M sleep   0:00  0.00%  0.00% postgres: 12/main: postgres postgres [local] idle
627695 postgres  20    0  131G 6924K sleep   0:00  0.00%  0.00% postgres: 12/main: logical replication launcher

pg_wal directory:

enter image description here

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Paul Toone
  • 123
  • 1
  • 3
  • 12

1 Answers1

1

Everything is just fine, and htop is lying to you.

Of course the background processes that access shared buffers will use that memory, and since it is shared memory, it is reported for each of these processes. In reality, it is allocated only once.

The shared memory allocated by PostgreSQL is slightly bigger than shared_buffers, so if that parameter is set to 128GB, you reported data make sense and are perfectly normal.

If you set max_wal_size = 32GB, it is normal to have a lot of WAL segments.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263