List:General Discussion« Previous MessageNext Message »
From:Ow Mun Heng Date:October 10 2006 12:33am
Subject:Re: InnoDB, 1 file per table or 1 BIG table?
View as plain text  
On Mon, 2006-10-09 at 14:13 -0700, Bruce Dembecki wrote:
> 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  
>  of course every little bit helps.

Of course.. Esp when the Box is no Big Iron. :-)

> corrupt InnoDB table file under file_per_table means only one table  
> is at risk as opposed to the entire database. 

Didn't see it that way. That's Good as well.

> 
> 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  
> ... big performance gain when moving files  
> around the network.

This is useful as well, for backup purposes.

> 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.

I thought symlinks were only applicable when doing/using myISAM tables?
Didn't I read somewhere that for innodb, you can't really copy the files
to another directory (I presume this meant symlinking as well) because
they're still tied down to the BIG Tablespace/Table Log File?


> 
> 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.

That's more than enough reason for me to switch.

Thanks for all the answers. 

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