On Oct 9, 2006, at 7:15 AM, Ow Mun Heng wrote:
Hi All,
Just wanted to know if it would be faster/better to implement this
option into my.cnf
innodb_file_per_table = 1
which would essentially make each table a file on it's own rather than
have it all in 1 file.
My belief is that it would be slightly more advantageous compared to 1
BIG file.
eg: 1 10GB file would perform poorer than 10 1GB files.
Is this statement true and how far is is true?
There are some minor performance benefits here when run against
benchmarks... but tiny. It is generally true that for tiny tiny
tables, it's a lot easier to find a 20byte row in a 100K file than it
is finding it in a 40Gbyte file. While that is true, InnoDB is pretty
efficient, and really knows how to pull data out of the big table
space well... most of the benchmark gains we've seen and others have
reported are in the sub 1% area... of course every little bit helps.
There are some general management benefits for some people, others
may find the changes less helpful, depending on your circumstances
etc... For us, little things make a difference... for example, a
corrupt InnoDB table file under file_per_table means only one table
is at risk as opposed to the entire database. Someone else mentioned
Optimze Table advantages and freeing up disk space.
One of the big things that really really helps us is having files
that are appropriate in size for the data... That means mostly our
data files are well sized for our data... before we had 40G of table
space and usually only 15G of data... backups and file copies and so
on involved copying all 40Gbytes of the table space... now the backup
processes and other things we do which involve moving data files
around only move the actual amount of data we have, not all the empty
table space left for growth... big performance gain when moving files
around the network.
Files can still be stored on different storage devices by making
symlinks within the data directory for specific database directories
or even specific table files.
We really really like innodb_file_per_table - but mostly because it
makes our lives easier in many ways, not so much for performance
reasons.
Best Regards, Bruce.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
