Arpit's Newsletter read by 90000+ engineers
Weekly essays on real-world system design, distributed systems, or a deep dive into some super-clever algorithm.
On the 5th of May, 2020, GitHub experienced an outage because of this very reason. One of their shared table having an auto-incrementing ID column hits its max limit. Let’s see what could have been done in such a situation.
GitHub used 4 bytes signed integer as their ID column, which means the value can go from -2147483648
to 2147483647
. So, now when the ID column hits 2147483647
and tries to get the next value, it gets the same value again, i.e., 2147483647
.
For MySQL, 2147483647
+ 1
= 2147483647
So, when it tries to insert the row with ID 2147483647
, it gets the Duplicate Key Error given that a row already exists with the same ID.
A situation like this is extremely critical given that the database is not allowing us to insert any row in the table. This typically results in a major downtime of a few hours, and it depends on the amount of data in the table. There are a couple of ways to mitigate the issue.
Quickly fire the ALTER
table and change the data type of the ID column to UNSIGNED INT
or BIGINT
. Depending on the data size, an ALTER query like this will take a few hours to a few days to execute. Hence this approach is suitable only when the table size is small.
The idea here is to create an empty table with the same schema but a larger ID range that starts from 2147483648
. Then rename this new table to the old one and start accepting writes. Then slowly migrate the data from the old table to this new one. This approach can be used when you can live without the data for a few days.
Although mitigation is great, it is better to place a monitoring system that raises an alert when the ID reaches 70% of its range. So, write a simple DB monitoring service that periodically checks this by firing a query on the database.
Here's the video ⤵
Alongside my daily work, I also teach some highly practical courses, with a no-fluff no-nonsense approach, that are designed to spark engineering curiosity and help you ace your career.
A no-fluff masterclass that helps experienced engineers form the right intuition to design and implement highly scalable, fault-tolerant, extensible, and available systems.
An in-depth and self-paced course for absolute beginners to become great at designing and implementing scalable, available, and extensible systems.
A self-paced and hands-on course covering Redis internals - data structures, algorithms, and some core features by re-implementing them in Go.
Arpit's Newsletter read by 90000+ engineers
Weekly essays on real-world system design, distributed systems, or a deep dive into some super-clever algorithm.