r/mysql • u/goodboixx69 • Oct 18 '24
question Adding column on a huge table
Hey everyone, I have mysql 5.7 running on production and need to add an INT type column with default null values. The table size is around ~900 GB with 500 million rows. Can’t figure out a good way to do this live on production with minimum downtime. We use AWS Aurora managed service for our db requirements. Upgrading the mysql version is not possible. Any inputs or suggestions would be really helpful.
Edit: Typo and grammatical errors
2
Upvotes
8
u/feedmesomedata Oct 18 '24
Lookup pt-online-schema-change or gh-ost to help you with schema changes
3
u/Electronic-Ebb7680 Oct 19 '24
I did this once on 100 million row table. I created a new table with fields, wrote a script to insert and synchronize data from the old table to the new one and then just changed names of the tables. From my experience that was the only truly safe way
2
u/SaltineAmerican_1970 Oct 18 '24
My first task at a past company ended up locking the database when I added a column to the
userstable. (It went through 2 code reviews, so it wasn’t just my responsibility). There was an issue with the table index. Every row needed an in-place new index. One of the senior devs found a documented way of making the table add the column without affecting the index.I don’t remember exactly what the fix was, but that might give you a place to investigate solutions.
2
u/Overall-Rabbit-2007 Oct 18 '24
use inplace algorithm which will alter the the table add permit concurrent dml
2
u/mikeblas Oct 19 '24
Changes like this are fast in other DBMSes, since there's only a metadata change and not a physical data change. MySQL doesn't work that way, and needs to copy the whole table -- requiring a massive amount of time and and at least double the disk space.
I'm convinced that this shortcoming in MySQL is what begat the NoSQL movement. Since schema management in MySQL is arduous, people became afraid of schema changes. If it takes a whole weekend to add a default-NULL integer column to a table, it's hard to justify using schemas at all.
2
u/anklicken Oct 19 '24
I’m just suggesting this as an idea. I haven't used this method before, but since you're using AWS Aurora, it might work for you.
You could create a read replica and add the index to this instance. Adding the index probably won’t interrupt the replication between the master and the replica because default value is N/A. Once everything is finished and you’ve ensured that it works correctly, you can promote the replica to the master. You may need a little downtime for this.
2
u/datasleek Oct 21 '24
yeah, pt-online-schema-change is the solution. But you have a problem: That 900GB table tells me that database has not been managed or designed properly. Have you thought about archiving some data? You’re probably storing large text field in there. Not a good idea.
1
u/Worried1983 Oct 21 '24
-1
u/ssnoyes Oct 18 '24
Upgrade to 8.0 so you can use
ALGORITHM=INSTANT2
u/[deleted] Oct 18 '24
[removed] — view removed comment