From: Ben Clewett
Date: June 15 2007 2:46pm
Subject: Re: Innodb tablespace
List-Archive: http://lists.mysql.com/mysql/207503
Message-Id: <4672A65E.4010004@clewett.org.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

 > Hi all,
 >
 > Are there any reasons why one would NOT use separate ibd files for each
 > table

Fragmentation for one.

A single file can re-use empty space from deleted rows for any added 
rows.  A single file can only re-use space from that one file.

Therefore the sum table size will be larger with many files.  Depending 
on how much data you regularly delete.

(Fragmentation also occurs when row sizes are increased.)

The file system is also an issue.  Lots of files require a good file 
system, like Reiserfs, and not FAT32.  A single file is just as 
efficient with any file system.

But I don't believe there are any IO difference.  The same number of 
file handles are used, whether they all access one file or 1000 files.

Personally I like a single file, it's easier to administer and HotBackup 
works.

But, if any IonnDB developers read this mailing list, we really need to 
be able to break this file using defined table space, as with Oracle. 
Then have as many/few files as we like....

Ben




Olaf Stein wrote:
> Hi all,
> 
> Are there any reasons why one would NOT use separate ibd files for each
> table (--innodb_file_per_table). It seems logical to me to separate what
> does not belong together logically (different databases), but I as the
> shared tablespace is the default I wonder if it has nay advantages I am not
> aware of
> 
> Thanks
> Olaf
> 
>