Quantcast

Alter table lack of RAM

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Alter table lack of RAM

Alex
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Alter table lack of RAM

Andrew Morgan-5
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:[hidden email]]
> Sent: 17 February 2013 07:29
> To: [hidden email]
> 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
>

--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Alter table lack of RAM

Alex
Hi Andrew,

Thanks a lot. I like option 3 as it is simple and faster(I guess).
Is option 5 still an online operation if there is continuous traffic?

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:[hidden email]]
Sent: 2013年2月18日 19:18
To: LIU Xun
Cc: [hidden email]
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:[hidden email]]
> Sent: 17 February 2013 07:29
> To: [hidden email]
> 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
>

--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Alter table lack of RAM

Andrew Morgan-5





On 19 Feb 2013, at 01:21, LIU Xun <[hidden email]> 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:[hidden email]]
> Sent: 2013年2月18日 19:18
> To: LIU Xun
> Cc: [hidden email]
> 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:[hidden email]]
>> Sent: 17 February 2013 07:29
>> To: [hidden email]
>> 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
>>

--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster

Loading...