ฝัง
- เผยแพร่เมื่อ 4 มิ.ย. 2024
- System Design for SDE-2 and above: arpitbhayani.me/masterclass
System Design for Beginners: arpitbhayani.me/sys-design
Redis Internals: arpitbhayani.me/redis
Build Your Own Redis / DNS / BitTorrent / SQLite - with CodeCrafters.
Sign up and get 40% off - app.codecrafters.io/join?via=...
In this video, I explained how Shopify rebalances data shards in its MySQL database without downtime. A scalable system must handle excessive load by moving data between shards. Shopify's tool, Ghostferry, performs batch copying and tailing bin logs to ensure data integrity during migration. Factors like historical utilization and traffic help decide shard allocation. The process involves automating data movement, ensuring availability, preventing data loss, and optimizing performance. The phased approach includes batch copying, capturing live events, and updating routing tables to achieve seamless data migration.
Recommended videos and playlists
If you liked this video, you will find the following videos and playlists helpful
System Design: • PostgreSQL connection ...
Designing Microservices: • Advantages of adopting...
Database Engineering: • How nested loop, hash,...
Concurrency In-depth: • How to write efficient...
Research paper dissections: • The Google File System...
Outage Dissections: • Dissecting GitHub Outa...
Hash Table Internals: • Internal Structure of ...
Bittorrent Internals: • Introduction to BitTor...
Things you will find amusing
Knowledge Base: arpitbhayani.me/knowledge-base
Bookshelf: arpitbhayani.me/bookshelf
Papershelf: arpitbhayani.me/papershelf
Other socials
I keep writing and sharing my practical experience and learnings every day, so if you resonate then follow along. I keep it no fluff.
LinkedIn: / arpitbhayani
Twitter: / arpit_bhayani
Weekly Newsletter: arpit.substack.com
Thank you for watching and supporting! it means a ton.
I am on a mission to bring out the best engineering stories from around the world and make you all fall in
love with engineering. If you resonate with this then follow along, I always keep it no-fluff. - วิทยาศาสตร์และเทคโนโลยี
Another approach could be that instead of moving the shop which was making the shard hot we can move the other shops' rows to rebalance the load !
Great video Arpit!!
One more step is missing after cutover, before we prune old records from source database, either we need to stop Kafka consumer listening to delete CDC events or make an entry in consumer to filter/ignore these records otherwise we may end up applying these delete on target database as well :)
Hahhahaha 😂😂😂 true. We should definitely switchoff the CDC once everything caught up 😀😀
It once happened with me and I was thinking where did the data go???? 😂😂😂😂😂
@@AsliEngineering 😅
Very well explained👏🏼. Thank you Arpit.
Great notes and content. Awesome bro. Keep rocking
Great video Aprit. Thanks 🙏🏻
Hello @arpit, Thanks for the great video won't they have a replication/ data warehouse to store the data incase of some failure if yes cant we do bulk load from the place instead of reading from the current DB?
Very well explained
good stuff but shouldn't this be in phased manner like first stopping read from DB1(write still going on) and then stop writes or having some sort of phased treatment/dialup?
I absolutely love the content of this channel. Thank you, Arpit.
Do we have any channel out there that posts similar content talking about engineering challenges and how they are solved? If we have any please comment them. Thanks in advance.
Hello @arpit, I see you are talking only about the data but how about indexes ? so shopify doesn't use indexes on those table ? i don't think that could be a case.
Indexes are implicitly managed by the database.
Wondering what would happen during the Black Friday sale, multiple stores (or shops) will be hot. Additionally, a very small downtime (even half a minute) would be of great business loss
Hi arpit, great video and really appreciate your efforts...
have a few doubts it will be great if you or anyone here can clearify those.
1. If we move a particular shop(which was making the shard hot) from one shard to another, how will it solve the issue? I mean wouldn't it just make our other shard(where we copied data) hot?
2. If we batch copy data from hot shard wouldn't that increase the load on the shard and might bring it down? I don't exactly know how much load the batch copying will bring in...
3. What would be ideal threshold, of load on shard(e.g. 80%, 70% resource utilisation), after which we should think about moving data? or it is when we see some large difference between resource utilisation among various shard present
A hot partition problem is a subjective problem and is often related to Database Internals.
- It involves dealing with any kind of mandatory operation that couldn't wait any longer say "backup, compaction/VACCUM, Write Repair, Read Repair ", and these operations often add additional IO networking to the already occupied CPU core. And following that DB goes into an imbalanced state.
- Shiting of shards involves the redistribution of the data volume as well as offloading of mentioned operation to other cores/NUMA sockets.
- So rectifying the hot partition problem is more about rectifying the imbalance.
- Don't rely on a database unless its shard balance mechanism is identical to the application need
From the perspective of DB, only replicas can suffer from hot partition problems. However, this is not true for many applications and hence Shopify has an application-space driver, and "Batch copying" is a lot more economical especially when you have more control over the driver that triggers the same batch operation. This is in contrast with DB tooling which can only offer some amount of abstract control.
"There is no threshold until you start having one" i.e. application-space driver for shard migration would work on application-specific load pattern.
Firstly thanks Arpit bhaiya for explaining in such ease!!
Now even I wanted to ask about the second point which Sagar Nikam mentioned - won't it put more load on our initial shard when we copy data from it to another one?
1. the non-hot shop are being moved to reduce the load on the pod x db which has higher load due to shop m
2. This is via bin-log so it is mysql all transactions are logged in a "file" which can be used to replicate the data to different databases or messaging system (see. debezium)
3. That really depends on the use cases and the impact on the system due to the load, which is very much to 'trying' out different configurations (and as mentioned in the video, its driven by analytics team)
@@dharins1636 ohh thanks, didn't knew about Debezium
What if the lag never dies? That is the there are always new writes to the old DB.
Lag has to ear off it is a function of CPU utilisation.
There is a possibility that lag is increasing because.of an underlying hardware failure and in that case you create a new replica with a recent snapshot requiring you a minimal time to catch up.
@@AsliEngineering I understand the data pump would transfer the data from the new db to the old one. However, the NGINX is still sending the requests for shop 2 to the old db. So there seems to be no guarantee that there wouldn't be any writes to the old db. Or is there something which I am completely missing?
@Swagato Chatterjee writes are cutoff from old db for sometime through a knob. Covered in video.
You take a small downtime (not really a downtime but a glitch with retries) and stop accepting writes for that shop altogether so that other DB could catchup.
@@AsliEngineering thanks it makes sense now.
I've one question. Won't they already have replicas? Can't they copy from replicas, why put load on master?
What if some row gets updated after we copied that row
The big log would take care of that
bin-log is serial hence it tracks all the changes with respect to the changes done in time :)
@@dharins1636 okay so we have to replicate that entire query on new db. Is that right?
@@tesla1772 Yes, we do copy that binlog up until the current change to new replica + new request would also be queued up AFTER binlog to be applied on new replica. Because if we send new database requests(insert, delete) before applying binlog, data integrity would be lost.