List:General Discussion« Previous MessageNext Message »
From:Adarsh Sharma Date:January 25 2012 11:07am
Subject:Re: Suggestions for ibdata files structure
View as plain text  
Adarsh Sharma wrote:
> Thanks for the suggestions.
>
> Last thing to close this topic :-
> I create new partitions & files for ibdata files as :-
>
> log-bin=/hdd5-1/mysql-bin
>   
> innodb_data_file_path = 
>
> /hdd2-1/innodb_data1/ibdata1:250G;/hdd3-1/innodb_data2/ibdata2:250G;/hdd4-1/innodb_data3/ibdata3:8G:autoextend
> 
> ( When I delete old ibdata files & start mysql again to create new 
> ibdata files ( 250 GB) , mysql takes too much time :
> [root@s6-mysd-1 mysql]# /etc/init.d/mysql start
> Starting
> MySQL....................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................
> ERROR! 
>
> Log says 
>
> 120125 16:02:56  InnoDB: Setting file /hdd2-1/innodb_data1/ibdata1 size to 256000 MB
> InnoDB: Database physically writes the file full: wait...
>
> What is the reason behing this, I think connect_timeout may be the issue.
>
>   
> Now I need to place some innodb tables with partitioning enabled. 
> Please check the table structure.
>
> CREATE TABLE `metadata` (
>   `meta_id` bigint(20) NOT NULL AUTO_INCREMENT,
>   `u_id` bigint(20) DEFAULT NULL,
>   `c_p_url` varchar(800) DEFAULT NULL,
>   `meta_field` varchar(200) DEFAULT NULL,
>   `meta_value` varchar(2000) DEFAULT NULL,
>   `dt_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
>   PRIMARY KEY (`meta_id`)
> ) ENGINE=InnoDB AUTO_INCREMENT=447567739 DEFAULT CHARSET=utf8
>
> You can check the size of auto_increment column & how can i partition 
> my data effectively fore,g:
>
> |PARTITION BY RANGE (year(dt_stamp)) (PARTITION p0 VALUES LESS THAN 
> (2000),
> -> PARTITION p1 VALUES LESS THAN (2001) , PARTITION p2 VALUES LESS 
> THAN (2002) ,
> -> PARTITION p3 VALUES LESS THAN (2003) , PARTITION p4 VALUES LESS 
> THAN (2004) ,
> -> PARTITION p5 VALUES LESS THAN (2005) , PARTITION p6 VALUES LESS 
> THAN (2006) ,
> -> PARTITION p7 VALUES LESS THAN (2007) , PARTITION p8 VALUES LESS 
> THAN (2008) ,
> -> PARTITION p9 VALUES LESS THAN (2009) , PARTITION p10 VALUES LESS 
> THAN (2010)
>
> ||
> Above command partition data by year wise but i want to partition by 
> month wise ( fore.g 3 months data in one partition & so on). How can I 
> achieve this
>
> Where all these partitions get stored , do it makes internal 
> partitions in ibdata1 or ibdata2 or in ibdata3 files.
>
>
>
> Thanks & Regards
> Adarsh Sharma
> ||||
>
> |
>
>
>
>
>
>
> Johan De Meersman wrote:
>>
>> ------------------------------------------------------------------------
>>
>>     *From: *"Adarsh Sharma" <adarsh.sharma@stripped>
>>
>>     Thanks for the quick response , Ya I am aware of the full dump &
>>     restore & changing my.cnf for that.
>>     Just need to confirm that this configuration is better than the
>>     previous one or may any other ( Single 1TB with
>>     ibdata1:10MB:autoextend) is best for keeping away from future issues.
>>
>>
>> Personally, I'm partial to innodb-file-per-table because of the 
>> easier space management. I'm not entirely sure, but it might well be 
>> that innodb will also start if a tablefile is missing, while it 
>> almost certainly won't start when a normal datafile is missing.
>>
>> You should definitely look into hard- or software RAID setup for your 
>> datafiles, though. Apart from redundancy benefits, most RAID setups 
>> will also help your read performance along. RAID 10 or 51 are 
>> generally recommended for databases, although you can certainly get 
>> away with simple 1 or 5, too, if you don't need the last drop of 
>> performance.
>>
>>
>>
>>
>> -- 
>> 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 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