37

By default MySQL InnoDB stores all tables of all DBs in one global file. You can change this by setting innodb_file_per_table in the config, which then creates one data file for each table.

I am wondering why innodb_file_per_table is not enabled by default. Are there downsides to using it?

UpTheCreek
  • 1,598
  • 10
  • 31
  • 45

6 Answers6

36

I have the complete answer for this one.

Once innodb_file_per_table is put in place, and new InnoDB tables can be shrunk using ALTER TABLE <innodb-table-name> ENGINE=InnoDB'; This will shrink new .ibd files GUARANTEED.

If you run ALTER TABLE <innodb-table-name> ENGINE=InnoDB'; on an InnoDB table created before you used innodb_file_per_table, it will yank the data and indexes for that table out of the ibdata1 file and store it in a .ibd file, This will leave a permanent pigeon whole in the ibdata1 that can never be reused.

The ibdata1 file normally houses four types of information

Here is the guaranteed way to shrink the ibdata1 file pretty much forever...

STEP 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)

STEP 02) Drop all databases (except mysql, information_schema and performance_schema schemas)

STEP 03) Shutdown mysql

STEP 04) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

  • STEP 05) Delete ibdata1, ib_logfile0 and ib_logfile1 (see update below before deleting!)

At this point, there should only be the mysql schema in /var/lib/mysql

  • STEP 06) Restart mysql

This will recreate ibdata1 at 10MB (do not configure the option) , ib_logfile0 and ib_logfile1 at 1G each

  • STEP 07) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata and intermittent MVCC data.

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

  • mytable.frm (Storage Engine Header)
  • mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable OR ALTER TABLE mydb.mytable ENGINE=InnoDB; and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this numerous times in my career as a MySQL DBA without so much as a single problem thereafter. In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 50MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul.

UPDATE 2013-07-02 15:08 EDT

There is a caveat I have in this regard that I updated in other posts of mine but I missed this: I am updating my answer a little more with innodb_fast_shutdown because I used to restart mysql and stop mysql to do this. Now, this one-step is vital because every transaction uncommitted may have other moving parts within and outside of the InnoDB Transaction Logs (See InnoDB Infrastructure).

Please note that setting innodb_fast_shutdown to 2 would clean the logs out as well but more moving parts still exist and gets picked on Crash Recovery during mysqld's startup. Setting of 0 is best.

Community
  • 1
RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
  • Great info - thanks! 50GB>>50MB - that's pretty impressive! – UpTheCreek Feb 05 '11 at 20:56
  • Hi, i've tried to do exactly as you wrote here, the 'only' problem is that the server doesn't start afterwards. if i do service mysql start it just hangs there. If i switch back my old cnf file everything is ok. Have you got any clue on this? – Nicola Peluchetti Jul 15 '11 at 14:26
  • This question is for Nicola : Did you do Step 5 ??? – RolandoMySQLDBA Jul 15 '11 at 14:48
  • Another question for @Nicola : How much RAM does you have in your system ??? – RolandoMySQLDBA Jul 15 '11 at 14:50
  • Another question for @Nicola : What exactly did mysql do when you restarted ??? – RolandoMySQLDBA Jul 15 '11 at 14:55
  • Thanks a lot @RolandoMySQLDBA for this guide! In my case it worked except a minor issue. On MySQL 4.1.22 you have to use "innodb_flush_method=unbuffered " otherwise MySQL won't start (the option O_DIRECT is not available on that version) – Denis Fuenzalida Apr 16 '13 at 16:07
  • 2
    **Be careful!** The option `innodb_fast_shutdown=0` must be set in MySQL, before shutting it down to delete the log files! (`ib_logfile0` and `ib_logfile1`) **Otherwise, you could lose data!** – Totor Jul 02 '13 at 18:39
  • @Totor you have a good eye. I mentioned that in many posts later on such as 1) http://serverfault.com/a/509309/69271, 2) http://stackoverflow.com/a/4056261/491757, 3) http://dba.stackexchange.com/a/41555/877. 4) http://dba.stackexchange.com/a/1265/877. This post of mine is so old. I add that warning to my answer. Thank you. – RolandoMySQLDBA Jul 02 '13 at 19:02
  • @Totor I just updated my answer with the caveat. Thanks again. – RolandoMySQLDBA Jul 02 '13 at 19:09
