5 ms·
One problem with the stored procedure approach is that it scales terribly. If your logic is in app servers and your state in a DB, you can add more app servers
by BurningFrog 10y ago
One problem with the stored procedure approach is that it scales terribly.
If your logic is in app servers and your state in a DB, you can add more app servers, and it will be a long time until your DB get overwhelmed.
When your DB is doing both, the choking point is much earlier.
- PeCaN 10y agoThat's a valid point. Seeing as I don't build anything that has to scale well I can't really comment on this, but I don't think it scales too badly. With stored procedures it's more or less just your model in the database, not your whole application logic. Using a database to replace your controllers wouldn't scale so well, but replacing fat models would still scale fine.
- btilly 10y agoIt significantly depends on the application, but it shouldn't be more than a factor of 5-10 or so... A factor of 5-10 can matter. A lot.
- peschkaj 10y agoI've yet to see empirical proof that stored procedures don't scale well. If you write an application in your SQL dialect of choice, that likely won't scale well, but putting data access behind an API should scale well no matter if that API is in Rails, Spring, or a stored procedure.
- btilly 10y agoI don't know what you consider "empirical proof", but it seems obvious to me. And should be obvious to anyone competent who has ever had to scale stuff. A system fails to scale when it has a bottleneck, and that bottleneck gets overwhelmed. You make it scale better by scaling the bottleneck, which can be done by moving work out of the bottleneck, or by parallelizing it in some way. The natural bottleneck for any system that has to synchronize data is the locking around synchronizing that data. That is because things that do not need synchronization can easily be parallelized. You therefore scale that bottleneck until the more fundamental one emerges. In a standard database driven website, that bottleneck is always in the database. And therefore your scaling limit is the capacity of your database. As follows normal scaling advice, you need to move work out of the database, or remove the database as a scaling limit. Moving work from stored procedures to the application is an example of moving work out of the database. So is having queries run against read-only replicas instead of the read/write master. Sharding your database and moving to a distributed NoSQL architecture are examples of removing the bottleneck. Of the two approaches, the much simpler and safer one is to move work out of the database. Going NoSQL is cool, but unless you really know what you're doing, it is both unlikely to buy you what you wanted, and leaves you open to obscure data consistency problems.
- Jweb_Guru 10y agoWhen it comes to data validation, none of what you just said applies, because you need to perform your work inside a transaction. You can validate the data outside your database and then confirm that nothing's changed (optimistic concurrency control) but you can do that just as easily inside the database, with lower latency and greater throughput (and in situations with lots of contention this can lead to many more aborts than other concurrency control mechanisms, so be careful!) because many databases have OCC built in. If you can afford to relax consistency due to aspects of your data model, you can use a database with a relaxed consistency model and--again--get far better performance than an ad-hoc solution in your application. It's hugely unclear to me why you think you skirting transactional requirements by performing work in your application is less complex than using a NoSQL database (or using a database that utilizes MVCC or can otherwise provide long-lived read snapshots). Frankly, I also disagree that for most websites the bottleneck is the database. For many websites, database latency / throughput constraints don't ever become the dominant factor in end-to-end requests because of all the layers they have to get through in order to get to the database in the first place, combined with a relatively low number of requests per second (commodity relational databases on commodity hardware can easily handle many thousands per second, and IIRC Google Search only had to handle 40k rps from real clients in a recent press release) and inefficient code elsewhere in the stack.
- btilly 10y agoJust put transactions in the application. With row level locking, the odds of running into contention problems are low. If you've been careful to always lock tables in the same order, then deadlocks are a non-issue. At that point, having longer running transactions does not matter. What matters is contention for the internal locking mechanisms inside of the database. And limiting how much extraneous stuff you have improves that. Now it is easy to screw up an application. It is easy to screw up a database design. It is easy to screw up queries and query plans. But all of those are fixable in relatively straightforward ways. And once you do that, you will wind up with database throughput as your bottleneck. As for NoSQL, the problem is this. Moving to that architecture requires taking an up front hit on transactional complexity, usually requires several times the hardware (data needs to be stored multiple times for hardware failure), puts a lot of stress on your network and latency, and is really easy to screw up. Just using a popular out of the box solution is not enough - see https://aphyr.com/tags/jepsen https://aphyr.com/tags/jepsen for a list of real failure modes on stuff that will look perfectly fine in testing. It is a necessary challenge to accept if you want to go beyond a certain scale. But you should not accept that challenge unless you have good reason to do so.
- Jweb_Guru 10y agoLook, this is just wrong. All the best-performing research databases use stored procedures because they perform much better than other approaches. Your bottleneck when it comes to validating your data is, generally speaking, your database, so you're not going to scale better by moving the logic farther away from it (and introducing a bunch of round-trip latency) and this is empirically confirmed in benchmarks of stuff like TPC-C. It's a really tired argument, especially when virtually every aspect of stored procedures but their performance is irritating.
- paulryanrogers 10y agoIME, the win in the application layer is when you can have faster copies pulled from a cache or validate rules that don't relate to other records.
- BurningFrog 10y agoThat stored procedures scale badly is different from if they perform badly. With application logic on separate stateless app servers, you can quickly spin up as many of those as you need when your site goes viral. If it's in your DB, you can't. If the stored procedures are fast or slow doesn't really enter into that equation. It's about the difference between 1 and N. I actually did work at a startup that got into serious trouble because much of their application logic was in the DB.
- Jweb_Guru 10y agoWere you serving something that had to live in the database, or were you serving cached copies that didn't actually need to be updated or read within a transaction? There's a crucial difference there. The moment you are required to access the database at all, the equation changes (which is usually true for data validation, inserts, updates or deletes, and constraint management). If all you're doing is reading static data, there's no need to touch the database more than once and you can do much better. Moreover, if all you need is causal or eventual consistency (for application-specific reasons), you can use databases that provide those semantics. In pretty much all cases, once you've chosen the correct database / semantics for your use case, you'll be better off from a performance perspective with stored procedures; 1:N doesn't really enter into it because databases can run with their own isolated state on multiple nodes just as well as applications can (indeed, there are some caching systems built around this observation)!
- codersteve 10y agoMany applications don't need Facebook/Twitter scale and will never have this problem.