List:General Discussion« Previous MessageNext Message »
From:Bruce Dembecki Date:October 9 2006 9:13pm
Subject:Re: InnoDB, 1 file per table or 1 BIG table?
View as plain text  
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.
Thread
InnoDB, 1 file per table or 1 BIG table?Ow Mun Heng9 Oct
  • Re: InnoDB, 1 file per table or 1 BIG table?Dan Nelson9 Oct
  • Re: InnoDB, 1 file per table or 1 BIG table?James Eaton9 Oct
    • Re: InnoDB, 1 file per table or 1 BIG table?Dan Nelson9 Oct
  • Re: InnoDB, 1 file per table or 1 BIG table?Bruce Dembecki9 Oct
    • Re: InnoDB, 1 file per table or 1 BIG table?Ow Mun Heng10 Oct
  • Re: InnoDB, 1 file per table or 1 BIG table?James Eaton9 Oct
    • Re: InnoDB, 1 file per table or 1 BIG table?Ow Mun Heng10 Oct