9

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
nachito
  • 245
  • 1
  • 4
  • 11

2 Answers2

13

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.

Jeff Ferland
  • 20,547
  • 2
  • 62
  • 85
  • It looks like I'm just under 50% utilized. `InnoDB free: 7364608 kB` – nachito Apr 18 '12 at 15:57
  • @nachito That sounds like it should be fine. If you feel you need the space back for the filesystem, reclaim it. If you're just worried about performance, don't. Also, for performance, it's best to keep your database files on their own partition, so if you're focusing on performance you wouldn't see a usable reclaim anyway. – Jeff Ferland Apr 18 '12 at 16:05
6

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.

Igor Kryltsov
  • 103
  • 3
pQd
  • 29,981
  • 6
  • 66
  • 109
  • I understand it won't ever shrink w/o dumping and reloading the tables. However, if I have dropped InnoDB databases will it use that "free" space before it starts to grow again? – nachito Apr 18 '12 at 15:41
  • 1
    @nachito Yes, it will. – Jeff Ferland Apr 18 '12 at 15:55
  • http://dev.mysql.com/doc/refman/5.0/en/innodb-file-space.html and http://stackoverflow.com/q/634257/90322 – Jeff Ferland Apr 18 '12 at 16:21
  • Your answer is just a straightforward suggestion without clouding the issue like my answer seems to have done. +1 !!! – RolandoMySQLDBA Apr 18 '12 at 17:25
  • 1
    Well, the answer is a solution to reducing the space used by the data files, but it's not a discussion of the merit related to that, and that's the question as I read it. Is a 15GB ibdata file a big deal? – Jeff Ferland Apr 18 '12 at 17:26