List:Cluster« Previous MessageNext Message »
From:Andrew Morgan Date:February 19 2013 6:51am
Subject:Re: Alter table lack of RAM
View as plain text  




On 19 Feb 2013, at 01:21, LIU Xun <Xun.Liu@stripped> wrote:

> Hi Andrew,
> 
> Thanks a lot. I like option 3 as it is simple and faster(I guess).

Doing the bulk reload could take a while. You'd probably want to break up the file and
have multiple clients load in parallel to speed things up. 

> Is option 5 still an online operation if there is continuous traffic? 

Yes - stop one data node in the node group, upgrade it, restart it and then repeat for the
other. Of course you also need to increase DataMemory (and possibly IndexMemory) and
perform a rolling restart (automatic if using MySQL Cluster Manager). 

> 
> BTW, another factor should be considered is disk space, because LCP and REDO would
> require more space during alter operation.
> 
> Regards,
> Alex
> 
> -----Original Message-----
> From: Andrew Morgan [mailto:andrew.morgan@stripped] 
> Sent: 2013年2月18日 19:18
> To: LIU Xun
> Cc: cluster@stripped
> Subject: RE: Alter table lack of RAM
> 
> Hi Alex,
> 
> You're correct that the on-line repartitioning of data temporarily uses extra memory
> on the data nodes. In most cases the application data is split over many tables and so
> relatively little memory is needed when repartitioning each table. If the data is in one
> big table and you don't have the available RAM then there are a number of options...
> 
> 1) Perform the data migration yourself in an iterative fashion. e.g.
> 
> create cdr2 (....) partition by ...;
> 
> loop
> {
>   insert into cdr2 select * from cdr order by .... LIMIT 1000;
>   delete from cdr order by .... LIMIT 1000; } until cdr empty.
> 
> Some extra memory will be used, but shouldn't be double.  Best effect would be if the
> order by .... could follow insert-order in some way.
> 
> 2) Backup the data and restore it on another Cluster (with more memory), repartition
> and then revers the process.
> 
> 3) Use mysqldump, empty the table, repartition it and then load the data back in
> 
> 4) Convert the table to be disk-based (on-line operation), repartition and then
> convert it back into an in-memory table
> 
> 5) (Temporarily) add extra RAM to the data nodes (this is an on-line operation).
> 
> Regards, Andrew,
> 
>> -----Original Message-----
>> From: Alex [mailto:xun.liu@stripped]
>> Sent: 17 February 2013 07:29
>> To: cluster@stripped
>> Subject: Alter table lack of RAM
>> 
>> Hello,
>> 
>> I have a NDB table called cdr which has 10M rows, roughly 20Gb. And, 
>> the table cdr is partitioned by cdrYear.
>> 
>> Now, I want to change the partition by below command,
>> * alter table cdr partitioned by key(areaCode)*
>> 
>> It was said "When you do an alter table, a new copy of the table is 
>> created and the is copied to that table, which is later renamed to the 
>> original, and the original table is removed."
>> 
>> That means, there would be two table in RAM when altering table.
>> The total RAM of my server is 30Gb. if two tables are existing, they 
>> would require at least 40Gb.
>> 
>> So, can this alter operation success since there are lack of 10Gb RAM?
>> 
>> Thanks
>> Alex
>> 
>> 
>> 
>> --
>> View this message in context: http://mysql- 
>> cluster.1050023.n4.nabble.com/Alter-table-lack-of-RAM-tp4655164.html
>> Sent from the MySQL - Cluster mailing list archive at Nabble.com.
>> 
>> --
>> MySQL Cluster Mailing List
>> For list archives: http://lists.mysql.com/cluster
>> To unsubscribe:    http://lists.mysql.com/cluster
>> 
Thread
Alter table lack of RAMAlex17 Feb
  • RE: Alter table lack of RAMAndrew Morgan18 Feb
    • RE: Alter table lack of RAMLIU Xun19 Feb
      • Re: Alter table lack of RAMAndrew Morgan19 Feb