12

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
|improve this question

migrated from stackoverflow.com Oct 23 '13 at 14:43

This question came from our site for professional and enthusiast programmers.

  • Define pain, take the shortest amount of time....? Fastest results? – Alec Teal Oct 23 '13 at 13:40
  • @AlecTeal Take the shortest amount of time – Noam Oct 23 '13 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 '13 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. – ypercubeᵀᴹ Oct 23 '13 at 13:50
  • 2
    If short field names helped reduce table size, it would be 600 MB. – Jon of All Trades Oct 23 '13 at 15:07
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.

|improve this answer
  • 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 '13 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 '13 at 13:49
  • @AlecTeal Do you have any formal reference to that? – Noam Oct 23 '13 at 14:02
  • @Noam see my answer. – Alec Teal Oct 23 '13 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 '13 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
|improve this answer
  • 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. – Jakub Głazik Jan 23 '15 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.