4 ms·

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/s

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.
  - saltcured 3y ago
    > My point is instead of doing 100k transactions in your web app, you should look at how to gather them into batches. This sounds odd to me. Assuming 100k independent web requests, a reasonable web app ought to be 100k transactions, and the database is the bottleneck. Suggesting that the web app should rework all this into one bulk transaction is ignoring the supposed value-add of the database abstraction and re-implementing it in the web app layer. And most attempts are probably going to do it poorly. They're going to fail at maintaining the coherence and reliability of the naive solution that leaves this work to the database. Of course, one ought to design appropriate bulk operations in a web app where there is really one client trying to accomplish batches of things. Then, you can also push the batch semantics up the stack, potentially all the way to the UX. But that's a lot different than going through heroics to merge independent client request streams into batches while telling yourself the database is not actually the bottleneck...

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.
- saltcured 3y ago
  > My point is instead of doing 100k transactions in your web app, you should look at how to gather them into batches. This sounds odd to me. Assuming 100k independent web requests, a reasonable web app ought to be 100k transactions, and the database is the bottleneck. Suggesting that the web app should rework all this into one bulk transaction is ignoring the supposed value-add of the database abstraction and re-implementing it in the web app layer. And most attempts are probably going to do it poorly. They're going to fail at maintaining the coherence and reliability of the naive solution that leaves this work to the database. Of course, one ought to design appropriate bulk operations in a web app where there is really one client trying to accomplish batches of things. Then, you can also push the batch semantics up the stack, potentially all the way to the UX. But that's a lot different than going through heroics to merge independent client request streams into batches while telling yourself the database is not actually the bottleneck...