6 ms·

The 1-7 list you mention definitely deserves it’s own blogpost and how to implement these. I’m currently not using any of these except 1, and probably don’t nee

by kalev 5y ago

The 1-7 list you mention definitely deserves it’s own blogpost and how to implement these. I’m currently not using any of these except 1, and probably don’t need the rest for a while but I do want to know what I should do when I need it. For example: what and how should things be cached? When and how to denormalize, why is it needed? Why append-only and how? Never ‘sharded’ before, no idea how that works. Heard some things of everything async/in the background, but how would that work practically?

BrentOzar 5y ago
> what and how should things be cached? If something is read much more frequently than it changes, store it client-side, or store it temporarily in an in-memory-only, not-persisted-to-disk "persistence" layer like Redis. For example, if you're running an online store, your product list doesn't change all that often, but it's queried constantly. The single source of truth lives in a relational database, but when your app needs to fetch the list of products, it should first check the caching layer to see if it's available there. If not, fetch it from the database, but then write it into the cache so that it's available more quickly the next time you need it. > When and how to denormalize, why is it needed? When you need to join several tables together in order to retrieve a result set, and especially when you need to do grouping to get the result set, and the retrieval & grouping is presenting a performance problem, then pre-bake that data on a regular basis, flattening it out into a table optimized for read performance. Again with the online store example, let's say you want to show the 10 most popular products, with the average review score for each product. As your store grows and you have millions of reviews, you don't really want to calculate that data every time the web page renders. You would build a simpler table that just has the top 10 products, names, IDs, average rating, etc. Rendering the page becomes much more simple because you can just fetch that list from the table. If the average review counts are slightly out of date by a day or two, it doesn't really matter. > Why append-only and how? If you have a lot of users fighting over the same row, trying to update it, you can run into blocking problems. Consider just storing new versions of rows. But now we're starting to get into the much more challenging things that require big application code changes - that's why the grandparent post listed 'em in this order. If you do the first two things I cover above there, you can go a long, long, long way.
pedrosorio 5y ago
As a sibling comment mentioned, read DDIA: https://dataintensive.net/ https://dataintensive.net/
latch 5y ago
It's hard to answer this in general. Most out-of-the-box scaling solutions have to be generic, so they lean on distribution/clustering (e.g., more than one + coordination) so they're expensive. Consider something like an amazon product page. It's mostly static. You can cache the "product", and calculate most of the "dynamic" parts in the background periodically (e.g., recommendation, suggestions) and serve it up as static content. For the truly dynamic/personalized parts (e.g., previous purchased) you can load this separately (either as a separate call from the client or let the server pieces all the parts together for the client). This personalized stuff is user specific, so [very naively]: conn = connections[hash(user_id) % number_of_db_servers] conn.row("select last_bought from user_purchases where user_id = $1 and product_id = $2", user_id, product_id) Note that this is also a denormalization compared to: select max(o.purchase_date) from order o join order_items oi on o.id = oi.order_id where o.user_id = $1 and oi.product_id = $2 Anyways, I'd start with #7. I'd add RabbitMQ into your stack and start using it as a job queue (e.g. send forget password). Then I'd expand it to track changes in your data: write to "v1.user.create" with the user object in the payload (or just user id, both approaches are popular) when a user is created. It should let you decouple some of the logic you might have that's being executed sequentially on the http request, making it easier to test, change and expand. Though it does add a lot of operational complexity and stuff that can go wrong, so I wouldn't do it unless you need it or want to play with it. If nothing else, you'll get more comfortable with at-least-once, idempotency and poison messages, which are pretty important concepts. (to make the write to the DB transactionally safe with the write to the queue, lookup "transactional outbox pattern").
toast0 5y ago
> Never ‘sharded’ before, no idea how that works. Sharding sucks, but if your database can't fit on a single machine anymore, you do what you've got to do. The basic idea is instead of everything in one database on one machine (or well redundant group of machines anyway), you have some method to decide for a given key what database machine will have the data. Managing the split of data across different machines is, of course, tricky in practice; especially if you need to change the distribution in the future. OTOH, Supermicro sells dual processor servers that go up to 8 TB of ram now; you can fit a lot of database in 8 TB of ram, and if you don't keep the whole thing in ram, you can index a ton of data with 8 TB of ram, which means sharding can wait. In contrast, eBay had to shard because a Sun e10k, where they ran Oracle, could only go to 64 GB of ram, and they had no choice but to break up into multiple databases.
- bigiain 5y ago
  > you have some method to decide for a given key what database machine will have the data Super simple example, splitting there phone book into two volumes, A-K and L-Z. (Hmmmm, is a "phonebook" a thing that typical HN readers remember?) > you can fit a lot of database in 8 TB of ram, and if you don't keep the whole thing in ram, you can index a ton of data with 8 TB of ram, which means sharding can wait. For almost everyone, sharing can wait until after the business doesn't need it any more. FAANG need to shard. Maybe a few thousand other companies need to shard. I suspect way way more businesses start sharding when realistically spending more on suitable hardware would easily cover the next two orders of magnitude of growth. One of these boxes maxed out will give you a few TB of ram, 24 cpu cores, and 24x16TB NVMe drives which gives you 380-ish TB of fairly fast database - for around $135k, and you'd want two for redundancy. So maybe 12 months worth of a senior engineer's time. https://www.broadberry.com/performance-storage-servers/cyberstore-248n-performance-nvme https://www.broadberry.com/performance-storage-servers/cyber...
  - Zababa 5y ago
    > So maybe 12 months worth of a senior engineer's time. In America. When the salaries are 2/3 times lower, people spend more time to use less hardware.

