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.

Odiliaodille

thanks @Nick. So is everything working for you? Does the fix apply to old instances? – Plutus

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

Plutus

yeah that def works. really really hope google can tweak their image a bit tho, since this seems like it kills a big chunk of the performance boost of upgrading to pg11. – Odiliaodille

agreed! Partitions is the big win in the short term for me so I can prob live with it if there's no other issues – Plutus

accepting this answer for now. it's not ideal but it does work! – Odiliaodille

Also I went ahead and filed a bug on this with Google about a month ago. Looks like it got assigned to someone but who knows how long until it gets looked at. issuetracker.google.com/issues/132367953 I dont know if it will actually help but if you're impacted by this, starring the linked issue might raise priority and speed up the time to resolution. – Odiliaodille

cool, starred it. Just gone live in production with 11, fine apart from that (fingers crossed!). – Plutus

looks like google acknowledged the issue and has begun working on it! – Odiliaodille

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.

Almuce

I really hope my tier instance isn't the issue, because 30gigs / 8cpu's isn't exactly tiny. Do you have any specific suggestions as far as what to tune those db flags to? – Odiliaodille

@PawelCzuczwara The link you posted is for mysql. 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 – Plutus

Should you not try to lower the limits? sorry for that, those tiers are for all database types: cloud.google.com/sql/pricing – Almuce