Google Cloud SQL PG11 : could not resize shared memory segment

Question

I recently upgraded a Postgres 9.6 instance to 11.1 on Google Cloud SQL. Since then I've begun to notice a large number of the following error across multiple queries:

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

From what I've read, this is probably due to changes that came in PG10, and the typical solution involves increasing the instance's shared memory. To my knowledge this isn't possible on Google Cloud SQL though. I've also tried adjusting work_mem with no positive effect.

This may not matter, but for completeness, the instance is configured with 30 gigs of RAM, 120 gigs of SSD hd space and 8 CPU's. I'd assume that Google would provide an appropriate shared memory setting for those specs, but perhaps not? Any ideas?

UPDATE Setting the database flag random_page_cost to 1 appears to have reduced the impact the issue. This isn't a full solution though so would still love to get a proper fix if one is out there. Credit goes to this blog post for the idea.

UPDATE 2 The original issue report was closed and a new internal issue that isnt viewable by the public was created. According to a GCP Account Manager's email reply however, a fix was rolled out by Google on 8/11/2019.

Answer 1

This worked for me, I think google needs to change a flag on how they're starting the postgres container on their end that we can't influence inside postgres.

https://www.postgresql.org/message-id/CAEepm%3D2wXSfmS601nUVCftJKRPF%3DPRX%2BDYZxMeT8M2WwLSanVQ%40mail.gmail.com

Bingo. Somehow your container tech is limiting shared memory. That error is working as designed. You could figure out how to fix the mount options, or you could disable parallelism with max_parallel_workers_per_gather = 0.

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

Answer 2

You may consider increasing Tier of the instance, that will have influence on machine memory, vCPU cores, and resources available to your Cloud SQL instance. Check available machine types

In Google Cloud SQL PostgreSQL is also possible to change database flags, that have influence on memory consumption:

• max_connections: some memory resources can be allocated per-client, so the maximum number of clients suggests the maximum possible memory use
• shared_buffers: determines how much memory is dedicated to PostgreSQL to use for caching data
• autovacuum - should be on.

I recommend lowering the limits, to lower memory consumption.