How big is too big for ibdata file?

When this can be a problem

If you run show table status on a table and the Data_free field makes up the vast majority of your ibdata1 file's size, then you may have a lot of wasted space. A great deal of insert / delete will make that an issue. If that's the case and transient insertions and deletions make up the bulk of your data, then you have a good case for file per table.

That's not an automatic "yes", though. There's a lot of talk in the world about internal fragmentation inside of InnoDB files, but putting them into a filesystem as file-per-table just moves your fragmentation to the filesystem level instead of the database level.

Why this usually isn't a problem

Think of your InnoDB file as a filesystem rather than a file. If you have a lot of files, you'll need a big filesystem.

For the most part, filesystems do really well at scaling up to handle terabytes of data and untold numbers of files. Sometimes they run into issues with poor indexing (e.g., limits to the number of files in a directory before a performance impact), but for the most part the modern filesystem can rock out well into the terabyte range.

InnoDB functions the same way. The size of your data file can be huge... and like large filesystems, that can present issues with backing up your data. However, just as splitting your filesystem into multiple partitions doesn't help with this issue, neither does trying to manipulate innodb. While you can use innodb_file_per_table, I rarely recommend it.

Much like your filesystem, the better answer is to know the limits internally and work within that. Understand indexes and apply them appropriately. Don't work at trying to split up InnoDB, it isn't meant for that.

Since I'm struggling to constructively convey the concept, here's a quick read that words this better than I can: Terabytes is not big data, petabytes is.

I remember a really really old MySQL marketing slide where the customer was running a data warehouse with some terabytes. Many years ago. InnoDB or MyISAM, both would work. This is standard off the rack MySQL stuff.

Don't sweat a 15GB database.

ibdata files dont shrink - if you recently dropped some tables or removed a lot of rows - innodb in your config will not release the free space back to the filesystem. i'd suggest you:

• backup all of your data eg with mysqldump
• add to my.cnf innodb_file_per_table directive
• restart mysql
• drop all databases using innodb engine
• stop mysql
• remove ibdata file
• rm ib_logfile[01]
• start mysql, check syslog if all is ok
• reload your dump

in this way you'll be able to reclaim the space whenever you drop innodb table/database - associated idb files will be removed immediately.