From: Andrew Morgan
Date: February 18 2013 11:17am
Subject: RE: Alter table lack of RAM
List-Archive: http://lists.mysql.com/cluster/8485
Message-Id: <3d2121c4-b837-4dc6-8df2-a1aded02db6a@default>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable

Hi Alex,

 You're correct that the on-line repartitioning of data temporarily uses ex=
tra memory on the data nodes. In most cases the application data is split o=
ver many tables and so relatively little memory is needed when repartitioni=
ng each table. If the data is in one big table and you don't have the avail=
able 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), re=
partition and then revers the process.

3) Use mysqldump, empty the table, repartition it and then load the data ba=
ck 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 operat=
ion).

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
>=20
> Hello,
>=20
> I have a NDB table called cdr which has 10M rows, roughly 20Gb. And, the
> table cdr is partitioned by cdrYear.
>=20
> Now, I want to change the partition by below command,
> * alter table cdr partitioned by key(areaCode)*
>=20
> It was said "When you do an alter table, a new copy of the table is creat=
ed
> and the is copied to that table, which is later renamed to the original, =
and the
> original table is removed."
>=20
> 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 woul=
d
> require at least 40Gb.
>=20
> So, can this alter operation success since there are lack of 10Gb RAM?
>=20
> Thanks
> Alex
>=20
>=20
>=20
> --
> 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.
>=20
> --
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:    http://lists.mysql.com/cluster
>=20