Обсуждение: 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 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 > > 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
On 9 Jul 2019, at 12:53, Jean Louis <bugs@gnu.support> wrote:* 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 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
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 surprisedthat Postgres uses more space in /dev/shm than sharred_buffers parameter allows, probably I don't understand whatthis parameter means. > > I have no opportunity to enlarge total RAM and probably this query requires too much RAM to execute. Should Postgres justuse 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 surprisedthat Postgres uses more space in /dev/shm than sharred_buffers parameter allows, probably I don't understand whatthis parameter means. > > > > I have no opportunity to enlarge total RAM and probably this query requires too much RAM to execute. Should Postgresjust 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 instanceand only one query. EXPLAIN shows "Workers Planned: 2" for this query. Why it can use more than 1GB of /dev/shm? Konstantin > 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 surprisedthat Postgres uses more space in /dev/shm than sharred_buffers parameter allows, probably I don't understand whatthis parameter means. >>> >>> I have no opportunity to enlarge total RAM and probably this query requires too much RAM to execute. Should Postgresjust 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 instanceand 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 isout 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 lastentry 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