Got your comment on my blog, but since you posted here to I answer here.
On 2011-04-28 00.03, Moses wrote:
> Hi Guys,
> Would it be possible to extend a table size using the ALTER TABLE ...
> PARTITION() statement?
You could, but it is not an online operation. It is a copying alter table.
ALTER TABLE t1 PARTITION BY KEY(a) PARTITIONS 8;
This will increase the number of partitions from 2 (default on a two
node cluster) to 8.
But you need to have enough free datamemory to hold a copy of table t1.
And you should not do any modifications on t1 while you do this ALTER
(you must lock it on all mysql servers).
> If so, would that be preferrable to creating a new data file using ADD
> DATAFILE... statement? What are thre pros and cons of each?
Add data files is an online op, so you can do it while you have traffic
on the cluster.
However, you must have STORAGE DISK for the table (by default the tables
are stored in RAM).
> Any more notes would be very helpful seeing as the maximum table size for each
> partion (hence node?) is about 46million.
To get more partitions you should specify 'max_rows=2x<the max rows you
will ever store in the table>'
This will setup more internal partitions.
You can also create the table with
CREATE TABLE( ... ) PARTITION BY KEY (pk) PARTITIONS n
Where 'n' is the number of partitions.