List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:March 16 2011 6:58am
Subject:Re: Suggestions for InnoDB files
View as plain text  
----- Original Message -----
> From: "Adarsh Sharma" <adarsh.sharma@stripped>
> 
> Dear all,
> 
> I have doubt regarding the storage structure for Innodb files :
> 
> Our database server has the following paths :
> 
> /dev/sda5              69G   35G   32G    52% /hdd1-1
> /dev/sdb1             274G  225G   36G  87% /hdd2-1
> /dev/sdc5             274G  225G   36G  87% /hdd3-1
> /dev/sdd5             274G  218G   43G  84% /hdd4-1
> /dev/sde1             266G  184G   69G  73% /hdd5-1

Interesting, but why like this instead of simply larger disks or raidsets ?

> Is it better to have innodb_file_per_table on.
>                       or
> innodb_data_file_path =
>
> /hdd2-1/innodb_data1/ibdata1:8G;/hdd3-1/innodb_data1/ibdata2:8G;/hdd4-1/innodb_data1/ibdata3:8G;/hdd2-1/innodb_data1/ibdata4:8G;
[unmanageable mess cut]

Why would you use 8G datafiles instead of large, partition-filling ones?

> which is currently set because to increase performance to read from
> separate small files instead of reading from one large one because
> one table is expected to grow more than 300 GB & some tables are near
> about 60-80 GB & increasing day by day.

I should check up on InnoDB internals wether it strips across datafiles, but from a disk
point of view, many smaller files aren't likely to be faster than one large one.

> Make sure the disk /hdd2-1/innodb_data1 is big enough
>  /hdd2-1/innodb_data1 is going to need be a large RAID10 set

A good RAID10 is recommended for databases anyway; I suggest you go with that.

> What is the best configuration for them so that we doesn't hit
> performance issues.

Performance issues are oftentimes more dependant on how you use the DB than how you set it
up; but a good setup never hurt anyone, of course.

Consider throwing all those disk partitions into a single RAID10 set, either through
underlying hardware or using MD on Linux. Even if you already have hardware RAID under
those devices and can't modify that, consider concatenating the individual devices with
LVM to benefit from striping.

InnoDB file-per-table should yield roughly the same performance as global datafiles,
albeit with more file descriptors used. If you want to be able to reclaim space, go for
file-per-table; if all space is for InnoDB anyway, monolithic storage might be slightly
more convenient. I seem to recall InnoDB can use raw devices, too; I'm not sure wether
there's a big performance gain, though. I that Oracle has stepped down from recommending
that in recent years, stating only marginal gains.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
Thread
Suggestions for InnoDB filesAdarsh Sharma16 Mar
  • Re: Suggestions for InnoDB filesJohan De Meersman16 Mar
Re: Suggestions for InnoDB filesJohan De Meersman16 Mar
  • Re: Suggestions for InnoDB filesAdarsh Sharma16 Mar
    • Re: Suggestions for InnoDB filesJohan De Meersman16 Mar
    • RE: Suggestions for InnoDB filesRolando Edwards16 Mar