List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:October 9 2006 6:54pm
Subject:Re: InnoDB, 1 file per table or 1 BIG table?
View as plain text  
In the last episode (Oct 09), James Eaton said:
> From: "Dan Nelson" <dnelson@stripped>
> >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?

When there's a single tablespace, OPTIMIZE TABLE will compact and
rebuild your table and indexes just like normal, but since the
containing tablespace cannot shrink, mysql can't return the freed space
back to the OS.
 
> 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.

Single-tablespace was the first implementation, that's all.  One
advantage it does have is if you aren't using RAID, or have multiple
slow RAID volumes, you can use single-tablespace mode with multiple
tablespace files on different disks to improve performance.

-- 
	Dan Nelson
	dnelson@stripped
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