List:General Discussion« Previous MessageNext Message »
From:James Eaton Date:October 9 2006 5:03pm
Subject:Re: InnoDB, 1 file per table or 1 BIG table?
View as plain text  
----- Original Message ----- 
From: "Dan Nelson" <dnelson@stripped>
To: "Ow Mun Heng" <Ow.Mun.Heng@stripped>
Cc: <mysql@stripped>
Sent: Monday, October 09, 2006 9:12 AM
Subject: Re: InnoDB, 1 file per table or 1 BIG table?


> In the last episode (Oct 09), Ow Mun Heng said:
>> 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?
>
> I don't think that the number of files has any impact on query speed.
> The advantage file-per-table gives you is the ability to recover unused
> space easily by running OPTIMIZE TABLE.  With a single tablespace, the
> only way to recover space is to dump all the tables, delete all the
> tablespace files, and reload.


Are you saying OPTIMIZE TABLE has no effect when there's a single 
tablespace, or are you saying that running an OPTIMIZE table causes MySQL 
to go through all those gyrations?

So then what are the advantages of having a single tablespace over 
file-per-table?  I'd assume there must be some if that's the default 
configuration.

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