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

141–150 of 282 posts

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

#141

Post by fauigerzigerk »

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 hours? Can this really be too much lock contention for a single database row?

I realise Shopify engineers are neither stupid nor inexperienced. Hence my surprise. I would have liked to hear more about that specific problem.

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

#144

Post by gregoriol »

progx wrote: post #142

Why is it so hard for many people to accept, that this is a solution for a specific problem of shopify? They did not say that Redis is bad and MySql is good. They only a solve their problem.

Even the good teams may make bad choices, that's why it is interesting to read their ideas and discuss them

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

#145

Post by 21asHak »

They are hiring with AI slop:

https://www.shopify.com/careers/disciplines/engineering-data

Pair programming and forced AI, that sounds like absolute hell. Glorification of Lütke who didn't do that much in open source and now props up his ego by thinking "AI can do it so it wasn't all that difficult all along."

I don't think he ever worked on complex parts of Ruby. The people he now oppresses did.

Ruby should note that this company is actively repelling people from using the language. I really want to switch, but then I see Claude contributions in Ruby core, the influence of this slop company, and think it isn't worth it.

Oh, and they bought DHH in 2024 for his 180° turnaround on AI. He is now an AI booster, so Rails is out of the question as well.

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

#146

Post by codedokode »

bijowo1676 wrote: post #59
soontimes wrote: post #56

Earlier quoted context omitted.

Ok, but before inserting you must ensure that inventory is not depleted, which means you need to know the count and you need to lock the row. So you still have contention on that item. Them having a 1k buffer allows not to take a lock on a single row every time, and only do it when buffer is empty

there is no need to lock the row, since you a dealing with a shopping cart, not individual item piece. when you run aggregate functions, lock is no needed, it is actually better to run it with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; for aggregation the check for oversold items is extremely cheap: with current_order as ( select $SKU1, $q2 as quantity union select $SKU2, $q2 as quantity ), with carts as ( sel…

The item is reserved when the user decides to place an order, but before paying for it. Not when a product is added to the cart because the user can keep it there for a month and end up not buying.

You reserve the product by creating an "active_cart" entry. Your solution has a problem, that when you run the check, it might say the product is available, but before you create an "active_cart" to reserve it from thread A, another thread B reserves it and you end up reserving a product that is not available anymore. You end up with SUM(active_cart.quantity) > inventory.available_units.

That is exactly why the database has locks - to prevent this situation. With locks, thread A decrements inventory.available_units and that row is locked until the end of transaction. Other threads (if they do SELECT FOR UPDATE instead of SELECT) cannot see the old, invalid value until thread A either commits and the value is updated or rollbacks. However, locks cause performance issues and that is why shopify uses the architecture from the article - instead of 100 users fighting for the lock on the same row with available amount, each user locks only rows with units they plan to buy.

Interestingly, MySQL docs has the documentation page with a similar case: https://dev.mysql.com/blog-archive/mysql-8-0-1-using-skip-lo...

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

#147

Post by kgeist »

znpy wrote: post #97
nurettin wrote: post #93

At that revenue, why not make your own filesystem, database and index structure? There is no way mysql is the best possible software for this use case. Why stop innovation and hand everything over to ops?

Most likely? Time. Using off the shelf software means you mostly design how to plumb things together and how to make them correct , safe and scalable. The things you mention, on the other hand, carry the same requirements but are also much complex to develop AND to maintain.

The social network VK internally uses highly specialized database engines per business domain. They don't use stock DBs. They have a DB engine for posts, a DB engine for likes, etc. They have a team of DB engineers. Their DB load was around 250 mln RPS 3 years ago. Stock DBs were harder to scale for them. I guess if you have immense highload, having a team of DB engineers can be cheaper because you can save a lot on servers. I reviewed their code. A DB engine's source code is pretty compact and simple (relatively speaking) because they deal with very specific domain entities, so they don't have to account for all the possible user query combinations that a general-purpose DB would have to support. It was mostly shards+binlog+snapshots+views in RAM. Considering that Telegram was founded by former VK engineers, I suspect they have something similar.

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

#148

Post by codedokode »

el1s7 wrote: post #127
codedokode wrote: post #122

Earlier quoted context omitted.

Redis doesn't have transactions and persistence. No persistence means the data gets lost if machine shuts down or process crashes. Furthermore, after restart you will need to regenerate the data which can take time. That's why Redis is a cache and not a database. You can fix the persistence issue (Redis can write WAL log, don't remember if it does fsync or not), but then Redis won't be able to handle those thousands…

Everything you said is incorrect. Redis does have transactions, as well as data persistence. All cloud providers provide managed redis instances with automatic backups as well.

I mentioned that Redis can write changelog (called AOF in the docs [1]). However, if you tell it to do fsync on every update (like SQL databases do), it stops being that fast and spends time waiting for the filesystem.

Furthermore, the RDB snapshot mechanism (when Redis forks and forked process writes the snapshot) can double memory consumption and cause thousands of page faults in Redis process if there are many writes happening.

The docs contains corresponding warnings. "Cloud backups" are marketing terms and not ACID guarantees.

As one more disadvantage, Redis has no SQL and you cannot easily view the data.

As for transactions, indeed it seems to have them, but their execution is serialized, i.e. when MySQL can prepare 100 transactions in parallel, Redis will execute them sequentially.

[1] https://redis.io/docs/latest/operate/oss_and_stack/managemen...

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

#149

Post by kevincox »

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…

Flash sales are a huge scaling issue for Shopify. There are celebrities who want to sell thousands of items in a few minutes window at the end of an advertised countdown.

Basically this is an incredibly rare case but a feature that they want to support.

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

#150

Post by codedokode »

jdw64 wrote: post #124
codedokode wrote: post #122

Earlier quoted context omitted.

Redis doesn't have transactions and persistence. No persistence means the data gets lost if machine shuts down or process crashes. Furthermore, after restart you will need to regenerate the data which can take time. That's why Redis is a cache and not a database. You can fix the persistence issue (Redis can write WAL log, don't remember if it does fsync or not), but then Redis won't be able to handle those thousands…

Does Redis become that slow when you enable both AOF and RDB? Sure, there's a write cost, but it doesn't lose its ability to maintain tens of thousands of connections. Redis supports AOF and lets you choose the fsync policy. But I think using only MySQL is unnecessarily expensive, just to get single transaction tracking for bug tracing. So the article's argument seems to be: 'Use only MySQL as a solution to the distr…

The fsync policy equivalent to SQL database would be "fsync on each change before reporting successful update to the app". Redis (as many NoSQL databases) also doesn't have SQL and is a pain to view the data, you need to write extra tools when investigating the problems.

RDB snapshots can cause multiple page faults due to use of fork() and CoW.

> It's easier to scale

The company in question manages online stores and they could easily scale by allocating a separate database for each store (sharding).

> But I think using Redis is much more elegant.

I cannot agree because I think using a single database for all the data is more elegant, than multiple different databases and there are less problems to deal with. I dislike microservice-style architecture strongly and believe it is mostly good for wasting company's money.

> 'Use only MySQL as a solution to the distributed transaction consistency problem between two different storage systems, Redis and MySQL!'

I read it as "do not create unnecessary work by using a single database".

Post reply on HN