How big is too big for ibdata file?

My ibdata file is very large, at least it seems to me to be very large. Is this excessive or not that bad?

-rw-rw---- 1 mysql mysql  15G Apr 18 10:11 ibdata1

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.

Check more discussion of this question.

Related posts:

1. How can I make sense out of ibdata
2. copy a MySQL table which has data in ibdata file
3. MySQL cancelled import – orphaned data in ibdata file
4. MySQL moving ibdata & ib_logfile
5. How do I free up deleted data to the filesystem in MySQL