From: Adarsh Sharma
Date: March 16 2011 5:37am
Subject: Suggestions for InnoDB files
List-Archive: http://lists.mysql.com/mysql/224658
Message-Id: <4D804CAB.5010408@orkash.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------030904040105090901040204"

--------------030904040105090901040204
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

  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

Is it better to have innodb_file_per_table on. =20
                      or
innodb_data_file_path =3D=20
/hdd2-1/innodb_data1/ibdata1:8G;/hdd3-1/innodb_data1/ibdata2:8G;/hdd4-1/i=
nnodb_data1/ibdata3:8G;/hdd2-1/innodb_data1/ibdata4:8G;/hdd3-1/innodb_dat=
a1/ibdata5:8G;/hdd4-1/innodb_data1/ibdata6:8G;/hdd2-1/innodb_data1/ibdata=
7:8G;/hdd3-1/innodb_data1/ibdata8:8G;/hdd4-1/innodb_data1/ibdata9:8G;/hdd=
2-1/innodb_data1/ibdata10:8G;/hdd3-1/innodb_data1/ibdata11:8G;/hdd4-1/inn=
odb_data1/ibdata12:8G;/hdd2-1/innodb_data2/ibdata13:8G;/hdd3-1/innodb_dat=
a2/ibdata14:8G;/hdd4-1/innodb_data2/ibdata15:8G;/hdd2-1/innodb_data2/ibda=
ta16:8G;/hdd3-1/innodb_data2/ibdata17:8G;/hdd4-1/innodb_data2/ibdata18:8G=
;/hdd2-1/innodb_data2/ibdata19:8G;/hdd3-1/innodb_data2/ibdata20:8G;/hdd4-=
1/innodb_data2/ibdata21:8G;/hdd2-1/innodb_data2/ibdata22:8G;/hdd3-1/innod=
b_data2/ibdata23:8G;/hdd4-1/innodb_data2/ibdata24:8G;/hdd2-1/innodb_data3=
/ibdata25:8G;/hdd3-1/innodb_data3/ibdata26:8G;/hdd4-1/innodb_data3/ibdata=
27:8G;/hdd2-1/innodb_data3/ibdata28:8G;/hdd3-1/innodb_data3/ibdata29:8G;/=
hdd4-1/innodb_data3/ibdata30:8G;/hdd2-1/innodb_data3/ibdata31:8G;/hdd3-1/=
innodb_data3/ibdata32:8G;/hdd4-1/innodb_data3/ibdata33:8G;/hdd2-1/innodb_=
data3/ibdata34:8G;/hdd3-1/innodb_data3/ibdata35:8G;/hdd4-1/innodb_data3/i=
bdata36:8G;/hdd2-1/innodb_data4/ibdata37:8G;/hdd3-1/innodb_data4/ibdata38=
:8G;/hdd4-1/innodb_data4/ibdata39:8G;/hdd2-1/innodb_data4/ibdata40:8G;/hd=
d3-1/innodb_data4/ibdata41:8G;/hdd4-1/innodb_data4/ibdata42:8G;/hdd2-1/in=
nodb_data4/ibdata43:8G;/hdd3-1/innodb_data4/ibdata44:8G;/hdd4-1/innodb_da=
ta4/ibdata45:8G;/hdd2-1/innodb_data4/ibdata46:8G;/hdd3-1/innodb_data4/ibd=
ata47:8G;/hdd4-1/innodb_data4/ibdata48:8G;/hdd2-1/innodb_data5/ibdata49:8=
G;/hdd3-1/innodb_data5/ibdata50:8G;/hdd4-1/innodb_data5/ibdata51:8G;/hdd2=
-1/innodb_data5/ibdata52:8G;/hdd3-1/innodb_data5/ibdata53:8G;/hdd4-1/inno=
db_data5/ibdata54:8G;/hdd2-1/innodb_data5/ibdata55:8G;/hdd3-1/innodb_data=
5/ibdata56:8G;/hdd4-1/innodb_data5/ibdata57:8G;/hdd2-1/innodb_data5/ibdat=
a58:8G;/hdd3-1/innodb_data5/ibdata59:8G;/hdd4-1/innodb_data5/ibdata60:8G;=
/hdd2-1/innodb_data6/ibdata61:8G;/hdd3-1/innodb_data6/ibdata62:8G;/hdd2-1=
/innodb_data6/ibdata63:8G;/hdd3-1/innodb_data6/ibdata64:8G;/hdd2-1/innodb=
_data6/ibdata65:8G;/hdd3-1/innodb_data6/ibdata66:8G;/hdd2-1/innodb_data6/=
ibdata67:8G;/hdd3-1/innodb_data6/ibdata68:8G;/hdd2-1/innodb_data7/ibdata6=
9:8G;/hdd3-1/innodb_data7/ibdata70:8G;/hdd2-1/innodb_data7/ibdata71:8G;/h=
dd3-1/innodb_data7/ibdata72:8G;/hdd2-1/innodb_data7/ibdata73:8G;/hdd3-1/i=
nnodb_data7/ibdata74:8G;/hdd2-1/innodb_data7/ibdata75:8G;/hdd3-1/innodb_d=
ata7/ibdata76:8G;/hdd4-1/innodb_data6/ibdata77:8G;/hdd4-1/innodb_data6/ib=
data78:8G;/hdd4-1/innodb_data6/ibdata79:8G;/hdd4-1/innodb_data6/ibdata80:=
8G;/hdd4-1/innodb_data7/ibdata81:8G;/hdd5-1/innodb_data1/ibdata82:8G;/hdd=
5-1/innodb_data1/ibdata83:8G;/hdd5-1/innodb_data1/ibdata84:8G;/hdd5-1/inn=
odb_data1/ibdata85:8G;/hdd5-1/innodb_data2/ibdata86:8G;/hdd5-1/innodb_dat=
a2/ibdata87:8G;/hdd5-1/innodb_data2/ibdata88:8G;/hdd5-1/innodb_data2/ibda=
ta89:8G;/hdd5-1/innodb_data3/ibdata90:8G;/hdd5-1/innodb_data3/ibdata91:8G=
;/hdd5-1/innodb_data3/ibdata92:8G;/hdd5-1/innodb_data3/ibdata93:8G;/hdd5-=
1/innodb_data4/ibdata94:8G;/hdd5-1/innodb_data4/ibdata95:8G;/hdd5-1/innod=
b_data4/ibdata96:8G;/hdd5-1/innodb_data4/ibdata97:8G;/hdd5-1/innodb_data5=
/ibdata98:8G;/hdd5-1/innodb_data5/ibdata99:8G;/hdd5-1/innodb_data5/ibdata=
100:8G;/hdd5-1/innodb_data5/ibdata101:8G:autoextend

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

                  or

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

=20


MyISAM tables get stored in one /hdd1-1 directory.
Tables size grows day by day . Infact, one InnoDBtable is increasing=20
3GB/per day .

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



Thanks & best Regards,

Adarsh Sharma



--------------030904040105090901040204--