Postgres 11 ERROR could not resize shared memory segment

[Nick Fellows]

We just upgraded to the new Postgres 11 image from 9.6 and are dealing with a flood of the below error (with varying memory sizes) across multiple queries:

org.postgresql.util.PSQLException: ERROR: could not resize shared memory segment "/PostgreSQL.78044234" to 2097152 bytes: No space left on device

I know that there were a lot of changes under the hood in PG 10 that potentially account for this, and it appears that the most common solution to the problem is to increase shared memory, which seems to be impossible to do with Google Cloud SQL.  Is this a problem anyone else is dealing with, and if so, is there anything we can do on our end, outside of refactoring our queries to be simpler?

[Nick Fellows]

Quick update - setting the db flag random_page_cost to 1 seems to have gotten rid of the problems...for now at least.  I got the idea from this blog post - my understanding is that these instances are optimized for HDD even if SSD is used, resulting in terrible performance under some circumstances.  Changing random_page_cost to 1 fixes that.

[Eamon Keane]

@Nick

I got that too. Happened when doing certain queries. No particular discernible logic behind it, but possibly to do with the number of parallel workers planned.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/1e11eb50-a43a-417d-9013-08b34443a6e0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Eamon Keane]

Tried the random_page_cost but that didn't work for my query.

[diogoa...@google.com]

Please try the possible solutions suggested in this StackOverflow post.

[Nick]

That's my SOF post :) As I noted there 30gig / 8cpu seems like a pretty good size, if not serious overkill for our current needs.  And those same specs were working without issue under PG 9.6.

--

You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/23788ee1-6e15-4a8d-8306-dca836b238bb%40googlegroups.com.

[Eamon Keane]

That link discusses postgres. I've tried several postgres flags: work_mem 1000000000 temp_file_limit 1000000000 temp_buffers 100000000 parallel_setup_cost 1000000000 random_page_cost 1.

And also bumped from 2 cores to 8 cores to no effect. The main other discussion of this topic online discusses changing the "--shm-size" of the docker container as a resolution. I'm not sure how that is implemented by CloudSQL. Presumably it's a container running on ContainerOS probably with containerd. https://meta.discourse.org/t/pg-throws-could-not-resize-shared-memory-segment-error/84744

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/CAL1PA%2B8SkB%3D%2BJ_V8CBuLYpAaMubOGt0wXWxgEC9Jnrj1ro1bXw%40mail.gmail.com.

[Eamon Keane]

*discusses mysql

[Eamon Keane]

The solution for me was to hardcode max_parallel_workers_per_gather for my production user.

show max_parallel_workers_per_gather;

-- 2

-- Run your query

-- Query fails

alter user ${MY_PROD_USER} set max_parallel_workers_per_gather=0;

-- Run query again -- query should work

alter user ${MY_PROD_USER} set max_parallel_workers_per_gather=2;

-- -- Run query again -- fails