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.sha...@orkash.com>
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
