4 ms·

It's probably a bad idea to switch to read only replicas for reads pre-emptively, vs vertically scaling up the database. Doing so adds a lot of incidental compl

by gfodor 7y ago

It's probably a bad idea to switch to read only replicas for reads pre-emptively, vs vertically scaling up the database. Doing so adds a lot of incidental complexity since you have to avoid read after writes, or ensure the reads come from the master. The reason punting on this is a good idea is because you can get pretty far with vertical scaling, database optimization, and caching. And when push comes to shove, you are going to need to shard the data anyway to scale writes, reduce index depths, etc. So a re-architecture of your data layer will need to happen eventually, so it may turn out that you can avoid the intermediate "read from replica" overhaul by just punting the ball until sharding becomes necessary.

danenania 7y ago
For those who have reached vertical database write scaling limits and had to start sharding, I'm curious what kind of load that entails? Looking at RDS instances, the biggest one is db.r5.24xlarge with 48 cores and 768 gb ram. I imagine that can take you quite a long way--perhaps even into millions of users territory for a well-designed crud app that's read-heavy and doesn't do anything too fancy?
- bcrosby95 7y ago
  Very far I would guess. 10 years ago we took a single bare metal database server running mysql with 8 cores and 64gb of memory to 8 million daily users. 15k requests per second of per user dynamic pages at peak load. We did use memcached where we could.
- adventured 7y ago
  > the biggest one is db.r5.24xlarge with 48 cores and 768 gb ram. I imagine that can take you quite a long way--perhaps even into millions of users territory That will run Stackoverflow's db by itself for reference, along with sensible caching (they're very read-heavy and cache like crazy). Here's their hardware for their SQL server for 2016: 2 Dell R720xd Servers featuring: Dual E5-2697v2 Processors (12 cores @2.7–3.5GHz each), 384 GB of RAM (24x 16 GB DIMMs), 1x Intel P3608 4 TB NVMe PCIe SSD (RAID 0, 2 controllers per card), 24x Intel 710 200 GB SATA SSDs (RAID 10), Dual 10 Gbps network (Intel X540/I350 NDC). https://nickcraver.com/blog/2016/03/29/stack-overflow-the-hardware-2016-edition/ https://nickcraver.com/blog/2016/03/29/stack-overflow-the-ha...
- gfodor 7y ago
  Yeah 10 years ago you could support millions of users on a high traffic site on a single box. (This was on postgres in my case.) Today, I'd guess at least a 10x increase due to both software optimizations and increased hardware capabilities, if not significantly more. Truthfully, unless you're working on some kind of non-transactional problem like analytics, even assuming you will need to shard the data or scale out reads ever due to user activity is borderline irrational unless you have extremely robust projections. The database will be the last domino to fall after you've added sufficient caching and software optimization. It's so far down field for most projects (and the incidental complexity cost so high) that my personal bias is that even having the conversation about such things on most projects isn't even worth the opportunity cost vs talking about something else. Even then, the first thing to fall over will probably be write heavy analytics-like tables that are usually append only due to index write load. Out of the box, you can often 'solve' this by partitioning the table (instead of sharding.) In modern DBs, this is a simple schema change.
jedberg 7y ago
The problem with going to the "top of the vertical" scaling so to speak is that one day, if you're lucky, you'll have enough traffic that you'll reach the limit. And it will be like hitting a wall. And then you have to rearchitect your data layer under extreme duress as your databases are constantly on fire. So you really need to find the balance point and start doing it before your databases are on fire all the time.
- toast0 7y ago
  Assuming you have a relatively stable growth curve, you should have some ability to predict how long your hardware upgrades will last. With that, you can start planning your rearchitecture if you're running out of upgrades, and start implementing when your servers aren't yet on fire, but are likely to be. Today's server hardware ecosystem isn't advancing as reliably as it was 8 years ago, but we're still seeing significant capacity upgrades every couple years. If you're CPU bound, the new Zen2 Epyc processors are pretty exciting, I think they also increased the amount of accessible ram, which is also a potential scaling bottleneck.
  - jedberg 7y ago
    > Assuming you have a relatively stable growth curve, you should have some ability to predict how long your hardware upgrades will last. But that's not how the real world works. The databases don't just slowly get bad. They hit a wall, and when they do it is pretty unpredictable. Unless you have your scaling story set ahead of time, you're gonna have a bad day (or week).
    - wolco 7y ago
      That's exactly how the real world works. Databases will get slow, then slower. Resources get used. Unpredictable not really. Maybe you've run out of space or ram or processes are hanging. The database will never just start rending html or formatting your disk or email someone. It is pretty predictable.
      - 7y ago
