2 ms·

IME it's quite rare for the database to be the bottleneck. People just don't know how to use it right. It's a pretty simple optimization to gather web requests

IME it's quite rare for the database to be the bottleneck. People just don't know how to use it right. It's a pretty simple optimization to gather web requests into batches, and that can easily 10x your database throughput. At my last job we had architects pushing to do the opposite and turn batch files into single requests! Argh! Also if people are using RDS with EBS, they'll think storage IO is ~500x slower than a modern NVMe disk really is, which will warp their perception of how well an RDBMS should scale. Their gp3 SSD storage comes with 3k IOPS baseline up to 16k[0]. lol. "SSD storage". The volume size "scales" up to 16TB. You can get a new condition P5410 for $420 retail: 8 TB/drive with 100x the performance of a maxed out EBS volume. Similar misconceptions must exist with application performance. Lambda advertises being able to scale up to "tens of thousands" of concurrent requests[1]. My 6th gen i5 can do that with a few GB of RAM allocated to a single JVM process... [0] https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/general-purpose.html#gp3-performance https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/general-... [1] https://docs.aws.amazon.com/lambda/latest/dg/gettingstarted-limits.html https://docs.aws.amazon.com/lambda/latest/dg/gettingstarted-...

corethree 3y ago
Database is the primary bottleneck of almost all web apps. This is different from solving bottlenecks you may find in other places. Let me put it this way. If you did everything right, then the slowest part of your web application is usually the database. Now you may find problems in the web application and fix bottlenecks there but that's not what I'm talking about. I'm talking about the primary bottleneck. In web basically the database is doing all the work the web application is just letting data pass through. The database is so slow that the speed of the web application becomes largely irrelevant. That's why languages like ruby or python can be used for the application, that's why C++ is rarely needed. This ultimately makes sense logically. The memory hierarchy in terms of speed goes from CPU cache to random access to fs with fs being the slowest. Web applications primarily use fs as it's all just about manipulating and reading state from the db. while a triple A game primarily runs on random access. Games do use the fs but usually that's buffering done in the background or load screens. This is why your reply doesn't refute a single thing I said. It doesn't matter if dbs use nvme. FS is still slower than an in memory store by a huge magnitude. Not to mention the ipc or network connection is another bottleneck. Web apps need to only be trivially made magnitudes faster then then the ipc and database and it's fine. Your batch calls to the db are only optimizations to the db which is still magnitudes slower then ram. Try switching those batch requests to in memory storage on the same process and keep the storage from bleeding pages to the fs and that will be waaay faster. After you fix that the bottlenecks of most web apps lie in the http calls and parsing and deserializarion of the data. But usually nobody in web cares for that stuff because like I said the db is way slower. Do you think a game engine can afford to do this stupid extra parsing and serialization step in between the renderer and the world updater? Hell no. That's why web developers can put all kinds of crap in the http handlers, because it doesn't matter. Though I will say I have seen a bottleneck where parsing was actually slower than access to redis. But that's rare and redis is in-memory so it's mostly the ipc or network connection here.
- ndriscoll 3y ago
  I don't know how your performance expectations are calibrated, but for example on my i5 (4 cores) using a SATA SSD, I can get ~100k "Hello World" web requests/second, and ~60-70k web requests that update the database/second. The database can do ~300k updates/second on my hardware by doing something like generating synthetic updates in SQL, or updating by joining to another table, or doing a LOAD DATA INFILE. Obviously the app is the primary bottleneck (well, they both are; CPU time is the bottleneck. Using an NVMe disk doesn't move the needle much). That's without TLS on the http requests. Turns out json parsing has a noticable cost. Meanwhile I see posts about mastodon running into scaling issues with only a few hundred thousand users. Are they each doing 1+ toot/second average or something? 'cause postgres should not be a bottleneck on pretty much any computer with that few users. Nothing should be a bottleneck at that level.
  - deleted 3y ago
    [deleted]
  - corethree 3y ago
    Your measuring io. Db has to do io too which you likely aren't measuring. You are prob doing 300k writes with only one io call to indicate 300k was done. Then for the web app your doing 100k io calls. If you want to do an equivalent test on the web app you need to have the web app write to in memory like 300k times on one request. But that's not the best way to test. The full end to end test is the io call to the web app then processing in the web app then the io call from the web app to the db then processing in the db, then the return trip. Picture a timeline with different colored bars of different length with the length indicating the amount of time spent in each section of the full e2e tests. You will find that the web app should be fastest section. (It should also be divided into two for the initial request and the return trip with db access in between) Flamegraph generators can easily create this visualization. Also be sure to handle what the other replier said as well. Make sure the db isn't caching stuff. I would say a read test here is a good one. The web app parses a request, translates it to SQL, the. The SQL has to join and search through some tables and deliver results back to the user. I would run the profiling with many simultaneous requests as well. This isn't fully accurate though as write requests lock sections of the db which actually are a big slow down as well which may be too much work to simulate here.

