>... we run an unusual workload
ya, right. just make up some reason not following the best practices
Postgres Postmaster does not scale
61–70 of 94 posts
Re: Postgres Postmaster does not scale
#62Earlier quoted context omitted.
Sharding is often not easy. Depending on the application, it may add significant complexity to the application. For example, what do you do if you have data related to multiple customers? How do you handle customers of significantly different sizes? And that is assuming you have a solution for things like balancing, and routing to the correct shard.
deja vu did you comment exactly the same things some months ago?
Not that I recall
Re: Postgres Postmaster does not scale
#63> sudo echo $NUM_PAGES > /proc/sys/vm/nr_hugepages This won't work :) echo will run as root but the redirection is still running as the unprivileged user. Needs to be run from a privileged shell or by doing something like sudo sh -c "echo $NUM_PAGES > /proc/sys/vm/nr_hugepages" The point gets across, though, technicality notwithstanding.
Or echo $NUM_PAGES | sudo tee /proc/sys/vm/nr_hugepages I've always found it odd that there isn't a standard command to write stdin to a file that doesn't also write it to stdout. Or that tee doesn't have an option to supress writing to stdout.
I've always thought that there should be `cat -o output-file` flag for that. GNU coreutils have miriads of useless flags and missing one actually useful flag LoL.
And probably `echo -o output-file` as well.
Re: Postgres Postmaster does not scale
#64Earlier quoted context omitted.
can't believe postgres still uses a process-per-connection model that leads to endless problems like this one.
You can't process significantly many more queries than you've got CPU cores at the same time anyway.
I disagree. If that was the case, pgBouncer wouldn't need to exist.
The problem of resource usage for many connections is real.
Re: Postgres Postmaster does not scale
#65Earlier quoted context omitted.
You can't process significantly many more queries than you've got CPU cores at the same time anyway.
Much of the time in a transaction can reasonably be non-db-cpu time, be it io wait or be it client CPU processing between queries. Note I'm not talking about transactions that run >10 seconds, just ones with the queries themselves technically quite cheap. At 10% db-CPU-usage, you get a 1 second transaction from just 100ms of CPU.
In a properly optimized database absolute majority of queries will hit indices and most data will be in memory cache, so majority of transactions will be CPU or RAM bound. So increasing number of concurrent transactions will reduce throughput. There will be few transactions waiting for I/O, but if majority of transactions are waiting for I/O, it's either horrifically inefficient database or very non-standard usage.
Re: Postgres Postmaster does not scale
#66I'm a bit confused here, do they have a single database they're writing to? Wouldn't it be easier and more reliable to shard the data per customer?
Sharding is often not easy. Depending on the application, it may add significant complexity to the application. For example, what do you do if you have data related to multiple customers? How do you handle customers of significantly different sizes? And that is assuming you have a solution for things like balancing, and routing to the correct shard.
Presumably sharding is a lot easier than trying to debug lockups in individual postgres thread? It's well known, we've been doing it for at least 30+ years as an industry.
Re: Postgres Postmaster does not scale
#67> We record millions of meetings every week. My first thought was "why even use big databases, you have perfect workload to shard it between a bunch of instances and as a bonus any downtime would only affect smaller part of customers"
This is not a big database usecase. It just needs one to not do silly things like opening a new database session for every query when it's well documented that this is expensive.
Ha, .net does that automatically unless you really want not to - connection pooling I mean.
Re: Postgres Postmaster does not scale
#68Earlier quoted context omitted.
You can't process significantly many more queries than you've got CPU cores at the same time anyway.
I disagree. If that was the case, pgBouncer wouldn't need to exist. The problem of resource usage for many connections is real.
It's about queueing work, not running all these queries at the same time. You can run pgbouncer or you can have a pool on your backend. Having more connections won't make it go faster, so that really seems like a low-priority thing for postgres to me. Even if you integrated pooling into postgres the overhead of auth would be still taking time for small queries anyway.
Re: Postgres Postmaster does not scale
#69Earlier quoted context omitted.
Or echo $NUM_PAGES | sudo tee /proc/sys/vm/nr_hugepages I've always found it odd that there isn't a standard command to write stdin to a file that doesn't also write it to stdout. Or that tee doesn't have an option to supress writing to stdout.
You forgot the "sudo" before "tee" > write stdin to a file that doesn't also write it to stdout You mean like "dd of=/path/file" ?
I physically/literally squinted when I saw disk destroyer.
I know it's useful for other things, but it has become a fearful instinct at this point.
Re: Postgres Postmaster does not scale
#70Some a prime example of a service that naturally peaks at round hours. We have a habbit of never scheduling long running processes at round hours. Usually because they tend to be busier. https://hakibenita.com/sql-tricks-application-dba#dont-sched...
I wish more applications would adopt the "H" option that Jenkins uses in it's cron notation - essentially it is a randomiser, based on some sort of deterministic hashing function. So you say you want this job to run hourly and it will always run at the same minute past the hour, but you don't know (or care) what that minute that is. Designed to prevent the thundering herd problem with scheduled work.
systemd timers have this, and more.
https://www.freedesktop.org/software/systemd/man/latest/syst...