MySQL Forums
Forum List  »  Partitioning

Re: MAX_ROWS on Partitions
Posted by: Adam Berrada
Date: February 28, 2009 06:29PM

Thanks for the distinction. I guess that I was confused about what exactly a partition in an NDB table is considered as; a layer, an implementation detail, a separate table... a sub-table with it's own table properties.

I was actually thinking that maybe a partition in an NDB table might be separate from the containing NDB table. This would have allowed me to define MAX_ROWS=4294967295 on each partition in the NDB table - placing the limit on each separate partition (treating each partition as a table with it's own limits), instead of a global limit on the NDB table; Thus making the containing NDB table be able to go beyond the current limit of 4.3billion rows. However, I see that this would suggest that each partition is treated as a separate table... which is not the case (an "implementation detail," or a layer). I wish that the Reference Manual would include a separate, and more detailed, entry on MAX_ROWS; including the detail that any MySQL table cannot (currently) have more than 4294967295 rows.

ex:

table XYZ: maximum# rows MAX_ROWS = 4294967295
partition 1: 47,000,000 rows
partition 2: 1,500,000,000 rows
partition 3: 20,000 rows
.
.
partition n: N rows
(as of MySQL 5.1, this is limited to a maximum of 1024 partitions/subpartitions)

Now, if I could set the MAX_ROWS property on each partition to ~ 4.3b (giving table XYZ 4294967295*N possible rows), ...instead of having table XYZ limited to 4.3b maximum possible rows... then I wouldn't ever have to worry about the table running out of rows; b/c I wouldn't allow any one partition to ever get to 4.3 billion rows. With all the rows added together from all of table XYZ's partitions, the table may very well someday exceed 4.3 billion rows. If the 4.3b limit still exists when that day comes, then the table will crash. So, until that limit is lifted, I guess that I am going to have to settle on creating multiple tables instead on one giant table with N partitions.

BTW I'm basing the 4.3b limit on articles like:
http://jeremy.zawodny.com/blog/archives/000796.html

Which indicates (at the bottom of the article) that a table in MySQL 5.1 (or previous release) cannot exceed 4294967295 rows.
Maybe this limit has changed since then??? If not, then I hope that they change it in a future release.

Thanks for the reply. If anyone has any information about if this 4.3b row limit still exists (or has increased) in 6.0, then please let me know. I'll post this question under General. Thanks again.

Options: ReplyQuote


Subject
Views
Written By
Posted
5896
December 19, 2008 11:22PM
3026
February 26, 2009 12:58AM
Re: MAX_ROWS on Partitions
3677
February 28, 2009 06:29PM
3491
March 01, 2009 01:05AM
3220
March 01, 2009 08:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.