Re: PGSQL 11.4: shared_buffers and /dev/shm size

On Wed, Jul 10, 2019 at 12:27 AM Konstantin Malanchev <hombit(at)gmail(dot)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