by kalev 5y ago

BrentOzar 5y ago

> what and how should things be cached? If something is read much more frequently than it changes, store it client-side, or store it temporarily in an in-memory-only, not-persisted-to-disk "persistence" layer like Redis. For example, if you're running an online store, your product list doesn't change all that often, but it's queried constantly. The single source of truth lives in a relational database, but when your app needs to fetch the list of products, it should first check the caching layer to see if it's available there. If not, fetch it from the database, but then write it into the cache so that it's available more quickly the next time you need it. > When and how to denormalize, why is it needed? When you need to join several tables together in order to retrieve a result set, and especially when you need to do grouping to get the result set, and the retrieval & grouping is presenting a performance problem, then pre-bake that data on a regular basis, flattening it out into a table optimized for read performance. Again with the online store example, let's say you want to show the 10 most popular products, with the average review score for each product. As your store grows and you have millions of reviews, you don't really want to calculate that data every time the web page renders. You would build a simpler table that just has the top 10 products, names, IDs, average rating, etc. Rendering the page becomes much more simple because you can just fetch that list from the table. If the average review counts are slightly out of date by a day or two, it doesn't really matter. > Why append-only and how? If you have a lot of users fighting over the same row, trying to update it, you can run into blocking problems. Consider just storing new versions of rows. But now we're starting to get into the much more challenging things that require big application code changes - that's why the grandparent post listed 'em in this order. If you do the first two things I cover above there, you can go a long, long, long way.

pedrosorio 5y ago

As a sibling comment mentioned, read DDIA: https://dataintensive.net/ https://dataintensive.net/

latch 5y ago

It's hard to answer this in general. Most out-of-the-box scaling solutions have to be generic, so they lean on distribution/clustering (e.g., more than one + coordination) so they're expensive. Consider something like an amazon product page. It's mostly static. You can cache the "product", and calculate most of the "dynamic" parts in the background periodically (e.g., recommendation, suggestions) and serve it up as static content. For the truly dynamic/personalized parts (e.g., previous purchased) you can load this separately (either as a separate call from the client or let the server pieces all the parts together for the client). This personalized stuff is user specific, so [very naively]: conn = connections[hash(user_id) % number_of_db_servers] conn.row("select last_bought from user_purchases where user_id = $1 and product_id = $2", user_id, product_id) Note that this is also a denormalization compared to: select max(o.purchase_date) from order o join order_items oi on o.id = oi.order_id where o.user_id = $1 and oi.product_id = $2 Anyways, I'd start with #7. I'd add RabbitMQ into your stack and start using it as a job queue (e.g. send forget password). Then I'd expand it to track changes in your data: write to "v1.user.create" with the user object in the payload (or just user id, both approaches are popular) when a user is created. It should let you decouple some of the logic you might have that's being executed sequentially on the http request, making it easier to test, change and expand. Though it does add a lot of operational complexity and stuff that can go wrong, so I wouldn't do it unless you need it or want to play with it. If nothing else, you'll get more comfortable with at-least-once, idempotency and poison messages, which are pretty important concepts. (to make the write to the DB transactionally safe with the write to the queue, lookup "transactional outbox pattern").

toast0 5y ago

> Never ‘sharded’ before, no idea how that works. Sharding sucks, but if your database can't fit on a single machine anymore, you do what you've got to do. The basic idea is instead of everything in one database on one machine (or well redundant group of machines anyway), you have some method to decide for a given key what database machine will have the data. Managing the split of data across different machines is, of course, tricky in practice; especially if you need to change the distribution in the future. OTOH, Supermicro sells dual processor servers that go up to 8 TB of ram now; you can fit a lot of database in 8 TB of ram, and if you don't keep the whole thing in ram, you can index a ton of data with 8 TB of ram, which means sharding can wait. In contrast, eBay had to shard because a Sun e10k, where they ran Oracle, could only go to 64 GB of ram, and they had no choice but to break up into multiple databases.

bigiain 5y ago
> you have some method to decide for a given key what database machine will have the data Super simple example, splitting there phone book into two volumes, A-K and L-Z. (Hmmmm, is a "phonebook" a thing that typical HN readers remember?) > you can fit a lot of database in 8 TB of ram, and if you don't keep the whole thing in ram, you can index a ton of data with 8 TB of ram, which means sharding can wait. For almost everyone, sharing can wait until after the business doesn't need it any more. FAANG need to shard. Maybe a few thousand other companies need to shard. I suspect way way more businesses start sharding when realistically spending more on suitable hardware would easily cover the next two orders of magnitude of growth. One of these boxes maxed out will give you a few TB of ram, 24 cpu cores, and 24x16TB NVMe drives which gives you 380-ish TB of fairly fast database - for around $135k, and you'd want two for redundancy. So maybe 12 months worth of a senior engineer's time. https://www.broadberry.com/performance-storage-servers/cyberstore-248n-performance-nvme https://www.broadberry.com/performance-storage-servers/cyber...
- Zababa 5y ago
  > So maybe 12 months worth of a senior engineer's time. In America. When the salaries are 2/3 times lower, people spend more time to use less hardware.