List:General Discussion« Previous MessageNext Message »
From:Roland RoLaNd Date:May 24 2014 5:52am
Subject:RE: missing data after partitioning an existing table
View as plain text  
I APOLOGIZE for  unwilling spamming this list! 
i switched to plain text, and the formatting is still messed up when i paste from
sublime.

Background information:

i have a 35 GB sized table which is causing performance issue, which is why i decided to
add partitioning by month using the timestamp column.
IT would be preferable to auto add every month instead of specifying it, but i don't know
how to do that, so instead i did it manually for existing, and future month (1 year ahead)

altering the table went for hours without any result, so i stopped it and created a new
identical(but with partitions) table and tried inserting the content into it from the
existing database. this worked smoothly and took about 2 hours to finish without any
errors nor warnings. but when i do a count(*) i notice that there's more than 22 million
record missing from the partitioned table..

These are the steps i took:




" 

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')) ); 
"

## After which, i issued the following:

## Hoping it will increase the speed of import of one table to another, retrieved these
from a mysqldump file..
alter table  p3_dna_new  DISABLE KEYS;
LOCK TABLES `p3_dna_new` WRITE;


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? 






----------------------------------------
> From: r_o_l_a_n_d@stripped
> To: mysql@stripped
> Subject: RE: missing data after partitioning an existing table
> Date: Sat, 24 May 2014 08:38:06 +0300
>
>
>
>
> Excuse me , and thanks for the heads up.
> 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:
>
> ALTER TABLE `pe_dna_new` DISABLE KEYS;LOCK TABLES `pe_dna_new` WRITE;
> 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
>
>
>
>> Date: Fri, 23 May 2014 21:19:18 +0200
>> From: tech@stripped
>> To: mysql@stripped
>> Subject: Re: missing data after partitioning an existing table
>>
>> Hi there,
>>
>> Le 23/05/2014 21:06, Roland RoLaNd a écrit :
>>> [...]
>>
>> Ouch ....
>>
>> This post is somewhat ... unreadable !
>>
>> Please format !
>>
>> Christophe.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql
>>
>
>
 		 	   		  
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