MySQL Forums
Forum List  »  Partitioning

Re: MAX_ROWS on Partitions
Posted by: Rick James
Date: March 01, 2009 01:05AM

Jeremy's blog was written about 6 years ago. While it is mostly valid, it has not kept up with the times.

You still have some confusion...

"Engines" needs more discussion...

MyISAM -- The main way MySQL structured things 6 years ago. It is simple. It had a _default_ limit of 4G fixed length rows or 4GB dynamic length rows. ('Dynamic' means that VARCHAR or something else of non-fixed length was in the table.) MAX_ROWS, etc is the way to bust out of that limit. And the latest 5.1 defaults to 2^48 instead of 2^32, so you are unlikely to actually hit the _default_ limit.

InnoDB -- MySQL absorbed InnoBase to acquire a transactional engine. About then, MySQL restructured the base code to allow for adding on any number of engine types. InnoDB was later bought by Oracle, but is still a significant part of MySQL.

NDB (Cluster) -- Another company had developed a highly reliable 24/7 database primarily for embedding in telephone equipment. That was acquired by MySQL and became another "Engine" in the suite included with MySQL.

Federated, BlackHole, Archive, etc. -- Some more "Engines".

Maria, Falcon -- New engines, actively being developed. (They are only in Alpha state now.)

InfoBright, ScaleDB, and many others -- Commercial (cost money) engines.

Partition -- A layer in front of other engines. You can partition a MyISAM or InnoDB table to effectively have multiple sub-tables. Notice the reference to MyISAM in this example:
Create Table: CREATE TABLE `trb3` (
  `id` int(11) default NULL,
  `name` varchar(50) default NULL,
  `purchased` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
PARTITION BY RANGE (YEAR(purchased)) (
  PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM, 
  PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM, 
  PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM, 
  PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
)
taken from http://dev.mysql.com/doc/refman/5.1/en/partitioning-info.html

So, unless I am mistaken, you can have the underlying MyISAM tables be larger than 4G by using MAX_ROWS and AVG_ROW_LENGTH. See
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

Options: ReplyQuote


Subject
Views
Written By
Posted
5587
December 19, 2008 11:22PM
2928
February 26, 2009 12:58AM
3533
February 28, 2009 06:29PM
Re: MAX_ROWS on Partitions
3393
March 01, 2009 01:05AM
3089
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.