Re: [google-cloud-sql-discuss] Re: Postgres 11 ERROR could not resize shared memory segment

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

On Wed, Apr 24, 2019 at 2:43 PM Eamon Keane <eamon.keane1@gmail.com> wrote:

*discusses mysql

On Wed, Apr 24, 2019 at 2:43 PM Eamon Keane <eamon.keane1@gmail.com> wrote:

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

On Wed, Apr 24, 2019 at 1:53 PM Nick <halfhp@gmail.com> wrote:

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.

On Tue, Apr 23, 2019 at 10:25 PM diogoalmeida via Google Cloud SQL discuss <google-cloud-sql-discuss@googlegroups.com> wrote:

Please try the possible solutions suggested in this StackOverflow post.

On Tuesday, April 23, 2019 at 4:00:06 AM UTC-4, Eamon Keane wrote:

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

On Tuesday, April 23, 2019 at 3:31:23 AM UTC+1, Nick Fellows wrote:

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?

--
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-discuss+unsubscribe@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.
For more options, visit https://groups.google.com/d/optout.

--
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-discuss+unsubscribe@googlegroups.com.
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.
For more options, visit https://groups.google.com/d/optout.

--
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-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/CACvXZjdTpwi48BpHheVFn5dnQuYfTr80T_E8fyY8qv3RzUQFYw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.