List:General Discussion« Previous MessageNext Message »
From:Prabhat Kumar Date:June 14 2012 8:00pm
Subject:Re: Is there any performance difference, maintaining separate ibdata
files for each and every table insted of having one singl tabale for all databas...
View as plain text  
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 <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
>
>


-- 
Best Regards,

Prabhat Kumar
MySQL DBA

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

Thread
Is there any performance difference, maintaining separate ibdatafiles for each and every table insted of having one singl tabale for all databases.Pothanaboyina Trimurthy15 May
  • RE: Is there any performance difference, maintaining separateibdata files for each and every table insted of having one singl tabale forall databases.Rick James14 Jun
    • Re: Is there any performance difference, maintaining separate ibdatafiles for each and every table insted of having one singl tabale for all databases...Prabhat Kumar14 Jun