Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL, Memory and the Cloud

sosna.de

Post reply on HN
Print view View original

31–40 of 60 posts

Re: PostgreSQL, Memory and the Cloud

#31

Post by yashap »

aeyes wrote: post #8

Wow, the title of this post is very calm compared to what is actually happening. CloudSQL Postgres is running with a misconfigured OS OOM killer, crashes Postmaster randomly even if memory use is below instance spec. GCP closes this bug report as "Won't fix". This is a priority 1 issue. Seeing a wontfix for this has completely destroyed my trust of their judgement. The bug report states that they have been in contact…

So many GCP products are surprisingly terrible. Certainly not all of them, some are really good, like GKE, Cloud Storage and Cloud Load Balancer. But Cloud SQL is pretty weak, and products like Cloud Logging, Cloud Metrics and Cloud Tracing are legitimately terrible. Cloud NAT is pretty sketchy too, and can lead to a lot of egress issues if not configured perfectly.

My current workplace uses GCP, my last workplace used AWS, and personally I’ve found AWS to have much higher average quality. At my current workplace we’ve stopped using Cloud SQL, and moved our Postgres usage to Aiven (with VPC peering). Aiven seem to do a much better job operating Postgres than GCP do.

Re: PostgreSQL, Memory and the Cloud

#32

Post by jashmatthews »

jeffbee wrote: post #28
thyrsus wrote: post #10

Are there recommendations for learning about Linux kernel memory management? Two anecdata: * I had some compute servers that were up for 200 days. The customers noticed that they were half as fast as identical hardware just booted. Dropping the file system cache ("echo 3 | sudo dd of=/proc/sys/vm/drop_cache") brought the speed back up to the newly deployed servers. WTF? File system caches are supposed to be zero cost…

Explicit hugepages on x86 are difficult to manage. Most people using off-the-shelf software can only take advantage of it by configuring, for example, innodb buffer pools to use them. However if your compute server really is a database, then you'll find the performance benefit is well worth the configuration. For other processes you'll need a hugepage-aware allocator such as tcmalloc (the new one, not the old one) an…

THP is a net loss for many workloads, including PG https://www.percona.com/blog/2019/03/06/settling-the-myth-of...

For workload using forking and CoW sharing like Redis or CRuby it negates the entire benefit of CoW since flipping a single bit copies the entire huge page.

Re: PostgreSQL, Memory and the Cloud

#33

Post by jeffbee »

jashmatthews wrote: post #32
jeffbee wrote: post #28

Earlier quoted context omitted.

Explicit hugepages on x86 are difficult to manage. Most people using off-the-shelf software can only take advantage of it by configuring, for example, innodb buffer pools to use them. However if your compute server really is a database, then you'll find the performance benefit is well worth the configuration. For other processes you'll need a hugepage-aware allocator such as tcmalloc (the new one, not the old one) an…

THP is a net loss for many workloads, including PG https://www.percona.com/blog/2019/03/06/settling-the-myth-of... For workload using forking and CoW sharing like Redis or CRuby it negates the entire benefit of CoW since flipping a single bit copies the entire huge page.

That's what used to happen but since kernel 5.8, anonymous shared pages that are dirtied by child processes are instead divided into normal pages, in the same way they would be if they were named (file-backed) mappings.

Re: PostgreSQL, Memory and the Cloud

#34

Post by Winsaucerer »

Are there any good/recommended books or resources for someone who wants to learn how to run postgresql well? E.g, what defaults to change and when, settings for the host OS (such as in the parent linked article), overall tips/insights/recommendations.

Re: PostgreSQL, Memory and the Cloud

#35

Post by Sytten »

aeyes wrote: post #8

Wow, the title of this post is very calm compared to what is actually happening. CloudSQL Postgres is running with a misconfigured OS OOM killer, crashes Postmaster randomly even if memory use is below instance spec. GCP closes this bug report as "Won't fix". This is a priority 1 issue. Seeing a wontfix for this has completely destroyed my trust of their judgement. The bug report states that they have been in contact…

I concur with the other comments, Cloud SQL is a very mediocre service at best. Lot's of weird issues and the engineering team doesn't seem to care. We also had the segfault due to query insight. Just the fact that you can't upgrade your database version without creating a new instance and restoring a backup is just bad. I also suggest aiven as an alternative that works very well and cost is reasonable.

Re: PostgreSQL, Memory and the Cloud

#36

Post by yashap »

yashap wrote: post #31
aeyes wrote: post #8

Wow, the title of this post is very calm compared to what is actually happening. CloudSQL Postgres is running with a misconfigured OS OOM killer, crashes Postmaster randomly even if memory use is below instance spec. GCP closes this bug report as "Won't fix". This is a priority 1 issue. Seeing a wontfix for this has completely destroyed my trust of their judgement. The bug report states that they have been in contact…

So many GCP products are surprisingly terrible. Certainly not all of them, some are really good, like GKE, Cloud Storage and Cloud Load Balancer. But Cloud SQL is pretty weak, and products like Cloud Logging, Cloud Metrics and Cloud Tracing are legitimately terrible. Cloud NAT is pretty sketchy too, and can lead to a lot of egress issues if not configured perfectly. My current workplace uses GCP, my last workplace us…

An example of the kinds of quality issues you run into with so many GCP products: https://github.com/googleapis/cloud-trace-nodejs/issues/1272

Basically, their Cloud Tracing product is broken for modern Node/Postgres (in terms of showing PG queries and whatnot in traces), users have found the issue (and a seemingly super simple fix), but it’s been over a year and Google still haven’t fixed it. Google’s response is “yeah, we know pretty core functionality of this product is broken, but we’re not fixing it in the near future.” Or maybe ever? Many of their products feel semi-abandoned like this, especially in their observably stack - major bugs and/or performance issues that they never fix, and extremely limited features.

Cloud SQL isn’t terrible, but at least the Postgres version is one of the weaker managed Postgres offerings out there. And their whole observability stack (Logging/Monitoring/Tracing/Error Reporting) is legit terrible compared to competing products. Compared to other products I’ve used in the space, Cloud Logging is unbelievably worse than Sumo Logic, Cloud Metrics soooo much worse than Grafana+Prometheus, Cloud Tracing way worse than offerings from Datadog or New Relic, Cloud Error Reporting is ridiculously far behind Sentry, etc.

The GCP options are often quite cheap, but it shows in their extremely limited features, poor performance and plentiful bugs. Go with GCP for the things they do well, but don’t bother adopting their solution for everything simply to stick with one platform, as so many of their products are just so poor compared to competitors.

Re: PostgreSQL, Memory and the Cloud

#37

Post by thyrsus »

jeffbee wrote: post #28
thyrsus wrote: post #10

Are there recommendations for learning about Linux kernel memory management? Two anecdata: * I had some compute servers that were up for 200 days. The customers noticed that they were half as fast as identical hardware just booted. Dropping the file system cache ("echo 3 | sudo dd of=/proc/sys/vm/drop_cache") brought the speed back up to the newly deployed servers. WTF? File system caches are supposed to be zero cost…

Explicit hugepages on x86 are difficult to manage. Most people using off-the-shelf software can only take advantage of it by configuring, for example, innodb buffer pools to use them. However if your compute server really is a database, then you'll find the performance benefit is well worth the configuration. For other processes you'll need a hugepage-aware allocator such as tcmalloc (the new one, not the old one) an…

3rd party closed source software; I think it's using the C library malloc - which uses sbrk for small things, but uses mmap for >= 128k. Fun historical fact: the Red Hat/CentOS 5 kernel ulimit didn't limit mmap allocations :-/

Re: PostgreSQL, Memory and the Cloud

#39

Post by arcticfox »

aeyes wrote: post #8

Wow, the title of this post is very calm compared to what is actually happening. CloudSQL Postgres is running with a misconfigured OS OOM killer, crashes Postmaster randomly even if memory use is below instance spec. GCP closes this bug report as "Won't fix". This is a priority 1 issue. Seeing a wontfix for this has completely destroyed my trust of their judgement. The bug report states that they have been in contact…

I migrated off CloudSQL even when they tried to pay me to use it (startup credits). It's not worth risking your business with GCP. Sad, but that's what I've learned...

I'd consider Aiven if I were still on GCP and looking for a solid managed Postgres provider. As it is, I'm now on DigitalOcean and fairly happy with their managed Postgres offering, but there are a few rough edges so I'm actually still looking at Aiven even though everything else I have is on DO...

Re: PostgreSQL, Memory and the Cloud

#40

Post by lokedhs »

Diggsey wrote: post #5
yjftsjthsd-h wrote: post #2

So are there problems with disabling overcommit? Or is it really that simple (at least for dedicated db hosts)?

Some programs allocate a lot of virtual memory and then don't use it. Also, linux's forking model can result in a lot of virtual memory being allocated if a heavy-weight program tries to fork+exec a lot of smaller programs, since fork+exec it not atomic and briefly doubles the virtual memory usage of the original program. I think there are better ways to spawn programs that don't suffer from this problem now... If yo…

The fork issue is solved by adding swap. Making sure you have plenty of swap solves these issues, and I'd like to argue that it is more reliable than using overcommit.
Post reply on HN