From: Adarsh Sharma
Date: January 23 2012 9:50am
Subject: Suggestions for ibdata files structure
List-Archive: http://lists.mysql.com/mysql/226642
Message-Id: <4F1D2D69.6040404@orkash.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Dear all,

I have some doubts regarding the configuration of Innodb files structure.
As we know all myisam tables are stored in database directory and innodb 
use tablespaces ( ibdata1 ).
In My application , databases are the mixture of mysql & innodb storage 
engines.
In my database ,size of innodb tables may be between 100 & 200 Gb & may 
be some tables >300Gb.I have 4 Hds.
/dev/sdb1             274G  225G   36G  87% /hdd2-1
/dev/sdc5             274G  225G   36G  87% /hdd3-1
/dev/sdd5             274G  219G   42G  85% /hdd4-1
/dev/sde1             266G  184G   69G  73% /hdd5-1

Data is inserted continuously.
Presently I have a structure like below
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;/hdd3-1/innodb_data1/ibdata5:8G;/hdd4-1/innodb_data1/ibdata6:8G;/hdd2-1/innodb_data1/ibdata7:8G;/hdd3-1/innodb_data1/ibdata8:8G;/hdd4-1/innodb_data1/ibdata9:8G;
/hdd2-1/innodb_data1/ibdata10:8G;/hdd3-1/innodb_data1/ibdata11:8G;/hdd4-1/innodb_data1/ibdata12:8G;/hdd2-1/innodb_data2/ibdata13:8G;/hdd3-1/innodb_data2/ibdata14:8G;
/hdd4-1/innodb_data2/ibdata15:8G;/hdd2-1/innodb_data2/ibdata16: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/innodb_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/ibdata27: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/ibdata36: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;/hdd3-1/innodb_data4/ibdata41:8G;/hdd4-1/innodb_data4/ibdata42:8G;/hdd2-1/innodb_data4/ibdata43:8G;/hdd3-1/innodb_data4/ibdata44:8G;
/hdd4-1/innodb_data4/ibdata45:8G;/hdd2-1/innodb_data4/ibdata46:8G;/hdd3-1/innodb_data4/ibdata47:8G;/hdd4-1/innodb_data4/ibdata48:8G;/hdd2-1/innodb_data5/ibdata49:8G;
/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/innodb_data5/ibdata54:8G;
/hdd2-1/innodb_data5/ibdata55:8G;/hdd3-1/innodb_data5/ibdata56:8G;/hdd4-1/innodb_data5/ibdata57:8G;/hdd2-1/innodb_data5/ibdata58: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/ibdata69:8G;
/hdd3-1/innodb_data7/ibdata70:8G;/hdd2-1/innodb_data7/ibdata71:8G;/hdd3-1/innodb_data7/ibdata72:8G;/hdd2-1/innodb_data7/ibdata73:8G;/hdd3-1/innodb_data7/ibdata74:8G;
/hdd2-1/innodb_data7/ibdata75:8G;/hdd3-1/innodb_data7/ibdata76:8G;/hdd4-1/innodb_data6/ibdata77:8G;/hdd4-1/innodb_data6/ibdata78: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;/hdd5-1/innodb_data1/ibdata83:8G;/hdd5-1/innodb_data1/ibdata84:8G;
/hdd5-1/innodb_data1/ibdata85:8G;/hdd5-1/innodb_data2/ibdata86:8G;/hdd5-1/innodb_data2/ibdata87:8G;/hdd5-1/innodb_data2/ibdata88:8G;/hdd5-1/innodb_data2/ibdata89: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/innodb_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/ibdata100:8G;/hdd5-1/innodb_data5/ibdata101:8G:autoextend

But I don't think this is the correct way to store data for better 
performance.
Do i need to set partitioning for bigger tables or some standard ways to 
configure table spaces in mysql.


Thanks