List:Cluster« Previous MessageNext Message »
From:Andrew Morgan Date:February 18 2013 11:17am
Subject:RE: Alter table lack of RAM
View as plain text  
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