Does this still hold true? Given that SSDs are now commonly used by many hosting providers - is database/disk access still a bottleneck for server-side web apps?
The database is the bottleneck because it's much harder to scale than applications.
The path of evolution in the industry:
1. Stateful application - usually only 1 server, not distributed at all. It's very hard to scale.
2. An obvious solution is to make application stateless and having a centralized state. Then the application is very easy to scale and operate. And databases slowly became the bottleneck because there are much more applications than databases.
3. Then not so obvious solution is to split your problem domain into solution contexts, where every context become an application and each application have a database to talk to. So the databases are still not really distributed but it's sort of distributed by your sub-domain of business. (I think it's the industry mainstream or becoming mainstream now)
4. Then the non-obvious solution is to have truly distributed states split across your application. Basically, the goal is you can distribute any object across a whole lot cluster, in a more or less transparency way which lets you treat the cluster as a whole without concern about individual machines. (There are some cutting edge stuff yet to become mainstream, like Orleans / Lasp-lang or just cluster/shared actor like Akka, or just distributed Erlang, etc)
It's an interesting example that short-term solution is in a totally different direction from the powerful solution. To address this in short-term is to extract and centralize the state, while to address this in the long-term is to truly distribute the state.
Although a distributed data store on a few cheaper VPSes/nodes is probably less expensive.
Operations that need ACID get ACID and operations that don't, don't.
And every production-grade DB make read replicas easy.
Yes, multi-master is the interesting and challenging thing to us, but single-master still has a lot of juice.
So an optimized query set from an ORM will end up querying the DB like this: First it selects the 5 rows from the order table, but since you left out any join clauses, it then runs 5 more selects on each of the rows returned from the order table to pull the related rows from the items table. So we are now up to 6 queries.
But each row of the item table references 5 manufacturers (again, as a simple schema example). So each of the 'item' 'objects' (5 per order) has to then make a query to fetch the sku manufacturer. So that is 25 more select statement queries on top of the original 6. So using the ORM properly and building out the right join clauses, you would have made 1 query. Instead you made 31. Now lets say that DB is not on the same machine, but a different machine on the network. Throw in 2ms - 10ms latency per query and now you are looking at 62 - 310ms of latency to fetch your data. And that is just for 5 'rows' of data. Do the math for a more realistic dataset, 500-1000 rows or so. Now you're looking at minutes of latency for your data to come from the DB.
The worse case I've seen recently was this exact case. To generate an inventory page to show an html table with about 300 rows, the DB was being queried over 300,000 times with a page load of about 7 minutes (and getting longer everyday). I added the proper join clauses to the ORM and one query was issued which would return the result set in about 700ms which put the page load time to about 1 second. A 1 second page load imo is still not very good. But it seemed to impress everyone that with 1 hour of work, a page load went from from 7 minutes to 1 second....
For what ever reason, most people don't understand this anymore. Instead of learning SQL, the line of thought is to upgrade the machine, move to a 'no-sql' DB, introduce some caching layer, and many number of other ideas. But "lets make sure we are querying the DB instead of iterating through it" is never discussed.
db.Query("select foo, bar from baz")
var baz Baz
row.Scan(&baz.foo, &baz.bar)
Therefore, looking for a safer solution, I've also looked at various options in my last Go project, and ended up with SQLBoiler. It supports fetching related documents in a single query and seems to be quite performant.
I don't think that an ORM saves you from having to have a versioning policy, schema migrations, and tests for that. If your code expects a data structure that looks like map[string]string{"foo":"bar"} but the database column is now called "quux", you're not going to find what the code is looking for at runtime. The difference is you get an empty string instead of an explicit error.
Ultimately I find it very annoying. When I was at Google I used spanner, and everything in the database was just a protocol message. Want to rename something? Feel free. Only the code cares, the database still uses the tag number. Want to add a field? Code compiled against an older version of the protocol message will just silently ignore it.
In the end, I think bolting on relational semantics to an object database is a lot easier than bolting on object semantics to a relational database. But to do that, you have to undo 50 years of thinking about database storage.
This is interesting. Do you have any more information on it?
Blow them away and recreate them all from source after you run your schema and data migration scripts. Then you'll know at build time whether your queries still match the schema.
It helps to also not be afraid of the star when writing queries. That can keep you safe from missing new columns. You just need to take a bit of care and think about what you're doing.
A "well structured" database can lead to performance issues. Every join and sub-query has a cost and they add up.
The funny thing is that on this project, I largely stepped back from anything to do with the Database because the target was only around 100 users or so on a deployment that could largely be dealt with by faster hardware. Now, stuck trying to scale to more users than that and stuck with layers of procedures/functions that don't perform well at all. I wanted a few loosely coupled tables with some destructured (json) data in them. Wouldn't be having half the issues today.
Also, not a fan of ORMs... it's usually easier to do simpler mapping directly or a simpler data mapper that isn't a more typical full on ORM.
Moving from denormalization to denormalization can be hard, and you end up with a sui generis application, versus a normalized database where so much has been written about how to deal with problems up to a reasonably large scale, although the document store style does seem to be reasonably popular so maybe I'm missing something.
Almost every app I have worked on had a database that were several tables for a single object and several for the where clause. As good as the table layout seemed to be in terms of logic it just led to slow queries like you said joins and sub-queries come at a cost. Then again, I have seen clients balk at the idea of fixing the database and insist on a more unreadable yet performant query. But its their time and money so I don't fight back.
Sometimes, a really good DBA is what a project needs because so few programmers are good at performant design.
You can get pretty far with select-related and prefetch_related alone, but sometimes there’s a need to break out SQL in order to get past some kind of performance barrier.
I started my career in Django without any SQL background and then moved to a financial company where everything was done in MSSQL.
If I never have to write or read a SQL query again, I would consider that a good thing. Although I had a few fun learning experiences.
There are ways to alleviate this issue but they have different trade offs. One of the most used pattern is "eventual consistency".
You might want to read about the CAP theorem if you have not heard about it: https://en.wikipedia.org/wiki/CAP_theorem
https://www.cockroachlabs.com/blog/serializable-lockless-dis...
Say you're an ecomm, how do you update inventory on an item "concurrently"? If you're going to say "locks" or "atomic commit", that's exactly what I mean by "you have to serialize your writes".
If you can tolerate eventual consistencies (most problem can), than the database is no longer the slower piece.
[1] https://en.wikipedia.org/wiki/Multiversion_concurrency_contr...
This is not true, and not what we talk about when we say it's almost always the db.
The usual cause of database problems is a single big query that is causing a slow page, or a lot of medium size queries inside a poorly written loop. Whether it be bad joins, missing indexes, select statements in the select statements, operations in select statements that force the db to run the query line by line instead of as a set (the technical term suddenly escapes me), it's the queries that almost always cause db slowdowns.
It rarely has anything to do with writes, and then in my experience usually had to do with escalating locks, and I haven't seen that problem in ages as hardwares got faster.
It depends who's talking and what they are talking about.
If we're talking about fixing slow website, then sure, bad big ugly queries are one dominant issue, but not the only one. If we're talking about architecture of a greenfield project, then balancing between concurrent writes and consistency is going to be your ultimate problem, everything else can be "duplicated" (read-duplicated DB servers, caching strategies, multiple apps server, etc...).
Again, I just think you're not considering the vast majority of us here work in terms of thousands, 10s of thousands or millions of users, not the sort of scales where anything you're saying ever becomes relevant.
For most of us, if you spend any time thinking about that, you're utterly wasting it. It's pointless. Greenfield or not.
It comes in faster than you think. One of the company I work for was a small company, at the time I think we were 4 or 5 including the 2 founders, they sell some specific information as a service through APIs. They charge per call, so you have to update the customer funds in their account on each API call. Yes, people can want and make lots of APIs call very fast, and in a business which charges per API call, this is a good thing, but you have to find strategies and make business decisions on how to handle accepting and replying to new API calls while maintaining their account balance.
(BTW, the "can't reply" issue happens on HN when there's a long comment thread between just two people that's being rapidly updated. If you wait 10 minutes or so you'll be able to reply.)
So you only ever have one app server?
Sure, this specific issues could be handled other ways, but in general global state is hard.
You can get surprisingly far on one app server (or sometimes one app server with 2 mirrors for failover). See eg. the latest TechEmpower benchmarks [1], where common Java and C++ web frameworks can serve 300k+ reqs/sec off of bare-metal hardware. The OP indicated that these API requests are basically straight queries anyway, and only need to write to update the billing information. Reads can run incredibly fast (both because they can usually be served out of cache and because they can be mirrored), so if your only bottleneck is the write, take the write out of the critical path.
In general global state is hard. Don't solve general problems, solve specific ones. Atomic counters have a well-understood and highly performant solution.
[1] https://www.techempower.com/benchmarks/#section=data-r17&hw=...
Also, I never said that you couldn't use a single app server because of performance; as you said, you can handle quite a bit of traffic on a single box, even with slower languages.
Even if it did, as the other comment says, you could easily work round it. If that was the behaviour there was no good reason to have it utterly perfect.
It's possible to kill your performance very quickly with databases if you request features that you don't actually need.
You need to decide if you absolutely do not want to serve any API call unless you are sure they've been paid for, in which case you have to create a commit transaction on that account for each call. Or you decide how much you can let a would be rotten customer get away with, and uses queues which leads to eventual consistencies.
I think part of why low-feature, specific-usecase DBs like Mongo can get such traction as a general purpose datastore (when marketed as such, of course, to sell more licenses, no matter how bad an idea it is—looking at you, Neo4j) is because so many devs don't know what a half-decent SQL DB can (and should) do for them in the first place.
I have a pet project that works with data that’s very graphy (timetabling app with lots of interdependent events); I tried using postges at first since I’m used to it, but found myself writing really ugly looking recursive queries. Neo4j seems to fit my use case a lot better, and their graphql plugin has been really useful.
[EDIT] it's also not nearly as well-supported so you'll find a lot of supporting libraries with multi-datastore support don't support N4J yet, or do but only in some crippled or poorly-tested (not widely deployed) fashion.
[EDIT EDIT] it's also not a great fit if you have a lot of constraints on or structure to your graph. At least as of when I used it ~1 year ago it had essentially no support for expressing things like "this type of node should only permit two outbound edges". You can work around that but the solutions will be less safe and/or suck.
Constraints and hosted multi-datastore solutions aren’t really an issue for me, but the type of query you mentioned about subgraphs might be. One query I know I’ll need is one that can quickly identify nodes with lots of neighbors that have a specific property.
The data is definitely very graphy, so I really wanted a graph database as the primary. Dgraph seems pretty good (advertises itself as more reliable and faster), but it sounds like graph databases might just be kind of oversold in general, and that I might want to reconsider. One other issue is speed of development, which the graphql plugin really increases, so I’ll probably stick with Neo4j for now. Swapping dbs theoretically shouldn’t be THAT painful if I don’t care about migrating data and keep the graphql layer the same.
Bottlenecks change all the time. You fix one, and now suddenly, the bottleneck lies somewhere else. There's almost always a bottleneck somewhere. A database bottleneck is relatively common, though.
From my experience, the bottleneck is mostly at some type of Big-O relevant scale in the code or database that using java vs ruby vs python doesn't make a huge difference.
However, if you're in a situation where speed is absolutely crucial - like high frequency trading (where milliseconds mean millions of dollars) - well, that opens a completely different can of worms.
However even with that said a modern SQL database can handle A LOT of traffic and I've had discussions about internal CRUD applications with a couple of thousand users at most where people say we must use a document database because relational databases does not scale. Insane.
Of course people can use document databases and similar where it fits but you get a lot of nice things with a traditional rdmbs that you might miss later.
If someone mentions a need for a technology choice based on performance, they had better come up with real numbers. Until they do, clarity, correctness and consistency should be the primary metrics. Don't solve a performance problem before you have the luxury of having a performance problem.
On one of my recent projects, the highest use data was stored in DynamoDB. The entire dataset could trivially fit in RAM. So much wasted effort.
The org also over used Spark, event streams, and elasticsearch.
One could argue (rationalize) that using DynamoDB, for instance, for the small stuff builds team competency applicable to the big stuff. Or that using one set of tools reduces dependencies. I'd have to see some case studies. Because from where I was sitting, 90% of our maintenance costs were from mitigating bad sacred cow design decisions.
I've worked on similar metastatic tech debt based on NoSQL. Much as I love Redis, it's not a duck (fly, swim, walk).
So I understand that people want to try new things but damn I wonder if I sounded the same when I wanted to start using React.
- "We can just use couchdb because then we won't have any problems with scale and we will never have to have any downtime because schema updates".
....yeah but that comes at a cost. You need to handle those schema updates in code. You need to handle those conflicts that arise from eventual consistency in code.
Anyway, I like the idea where you have a limited innovation budget where you can go for a new type of db but then you select other technologies that you are comfortable with and are true and tested. Not "Yeah, we will build this in a completely new way and we have no experience with docker, kubernetes, nodejs, document databases, vue.js, Elastic stack, prometheus or kibana" but this modern way of building things means everything will go much faster. Sometimes you have to go that route but the learning time can be pretty rough.
Write operations can be particularly expensive, read operations much less so, especially with application-level caching. Even as for write operations or notoriously expensive read operations such as full table scans modern RDBMS are optimised to a degree that for most applications database performance shouldn't be an issue.
While traditional hard drives competed with the network for the title of "system component with the lowest bandwidth" (see http://www.cs.cornell.edu/projects/ladis2009/talks/dean-keyn...) accessing data on an SSD is way faster than accessing a resource via network.
So, today the technical bottleneck (as in "component with the lowest bandwidth") in a typical web app system architecture is the network.
Of the few cases where the database was the bottleneck in practice, I cannot think of a single cases where it should have been the bottleneck in theory. The limitations were always a property of the specific database implementation being used, not intrinsic to databases as a class of software. For example, most open source databases have notoriously poor storage throughput as a product of their architecture; faster storage definitely helps but it often solves a problem that may not exist in other database implementations. In these cases the problem is an architectural mismatch.
Sometimes architectural mismatch is an engineering problem. A handful of workloads and data models are currently only possible with custom database engines e.g. high-throughput mixed workloads or data models that require continuous load shifting. As a practical matter, the exotic cases that require a custom database engine are also the cases where no company should try to build it themselves without a deep expert -- they are considered technically exotic for good reasons.
In my experience, people used to cloud services misjudge the power of correctly tuned modern hardware by two-three orders of magnitude.
Giving a general answer to your question is hard in the absence of further information.
Except for logs, of course. Those things are incredible disk space hogs. Also, serialising data to logs can surprisingly expensive. (Our ES logging cluster has tens to low hundreds of TB of storage, and it expires data after roughly 40 days.)
Of course, they don´t need hardware expenses, licensing is factored in, administration is orders of magnitude easier and cheaper, so the tradeoff is usually worth it.
Is the database a bottle neck for a CRM app that deals with millions of customer records? Probably because everything else is incredibly lightweight. Is it the bottleneck for an app that transcodes video on the server? Absolutely not.
And so on, for every imaginable app.
Your question is a bit open-ended.
Scaling your database is generally the hardest part. Any jerk can put 100 app servers behind a load balancer. Scaling data is significantly harder, especially when it comes to writes. There are options to get around this, but they come with compromises.
So, really, it depends on what you're doing. In general, use things you're comfortable with. Is Python slower than Rust? Sure. But do you need 2ms response times from your app server or is 10ms fine, especially when latency between the user and your first load balancer is likely 3-5x that number? Build it, test it, if it's too slow, cache it. If you can't cache it, break out the offending piece and optimize it or put it into a faster language.
In reality, when database becomes a bottleneck it means you're probably making enough that you can just throw more money at it.
Example: We struggled with some read-queries taking a long time when aggregating over millions of rows. To solve this, we used triggers to pre-compute aggregate groups in real-time during write, which allowed us to easily achieve sub-second response times. However, this caused a lot of write amplification and now this is becoming a bottleneck. We could probably solve some of these problems by looking into column stores, but that carries it's own downsides (operational complexity, real-time data loading, etc.). Or we could buy faster disks, but that gets expensive quickly.
YMMV but I certainly still experience more database/disk related bottlenecks than application-level ones.
tl;dr:
simple CRUD operations on few rows scale fine
data-science/analytics stuff involving millions of in one query scale rather badly
However, SQL isn't limited to row-stores. There are column-store implementations that are quite amazing for aggregate queries, e.g. Clickhouse. Using one of those would very likely work for us, but my understanding is that loading data into them in real-time is problematic.
Also, what type of aggregate?
For most crud operations any production ready database will not bottle neck before you have to fine tune your application server and application itself. Non thread safe application code and libraries have caused more performance issues than large join queries.
The bottleneck in my code is rarely the database but rather the interface that is used to it. I'm sure if you had thousands of users running high latency but low utilization queries you could reach the actual limit of the database.
99% of the performance problems I experience involve loading data from systems that don't let me access it directly and instead the data is simply dumped and my application has to load the dump. When you have to delete and reinsert 300000 rows every day and use the naive way of INSERTing the data then you will wait several minutes because the synchronous nature of SQL means that the client has to wait for a response on every statement. An easy way out is to use non-standard features like COPY or INSERT INTO x FROM unnest(...) in postgres. Except they aren't really pretty to use. Your 1 line Domain.save() suddenly turns into a 30 line query just for inserting some data. I still prefer the former for smaller datasets even if it means waiting 2 minutes on an initial import that only happens once because that can be quickly implemented and then you can simply throw the code away if you need something better.
I view this statement as a prediction, in the scientific sense: "If you re-implement a program in a faster language, but have the same database access, the user will not have any visible performance improvements."
Like almost anything else, the answer is, "it depends". It is often true.
However, I have falsified it in some specific instances. It is definitely possible to take a "scripting language", which ranges in the 20x-50x slower than C (a rough, but adequate, approximation of "optimal"), then layer it in a couple more layers of abstraction that may be really nifty and cool and awesome but also adds another 10x to the slowdown (or more... if you're not careful, this term can be unboundedly large), and get to the point that simply generating an HTML page takes a full second of a 3GHz's processor time. I have replaced things that did non-trivial database work with something in a compiled language and gone from "noticeably sluggish" to "essentially instant" without all that much effort.
In one of those cases, significant effort had been expended trying to figure out why the database was so slow, presumably because of the prevalence of this meme, when even the first glance at the output of a profiling tool said that was not where the problem was.
Performance is rarely the first thing to worry about, but I find it's something you do want to at least keep in mind while developing. If you do at least keep it in mind, I suspect the saying is generally true, and the raw speed of the underlying server language won't bother you much. But you are definitely much closer to the point where performance becomes problematic than you start at with a faster language, so you need to be that much more vigilant. People who are lulled into a false sense of security by the idea that the database is always the bottleneck can get blindsided by performance issues in their app.
I think the meme overall is damaging. It's not true enough to make it a core, unquestioned assumption that you can rely on, and if it can't be that, then you might as well simply use the correct answer, which is to use a real profiler and look at the real data. I pretty much always through at least a basic profile pass at any code that isn't just brute scripting.
I think most slowness is coming from bloated apps trying to push too much code/data over the crappy network. Additionaly it is easy to misconfigure AWS infrastructure.
And if you blindly follow that, you'll fall into exactly the trap I mentioned, where that "better architecture" involves layering another 10x+ slowdown on top of your already-slow scripting language and before you know it it's 100,000 cycles just to emit "<p>".
I think there's rather more compute-bound web-generation code out there in the world than people think, precisely because this meme makes it so people never even look. Of course you'll think it's all IO bound if you just assume it is and never check even a little bit whether that's true, which from what I can see, is the norm in the web programming world. The data can be screaming that you are in fact not IO bound, but if nobody's listening because "everybody knows" that can't be true... who's going to find out it's wrong?
Our primary apps deal with trading data at 15/30 minute intervals over multiple years. It's not uncommon to have millions of rows involved in a process.
Yes, nearly all of our performance problems boil down to the database. The majority of the time spent on the server is on data access.
Basic CRUD on small data wouldn't be the same.
Note that a single postgres instance with one core and 1gb of ram on 1 ssd can “scale linearly” by my definition, since you can easily double, triple, etc all those specs.
On the other hand, a fully populated data center running scale out software at 100% power capacity can’t scale linearly anymore, because you can’t upgrade it anymore. For that data center, power or maybe space is the bottleneck.
Short answer to the question you asked: SSD’s are not going to be the bottleneck for software that’s migrating off disk, and the database probably won’t either. Hardware trends mean the storage and database just got 10-100x faster, while the business logic maybe doubled in speed.
If the system was well balanced before, that means no one spent then-unnecessary effort on now-necessary optimizations on the compute side.
What has changed it is much easier for the client to be continually in touch with the server so that by the time the next "page" is loaded all necessary data has been retrieved.
This way the database is no longer the same obvious bottleneck.
Redis is awesome badass rockstar tech, but I try and avoid it for any single-processe thing, instead opting for a tried-and-true global (thread-safe) hash table. Network packets aren't free, and no matter how cool your database is, I don't see you avoiding that. Not to mention that there is a cost to (de)serializing data, which you don't pay if you use something built into the language itself.
I'm not saying to go drop Redis (or any database/cache), just to keep this in mind.
If you are largely static and most of your content is tethered to fixed keys that can map from URL patterns, then you could through a large redis cache in front and it wouldn't be the bottleneck... or enough ram to cache on-system.
There are more details to how much your disk or database are your bottleneck, but in almost every case, they are still much slower than your CPU or your network interface (that may be a bottleneck in some cases),
Then you read the article and it's just running the app and DB on the same physical server, and communicating over local sockets rather than a TCP/IP stack through various virtualization layers and across several networks, real and virtual—so, partying like it's 1999.
In the serverless world, memory accesses are the most expensive with writes being the most expensive of all (where expensive == slow AND literally expensive)