Earlier quoted context omitted.
why not write sh -c then?
Because 'sh' isn't in moreutils and is harder to remember, next question. Why use something portable when you can use cat pee?
Personally? Because I hate the smell.
Postgres Postmaster does not scale
71–80 of 94 posts
Re: Postgres Postmaster does not scale
#72Good reminder to always remember Chesterton's Fence. The post indicates that the bottleneck occurs when "many thousands" of EC2 instances are connecting simultaneously. In order for this to happen, presumably someone had to turn `max_connections` way up on their database server to make this to work at all. Seems like the issue could have been avoided at that point with a bit more understanding about why the default is an order of magnitude or more lower than whatever they tuned it to.
Re: Postgres Postmaster does not scale
#73Earlier quoted context omitted.
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.
Your arguments make sense for concurrent queries (though high-latency storage like S3 is becoming increasingly popular, especially for analytic loads).
But transactions aren't processing queries all the time. Often the application will do processing between sending queries to the database. During that time a transaction is open, but doesn't do any work on the database server.
Re: Postgres Postmaster does not scale
#74One of the many problems PgDog will solve for you!
The article addresses this, sort of. I don't understand how you can run multiple postmasters. > Most online resources chalk this up to connection churn, citing fork rates and the pid-per-backend yada, yada. This is all true but in my opinion misses the forest from the trees. The real bottleneck is the single-threaded main loop in the postmaster. Every operation requiring postmaster involvement is pulling from a fixed…
> I don't understand how you can run multiple postmasters.
I believe they're just referring to having several completely-independent postgres instances on the same host.
In other words: say that postgres is maxing out at 2000 conns/sec. If the bottleneck actually was fork rate on the host, then having 2 independent copies of postgres on a host wouldn't improve the total number of connections per second that could be handled: each instance would max out at ~1000 conns/sec, since they're competing for process-spawning. But in reality that isn't the case, indicating that the fork rate isn't the bottleneck.
Re: Postgres Postmaster does not scale
#75Earlier quoted context omitted.
The article addresses this, sort of. I don't understand how you can run multiple postmasters. > Most online resources chalk this up to connection churn, citing fork rates and the pid-per-backend yada, yada. This is all true but in my opinion misses the forest from the trees. The real bottleneck is the single-threaded main loop in the postmaster. Every operation requiring postmaster involvement is pulling from a fixed…
> I don't understand how you can run multiple postmasters. I believe they're just referring to having several completely-independent postgres instances on the same host. In other words: say that postgres is maxing out at 2000 conns/sec. If the bottleneck actually was fork rate on the host , then having 2 independent copies of postgres on a host wouldn't improve the total number of connections per second that could be…
That makes sense, thanks.
Re: Postgres Postmaster does not scale
#76Why do you need a connection to a database during the meeting? Doesn't it make more sense to record the meeting data to some local state first, and then serialize it to database at the end of the meeting or when a database connection is available? Or better yet, have a lightweight API service that can be scaled horizontally that is responsible for talking to the database and maintains its own pool of connections. The…
It took me a long time to realize this but yes asking people to just open and write to files (or S3) is in fact asking a lot.
What you describe makes sense, of course, but few can build it without it being drastically worse than abusing a database like postgres. It's a sad state of affairs.
Re: Postgres Postmaster does not scale
#77From the article: > The real bottleneck is the single-threaded main loop in the postmaster. A single-threaded event loop can do a lot of stuff. Certainly handle 4000 tasks of some sort in under 10s. Just offhand it seems like it would be eminently possible to handle incoming connections on the scale they describe in a single-threaded event loop. Clearly the existing postgres postmaster thread is a bottleneck as it is…
Servers usually have massive amounts of cores that are individually slow. Not surprised that single threading would be a bottleneck
Re: Postgres Postmaster does not scale
#78Earlier quoted context omitted.
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.
Your arguments make sense for concurrent queries (though high-latency storage like S3 is becoming increasingly popular, especially for analytic loads). But transactions aren't processing queries all the time. Often the application will do processing between sending queries to the database. During that time a transaction is open, but doesn't do any work on the database server.
It is bad application architecture. Database work should be concentrated in minimal transactional units and connection should be released between these units. All data should be prepared before unit start and additional processing should take place after transaction ended. Using long transactions will cause locks, even deadlocks and generally should be avoided. That's my experience at least. Sometimes business transaction should be split into several database transaction.
Re: Postgres Postmaster does not scale
#79> 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.
It’s not an option or feature because it’s built into the shell. Just slap a > /dev/null on the end.
Re: Postgres Postmaster does not scale
#80Earlier 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.
A process that is blocked for io, whether network or disk, will get taken off the cpu and another process put on the cpu. It doesn’t just waste the cpu until the quanta is gone.