From: Andrew Morgan Date: February 19 2013 6:51am Subject: Re: Alter table lack of RAM List-Archive: http://lists.mysql.com/cluster/8488 Message-Id: MIME-Version: 1.0 (1.0) Content-Type: text/plain; charset=big5 Content-Transfer-Encoding: quoted-printable On 19 Feb 2013, at 01:21, LIU Xun wrote: > Hi Andrew, >=20 > 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 th= e file and have multiple clients load in parallel to speed things up.=20 > Is option 5 still an online operation if there is continuous traffic?=20 Yes - stop one data node in the node group, upgrade it, restart it and then r= epeat for the other. Of course you also need to increase DataMemory (and pos= sibly IndexMemory) and perform a rolling restart (automatic if using MySQL C= luster Manager).=20 >=20 > BTW, another factor should be considered is disk space, because LCP and RE= DO would require more space during alter operation. >=20 > Regards, > Alex >=20 > -----Original Message----- > From: Andrew Morgan [mailto:andrew.morgan@stripped]=20 > Sent: 2013=A6~2=A4=EB18=A4=E9 19:18 > To: LIU Xun > Cc: cluster@stripped > Subject: RE: Alter table lack of RAM >=20 > Hi Alex, >=20 > 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 ov= er 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 availabl= e RAM then there are a number of options... >=20 > 1) Perform the data migration yourself in an iterative fashion. e.g. >=20 > create cdr2 (....) partition by ...; >=20 > loop > { > insert into cdr2 select * from cdr order by .... LIMIT 1000; > delete from cdr order by .... LIMIT 1000; } until cdr empty. >=20 > Some extra memory will be used, but shouldn't be double. Best effect woul= d be if the order by .... could follow insert-order in some way. >=20 > 2) Backup the data and restore it on another Cluster (with more memory), r= epartition and then revers the process. >=20 > 3) Use mysqldump, empty the table, repartition it and then load the data b= ack in >=20 > 4) Convert the table to be disk-based (on-line operation), repartition and= then convert it back into an in-memory table >=20 > 5) (Temporarily) add extra RAM to the data nodes (this is an on-line opera= tion). >=20 > Regards, Andrew, >=20 >> -----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,=20 >> 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=20 >> created and the is copied to that table, which is later renamed to the=20= >> 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=20 >> would 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-=20 >> 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