InnoDB fun

I’m running a few servers, some of them have MySQL on it. Obviously some tables use InnoDB as the storage engine, which by default is not configured really well. If you don’t have a lot of disk space, or just don’t like wasting space the default InnoDB setting will give you some headache. Developers decided that each and every InnoDB table’s data should be written to a set (by default only one) common file. The MyISAM storage engine creates a separate file for each table, so when you optimize the tables space occupied by deleted records and such is given back to the OS, the actual file size is reduced. InnoDB on the other hand can’t do something like this because the tables’ data is scattered throughout the file, so even if you delete data from tables and optimize them you can’t reclaim disk space. The only way to go about this is to dump all the database which have InnoDB tables, drop those databases, stop MySQL, add innodb_file_per_table=true to your my.cnf file under [mysqld] section, delete the ib_logfilen and ibdatan files, restart MySQL and reload your dump. This will make InnoDB behave like MyISAM, creating one file for each table. Alternatively you can only dump, drop and reload InnoDB tables, but this may not be feasible depending on the number of tables and databases 🙂