List:General Discussion« Previous MessageNext Message »
From:Adarsh Sharma Date:January 23 2012 12:35pm
Subject:Re: Suggestions for ibdata files structure
View as plain text  
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" <adarsh.sharma@stripped>
>> To: "mysql" <mysql@stripped>
>> 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
>>
>>     
>
>   


Thread
Suggestions for ibdata files structureAdarsh Sharma23 Jan
  • Re: Suggestions for ibdata files structureJohan De Meersman23 Jan
    • Re: Suggestions for ibdata files structureAdarsh Sharma23 Jan
      • Re: Suggestions for ibdata files structureJohan De Meersman23 Jan
  • Re: Suggestions for ibdata files structureRik Wasmus23 Jan
Re: Suggestions for ibdata files structureAdarsh Sharma25 Jan