Hi,
JinNo Kim wrote:
> Mikael,
>
> I've been comtemplating our dataset and range
> partitioning for a bit
> now and am very happy with it. I only really have
> 2 questions -
>
> 1) Is there an upper limit on the number of
> partitions that can be
> assigned to a table? I see '#define
> MAX_PARTITIONS 1024' in
> mysql-5.1/sql/unireg.h, but is there a problem
> with increasing this?
>
There is no specific limit happening at 1024 that makes it impossible to increase
but it is also not good to have it unbounded. There are some implementation issues
that arises when the number of partitions grow.
The idea to have it as a configuration parameter sounds like a good idea and should not
be very hard to do. I'll contemplate that.
The other question on AUTOPARTITION I think was a great idea. I always like ideas that are
taking the product to new levels of usability. I'll consider the details of how it could be
done and come back with some ideas on that. It's a fairly large task so I'll add it to our
internal Idea Log to make sure it doesn't get forgotten.
Rgrds Mikael
> If I'm looking at "daily" partitions and want 5
> years of history, I need
> 1827-1828 partitions, (depending on leap years).
> 10 years given the
> disk space might also be conceivable... Would it
> be possible to
> move this to the my.cnf file, run with the default
> of 1024, but allow
> the DBA to change the configuration file and
> restart the server?
>
> 2) Is there a compelling reason that the
> definition of the partition
> can not also explain the time and type of new
> partition that should
> be created as necessary? For instance (borrowing
> from the 5.1
> manual):
>
> If I have table tr:
>
> CREATE TABLE `tr` (
> `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 (1980) ENGINE =
> MyISAM,
> PARTITION p1 VALUES LESS THAN (1981) ENGINE =
> MyISAM,
> PARTITION p2 VALUES LESS THAN (1982) ENGINE =
> MyISAM,
> PARTITION p3 VALUES LESS THAN (1983) ENGINE =
> MyISAM,
> PARTITION p4 VALUES LESS THAN (1984) ENGINE =
> MyISAM,
> PARTITION p5 VALUES LESS THAN (1985) ENGINE =
> MyISAM,
> PARTITION p6 VALUES LESS THAN (1986) ENGINE =
> MyISAM,
> PARTITION p7 VALUES LESS THAN (1987) ENGINE =
> MyISAM,
> PARTITION p8 VALUES LESS THAN (1988) ENGINE =
> MyISAM,
> PARTITION p9 VALUES LESS THAN (1989) ENGINE =
> MyISAM,
> PARTITION p10 VALUES LESS THAN (1990) ENGINE =
> MyISAM,
> PARTITION p11 VALUES LESS THAN (1991) ENGINE =
> MyISAM,
> PARTITION p12 VALUES LESS THAN (1992) ENGINE =
> MyISAM,
> PARTITION p13 VALUES LESS THAN (1993) ENGINE =
> MyISAM,
> PARTITION p14 VALUES LESS THAN (1994) ENGINE =
> MyISAM,
> PARTITION p15 VALUES LESS THAN (1995) ENGINE =
> MyISAM
> )
>
> The above works fine, as long as I document and
> remember to ALTER
> TABLE ... ADD PARTITION every year (before the end
> of year :) ) until
> I run out of disk space or the data is no longer
> relevant.
>
> Specific to RANGE partitioning and given the
> example above, by the
> documentation if I attempt to insert a row with
> purchased =
> '1995-1-1' I will receive an error. How hard
> would it be to add a
> keyword to the partition definition telling the
> storage engine when I
> want it to create a new partition and how. So, for
> example using
> 'AUTOPARTITION' as my keyword below:
>
> CREATE TABLE `tr` (
> `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 (1980) ENGINE =
> MyISAM,
> AUTOPARTITION ON p(n+1) VALUES YEAR(purchased)+1
> LIMIT 200 ENGINE = MyISAM
> )
>
> Syntax explanation for example AUTOPARTITION:
>
>
>
>
> If the DBA decides to manually extend partitions
> and wants the
> existing behavior,
>
> 'ALTER TABLE tr AUTOPARTITION OFF;'
>
> (or create table without AUTOPARTITION, causing
> existing behavior -
> make OFF the default)
>
>
> On 1980-1-1 00:00:01, the first insert would cause
> the table to
> trigger the equivalent of:
>
> ALTER TABLE tr ADD PARTITION (PARTITION p1 VALUES
> LESS THAN (1981));
> then complete the insert.
>
>
> The only real concern I would have would be the
> action if I
> attempted to insert a value from 1981 first,
> causing
> ALTER TABLE tr ADD PARTITION (PARTITION p1 VALUES
> LESS THAN (1982));
>
> This wouldn't throw an error, but would result in
> a deviation from the
> partitioning scheme I had _intended_ and result in
> a partition 2x as
> large as I had hoped (assuming I didn't fix it
> before the end of
> 1981).
>
> I think that under normal circumstances, this
> would be a tremendous
> increase in utility. I have no real idea how the
> AUTOPARTITION
> could be implemented with other types of
> partitions so this may not
> be worth the effort.
>
> I'm not aware of any database capable of doing
> this, perhaps
> because it's too hard to do. I think it could be
> a friend to admins
> everywhere and would tremendously increase the
> utility of RANGE
> partitioning in MySQL.
>
> What do you think?
>
> -Jin No Kim
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com