mysql table to big_Altering MySQL table column type from INT to BIGINT

文章讲述了在使用Amazon RDS的PostgreSQL数据库时,如何处理因删除记录导致的ID间隙问题。作者建议了一种避免停机的多步骤方法,包括创建新的bigint列填充ID,夜间后台填充,更新主键,最后删除旧列。这种方法适用于有限的IOPS资源和不允许停机的情况。
摘要由CSDN通过智能技术生成

We had exactly same scenario but with postgresql, and i know how 50M fills up the whole range of int, its gaps in the ids, gaps generated by deleting rows over time or other factors involving incomplete transactions etc.

I will explain what we ended up doing, but first, seriously, testing a data migration for 50M rows on 2k rows is not a good test.

There can be multiple solutions to this problem, depending on the factors like which DB provider are you using? We were using mazon RDS and it has limits on runtime and what they call IOPS(input/output operations) if we run such intensive query on a DB with such limits it will run out of its IOPS quota mid way throuh, and when IOPS quota runs out, DB ends up being too slow and kind of just useless. We had to cancel our query, and let the IOPS catch up which takes about 30 minutes to 1 hour.

If you have no such restrictions and have DB on premises or something like that, then there is another factor, which is, if you can afford downtime?**

If you can afford downtime and have no IOPS type restriction on your DB, you can run this query directly, which will take a lot fo time(may half hour or so, depending on a lot of factors) and in the meantime

Table will be locked, as rows are being changed, so make sure not only this table is not getting any writes, but also no reads during the process, to make sure your process goes to the end smoothly without any deadlocks type situation.

What we did avoiding downtimes and the Amazon RDS IOPS limits:

In my case, we had still about 40M ids left in the table when we realized this is going to run out, and we wanted to avoid downtimes. So we took a multi step approach:

Create a new big_int column, name it new_id or something(have it unique indexed from start), this will be nullable with default null.

Write background jobs which runs each night a few times and backfills the new_id column from id column. We were backfilling about 4-5M rows each night, and a lot more over weekends(as our app had no traffic on weekends).

When you are caught up backfilling, now we will have to stop any access to this table(we just took down our app for a few minutes at night), and create a new sequence starting from the max(new_id) value, or use existing sequence and bind it to the new_id column with default value to nextval of that sequence.

Now switch primary key from id to new_id, before that make new_id not null.

Delete id column.

Rename new_id to id.

And resume your DB operations.

This above is minimal writeup of what we did, you can google up some nice articles about it, one is this. This approach is not new and pretty much common, so i am sure you will find even mysql specific ones too, or you can just adjust a couple of things in this above article and you should be good to go.

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值