List:General Discussion« Previous MessageNext Message »
From:Roland RoLaNd Date:May 23 2014 7:06pm
Subject:missing data after partitioning an existing table
View as plain text  
I know a couple of things, but table partitioning isn't one of them. i have a 35 GB sized
table which is causing performance issues, my research came across partitioning, which is
what i tried and failed with today. i tried altering the table, that kept going for 2
hours with absolutely no change ( i checked under /var/lib/mysql/DBname/Tablename* then
decided to create a new identical table, with the added partitions. my goal is to
partition my table by month, using the timestamp column. i did the following (i googled A
LOT to find a way to auto add months instead of manually adding them, i'm afraid i
couldn't find any decent how to) CREATE TABLE `p3_dna_new` ( `userid` int(11) NOT NULL,
`action` int(11) NOT NULL, `playper` float DEFAULT NULL, `songid` int(11) NOT NULL,
`timestamp` datetime NOT NULL, `playlstid` int(11) DEFAULT NULL, `playlstcnt` int(11)
DEFAULT NULL, `acttype` tinyint(4) DEFAULT NULL, `inserted` int(11) DEFAULT NULL,
`connectiontype` int(11) DEFAULT NULL, `reservedcol` tinyint(4) DEFAULT NULL, UNIQUE KEY
`p3_plays_nodups` (`userid`,`action`,`playper`,`songid`,`timestamp`), KEY
`p3_plays_inserted` (`inserted`), KEY `sp` (`songid`), KEY `p3_plays_timestamp`
(`timestamp`), KEY `playlstid` (`playlstid`) ) PARTITION BY RANGE ( TO_DAYS(timestamp) )
( PARTITION Feb2012 VALUES LESS THAN (TO_DAYS('2012-03-01')), PARTITION Mar2012 VALUES
LESS THAN (TO_DAYS('2012-04-01')), PARTITION Apr2012 VALUES LESS THAN
(TO_DAYS('2012-05-01')), PARTITION May2012 VALUES LESS THAN (TO_DAYS('2012-06-01')),
PARTITION Jun2012 VALUES LESS THAN (TO_DAYS('2012-07-01')), PARTITION Jul2012 VALUES LESS
THAN (TO_DAYS('2012-08-01')), PARTITION Aug2012 VALUES LESS THAN (TO_DAYS('2012-09-01')),
PARTITION Sept2012 VALUES LESS THAN (TO_DAYS('2012-10-01')), PARTITION Oct2012 VALUES
LESS THAN (TO_DAYS('2012-11-01')), PARTITION Nov2012 VALUES LESS THAN
(TO_DAYS('2012-12-01')), PARTITION Dec2012 VALUES LESS THAN (TO_DAYS('2013-01-01')),
PARTITION Jan2013 VALUES LESS THAN (TO_DAYS('2013-02-01')), PARTITION Feb2013 VALUES LESS
THAN (TO_DAYS('2013-03-01')), PARTITION Mar2013 VALUES LESS THAN (TO_DAYS('2013-04-01')),
PARTITION Apr2013 VALUES LESS THAN (TO_DAYS('2013-05-01')), PARTITION May2013 VALUES LESS
THAN (TO_DAYS('2013-06-01')), PARTITION Jun2013 VALUES LESS THAN (TO_DAYS('2013-07-01')),
PARTITION Jul2013 VALUES LESS THAN (TO_DAYS('2013-08-01')), PARTITION Aug2013 VALUES LESS
THAN (TO_DAYS('2013-09-01')), PARTITION Sept2013 VALUES LESS THAN (TO_DAYS('2013-10-01')),
PARTITION Oct2013 VALUES LESS THAN (TO_DAYS('2013-11-01')), PARTITION Nov2013 VALUES LESS
THAN (TO_DAYS('2013-12-01')), PARTITION Dec2013 VALUES LESS THAN (TO_DAYS('2014-01-01')),
PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')), PARTITION Feb2014 VALUES LESS
THAN (TO_DAYS('2014-03-01')), PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')),
PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')), PARTITION May2014 VALUES LESS
THAN (TO_DAYS('2014-06-01')), PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')),
PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')), PARTITION Aug2014 VALUES LESS
THAN (TO_DAYS('2014-09-01')), PARTITION Sept2014 VALUES LESS THAN
(TO_DAYS('2014-10-01')), PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')),
PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')), PARTITION Dec2014 VALUES LESS
THAN (TO_DAYS('2015-01-01')), PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01')),
PARTITION Feb2015 VALUES LESS THAN (TO_DAYS('2015-03-01')), PARTITION Mar2015 VALUES LESS
THAN (TO_DAYS('2015-04-01')), PARTITION Apr2015 VALUES LESS THAN (TO_DAYS('2015-05-01')),
PARTITION May2015 VALUES LESS THAN (TO_DAYS('2015-06-01')), PARTITION Jun2015 VALUES LESS
THAN (TO_DAYS('2015-07-01')), PARTITION Jul2015 VALUES LESS THAN (TO_DAYS('2015-08-01')),
PARTITION Aug2015 VALUES LESS THAN (TO_DAYS('2015-09-01')), PARTITION Sept2015 VALUES
LESS THAN (TO_DAYS('2015-10-01')), PARTITION Oct2015 VALUES LESS THAN
(TO_DAYS('2015-11-01')), PARTITION Nov2015 VALUES LESS THAN (TO_DAYS('2015-12-01')),
PARTITION Dec2015 VALUES LESS THAN (TO_DAYS('2016-01-01')) ); Then issued: INSERT INTO
p3_dna_new SELECT * FROM p3_dna_old; It finishes after a while, with absolutely no error
nor warning. if i issue the following: select count(*) from p3_dna_new; it will be
different than the result of select count(*) from p3_dna_old; If there's an issue,
shouldn't i get a warning or error message? NOTE: data spans between 2004 and may 2014,
and the table will still be used in the future, which is why i added extra month 		 	  
		  
Thread
missing data after partitioning an existing tableRoland RoLaNd23 May 2014
  • Re: missing data after partitioning an existing tableChristophe23 May 2014
    • RE: missing data after partitioning an existing tableRoland RoLaNd24 May 2014
      • RE: missing data after partitioning an existing tableRoland RoLaNd24 May 2014