4 ms·

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

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.
  - ndriscoll 3y ago
    My point is instead of doing 100k transactions in your web app, you should look at how to gather them into batches. Think of how an old school bank mainframe programmer would do something, then realize you can do that every couple milliseconds so it feels realtime. If you submit a batch every 5ms, then on average you are adding 2.5 ms of latency per request sitting in the batch buffer. But with 100k requests/second for example, you are getting batches of 500, which will get a lot more out of your database. Realistically, most people have far fewer than 100k requests/second, but the idea works at lower levels too. Even batches of 10 will give you a good boost. If you don't have enough requests/second to put together 10 requests/5 ms batch (2k req/s), then why worry about bottlenecks? Wall time spent on a single request is the wrong way to understand performance for scalability. You can amortize wall time across many requests. Also your db should be caching stuff for many workloads. Reddit gets about 80 GB/month of new posts/comments based on scraped dumps. An entire month of threads should fit in buffer pool cache no problem. If they really wanted to, their whole text history is only ~2.5TB, which you can fit in RAM for a relatively small amount of money compared to an engineer's salary. They should be getting very high cache hit ratios. Workloads with smaller working sets should always be cached.

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.
- ndriscoll 3y ago
  My point is instead of doing 100k transactions in your web app, you should look at how to gather them into batches. Think of how an old school bank mainframe programmer would do something, then realize you can do that every couple milliseconds so it feels realtime. If you submit a batch every 5ms, then on average you are adding 2.5 ms of latency per request sitting in the batch buffer. But with 100k requests/second for example, you are getting batches of 500, which will get a lot more out of your database. Realistically, most people have far fewer than 100k requests/second, but the idea works at lower levels too. Even batches of 10 will give you a good boost. If you don't have enough requests/second to put together 10 requests/5 ms batch (2k req/s), then why worry about bottlenecks? Wall time spent on a single request is the wrong way to understand performance for scalability. You can amortize wall time across many requests. Also your db should be caching stuff for many workloads. Reddit gets about 80 GB/month of new posts/comments based on scraped dumps. An entire month of threads should fit in buffer pool cache no problem. If they really wanted to, their whole text history is only ~2.5TB, which you can fit in RAM for a relatively small amount of money compared to an engineer's salary. They should be getting very high cache hit ratios. Workloads with smaller working sets should always be cached.