Partitioning in MySQL: HASH Partitioning
Posted by: Mikael Ronström
Date: June 14, 2005 03:02AM
Date: June 14, 2005 03:02AM
The development of partitioning is progressing at a good speed.
Most review points have been passed now for introducing this into 5.1 soon.
Work is currently ongoing to put together the first pre-alpha version of it.
I will continue today with an exploration of another variant of partitioning,
the HASH partitioning.
The main cause for HASH partitioning is to spread data, both to spread it over many disks
and to spread it to enable parallel table scans, to decrease the height of the index trees.
HASH partitioning in MySQL can also be used to simplify management of large tables, not in
the same manner as for RANGE partitions but to a limited extent.
There will be four types of HASH partitioning:
1) PARTITION BY HASH (f(f1,f2,..,fN)) where f is a integer, non-constant, non-random function of
the fields f1,..,fN. In this case the user specifies the hash function himself. The partition to use
is decided by performing a modulo operation on the result of the hash function =>
partition = module( f(..), no_partitions). This means that the data is evenly spread among the
partitions.
In principal any function is possible, but it is important to remember a few things about how the
partitioning function is used.
1) The partitioning is evaluated at least once for each record insert, updated or deleted.
2) If the partitioning function is an integer function and only contains one field and the function
grows or shrinks as the value of partitioning field grows then also partition pruning on ranges of
partitions can be performed. There might be limited support for this in some queries even with
many fields if only one field is not set but it is a tough theoretical problem to discover the functions
where this can be applied.
2) PARTITION BY LINEAR HASH (f(f1,..,fN)) which has exactly the same characteristics as the
previous with one important difference. Instead of performing a modulo operation one uses the
manner which is used for linear hashing in calculating the partition. As an example with 6 partitions
we do the following:
partitition= f(..) & 7
if (partition >= 6) then partition = f(..) & 3;
Thus partition 6 and 7 are folded into partition 2 and 3. This method has the disadvantage that data
is unevenly distributed. It has the advantage in that it becomes very easy to add a partition. In the
case above to add a 7th partition it is enough to split partition number 2 (starting at partition 0) and
change the above partition calculator by replacing 6 with 7.
This means that ALTER TABLE ADD PARTITION can be done much faster which is desirable for very
large tables consisting of possibly terabytes of data. It is also very interesting for tables in
MySQL Cluster.
3) PARTITION BY KEY (f1,...fN) This is similar to PARTITION BY HASH except that the user leaves
the decision on what hash function to use on the MySQL Server. The MySQL Server will use a
cryptographic function that ensures an even distribution of the data.
4) PARTITION BY LINEAR KEY (f1,..,fN) This is similar to PARTITION BY LINEAR HASH except that
as for PARTITION BY KEY the hash function is decided by the MySQL Server.
All types of HASH partitioning can be applied both on partitions and on subpartitions of a RANGE or
LIST partition.
When defining the HASH partitions most things except the above partition function and its fields
have defaults. The number of default partitions depends on the handler. The partition handler uses
one partition as the default whereas NDB Cluster uses the number of nodes in the cluster as
default (together with a calculation based on MAX_ROWS to ensure that all the rows fit in the
defined partitions). Default names of partitions are p0, p1, p2, p3 and so forth, defaults on the
partition options are the same as they are for the options when used as table options.
The following partition options can be defined
1) COMMENT, A comment on the partition
2) DATA DIRECTORY, same as the table option used by MyISAM handler
3) INDEX DIRECTORY, same as the table option used by MyISAM handler
4) MAX_ROWS, maximum number of rows in the partition
5) MIN_ROWS, minimum number of rows in the partition
6) NODEGROUP, Useful for MySQL Cluster to define in which node group the partition is to reside in
7) TABLESPACE, Currently only used by MySQL Cluster to define the table space used by the partition
In way of management commands all the management commands available will be available also
for HASH partitions. Most of them will however require a full reorganisation of the table. The
exception to this will be:
1) ALTER TABLE ADD PARTITION for partitioned tables using the keyword LINEAR
2) ALTER TABLE MERGE PARTITION for partitioned tables using the keyword LINEAR
Rgrds Mikael
PS: Not everything mentioned here will be available in the first pre-alpha version of 5.1. It is also
not defined the exact content of functionality for 5.1, there is also still some room to influence this
set of features.
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog: http://mikaelronstrom.blogspot.com
Edited 1 time(s). Last edit at 06/14/2005 10:28AM by Edwin DeSouza.
Most review points have been passed now for introducing this into 5.1 soon.
Work is currently ongoing to put together the first pre-alpha version of it.
I will continue today with an exploration of another variant of partitioning,
the HASH partitioning.
The main cause for HASH partitioning is to spread data, both to spread it over many disks
and to spread it to enable parallel table scans, to decrease the height of the index trees.
HASH partitioning in MySQL can also be used to simplify management of large tables, not in
the same manner as for RANGE partitions but to a limited extent.
There will be four types of HASH partitioning:
1) PARTITION BY HASH (f(f1,f2,..,fN)) where f is a integer, non-constant, non-random function of
the fields f1,..,fN. In this case the user specifies the hash function himself. The partition to use
is decided by performing a modulo operation on the result of the hash function =>
partition = module( f(..), no_partitions). This means that the data is evenly spread among the
partitions.
In principal any function is possible, but it is important to remember a few things about how the
partitioning function is used.
1) The partitioning is evaluated at least once for each record insert, updated or deleted.
2) If the partitioning function is an integer function and only contains one field and the function
grows or shrinks as the value of partitioning field grows then also partition pruning on ranges of
partitions can be performed. There might be limited support for this in some queries even with
many fields if only one field is not set but it is a tough theoretical problem to discover the functions
where this can be applied.
2) PARTITION BY LINEAR HASH (f(f1,..,fN)) which has exactly the same characteristics as the
previous with one important difference. Instead of performing a modulo operation one uses the
manner which is used for linear hashing in calculating the partition. As an example with 6 partitions
we do the following:
partitition= f(..) & 7
if (partition >= 6) then partition = f(..) & 3;
Thus partition 6 and 7 are folded into partition 2 and 3. This method has the disadvantage that data
is unevenly distributed. It has the advantage in that it becomes very easy to add a partition. In the
case above to add a 7th partition it is enough to split partition number 2 (starting at partition 0) and
change the above partition calculator by replacing 6 with 7.
This means that ALTER TABLE ADD PARTITION can be done much faster which is desirable for very
large tables consisting of possibly terabytes of data. It is also very interesting for tables in
MySQL Cluster.
3) PARTITION BY KEY (f1,...fN) This is similar to PARTITION BY HASH except that the user leaves
the decision on what hash function to use on the MySQL Server. The MySQL Server will use a
cryptographic function that ensures an even distribution of the data.
4) PARTITION BY LINEAR KEY (f1,..,fN) This is similar to PARTITION BY LINEAR HASH except that
as for PARTITION BY KEY the hash function is decided by the MySQL Server.
All types of HASH partitioning can be applied both on partitions and on subpartitions of a RANGE or
LIST partition.
When defining the HASH partitions most things except the above partition function and its fields
have defaults. The number of default partitions depends on the handler. The partition handler uses
one partition as the default whereas NDB Cluster uses the number of nodes in the cluster as
default (together with a calculation based on MAX_ROWS to ensure that all the rows fit in the
defined partitions). Default names of partitions are p0, p1, p2, p3 and so forth, defaults on the
partition options are the same as they are for the options when used as table options.
The following partition options can be defined
1) COMMENT, A comment on the partition
2) DATA DIRECTORY, same as the table option used by MyISAM handler
3) INDEX DIRECTORY, same as the table option used by MyISAM handler
4) MAX_ROWS, maximum number of rows in the partition
5) MIN_ROWS, minimum number of rows in the partition
6) NODEGROUP, Useful for MySQL Cluster to define in which node group the partition is to reside in
7) TABLESPACE, Currently only used by MySQL Cluster to define the table space used by the partition
In way of management commands all the management commands available will be available also
for HASH partitions. Most of them will however require a full reorganisation of the table. The
exception to this will be:
1) ALTER TABLE ADD PARTITION for partitioned tables using the keyword LINEAR
2) ALTER TABLE MERGE PARTITION for partitioned tables using the keyword LINEAR
Rgrds Mikael
PS: Not everything mentioned here will be available in the first pre-alpha version of 5.1. It is also
not defined the exact content of functionality for 5.1, there is also still some room to influence this
set of features.
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog: http://mikaelronstrom.blogspot.com
Edited 1 time(s). Last edit at 06/14/2005 10:28AM by Edwin DeSouza.
Subject
Views
Written By
Posted
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.