12

See bug.

Are there downsides to using it?

  • more open files
  • open/reopen overhead
  • .ibd file does not shrink(see 1, 2)

I always use innodb_file_per_table on large databases.

alvosu
  • 8,357
  • 24
  • 22
  • Even if you don't use it, ibdata files won't shrink, either :( – minaev Feb 03 '11 at 11:36
  • 1
    Thanks. I also wonder why there is no option to have a file-per-db? – UpTheCreek Feb 03 '11 at 11:46
  • 1
    @UpTheCreek, tables are entities. Databases are logical groups of entities, rather than entities in their own right. It's more obvious with MyISAM, where databases are directories and tables are files. – John Gardeniers Feb 03 '11 at 12:31
  • 1
    Just wanted to point out that while .ibd files do not shrink _automatically_, neither does `ibdata1`, the alternative to file-per-table. At least it's possible to shrink an .ibd using `optimize table`, which is trivial compared to shrinking ibdata1. – RomanSt Apr 27 '15 at 17:11
10

innodb_file_per_table is enabled by default in MariaDB.

Alex
  • 7,789
  • 4
  • 36
  • 51
4

The reason that I opted to not use innodb_file_per_table, is because each table is put in its own file, which means each table gets its own, separate overhead (file-signatures, etc.) which causes the total, overall size of the MySQL directory to be larger than if using a shared tablespace. In addition, there is more wasted space due to cluster-slack when having multiple, small files instead of a single, large one.

Granted, the additional overhead is not a massive amount in the grand scheme of things, especially if you are using a large drive or have a giant database, but for myself (and probably many “home-users”), it all added up and was still too much for the little drive with large clusters where I was keeping my MySQL store.

For example, my database store with my WordPress databases and a few other small databases (phpBB, dev, some AMP tests, etc.), converting to per-table changed it from 32MB to 50MB, and that is not even including the ibdata1 which still requires a minimum of 10MB, for a total of at least 60MB.

Like I said, this may not be too much of a problem for some people, especially enterprises, but if you are a home-user that is only hosting your site, blog, etc. then it can indeed be a factor in things like choosing a host provider because many hosts limit the size of your database in addition to total disk-usage.

Synetech
  • 908
  • 1
  • 12
  • 27
  • 3
    I was thinking you were nuts (who cares about ten megabytes???) until you got the the point about tight quotas at hosting providers. Never would have thought of that. – Dan Pritts Dec 04 '12 at 00:10
  • @DanPritts, especially free hosts. Besides, *you* may have a giant drive, but not everyone does. I’ve expanded my main data partition from 1GB to 2GB in the last year because it was too tight, but even 10MB here and 10MB there (especially with log files) can eat it up fast. Plus, don’t forget cluster-waste adds up too. Finally, it’s not even necessarily a *hard-drive*. For example, I am currently “portablizing” my web-site so that I can host it from any system, so a 2GB flash-drive is already limited. Thus, keeping sizes small and avoiding writes is critical. And then there’s embedded systems! – Synetech Dec 04 '12 at 17:50
  • Plus, it’s not 10MB (that’s the *absolute minimum* size for `IBDATA1`). It went from 30MB to ~85MB. By deleting the whole thing and importing a dump from scratch, I ended up with a 69MB insteadof the previous 30MB (one guess which database took up over half of it ☺). For some reason, despite using per-table, my `ibdata1` is still 18MB. ☹ – Synetech Dec 04 '12 at 18:51
  • Sounds rather like something i had with a CMS install with vs. one without selinux enabled, judging from the filesizes of 32M vs. 50M. I can't really believe the numbers, how many databases do you even have that some files' metadata can add up to MEGABYTES in size on otherwise identically systems? – sjas Jun 12 '17 at 15:05
3

Just to add a bit more info

Since mysql 5.6.6 its enabled by default

PerroVerd
  • 186
  • 5
1

with innodb_file_per_table=1, drop table can get slower see Here

Alex Zheng
  • 61
  • 3
  • and 'create table' can get slower too. See http://mysql-nordic.blogspot.co.il/2015/02/getting-back-create-table-speed-of.html – Dror Harari Sep 22 '16 at 08:02