Comments on: Reasons to use innodb_file_per_table https://shlomi-noach.github.io/blog/mysql/reasons-to-use-innodb_file_per_table Blog by Shlomi Noach Thu, 18 Dec 2014 08:48:26 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: MySQL performance tips for Zabbix | Zabbix Zone - 시스존 https://shlomi-noach.github.io/blog/mysql/reasons-to-use-innodb_file_per_table/comment-page-1#comment-297259 Thu, 18 Dec 2014 08:48:26 +0000 https://shlomi-noach.github.io/blog/?p=614#comment-297259 […] Some discussions about this: http://dom.as/2009/05/21/innodb-tablespace/ https://shlomi-noach.github.io/blog/mysql/reasons-to-use-innodb_file_per_table […]

]]> By: Optimizing MySQL server settings | Codingpedia.org https://shlomi-noach.github.io/blog/mysql/reasons-to-use-innodb_file_per_table/comment-page-1#comment-220738 Sat, 12 Oct 2013 07:29:44 +0000 https://shlomi-noach.github.io/blog/?p=614#comment-220738 […] the same as if you had this disabled, but you have now the possibility to rebuild the table. Follow Reasons to use innodb_file_per_table for more […]

]]> By: innodb_file_per_table for existing MySQL database | Olof Larsson https://shlomi-noach.github.io/blog/mysql/reasons-to-use-innodb_file_per_table/comment-page-1#comment-208628 Wed, 22 May 2013 13:37:41 +0000 https://shlomi-noach.github.io/blog/?p=614#comment-208628 […] https://shlomi-noach.github.io/blog/mysql/reasons-to-use-innodb_file_per_table […]

]]> By: MySQL标记贴 | 我爱花生米 https://shlomi-noach.github.io/blog/mysql/reasons-to-use-innodb_file_per_table/comment-page-1#comment-80335 Tue, 27 Mar 2012 13:54:32 +0000 https://shlomi-noach.github.io/blog/?p=614#comment-80335 […] MySQL InnoDB的幻读 MySQL索引背后的数据结构及算法原理 Jeremy Zawodny on MySQL Reasons to use innodb_file_per_table This entry was posted in DataBase, Paste and tagged InnoDB, MySQL. Bookmark the permalink. […]

]]> By: MySQL performance tips for Zabbix | Zabbix Zone https://shlomi-noach.github.io/blog/mysql/reasons-to-use-innodb_file_per_table/comment-page-1#comment-58013 Tue, 08 Nov 2011 10:33:52 +0000 https://shlomi-noach.github.io/blog/?p=614#comment-58013 […] Some discussions about this: http://dom.as/2009/05/21/innodb-tablespace/ https://shlomi-noach.github.io/blog/mysql/reasons-to-use-innodb_file_per_table […]

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/reasons-to-use-innodb_file_per_table/comment-page-1#comment-55313 Tue, 18 Oct 2011 12:29:16 +0000 https://shlomi-noach.github.io/blog/?p=614#comment-55313 Well, you’re in a serious problem.
I would tell you to open your backup, but I’m sure you’re asking this question as you don’t have one. Or you could rely on your replicating slave if you have one.
Depending on your file system, you may be able to restore the file itself.
If this is made possible, you may recover your innodb data using innodb-tools. But if you’re down to that, you may seek out help from Percona, who author those tools.

]]> By: rahul https://shlomi-noach.github.io/blog/mysql/reasons-to-use-innodb_file_per_table/comment-page-1#comment-55312 Tue, 18 Oct 2011 12:15:29 +0000 https://shlomi-noach.github.io/blog/?p=614#comment-55312 i want to know one thing>>>>if bychance iddata1 file has deleted then all table data would gone…..how i will resolve this solution.

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/reasons-to-use-innodb_file_per_table/comment-page-1#comment-52559 Fri, 23 Sep 2011 04:56:28 +0000 https://shlomi-noach.github.io/blog/?p=614#comment-52559 @John, George,

Ah, but it has been more than two years since this post was published. Much has happened since.
In particular, the emergence of InnoDB Plugin (now just InnoDB), replacing the older “builting” InnoDB.
InnoDB Plugic comes with many improvements; performance, scale up, bugfixes, blob storage, …
And to fully utilize its functionality (you can use half of its advantages without changing anything), you need to upgrade your tables to “barracuda format”.
Guess what? It requires innodb_file_per_table.
So file-per-table is now also a feature issue. You loose features (e.g. compression, blob storage) which can directly relate to performance issues.

]]> By: George M https://shlomi-noach.github.io/blog/mysql/reasons-to-use-innodb_file_per_table/comment-page-1#comment-52555 Fri, 23 Sep 2011 03:36:40 +0000 https://shlomi-noach.github.io/blog/?p=614#comment-52555 I’ve been running several MySQL servers for a number of years. I have one particular box that has a little over 400 databases totaling about 350GB. The server was originally setup using 3 ibdata files back on 4.0, and was later changed to file per table. There has not been any appreciable speed difference from what I can see. The server is very busy, averaging 6-7 GB of binlogs per day. There is file system overhead for sure, but the db files are stored on a 16 spindle 6gb/s sas array, so I don’t expect that any small gain would warrant moving back to single files again.

]]> By: John Scott https://shlomi-noach.github.io/blog/mysql/reasons-to-use-innodb_file_per_table/comment-page-1#comment-49169 Thu, 18 Aug 2011 17:47:01 +0000 https://shlomi-noach.github.io/blog/?p=614#comment-49169 I hail from DB2 land and I really enjoyed the ability to put one or more tables of my choosing in a particular tablespace and others in another tablespace.

I totally agree with many of the points here about the filesystem overhead of having a file per table. However, I also don’t want all tables to be in one file.

Is there not a happy medium in mysql?

]]>