PGSQL 11.4: shared_buffers and /dev/shm size
Hello,
I'm running PostgreSQL 11.4 on Linux 4.12.14 and I see the following issue while executing single one query:
ERROR: could not resize shared
memory segment "/PostgreSQL.1596105766" to 536870912 bytes: No space left on device
In my postgresql.conf I set sharred_buffers=256MB, I see that it is applied:
SHOW shared_buffers;
shared_buffers
----------------
256MB
At the same time during the query execution, I see a lot of files in /dev/shm with the total size more than 256MB
ls -lh /dev/shm
total 1.4G
-rw------- 1 postgres postgres 193K Jul 8 08:39 PostgreSQL.1026343462
-rw------- 1 postgres postgres 4.0M Jul 8 10:23 PostgreSQL.1066463809
-rw------- 1 postgres postgres 8.0M Jul 8 10:23 PostgreSQL.1154587693
-rw------- 1 postgres postgres 4.0M Jul 8 08:47 PostgreSQL.1292794952
-rw------- 1 postgres postgres 128M Jul 8 10:23 PostgreSQL.130026740
-rw------- 1 postgres postgres 8.0M Jul 8 10:23 PostgreSQL.1377271816
-rw------- 1 postgres postgres 16M Jul 8 10:23 PostgreSQL.1453162423
-rw------- 1 postgres postgres 128M Jul 8 10:23 PostgreSQL.1496397787
-rw------- 1 postgres postgres 8.0M Jul 8 10:23 PostgreSQL.1541518903
-rw------- 1 postgres postgres 64M Jul 8 10:23 PostgreSQL.1554139410
-rw------- 1 postgres postgres 2.0M Jul 8 10:23 PostgreSQL.1563273542
-rw------- 1 postgres postgres 256M Jul 8 10:23 PostgreSQL.1604524364
-rw------- 1 postgres postgres 64M Jul 8 10:23 PostgreSQL.1624127960
-rw------- 1 postgres postgres 4.0M Jul 8 10:23 PostgreSQL.1674892421
-rw------- 1 postgres postgres 128M Jul 8 10:32 PostgreSQL.179085785
-rw------- 1 postgres postgres 32M Jul 8 10:23 PostgreSQL.1921401343
-rw------- 1 postgres postgres 32M Jul 8 10:23 PostgreSQL.1931571650
-rw------- 1 postgres postgres 2.0M Jul 8 10:23 PostgreSQL.2002232858
-rw------- 1 postgres postgres 2.0M Jul 8 10:23 PostgreSQL.2057901523
-rw------- 1 postgres postgres 2.0M Jul 8 10:23 PostgreSQL.2110310896
-rw------- 1 postgres postgres 65M Jul 8 08:39 PostgreSQL.2136390898
-rw------- 1 postgres postgres 182K Jul 8 08:39 PostgreSQL.261370809
-rw------- 1 postgres postgres 16M Jul 8 10:23 PostgreSQL.397419407
-rw------- 1 postgres postgres 4.0M Jul 8 10:23 PostgreSQL.431734656
-rw------- 1 postgres postgres 128M Jul 8 10:23 PostgreSQL.478359814
-rw------- 1 postgres postgres 8.0M Jul 8 10:23 PostgreSQL.489042863
-rw------- 1 postgres postgres 64M Jul 8 10:23 PostgreSQL.590987495
-rw------- 1 postgres postgres 3.4M Jul 8 08:39 PostgreSQL.62466476
-rw------- 1 postgres postgres 16M Jul 8 10:23 PostgreSQL.629400316
-rw------- 1 postgres postgres 6.8K Jul 8 08:39 PostgreSQL.741718396
-rw------- 1 postgres postgres 16M Jul 8 10:23 PostgreSQL.770970033
-rw------- 1 postgres postgres 1.0M Jul 8 10:23 PostgreSQL.776045115
-rw------- 1 postgres postgres 64M Jul 8 10:23 PostgreSQL.795742467
-rw------- 1 postgres postgres 32M Jul 8 10:23 PostgreSQL.835134775
-rw------- 1 postgres postgres 32M Jul 8 10:23 PostgreSQL.953710812
How can I configure limit for total shared memory size?
Best regards,
Konstantin
* Konstantin Malanchev <hombit@gmail.com> [2019-07-09 11:51]:
Hello,
I'm running PostgreSQL 11.4 on Linux 4.12.14 and I see the following issue while executing single one query:
ERROR: could not resize shared
memory segment "/PostgreSQL.1596105766" to 536870912 bytes: No space left on deviceIn my postgresql.conf I set sharred_buffers=256MB, I see that it is applied:
SHOW shared_buffers;
shared_buffers
----------------
256MBAt the same time during the query execution, I see a lot of files in /dev/shm with the total size more than 256MB
ls -lh /dev/shm
How can I configure limit for total shared memory size?
The limit is mostly set by the memory, as /dev/shm
is like virtual memory or RAM disk.
Increase the RAM.
Jean
Hello Jean,
I have 8 GB RAM and /dev/shm size is 4GB, and there is no significant memory usage by other system processes. I surprised that Postgres uses more space in /dev/shm than sharred_buffers parameter allows, probably I don't understand what this parameter means.
I have no opportunity to enlarge total RAM and probably this query requires too much RAM to execute. Should Postgres just use HDD as temporary storage in this case?
Konstantin
Show quoted text
On 9 Jul 2019, at 12:53, Jean Louis <bugs@gnu.support> wrote:
* Konstantin Malanchev <hombit@gmail.com <mailto:hombit@gmail.com>> [2019-07-09 11:51]:
Hello,
I'm running PostgreSQL 11.4 on Linux 4.12.14 and I see the following issue while executing single one query:
ERROR: could not resize shared
memory segment "/PostgreSQL.1596105766" to 536870912 bytes: No space left on deviceIn my postgresql.conf I set sharred_buffers=256MB, I see that it is applied:
SHOW shared_buffers;
shared_buffers
----------------
256MBAt the same time during the query execution, I see a lot of files in /dev/shm with the total size more than 256MB
ls -lh /dev/shm
How can I configure limit for total shared memory size?
The limit is mostly set by the memory, as /dev/shm
is like virtual memory or RAM disk.Increase the RAM.
Jean
* Konstantin Malanchev <hombit@gmail.com> [2019-07-09 12:10]:
Hello Jean,
I have 8 GB RAM and /dev/shm size is 4GB, and there is no significant memory usage by other system processes. I surprised that Postgres uses more space in /dev/shm than sharred_buffers parameter allows, probably I don't understand what this parameter means.
I have no opportunity to enlarge total RAM and probably this query requires too much RAM to execute. Should Postgres just use HDD as temporary storage in this case?
That I cannot know. I know that /dev/shm could
grow as much as available free RAM.
Jean
On Tue, Jul 9, 2019 at 10:15 PM Jean Louis <bugs@gnu.support> wrote:
* Konstantin Malanchev <hombit@gmail.com> [2019-07-09 12:10]:
I have 8 GB RAM and /dev/shm size is 4GB, and there is no significant memory usage by other system processes. I surprised that Postgres uses more space in /dev/shm than sharred_buffers parameter allows, probably I don't understand what this parameter means.
I have no opportunity to enlarge total RAM and probably this query requires too much RAM to execute. Should Postgres just use HDD as temporary storage in this case?
That I cannot know. I know that /dev/shm could
grow as much as available free RAM.
Hi,
PostgreSQL creates segments in /dev/shm for parallel queries (via
shm_open()), not for shared buffers. The amount used is controlled by
work_mem. Queries can use up to work_mem for each node you see in the
EXPLAIN plan, and for each process, so it can be quite a lot if you
have lots of parallel worker processes and/or lots of
tables/partitions being sorted or hashed in your query.
--
Thomas Munro
https://enterprisedb.com
Hello Thomas,
Thank you for explanation. work_mem = 512MB and max_parallel_workers_per_gather = 2 and I run only one Postgres instance and only one query. EXPLAIN shows "Workers Planned: 2" for this query. Why it can use more than 1GB of /dev/shm?
Konstantin
Show quoted text
On 9 Jul 2019, at 13:51, Thomas Munro <thomas.munro@gmail.com> wrote:
On Tue, Jul 9, 2019 at 10:15 PM Jean Louis <bugs@gnu.support> wrote:
* Konstantin Malanchev <hombit@gmail.com> [2019-07-09 12:10]:
I have 8 GB RAM and /dev/shm size is 4GB, and there is no significant memory usage by other system processes. I surprised that Postgres uses more space in /dev/shm than sharred_buffers parameter allows, probably I don't understand what this parameter means.
I have no opportunity to enlarge total RAM and probably this query requires too much RAM to execute. Should Postgres just use HDD as temporary storage in this case?
That I cannot know. I know that /dev/shm could
grow as much as available free RAM.Hi,
PostgreSQL creates segments in /dev/shm for parallel queries (via
shm_open()), not for shared buffers. The amount used is controlled by
work_mem. Queries can use up to work_mem for each node you see in the
EXPLAIN plan, and for each process, so it can be quite a lot if you
have lots of parallel worker processes and/or lots of
tables/partitions being sorted or hashed in your query.--
Thomas Munro
https://enterprisedb.com
On Tue, Jul 9, 2019 at 11:11 PM Konstantin Malanchev <hombit@gmail.com> wrote:
Thank you for explanation. work_mem = 512MB and max_parallel_workers_per_gather = 2 and I run only one Postgres instance and only one query. EXPLAIN shows "Workers Planned: 2" for this query. Why it can use more than 1GB of /dev/shm?
For example, if you have one Parallel Hash Join in your plan, it could
allocate up to 512MB * 3 of shared memory (3 = leader process + 2
workers). It sounds like you'll need to set work_mem smaller. If you
run EXPLAIN ANALYZE you'll see how much memory is used by individual
operations. Usually it's regular private anonymous memory, but for
Parallel Hash it's /dev/shm memory.
--
Thomas Munro
https://enterprisedb.com
Thank you!
For example, if you have one Parallel Hash Join in your plan, it could
allocate up to 512MB * 3 of shared memory (3 = leader process + 2
workers).
I'm executing the query with smaller work_mem, it will take some time. But I still confused why it used all /dev/shm (4GB) and fails with "no space left" error while work_mem = 512MB.
If you
run EXPLAIN ANALYZE you'll see how much memory is used by individual
operations.
I cannot run EXPLAIN ANALYSE, because the query fails. This is explanation for the query:
EXPLAIN
CREATE MATERIALIZED VIEW IF NOT EXISTS new_mat_view
AS
SELECT * FROM my_view
INNER JOIN another_mat_view USING (oid)
ORDER BY oid, field_name;
Gather Merge (cost=5696039356565.87..10040767101103.24 rows=37237923518438 width=31)
Workers Planned: 2
-> Sort (cost=5696039355565.85..5742586759963.90 rows=18618961759219 width=31)
Sort Key: my_table.oid, my_table.field_name
-> Parallel Hash Join (cost=11030236131.39..255829470118.27 rows=18618961759219 width=31)
Hash Cond: (another_mat_view.oid = my_table.oid)
-> Parallel Seq Scan on another_mat_view (cost=0.00..652514.56 rows=31645556 width=8)
-> Parallel Hash (cost=636676233.38..636676233.38 rows=20353804801 width=31)
-> Parallel Seq Scan on my_table (cost=0.00..636676233.38 rows=20353804801 width=31)
Filter: (flag = '0000000000000000'::bit(16))
Konstantin
On Wed, Jul 10, 2019 at 12:27 AM Konstantin Malanchev <hombit@gmail.com> wrote:
I'm executing the query with smaller work_mem, it will take some time. But I still confused why it used all /dev/shm (4GB) and fails with "no space left" error while work_mem = 512MB.
I think it could fail that way for two reasons: /dev/shm size limit
(mount option, which I think you are saying you have set to 4GB?), or
your system ran out of RAM +swap. The directly listing in your first
message only shows 1.4GB, not 4GB, so perhaps it's the second problem.
-> Parallel Hash Join (cost=11030236131.39..255829470118.27 rows=18618961759219 width=31)
Hash Cond: (another_mat_view.oid = my_table.oid)
-> Parallel Seq Scan on another_mat_view (cost=0.00..652514.56 rows=31645556 width=8)
-> Parallel Hash (cost=636676233.38..636676233.38 rows=20353804801 width=31)
-> Parallel Seq Scan on my_table (cost=0.00..636676233.38 rows=20353804801 width=31)
Filter: (flag = '0000000000000000'::bit(16))
It's strange that it's hashing the ~20B row table instead of the ~30M row table.
--
Thomas Munro
https://enterprisedb.com
I think it could fail that way for two reasons: /dev/shm size limit
(mount option, which I think you are saying you have set to 4GB?), or
your system ran out of RAM +swap.
df /dev/shm
Filesystem 1K-blocks Used Available Use% Mounted on
shm 4194304 351176 3843128 9% /dev/shm
mount | grep /dev/shm
shm on /dev/shm type tmpfs (rw,nosuid,nodev,noexec,relatime,size=4194304k)
I have no swap and 8GB of RAM, when there is no active queries only ~800MB of RAM is used. So I don't believe that it is out of memory problem.
The directly listing in your first
message only shows 1.4GB, not 4GB, so perhaps it's the second problem.
I cannot catch the right moment with ls, but I've run bash for-loop that that logs "df /dev/shm" every minute and the last entry before fail shows that 89% of /dev/shm is used:
Filesystem 1K-blocks Used Available Use% Mounted on
shm 4194304 3732368 461936 89% /dev/shm
There is no other processes that use /dev/shm.
It's strange that it's hashing the ~20B row table instead of the ~30M row table.
It could be a question for another mail thread =)
Konstantin