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 issue). 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 wasted tablespace 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 <rja...@yahoo-inc.com> 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.trimur...@gmail.com] > > Sent: Tuesday, May 15, 2012 4:58 AM > > To: mysql@lists.mysql.com > > 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 > > -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
