Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We replaced Redis with MySQL for inventory reservations and it scaled

shopify.engineering

Post reply on HN
Print view View original

201–210 of 281 posts

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#201

Post by codedokode »

soontimes wrote: post #167
codedokode wrote: post #116

Could not they shard the inventory table by shop_id? As I understand, the order includes only items from one store, so there is no need to keep all the stores in a single table. Also, I wonder why they could not have a row status (available/reserved) and UPDATE it instead of deleting the rows.

They never said they don’t shard it, however this doesn’t solve the problem they were facing. Even if they have a single store (therefore a single shard), the burst demand may be high for the item in that shop, which creates contention for “remaining item quantity” resource. Their solution spreads this contention across several rows. > Also, I wonder why they could not have a row status (available/reserved) and UPDAT…

I now thought that "updating a row" might be more expensive than simply deleting because UPDATE is implemented as "mark row deleted" + "insert new version of a row" in a table which support multiple versions of a row (MVCC). So maybe using DELETE is actually faster - it just marks a row as "deleted in transaction X". Unless I forgot something.

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#202

Post by sgarland »

fauigerzigerk wrote: post #141
manbash wrote: post #8

> Instead of one row per item with a quantity column, we use one row per sellable unit. An item with 10 units has 10 rows. > But one row per unit for all inventory would break down at scale—an item with 50,000 units across 10 locations would mean 500,000 rows, and the reserve query would slow as it scans through them. Instead, we maintain a bounded pool of available rows, capped at 1,000 per item/location combination…

I agree, it does seem awfully complicated and there are quite a few pieces missing for this to be a complete solution. I'm a bit surprised about the scalability case against a simpler solution. This is not about Shopify's scale. We're talking about contention for a specific SKU of a specific seller at a specific warehouse location. How many shopping carts are competing for a single SKU at the payment stage at peak ho…

> Can this really be too much lock contention for a single database row?

When you consider how many other (often poorly-crafted) DB queries and service calls are being performed while holding the row locked, yes, it can rapidly add up. If the entire pipeline takes 500 msec, congratulations, you can’t sell more than 2 units per second of that SKU. If the item is popular and being actively hyped, then yes, that can be a problem.

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#203

Post by samudrijan »

energy123 wrote: post #165
briga wrote: post #159

Earlier quoted context omitted.

What exactly makes it "obvious" that this is written by AI? I could totally believe that AI was used to generate parts of it, but I really don't get the sense that the whole thing was written that way. I've seen way worse examples on this site. As software engineers we are constantly told that we need to heavily use these tools for our daily work. So is it surprising that software engineers use the same tools as writ…

Subheading and dot point spam, low density writing (the opposite of standard technical english), including useless detail (like enumerating stats on Shopify's scale), using contrastive parallelism, and other llm-isms. Even if it's not AI it's bad writing done by someone who has picked up AI's worst ticks. For example this subheading: > "The real bottleneck: connections, not CPU" That's two AI smells. AI likes to say…

Say the thing, not contrastive parallelism. Thanks for putting a name to that horrible LLM habit.

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#204

Post by matwood »

ChrisMarshallNY wrote: post #189
briga wrote: post #159

Earlier quoted context omitted.

What exactly makes it "obvious" that this is written by AI? I could totally believe that AI was used to generate parts of it, but I really don't get the sense that the whole thing was written that way. I've seen way worse examples on this site. As software engineers we are constantly told that we need to heavily use these tools for our daily work. So is it surprising that software engineers use the same tools as writ…

Well, one thing to remember: AI learned from us (See what I did, there?) . The training these LLMs got, was from endless human-slop, on sites like LinkedIn, and marketing copy, everywhere. In fact, don't be surprised, if we start learning from AI; reversing the process. But I think that it's only a matter of time, before almost everything will be at least touched by AI. I posted this, yesterday[0]. It wasn't a partic…

Yeah, people act like this style is new, when it absolutely is not. There's this weird romanticism of the time pre-LLMs where people imply all writing/code was perfect, and only now it's slop.

EDIT

I also read your linked comment and agree 100%.

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#205

Post by theptip »

misiek08 wrote: post #175
fauigerzigerk wrote: post #141

Earlier quoted context omitted.

I agree, it does seem awfully complicated and there are quite a few pieces missing for this to be a complete solution. I'm a bit surprised about the scalability case against a simpler solution. This is not about Shopify's scale. We're talking about contention for a specific SKU of a specific seller at a specific warehouse location. How many shopping carts are competing for a single SKU at the payment stage at peak ho…

We observe 200+ (can’t say closer number) purchases per second for single SKU with good marketing and price. The other thing that bothers me - why not real stable, but maybe „too old, medieval” solution with Redis as the main source of through - without any sync with SQL at all in terms of stock… it worked in my previous job with much higher traffic (1000s/s). Yup, we ended up with app-side sharding, but it was stupi…

You need a sql DB for the actual purchase transaction, you can’t keep financial records in Redis.

As they say in TFA they used Redis for the cart reservation but then you need to sync the two stores.

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#206

Post by drowsspa »

briga wrote: post #159
ramon156 wrote: post #143

Why even have a blog when you can't be arsed to write the posts. This is so obviously LLM-written. I have a positive view of Shopify engineers, but this kind of made a dent in that confidence.

What exactly makes it "obvious" that this is written by AI? I could totally believe that AI was used to generate parts of it, but I really don't get the sense that the whole thing was written that way. I've seen way worse examples on this site. As software engineers we are constantly told that we need to heavily use these tools for our daily work. So is it surprising that software engineers use the same tools as writ…

Using AI is a pretty good indicator that the reader will need to exert more effort than the writer did. Unless the reader also decides to use AI to summarize it, but then what's the point?

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#207

Post by sgarland »

codedokode wrote: post #201
soontimes wrote: post #167

Earlier quoted context omitted.

They never said they don’t shard it, however this doesn’t solve the problem they were facing. Even if they have a single store (therefore a single shard), the burst demand may be high for the item in that shop, which creates contention for “remaining item quantity” resource. Their solution spreads this contention across several rows. > Also, I wonder why they could not have a row status (available/reserved) and UPDAT…

I now thought that "updating a row" might be more expensive than simply deleting because UPDATE is implemented as "mark row deleted" + "insert new version of a row" in a table which support multiple versions of a row (MVCC). So maybe using DELETE is actually faster - it just marks a row as "deleted in transaction X". Unless I forgot something.

That's how Postgres' (and perhaps others) MVCC works, yes. MySQL / InnoDB, however, updates tuples in-place [0], and uses the undo log to recreate older versions as needed.

0: https://dev.mysql.com/doc/refman/8.4/en/innodb-multi-version...

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#208

Post by giancarlostoro »

KronisLV wrote: post #169
energy123 wrote: post #165

Earlier quoted context omitted.

Subheading and dot point spam, low density writing (the opposite of standard technical english), including useless detail (like enumerating stats on Shopify's scale), using contrastive parallelism, and other llm-isms. Even if it's not AI it's bad writing done by someone who has picked up AI's worst ticks. For example this subheading: > "The real bottleneck: connections, not CPU" That's two AI smells. AI likes to say…

I really wish that slop writing was disincentivized in whatever RLHF they do. I don’t want to read the weird LinkedIn pop-sci tone for the rest of my life in such amounts. I wonder if the average reader is also getting annoyed like this or whether they just don’t care - especially seeing what seems to get upvoted on your run of the mill social media sites. They probably collectively shape things more than I do.

The LinkedIn “techbro” writings are the worst. They talk like a cryptobro “APPLE JUST PUBLISHED A CODE REPOSITORY ON THEIR HAND CRAFTED LLM ANYONE CAN USE THIS TO COMPETE WITH OPENAI AND ANTHROPIC” and its some random simple LLM on github that handles very little compared to OpenAI and Anthropic.

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#209

Post by bijowo1676 »

codedokode wrote: post #119
bijowo1676 wrote: post #51

Earlier quoted context omitted.

you should, their design is not the best. There is middle ground between "one row per SKU" and "1000 rows per SKU". Its called one row per shopping cart*SKU combo. if two people order 100 and 500 items of the same SKU, respectively, the table should have only two rows: for order1 and order2. Not 600 rows.

The problem is that in this case you have to do splits/merges. And while there are products that are sold by 100 units at a time, I think in most cases people by 1-2 items so the hassle might be not worth it. Also you might not understand the original problem. Imagine if 100 customers want to buy product A. One thread starts a transaction, searches for amount of product A and UPDATE's it and goes searching for other…

ok, lets model situation of 100 customers and one last remaining item. Who will get the last item?

in shopify's design, it is a user who was the first to lock the row and have successful payment. Sounds good, but how often does it happen ? It's a rare and extreme case and they model their entire system after the rare even, and incur the overhead of 1000 rows per SKU per shop for all combination of SKU and shop_id for all the normal items that are not sold out in flash sale.

the same outcome could be achieved without locking and without creating 1000 rows:

  1. keep track of all active carts at the checkout in a table
  2. for each cart, record the timestamp in nanoseconds when user clicked Pay (but I would prefer timestamp of clicking Checkout)
  3. that timestamp will decide who gets the last available item.
  4. in a shopping cart, have explicit field for each SKU: inventory_reserved. 
  5. this decision mechanism is now explicit via global monotonic non-decreasing counter. It is no longer tied to payment processing gateway timeouts, not opaque and implicit mechanism relying on database internals and quirks of how DB engine locks and releases some placeholder rows.

Re: We replaced Redis with MySQL for inventory reservations and it scaled

#210

Post by bijowo1676 »

JamesSwift wrote: post #174
bijowo1676 wrote: post #91

Earlier quoted context omitted.

i also work in big tech and know that a lot of bullshit design creeps into system design and prod, because everyone is overworked, overstressed, wants to just get things done for the quarterly performance review as to not get shitcanned with severance re concurrency, it is not a big issue at all. stock exchanges deal with HFT traders and can easily deal with concurrency of orders. Same can be implemented with shopify…

Famously, stock is settled on a delay (and generally doesnt involve physical products that are not fungible). Im sure theres a lot to glean from how they handle concurrency but Im not sure they are solving the same problems.

settlement is a different process, what exchanges are doing is they match Buy and Sell orders.

you have an open Sell 1 APPL for $100.0. Millions of other HFT orders rush to scalp your single order. How do you think exchange matches your Sell to HFT's Buy orders? which Buy order gets fulfilled first?

Post reply on HN