corethree 3y ago

Database is the primary bottleneck of almost all web apps. This is different from solving bottlenecks you may find in other places. Let me put it this way. If you did everything right, then the slowest part of your web application is usually the database. Now you may find problems in the web application and fix bottlenecks there but that's not what I'm talking about. I'm talking about the primary bottleneck. In web basically the database is doing all the work the web application is just letting data pass through. The database is so slow that the speed of the web application becomes largely irrelevant. That's why languages like ruby or python can be used for the application, that's why C++ is rarely needed. This ultimately makes sense logically. The memory hierarchy in terms of speed goes from CPU cache to random access to fs with fs being the slowest. Web applications primarily use fs as it's all just about manipulating and reading state from the db. while a triple A game primarily runs on random access. Games do use the fs but usually that's buffering done in the background or load screens. This is why your reply doesn't refute a single thing I said. It doesn't matter if dbs use nvme. FS is still slower than an in memory store by a huge magnitude. Not to mention the ipc or network connection is another bottleneck. Web apps need to only be trivially made magnitudes faster then then the ipc and database and it's fine. Your batch calls to the db are only optimizations to the db which is still magnitudes slower then ram. Try switching those batch requests to in memory storage on the same process and keep the storage from bleeding pages to the fs and that will be waaay faster. After you fix that the bottlenecks of most web apps lie in the http calls and parsing and deserializarion of the data. But usually nobody in web cares for that stuff because like I said the db is way slower. Do you think a game engine can afford to do this stupid extra parsing and serialization step in between the renderer and the world updater? Hell no. That's why web developers can put all kinds of crap in the http handlers, because it doesn't matter. Though I will say I have seen a bottleneck where parsing was actually slower than access to redis. But that's rare and redis is in-memory so it's mostly the ipc or network connection here.

ndriscoll 3y ago
I don't know how your performance expectations are calibrated, but for example on my i5 (4 cores) using a SATA SSD, I can get ~100k "Hello World" web requests/second, and ~60-70k web requests that update the database/second. The database can do ~300k updates/second on my hardware by doing something like generating synthetic updates in SQL, or updating by joining to another table, or doing a LOAD DATA INFILE. Obviously the app is the primary bottleneck (well, they both are; CPU time is the bottleneck. Using an NVMe disk doesn't move the needle much). That's without TLS on the http requests. Turns out json parsing has a noticable cost. Meanwhile I see posts about mastodon running into scaling issues with only a few hundred thousand users. Are they each doing 1+ toot/second average or something? 'cause postgres should not be a bottleneck on pretty much any computer with that few users. Nothing should be a bottleneck at that level.
- deleted 3y ago
  [deleted]
- corethree 3y ago
  Your measuring io. Db has to do io too which you likely aren't measuring. You are prob doing 300k writes with only one io call to indicate 300k was done. Then for the web app your doing 100k io calls. If you want to do an equivalent test on the web app you need to have the web app write to in memory like 300k times on one request. But that's not the best way to test. The full end to end test is the io call to the web app then processing in the web app then the io call from the web app to the db then processing in the db, then the return trip. Picture a timeline with different colored bars of different length with the length indicating the amount of time spent in each section of the full e2e tests. You will find that the web app should be fastest section. (It should also be divided into two for the initial request and the return trip with db access in between) Flamegraph generators can easily create this visualization. Also be sure to handle what the other replier said as well. Make sure the db isn't caching stuff. I would say a read test here is a good one. The web app parses a request, translates it to SQL, the. The SQL has to join and search through some tables and deliver results back to the user. I would run the profiling with many simultaneous requests as well. This isn't fully accurate though as write requests lock sections of the db which actually are a big slow down as well which may be too much work to simulate here.