RANGE Partitioning - Questions/Suggestions

[JinNo Kim]

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?

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:

[AUTOPARTITION ON|OFF

p(max existing partition+integer)

VALUES {VALUE DEFINITION + increment value}

LIMIT on the number of partitions the engine can "autocreate", or
perhaps the number of partitions that can exist in the table before
no more new ones can be auto-created

ENGINE for new, autogenerated partitions ]


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