13

I need to change a datatype from INT to BIGINT in a 600GB MySQL table. The column has a unique index. I might be good with unsigned INT, but I assume changing to that or BIGINT will be pretty much same pain. The table's engine is InnoDB. What would be easier:

  1. ALTER TABLE
  2. Copying structure and INSERT INTO (SELECT *)
  3. Dumping table and changing dump file table definitions
  4. Anything else?

UPDATE: As requested, MySQL ver 5.5.15, no foreign keys and create table:

 CREATE TABLE `tbl` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    `created_at` datetime NOT NULL,
    `tid` bigint(20) NOT NULL,
    `t` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `f` tinyint(1) NOT NULL,
    `i_id` bigint(20) NOT NULL,
    `ir_id` int(11) NOT NULL,
    `r_c` int(11) NOT NULL,
    `r` tinyint(1) NOT NULL,
    `e` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `t` varchar(5) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `user` (`user_id`,`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=1657146169 DEFAULT CHARSET=utf8
8
  • Define pain, take the shortest amount of time....? Fastest results?
    – Alec Teal
    Oct 23, 2013 at 13:40
  • @AlecTeal Take the shortest amount of time
    – Noam
    Oct 23, 2013 at 13:43
  • Although it doesn't answer the question and this question was probbly discussed before the table getting that big, I would also look for a way to reduce this table's size.
    – Bartdude
    Oct 23, 2013 at 13:48
  • 1
    Which version of MySQL? It matters as some versions have ALTER TABLE ONLINE. Do you have foreign keys referencing this column? It would help if you showed the SHOW CREATE TABLE tablename; output. Oct 23, 2013 at 13:50
  • 2
    If short field names helped reduce table size, it would be 600 MB. Oct 23, 2013 at 15:07

2 Answers 2

2

Assuming your table does not have any triggers on it, you should consider using pt-online-schema-change since it will allow you to ALTER the table without locking it.

It will still take a fair amount of time given the size of the table.

Also, with this method or with ALTER TABLE you will need to make sure you have 600 GB of extra disk space to support two copies of the table while it is being rebuilt.

5
  • Got any time estimate? (I know it depends on many factors, but if u had to guess, what would be the range)
    – Noam
    Oct 23, 2013 at 13:47
  • @Noam you don't need to copy the table. If you use Alter table the WORST CASE is that it copies, if you are using a mature engine, it will just make a note to itself that anything before (some location in file or some record) uses int, anything post uses bigint, until you optimise.
    – Alec Teal
    Oct 23, 2013 at 13:49
  • @AlecTeal Do you have any formal reference to that?
    – Noam
    Oct 23, 2013 at 14:02
  • @Noam see my answer.
    – Alec Teal
    Oct 23, 2013 at 14:03
  • 1
    @Noam I'd estimate the ALTER TABLE could take 24 - 96 hours to run. You can get a better estimate by running the ALTER TABLE in a test environment.
    – Ike Walker
    Oct 23, 2013 at 14:28
2

Using percona toolkit pt-online-schema-change would be my choice on production without affecting the use. This will add some triggers to get your deltas and a temporal table that would rename after this is done.

example:

pt-online-schema-change --alter "CHANGE `id` `id` BIGINT  NOT NULL; " D=DB,t=TABLE
1
  • pt is the way to go. @Noam FYI: INT->BIGINT migration on a ~270GB table with a lots of rows on EC2 instance with quite heavy write activity on this table took 64hours using pt-online-schema-change. Jan 23, 2015 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.