{"id":3304,"date":"2011-02-15T10:01:15","date_gmt":"2011-02-15T08:01:15","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=3304"},"modified":"2011-02-15T10:01:15","modified_gmt":"2011-02-15T08:01:15","slug":"upgrading-to-barracuda-getting-rid-of-huge-ibdata1-file","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/upgrading-to-barracuda-getting-rid-of-huge-ibdata1-file","title":{"rendered":"Upgrading to Barracuda & getting rid of huge ibdata1 file"},"content":{"rendered":"

Some of this is old stuff, but more people are now converting to InnoDB plugin, so as to enjoy table compression, performance boosts. Same holds for people converting to Percona’s XtraDB. InnoDB plugin requires innodb_file_per_table<\/strong>. No more shared tablespace file.<\/p>\n

So your ibdata1<\/strong> file is some 150GB<\/strong>, and it won’t reduce. Really, it won’t reduce. You set innodb_file_per_table=1<\/strong>, do ALTER TABLE t ENGINE=InnoDB<\/strong> (optionally ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8<\/strong>), and you get all your tables in file-per-table .ibd<\/strong> files.<\/p>\n

But the original ibdata1<\/strong> file is still there. It has to be there, don’t delete it! It contains more than your old data.<\/p>\n

InnoDB tablespace files never reduce in size, it’s an old-time annoyance. The only way to go round it, if you need the space, is to completely drop them and start afresh. That’s one of the things so nice about file-per-table: an ALTER TABLE<\/strong> actually creates a new tablespace file and drops the original one.<\/p>\n

The procedure<\/h4>\n

The procedure is somewhat painful:<\/p>\n