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
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
Thanks
Olaf
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
