there is performance issues with a larger number of datafiles than a
single, that the reason innodb_file_per_table in not a default option.
other, with innodb_file_per_table, you'll use more resources, there can be
a problem if you have _many_ tables, there is obviously the problem to
keep number of open files (open tables / table_cache /
table_definition_cache) to manage. (Ofcourse, myisam tables always had that
Netx, with single file, table space can be shared between rows of different
tables and schemas. This means less wasted tablespace. where, with
innodb_file_per_table, each table may have unused tablspace, which can only
be utilized by rows of the same table. This means (sometimes much) more
but still innodb_file_per_table is preferable, this lead to a disk space
issue, since the ibdata1 file grew to 90G+ and it was not possible to
defragment tablespace using the Alter method (Infact is is hardly possible
to do in a production environment, since it leads to a huge downtime.)
On Thu, Jun 14, 2012 at 12:19 PM, Rick James <rjames@stripped> wrote:
> There should be little or no difference.
> If you are using thousands of tables, you might encounter overhead in
> opening the .ibd files.
> If you are tight on disk space, a single ibdata1 might be more efficient
> at reusing free blocks.
> OTOH, if you shrink or drop a big table, the freed space is not returned
> to the OS if you have a singe ibdata1.
> In most cases, I recommend innodb_file_per_table=1.
> > -----Original Message-----
> > From: Pothanaboyina Trimurthy [mailto:skd.trimurthy@stripped]
> > Sent: Tuesday, May 15, 2012 4:58 AM
> > To: mysql@stripped
> > Subject: Is there any performance difference, maintaining separate
> > ibdata files for each and every table insted of having one singl tabale
> > for all databases.
> > hi every one
> > Is there any performance difference, maintaining separate ibdata
> > files for each and every table insted of having one singl tabale for
> > all databases, for InnoDB Storage Engine.
> > please let me know the difference.
> > --
> > 3murthy
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql
My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat