List:General Discussion« Previous MessageNext Message »
From:Ben Clewett Date:June 15 2007 3:08pm
Subject:Re: Innodb tablespace
View as plain text  
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

Fragmentation for one.

A single file can re-use empty space from deleted data for any added 
tables and 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.)

There are file system problems as well.  Many files rely on a good file 
system, like Reiserfs, and not, say, Fat32.  Many files also result in a 
heavier hit on a journaling file system.  Important if you are using a 
lot of files, like many hundreds.

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

Also worth noting that an external single file is still used with 
file-per-table, which I suspect is used for referential constraint 
storage.  Therefore a complete division by database is not possible, all 
databases still use this one file.

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

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.


> Thanks
> Olaf

Innodb tablespaceOlaf Stein15 Jun
  • Re: Innodb tablespaceAnanda Kumar15 Jun
  • Re: Innodb tablespaceBen Clewett15 Jun
    • Re: Innodb tablespaceDan Nelson15 Jun
  • Re: Innodb tablespaceBen Clewett15 Jun