Comment by ndriscoll | Hacker News Reader

Comment by ndriscoll | Hacker News Reader

Parent Full thread

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-...

[1] https://docs.aws.amazon.com/lambda/latest/dg/gettingstarted-...

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.

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.

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.

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.

> 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...

There aren't really heroics involved in doing this. e.g. in a promise-based framework, put your request onto a Queue[(RequestData, Promise)]. Await the promise as you would with a normal db request in your web handler. Have a db worker thread that pulls off the queue with a max wait time. Now pretend you received a batch file for your business logic. After processing the batch, complete the promises with their results. The basic scaffolding is maybe 20 LoC in a decent language/framework.

Much easier than alternatives like sharding or microservices or eventual consistency anyway.

Basic toy example I made a while back to demonstrate the technique using a Repository Pattern: https://gist.github.com/ndriscoll/6a1df10c8ea1a25c48c615c409...

I think somewhere I have a more built-out example with error handling and and where you have more than just "body" in the model. In the real world the business logic for processing the batch and error handling is going to be a decent amount more complicated. etc. etc. But the point is more about how the batching mechanic works.

The batch request just eliminated io. It doesn't change the speed of the database overall. It would make that e2e test I mentioned harder to measure as how would you profile the specific query related to the request within that batch? I can imagine an improvement in overall speed, but I don't think this is a common pattern and the time spent in the db will still be slower than the web app for all processing related to the request.

Also for the Cache... Its a hard thing to measure and define right? Because it can live anywhere. It can live in the web app or on another third party app and depending on where it is, there's another io rt cost.

Because of this Usually I don't classify the cache as part of the db for these types of benchmarking. I just classify actual hits to the fs as the db bottleneck.

I mean if everything just hits an in memory cache that's more of bottleneck bypass in my opinion. Definitely important to have cache but measuring time spent in the cache is not equivalent to measuring where the bottleneck of your application lives.

My point is

> Web developers can write shitty code because most of the time the bottleneck is in the database. You just need to write code thats faster then the database and you don't need to optimize beyond that.

Is the wrong way to look at things. Shitty code might bottleneck because of the way it uses the database. But that's like saying "you can write shitty code because it will bottleneck on global locks". But you can write better code that uses fine-grained locks or read-write locks or something, or uses a lock-free algorithm, and now it's not your bottleneck. Or you could write shitty code that bottlenecks on single byte unbuffered IO, or you could not do that and not have that bottleneck. It's the exact same idea, really. Write code with more efficient access patterns, and your database won't be as much of a bottleneck.

To say something is a bottleneck, you need to look at what it's capable of under optimal usage patterns. If you have 1TB of data to transfer, and you have a 10 Gb/s link, it's going to take you a little while. If you have 100 MB of data, but you have a tiny receive window and send small packets, it might take you a while, but you're not bottlenecking on the network. Your code just isn't using the network well.

And in the real world, if your working set is smaller than a few TB, then you shouldn't be doing disk IO on your read workloads. You definitely shouldn't be doing read IO if your working set is in the 10s of GB or less. That's not a "bypass" if that's how real-world performance works. Don't run your database on a phone with 8 GB of RAM. Batching writes will cut down on fsync calls for the WAL, so you'll get better real-world performance with actual databases.

You're right that it's not a common pattern among web developers. That's why I'm bringing it up. It's not hard, but it's also not talked about (it is known among lower level programmers obviously. e.g. they're generally not doing unbuffered single byte file IO).

>Is the wrong way to look at things.

I'm not saying you can write ANY code and code so shitty that it's slower than the database. Heck you can have the handler sleep for a minute before forwarding the request to the DB. I obviously don't mean that.

I'm talking about a specific kind of shitty code in the sense you don't have to worry about move semantics, memory management. You don't have to preallocate memory, you don't have to think about a lot of stuff and optimize for the heap or the stack. That kind of thing.

Just write some trivial non-blocking code don't worry about anything else and you're overall good. I guess "shitty code" is the wrong choice of words here. You don't need to spend time optimizing is more what I mean. If you know what you're doing it's fine... no major effort needed to change things.

Reply on news.ycombinator.com