cactus2093 7y ago
There are some cases where adding a read replica can be helpful at almost no extra overhead - for instance if your product has something like a stats dashboard you'll have some heavy queries that are never going to result in a write after read and don't matter if they are sometimes a few ms or even a few seconds or tens of seconds out of date. Similarly if you have analysts poking around running exploratory queries, a read replica can be the first step towards an analytics workflow/data warehouse.

by gfodor 7y ago

danenania 7y ago

For those who have reached vertical database write scaling limits and had to start sharding, I'm curious what kind of load that entails? Looking at RDS instances, the biggest one is db.r5.24xlarge with 48 cores and 768 gb ram. I imagine that can take you quite a long way--perhaps even into millions of users territory for a well-designed crud app that's read-heavy and doesn't do anything too fancy?

bcrosby95 7y ago
Very far I would guess. 10 years ago we took a single bare metal database server running mysql with 8 cores and 64gb of memory to 8 million daily users. 15k requests per second of per user dynamic pages at peak load. We did use memcached where we could.
adventured 7y ago
> the biggest one is db.r5.24xlarge with 48 cores and 768 gb ram. I imagine that can take you quite a long way--perhaps even into millions of users territory That will run Stackoverflow's db by itself for reference, along with sensible caching (they're very read-heavy and cache like crazy). Here's their hardware for their SQL server for 2016: 2 Dell R720xd Servers featuring: Dual E5-2697v2 Processors (12 cores @2.7–3.5GHz each), 384 GB of RAM (24x 16 GB DIMMs), 1x Intel P3608 4 TB NVMe PCIe SSD (RAID 0, 2 controllers per card), 24x Intel 710 200 GB SATA SSDs (RAID 10), Dual 10 Gbps network (Intel X540/I350 NDC). https://nickcraver.com/blog/2016/03/29/stack-overflow-the-hardware-2016-edition/ https://nickcraver.com/blog/2016/03/29/stack-overflow-the-ha...
gfodor 7y ago
Yeah 10 years ago you could support millions of users on a high traffic site on a single box. (This was on postgres in my case.) Today, I'd guess at least a 10x increase due to both software optimizations and increased hardware capabilities, if not significantly more. Truthfully, unless you're working on some kind of non-transactional problem like analytics, even assuming you will need to shard the data or scale out reads ever due to user activity is borderline irrational unless you have extremely robust projections. The database will be the last domino to fall after you've added sufficient caching and software optimization. It's so far down field for most projects (and the incidental complexity cost so high) that my personal bias is that even having the conversation about such things on most projects isn't even worth the opportunity cost vs talking about something else. Even then, the first thing to fall over will probably be write heavy analytics-like tables that are usually append only due to index write load. Out of the box, you can often 'solve' this by partitioning the table (instead of sharding.) In modern DBs, this is a simple schema change.

jedberg 7y ago

The problem with going to the "top of the vertical" scaling so to speak is that one day, if you're lucky, you'll have enough traffic that you'll reach the limit. And it will be like hitting a wall. And then you have to rearchitect your data layer under extreme duress as your databases are constantly on fire. So you really need to find the balance point and start doing it before your databases are on fire all the time.

toast0 7y ago
Assuming you have a relatively stable growth curve, you should have some ability to predict how long your hardware upgrades will last. With that, you can start planning your rearchitecture if you're running out of upgrades, and start implementing when your servers aren't yet on fire, but are likely to be. Today's server hardware ecosystem isn't advancing as reliably as it was 8 years ago, but we're still seeing significant capacity upgrades every couple years. If you're CPU bound, the new Zen2 Epyc processors are pretty exciting, I think they also increased the amount of accessible ram, which is also a potential scaling bottleneck.
- jedberg 7y ago
  > Assuming you have a relatively stable growth curve, you should have some ability to predict how long your hardware upgrades will last. But that's not how the real world works. The databases don't just slowly get bad. They hit a wall, and when they do it is pretty unpredictable. Unless you have your scaling story set ahead of time, you're gonna have a bad day (or week).
  - wolco 7y ago
    That's exactly how the real world works. Databases will get slow, then slower. Resources get used. Unpredictable not really. Maybe you've run out of space or ram or processes are hanging. The database will never just start rending html or formatting your disk or email someone. It is pretty predictable.
    - 7y ago

cactus2093 7y ago

There are some cases where adding a read replica can be helpful at almost no extra overhead - for instance if your product has something like a stats dashboard you'll have some heavy queries that are never going to result in a write after read and don't matter if they are sometimes a few ms or even a few seconds or tens of seconds out of date. Similarly if you have analysts poking around running exploratory queries, a read replica can be the first step towards an analytics workflow/data warehouse.