From: Adarsh Sharma Date: January 23 2012 12:35pm Subject: Re: Suggestions for ibdata files structure List-Archive: http://lists.mysql.com/mysql/226645 Message-Id: <4F1D5427.50305@orkash.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------040202050602010007040607" --------------040202050602010007040607 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Ya i know that this is absolutely wrong , but I have some questions in my mind :- 1. I am planning to to fix one partition /hdd2-1 for my binary logs for HA, rotate them monthly to extra backups. 2. Remaining 3 system partitions ( /hdd3-1,/hdd4-1,/hdd5-1) contains ibdata1:250GB,ibdata2:250GB, ibdata3:200Gb :Autoextend. Store each innodb table data partitioning by date ( 6 months ) as my myisam tables remains in /var/lib/mysql/directory because their size increases in MBs. 3. What to do when my final partition /hdd5-1 is having no space ( less than 1 Gb ), do i need to configure RAID 10 for that. 4. I need 3 months past data at any given point of time , so I can manually alter ( delete ) my partitions for that. 5. How can I increase my select query performance on a 200Gb innodb table. Thanks Johan De Meersman wrote: > Gah, my eyes :-) > > That seems... needlessly complex. As a general rule, it's a good idea to have one datafile per physical disk. Putting more datafiles on a single disk doesn't magically increase parallellism. > > > > ----- Original Message ----- > >> From: "Adarsh Sharma" >> To: "mysql" >> Sent: Monday, 23 January, 2012 10:50:33 AM >> Subject: Suggestions for ibdata files structure >> >> 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 >> >> > > --------------040202050602010007040607--