CA2159269C - Method and apparatus for achieving uniform data distribution in a parallel database system - Google Patents
Method and apparatus for achieving uniform data distribution in a parallel database system Download PDFInfo
- Publication number
- CA2159269C CA2159269C CA002159269A CA2159269A CA2159269C CA 2159269 C CA2159269 C CA 2159269C CA 002159269 A CA002159269 A CA 002159269A CA 2159269 A CA2159269 A CA 2159269A CA 2159269 C CA2159269 C CA 2159269C
- Authority
- CA
- Canada
- Prior art keywords
- nodes
- data
- subpartitions
- node
- redistribution
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Expired - Fee Related
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F9/00—Arrangements for program control, e.g. control units
- G06F9/06—Arrangements for program control, e.g. control units using stored programs, i.e. using an internal store of processing equipment to receive or retain programs
- G06F9/46—Multiprogramming arrangements
- G06F9/50—Allocation of resources, e.g. of the central processing unit [CPU]
- G06F9/5083—Techniques for rebalancing the load in a distributed system
- G06F9/5088—Techniques for rebalancing the load in a distributed system involving task migration
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99941—Database schema or data structure
- Y10S707/99943—Generating database or data structure, e.g. via user interface
Landscapes
- Engineering & Computer Science (AREA)
- Software Systems (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a method and apparatus for distributing data of a table substantially uniformly across a parallel database system having a plurality of interlinked database nodes.
Data of the table is distributed across a group of nodes (nodegroup) in accordance with a partitioning arrangement.
Resource loading, for example, the workload or storage volume of the nodes is monitored. Data is moved from one or more nodes having higher resource loading to nodes having lower resource loading to achieve a substantially uniform distribution of the resource loading across the group of nodes concerned. In the course of moving data the selection of groups of data to be moved is performed in a manner to reduce the amount of data movement.
Data of the table is distributed across a group of nodes (nodegroup) in accordance with a partitioning arrangement.
Resource loading, for example, the workload or storage volume of the nodes is monitored. Data is moved from one or more nodes having higher resource loading to nodes having lower resource loading to achieve a substantially uniform distribution of the resource loading across the group of nodes concerned. In the course of moving data the selection of groups of data to be moved is performed in a manner to reduce the amount of data movement.
Description
METHOD AND APPARATUS FOR ACHIEVING UNIFORM DATA
DISTRIBUTION IN A PARALLEL DATABASE SYSTEM
Field of the Invention This invention relates generally to parallel database systems and more particularly to a method and apparatus for distributing data in a table across a group of nodes of a parallel database system. The invention is useful in relational database systems, particularly in statically partitioned systems.
Background of the Invention One method of exploiting parallel processing is to partition database tables across the nodes (typically containing one or more processors and associated storage) of a parallel data processing system. This is referred to as "declustering" of the table. If a database table is partitioned across only a subset of the nodes of the system then that table is said to be "partially declustered".
In full declustering, the information in each table of the parallel database system would be spread across the entire parallel database system which can of course result in significant inefficiency from excess communication overhead if small tables are distributed across a parallel database system having a large number of nodes .
When data of a table is partitioned across a parallel database system a non-uniform distribution of the data may occur in the initial distribution, or may occur over a period of time as the data present in the table changes, due to inserts or deletions, or when nodes are added to (or removed from) the group of nodes available for the table.
When the non-uniformity of data becomes significant the efficiency of the parallel database system may suffer as a result of unequal resource loading. This can result from excessive activity at some nodes or excessive data at these nodes while other nodes are more lightly loaded or have excess data storage capacity. A similar problem can occur when a node having higher `- 21S9269 processing capability compared to the processing capabilities of other nodes, is not loaded in proportion to its processing capability .
One solution to the non-uniformity of data distribution is discussed in "An Adaptive Data Placement Scheme for Parallel Database Computer Systems," by K.A. Hua and C. Lee, in Proceedings of the 16th Very Large Data Base Conference (VLDB), Australia, 1990. The method proposed in that discussion does not take the current placement of data into account and considers all partitions as candidates for moving. This can result in excessive data movement with an inefficient solution. In addition no contemplation is given to the minimization of communication overhead.
Sum~ary of the Invention The invention herein overcomes the problems of the prior art in providing a method of distributing data for a parallel database system so that the data is distributed in a substantially uniform manner across the system.
The invention provides a method and apparatus for distributing data of a table partitioned across a group of nodes of a parallel database system to achieve substantially uniform resource loading of the nodes while reducing the amount of data movement.
It is contemplated that the parallel database system referenced has a plurality of interlinked nodes in which each of the nodes is associated with storage and processing resources.
The table may be partitioned across the whole database system or a subset of nodes of the database system. In a statically partitioned database system a partitioning map is generated to define the mapping of partitions of the table being stored to the nodes in a group (nodegroup) of the nodes. The distribution of data to the nodes is done in accordance with a partitioning key value, a partitioning method, and information stored in the partitioning map. The partitioning key comprises a set of one or more defined fields for the table. The fields may be defined ``- 2159269 by a user, or by the system, for example. The partitioning key value is the value of a selected set of fields, usually for a particular row of the table.
Typical partitioning methods may include hash partitioning, range partitioning, or round-robin partitioning, which is applied to the key value to obtain an index value to the partitioning map which provides the node number where the row is to be stored.
One embodiment of the invention herein provides a method of distributing data of a table partitioned across a parallel database system having a number of nodes in which the method includes determining the resource loading associated with the table for each node of the system in which the table is partitioned; comparing the resource loading among the nodes; and if the resource loading among the nodes is distributed in a significantly unbalanced manner; identifying a subpartition contained within the partitions of the table in the nodes that can be moved to nodes having lower resource loading to obtain a substantially uniform distribution with reduced required movement of data and then moving the identified sub-partitions to the ZO nodes have lower resource loading to balance the loading of the node containing partitions of the table.
Another aspect of the invention provides a method in which subpartitions selected for movement are based on the weight (ie.
amount of data) of the subpartitions in descending data weight order. Preferably the selection of subpartitions for movement from one node to another excludes one or more of the largest subpartitions contained in the node from which the selection was made.
The method of Best Fit, which is well known, is used to determine the manner in which selected partitions are distributed among the nodes to obtain a substantially uniformed loading distribution. The "Greedy" approach to the method of best fit has proven to be advantageous.
In one aspect of the invention the resource loading that is to be balanced comprises the amount of data volume at each node.
In another aspect of the invention the resource loading ~` -comprises workload (activity) which is balanced in accordance with the invention.
A further aspect of the invention provides a method of selecting the manner of movement of subpartitions from the consideration of the balancing of workload or data volume storage to obtain the most efficient balancing scheme.
In still another aspect of the invention, the method of the invention redistributes data in a manner selected to reduce data communication overhead between the nodes of the parallel database system.
Yet another aspect of the invention provides a method for the substantially uniform distribution of data by obtaining file size information for all table partitions in the nodes of a parallel database system by reading file attributes for the files lS and obtaining database statistics on file volume and volume of file usage. A distribution listing file is generated depicting the current data distribution. Groups of data (subpartitions) are selected for redistribution among selected nodes (of the nodegroup). A listing is generated for redistribution of the data in which a best fit method is applied with data being selected for redistribution according to descending weight of groups of data to obtain a redistribution plan (eg. a partitioning map) in which data will be substantially uniformly distributed among the nodes (node group). The groups of data are then redistributed among the nodes of the node group in accordance with the redistribution plan. The redistribution plan advantageously contains a listing of where data selected for movement is to be moved.
Another aspect of the invention provides a method of obtaining substantially uniform distribution of database activity in a parallel database system. Transaction activity information is obtained for table partitions in the nodegroup preferably by reading a transaction log maintained by the database system. A
current workload distribution file is generated. Groups of data 3S are selected from nodes having excessive workload distribution for redistribution among selected more lightly loaded nodes. A
~ 21S9269 file listing (eg. a partitioning map) is generated describing a plan of redistribution of the selected data to achieve uniformity of workload. The selected data are redistributed in accordance with the listing plan.
In another aspect of the invention in order to assist in the reduction of data moved in balancing, subpartitions of data having the heaviest weightings are retained in the nodes from which other data is moved during balancing.
Another aspect of the invention provides an article of manufacture (a computer program product) comprising a computer useable medium having computer readable program code routines embodied therein for causing a computer system to distribute data of a table across a group of nodes of a parallel database system to obtain substantially uniform data distribution.
The invention also provides a computer program product for use on a computer system for distributing data of a table partitioned across a parallel database system having a number of nodes; including, a recording medium; a routine recorded on the medium for instructing said computer system to perform the steps of:
determining resource loading at node of the system associated with said table;
comparing resource loading among the nodes;
if said resource loading is distributed in a significantly unbalanced manner;
(a) selecting subpartitions contained within partitions of said table at said nodes having heavy loading for movement to nodes having lower resource loading to obtain a more uniform distribution;
(b) selecting subpartitions for retention at said nodes having heavy loading;
moving said subpartitions selected for movement to said nodes having lower resource loading at balance and resource loading among said nodes containing partitions of said table.
Brief Description of the Drawing~
The features of the invention will become more apparent by reference to the following description taken in conjunction with the accompanying drawings, in which:
Figure 1 is a data relationship diagram illustrating the data stored in catalogues (sets of table describing data in the database) of the database to implement partial declustering of tables in the database system;
Figure 2 is an illustration of a parallel database system;
Figure 3 is an i]lustration of partitioning keys and a partitioning map;
Figure 4 is an illustration of a series of steps performed by an embodiment of the invention herein.
Figure 5 is an illustration of a detailed series of steps lS performed by a specific embodiment of the invention herein to generate an output partitioning map file.
Description of the Preferred Embodiment While the invention herein is useful in a shared nothing parallel data processing system it is also useful in systems that share some or all resources. In a shared-nothing parallel database system implementing a relational database system, a single database can be stored across several computers (which includes a processor and storage) or nodes that do not share memory or disk storage. A technique called "horizontal partitioning" is used to spread the rows of each table in the database across multiple nodes. The advantage of horizontal partitioning is that one can exploit parallel input/output capability to improve the speed at which data is read from storage units associated with the nodes. The technique used to determine in which node a given row of a table is stored is called the "partitioning strategy". A number of suitable partitioning strategies exist, eg. key range, round robin, and hash partitioning. A set of columns (attributes) of the table are defined as the partitioning keys and their values in each row are used for hash or range partitioning for instance, in a hash partitioning strategy, a hash function is applied to values in a predetermined set of columns, namely the partitioning key columns, as illustrated in Figure 5, and the resultant value is used as the node number at which the corresponding row is stored.
While embodiments of the invention are discussed in terms of horizontal partitioning, it will be realized by those skilled in the art referring to this specification, that vertical partitioning can be utilized to spread the columns of a table, or tables across multiple nodes and that the operations land description pertaining to rows can be applied to columns when using vertical partitioning.
A specific implementation described herein makes use of nodegroups in order to support partial declustering of hash partition database tables. Nodegroups are subsets each of which is uniguely identified, eg. by a user provided name of the nodes of a parallel database system. Nodegroups are defined within each database, in this example, by a user, although the processing system can provide default nodegroup definitions.
At the time of their creation, tables are created within existing nodegroups. As a result, the data in the table is partitioned only across the set of nodes defined in the corresponding nodegroup.
Data Structures Figure 1 indicates the basic data structures used to implement partial declustering. The figure is basically an entity relationship diagram showing the relationship between various entities (i.e. the items with in the boxes). All the entities are specific to each database, except the entities called "nodes". Databases implemented in the parallel database system have access to the nodes of the parallel database system.
The entities that are specific to a database are tables, indexes and "nodegroups" and "partitioning maps".
Nodes In referring to Figure 2, in parallel database systems, ~ 21S9269 nodes NO-N5, represent a collection of computational resources including usually a processor 6 for processing, main memory 7, disk storage 11, and communication resources 9. The physical processor 6, which has its own main memory 7, and disks 11, and which can communicate with other processors, represents a node, eg. NO. It is also possible to implement multiple nodes in a single physical processor as long as each node manages its own memory disks and communications. In this case, such nodes will typically multiplex the use of a single physical processor or CPU. In the specific implementation herein, the shared-nothing parallel database system uses a known set of nodes across which data from databases can be stored. Each node is uniquely identified by a node identifier in the embodiment herein. The set of nodes is common to all databases in the system, that is to say, all databases in the system conceptually have the ability to operate on each of the nodes, however, whether they will or not depends on the specific implementation chosen by the database user applying the method of the invention herein.
Nodegroups Referring to Figure 1, the database object called NODEGROUP
DISTRIBUTION IN A PARALLEL DATABASE SYSTEM
Field of the Invention This invention relates generally to parallel database systems and more particularly to a method and apparatus for distributing data in a table across a group of nodes of a parallel database system. The invention is useful in relational database systems, particularly in statically partitioned systems.
Background of the Invention One method of exploiting parallel processing is to partition database tables across the nodes (typically containing one or more processors and associated storage) of a parallel data processing system. This is referred to as "declustering" of the table. If a database table is partitioned across only a subset of the nodes of the system then that table is said to be "partially declustered".
In full declustering, the information in each table of the parallel database system would be spread across the entire parallel database system which can of course result in significant inefficiency from excess communication overhead if small tables are distributed across a parallel database system having a large number of nodes .
When data of a table is partitioned across a parallel database system a non-uniform distribution of the data may occur in the initial distribution, or may occur over a period of time as the data present in the table changes, due to inserts or deletions, or when nodes are added to (or removed from) the group of nodes available for the table.
When the non-uniformity of data becomes significant the efficiency of the parallel database system may suffer as a result of unequal resource loading. This can result from excessive activity at some nodes or excessive data at these nodes while other nodes are more lightly loaded or have excess data storage capacity. A similar problem can occur when a node having higher `- 21S9269 processing capability compared to the processing capabilities of other nodes, is not loaded in proportion to its processing capability .
One solution to the non-uniformity of data distribution is discussed in "An Adaptive Data Placement Scheme for Parallel Database Computer Systems," by K.A. Hua and C. Lee, in Proceedings of the 16th Very Large Data Base Conference (VLDB), Australia, 1990. The method proposed in that discussion does not take the current placement of data into account and considers all partitions as candidates for moving. This can result in excessive data movement with an inefficient solution. In addition no contemplation is given to the minimization of communication overhead.
Sum~ary of the Invention The invention herein overcomes the problems of the prior art in providing a method of distributing data for a parallel database system so that the data is distributed in a substantially uniform manner across the system.
The invention provides a method and apparatus for distributing data of a table partitioned across a group of nodes of a parallel database system to achieve substantially uniform resource loading of the nodes while reducing the amount of data movement.
It is contemplated that the parallel database system referenced has a plurality of interlinked nodes in which each of the nodes is associated with storage and processing resources.
The table may be partitioned across the whole database system or a subset of nodes of the database system. In a statically partitioned database system a partitioning map is generated to define the mapping of partitions of the table being stored to the nodes in a group (nodegroup) of the nodes. The distribution of data to the nodes is done in accordance with a partitioning key value, a partitioning method, and information stored in the partitioning map. The partitioning key comprises a set of one or more defined fields for the table. The fields may be defined ``- 2159269 by a user, or by the system, for example. The partitioning key value is the value of a selected set of fields, usually for a particular row of the table.
Typical partitioning methods may include hash partitioning, range partitioning, or round-robin partitioning, which is applied to the key value to obtain an index value to the partitioning map which provides the node number where the row is to be stored.
One embodiment of the invention herein provides a method of distributing data of a table partitioned across a parallel database system having a number of nodes in which the method includes determining the resource loading associated with the table for each node of the system in which the table is partitioned; comparing the resource loading among the nodes; and if the resource loading among the nodes is distributed in a significantly unbalanced manner; identifying a subpartition contained within the partitions of the table in the nodes that can be moved to nodes having lower resource loading to obtain a substantially uniform distribution with reduced required movement of data and then moving the identified sub-partitions to the ZO nodes have lower resource loading to balance the loading of the node containing partitions of the table.
Another aspect of the invention provides a method in which subpartitions selected for movement are based on the weight (ie.
amount of data) of the subpartitions in descending data weight order. Preferably the selection of subpartitions for movement from one node to another excludes one or more of the largest subpartitions contained in the node from which the selection was made.
The method of Best Fit, which is well known, is used to determine the manner in which selected partitions are distributed among the nodes to obtain a substantially uniformed loading distribution. The "Greedy" approach to the method of best fit has proven to be advantageous.
In one aspect of the invention the resource loading that is to be balanced comprises the amount of data volume at each node.
In another aspect of the invention the resource loading ~` -comprises workload (activity) which is balanced in accordance with the invention.
A further aspect of the invention provides a method of selecting the manner of movement of subpartitions from the consideration of the balancing of workload or data volume storage to obtain the most efficient balancing scheme.
In still another aspect of the invention, the method of the invention redistributes data in a manner selected to reduce data communication overhead between the nodes of the parallel database system.
Yet another aspect of the invention provides a method for the substantially uniform distribution of data by obtaining file size information for all table partitions in the nodes of a parallel database system by reading file attributes for the files lS and obtaining database statistics on file volume and volume of file usage. A distribution listing file is generated depicting the current data distribution. Groups of data (subpartitions) are selected for redistribution among selected nodes (of the nodegroup). A listing is generated for redistribution of the data in which a best fit method is applied with data being selected for redistribution according to descending weight of groups of data to obtain a redistribution plan (eg. a partitioning map) in which data will be substantially uniformly distributed among the nodes (node group). The groups of data are then redistributed among the nodes of the node group in accordance with the redistribution plan. The redistribution plan advantageously contains a listing of where data selected for movement is to be moved.
Another aspect of the invention provides a method of obtaining substantially uniform distribution of database activity in a parallel database system. Transaction activity information is obtained for table partitions in the nodegroup preferably by reading a transaction log maintained by the database system. A
current workload distribution file is generated. Groups of data 3S are selected from nodes having excessive workload distribution for redistribution among selected more lightly loaded nodes. A
~ 21S9269 file listing (eg. a partitioning map) is generated describing a plan of redistribution of the selected data to achieve uniformity of workload. The selected data are redistributed in accordance with the listing plan.
In another aspect of the invention in order to assist in the reduction of data moved in balancing, subpartitions of data having the heaviest weightings are retained in the nodes from which other data is moved during balancing.
Another aspect of the invention provides an article of manufacture (a computer program product) comprising a computer useable medium having computer readable program code routines embodied therein for causing a computer system to distribute data of a table across a group of nodes of a parallel database system to obtain substantially uniform data distribution.
The invention also provides a computer program product for use on a computer system for distributing data of a table partitioned across a parallel database system having a number of nodes; including, a recording medium; a routine recorded on the medium for instructing said computer system to perform the steps of:
determining resource loading at node of the system associated with said table;
comparing resource loading among the nodes;
if said resource loading is distributed in a significantly unbalanced manner;
(a) selecting subpartitions contained within partitions of said table at said nodes having heavy loading for movement to nodes having lower resource loading to obtain a more uniform distribution;
(b) selecting subpartitions for retention at said nodes having heavy loading;
moving said subpartitions selected for movement to said nodes having lower resource loading at balance and resource loading among said nodes containing partitions of said table.
Brief Description of the Drawing~
The features of the invention will become more apparent by reference to the following description taken in conjunction with the accompanying drawings, in which:
Figure 1 is a data relationship diagram illustrating the data stored in catalogues (sets of table describing data in the database) of the database to implement partial declustering of tables in the database system;
Figure 2 is an illustration of a parallel database system;
Figure 3 is an i]lustration of partitioning keys and a partitioning map;
Figure 4 is an illustration of a series of steps performed by an embodiment of the invention herein.
Figure 5 is an illustration of a detailed series of steps lS performed by a specific embodiment of the invention herein to generate an output partitioning map file.
Description of the Preferred Embodiment While the invention herein is useful in a shared nothing parallel data processing system it is also useful in systems that share some or all resources. In a shared-nothing parallel database system implementing a relational database system, a single database can be stored across several computers (which includes a processor and storage) or nodes that do not share memory or disk storage. A technique called "horizontal partitioning" is used to spread the rows of each table in the database across multiple nodes. The advantage of horizontal partitioning is that one can exploit parallel input/output capability to improve the speed at which data is read from storage units associated with the nodes. The technique used to determine in which node a given row of a table is stored is called the "partitioning strategy". A number of suitable partitioning strategies exist, eg. key range, round robin, and hash partitioning. A set of columns (attributes) of the table are defined as the partitioning keys and their values in each row are used for hash or range partitioning for instance, in a hash partitioning strategy, a hash function is applied to values in a predetermined set of columns, namely the partitioning key columns, as illustrated in Figure 5, and the resultant value is used as the node number at which the corresponding row is stored.
While embodiments of the invention are discussed in terms of horizontal partitioning, it will be realized by those skilled in the art referring to this specification, that vertical partitioning can be utilized to spread the columns of a table, or tables across multiple nodes and that the operations land description pertaining to rows can be applied to columns when using vertical partitioning.
A specific implementation described herein makes use of nodegroups in order to support partial declustering of hash partition database tables. Nodegroups are subsets each of which is uniguely identified, eg. by a user provided name of the nodes of a parallel database system. Nodegroups are defined within each database, in this example, by a user, although the processing system can provide default nodegroup definitions.
At the time of their creation, tables are created within existing nodegroups. As a result, the data in the table is partitioned only across the set of nodes defined in the corresponding nodegroup.
Data Structures Figure 1 indicates the basic data structures used to implement partial declustering. The figure is basically an entity relationship diagram showing the relationship between various entities (i.e. the items with in the boxes). All the entities are specific to each database, except the entities called "nodes". Databases implemented in the parallel database system have access to the nodes of the parallel database system.
The entities that are specific to a database are tables, indexes and "nodegroups" and "partitioning maps".
Nodes In referring to Figure 2, in parallel database systems, ~ 21S9269 nodes NO-N5, represent a collection of computational resources including usually a processor 6 for processing, main memory 7, disk storage 11, and communication resources 9. The physical processor 6, which has its own main memory 7, and disks 11, and which can communicate with other processors, represents a node, eg. NO. It is also possible to implement multiple nodes in a single physical processor as long as each node manages its own memory disks and communications. In this case, such nodes will typically multiplex the use of a single physical processor or CPU. In the specific implementation herein, the shared-nothing parallel database system uses a known set of nodes across which data from databases can be stored. Each node is uniquely identified by a node identifier in the embodiment herein. The set of nodes is common to all databases in the system, that is to say, all databases in the system conceptually have the ability to operate on each of the nodes, however, whether they will or not depends on the specific implementation chosen by the database user applying the method of the invention herein.
Nodegroups Referring to Figure 1, the database object called NODEGROUP
2, is a named subset of the set of nodes in a shared-nothing parallel database system described herein. Each nodegroup in a given database is identified by a unique name. As indicated in Figure 1, the implementation of the invention herein supports a many-to-many (M-N) relationship between nodes and nodegroups. A
nodegroup 2, may contain one or more nodes and each node can be a member of zero or more nodegroups. A nodegroup must contain at least one node. Figure 3 illustrates another nodegroup formed from nodes N1, N2, N3, N4.
Partitioning Maps Referring again to Figure 1, partitioning map 3 is a data structure~associated with a nodegroup 2, which indicates the node on which a given row of a given table is stored. Each partitioning map has a unique partitioning map identification (PMID). As indicated in Figure 1, each nodegroup 2 is associated with one partitioning map 3 and each partitioning map 3 belongs only to one nodegroup. During redistribution a nodegroup may have two maps, the original one and the new one reflecting the redistribution plan.
A partitioning map can be generated by allocating node numbers to the partitions using a round robin allocation scheme to assign node numbers in the partitioning map. For example, in a 20 node system if there are three nodes in a nodegroup eg.
nodes 7, 11, 15 and assuming the partition map contains 4K (4096) entries then the partitioning map would be generated as 7, 11, 15, 7, 11, 15....... which would repeat to fill the entire 4K
space. This of course, assumes a uniform distribution of data, allocating an equal number of partitions for each node. Rows of the database are mapped to the nodes in the nodegroup using the partitioning map.
Tables Still referring to Figure 1, a database consists of a set of tables 4. a table 4 in the database is uniquely identified by the creator name and table name, in a typical implementation.
Each table is created within a nodegroup. A nodegroup can contain zero or more (N) tables.
Indexes A table 4 may have zero or more indexes 5 associated with it. Each index is uniquely identified by the name of the creator and the name of index in this implementation. Other identification methods are useful as well. Each index 5 is associated with a single table 4. Typical]y the index 5 consists of a fast access structure to access data within the table. This is well known in the art.
Partitioning Map Referring to Figure 3 the distribution of data of a table across a subset of nodes (nodes 1, 2, 3, 4) in a parallel system is illustrated.
In this illustration, A is a column of table 4 and is used as a partitioning key.
H( ), 15 is a hash function used to obtain a partition number when applied to the partitioning key value.
Partitioning map 3 is an array in which each entry contains a node number of the node in which rows of data that hash to this partition (node) are stored.
In this illustration column B is not used in hashing. The partitioning of data from column B follows that of column A.
From a review of Figure 3 the distribution of data Ai and Bi (i=1 to 6) from table 4 nodes N1, N2, N3 and N4 respectively, is accomplished using partitioning map 3, as may be readily appreciated.
Referring to the parallel database system depicted schematically in Figure 2 it may be seen that the system comprises a number of nodes and N0 to N5 some of which have data (T10, T20, T11, T21, T12, T13, T23) stored in the respective storage of the nodes N0 through N3 in which tables T1 and T2 have been partitioned. It may also be noted that nodes N4 and N5 do contain any partitions of tables T1 and T2. The representation indicates that the data of tables Tl and T2 are not uniformly distributed throughout Nodes N0 to N3. For instance Node N0 appears to be heavily loaded whereas Node N1 and Node N2 are more lightly loaded.
It should be noted as in the normal arrangement of parallel database systems each of the nodes N0 through N5 has associated with it a log L0 through L5. Log reader 20, which can comprise software operating from any node, is connected to each of logs L0 to L5 for the purpose of reading their contents. Files statistics monitor 21, which can be software operating from any node, is coupled to the storage of nodes N0 through N5 in order to monitor the data contained by their respective storage devices 11 .
The logreader 20 is a program that is able to access files at each node of the database system and determine the volume of l~ 2159269 database transactions issued against each table in the database.
This is done by reading database log files (Ll - L5).
The file statistics monitor 21 is a computer program that is able to read the files from each node of the parallel database system and determine the size of the data and index files corresponding at tables in the database.
Figure 4a depicts a specific implementation of the invention in which alternative paths are provided for determining and arranging for the redistribution of resource loading either based on the volume of data present at each node or the workload for instance transaction activity of the nodes. Depending on the potential advantages the most optimal distribution of data may be selected from balancing either workload activity or data volume storage. Depending on the processing capability of the individual nodes of the parallel database system either file size balancing or transaction activity may provide optimal efficiency.
The balancing of data volumes among nodes to achieve uniformity has been found to result insignificant efficiency improvements.
The benefits of the invention herein may be provided by a program product adapted to be operated in conjunction with a parallel database system. In the application of a software embodiment of the invention, a user employing the software operating on the parallel database computer system, initially Z5 determines which table stored on the parallel database system is to be considered for redistribution. Either the software or the user can determine whether redistribution is to be based on workload activity or data volumes. Conveniently an implementation of the invention may provide for periodic or occasional consideration of the tables by the database computer system for initiation of redistribution. Referring to Figure 4b, considering redistribution based on data volumes the apparatus of the invention obtains file size information for all table partitions (including subpartitions) by reading file attributes and obtaining database statistics relating thereto. It generates a current data distribution file which contains the weight of each subpartition (this information is used to compute the mean weight for each node (MNW)) from this information and then generates a partitioning map for the redistribution of the data based on the movement of subpartitions (eg. rows) of data such that the result of the redistribution assures that each node will have as close to the mean weight as possible. This is done by moving subpartitions of data from nodes having excessive loading to nodes having less loading. Priority is given to heavier subpartitions which are not moved from their original nodes.
When subpartitions are moved from nodes having excessive loading the subpartitions to be moved are considered in descending weight order. A Best Fit "greedy" approach is used to determine the node to which such subpartitions are moved.
Referring to Figure 4c an alternative method of the invention which may also be embodied in the software accomplishes redistribution of data based on workload (activity) of the nodes.
Again referring to the data of the table to be distributed the software of the invention obtains transaction activity information for all table partitions by reading the database logs associated with them (LO - L5 in Figure 2), and generates the current workload distribution file which depicts the current distribution of workload among the nodes. The current workload distribution file is then used to assign weights to the subpartitions of the table. With this information a new partitioning map is generated for the redistribution of data based on the movement of subpartitions of data to result in each node having as close to the mean weight of data as possible.
One specific embodiment of the invention advantageously provides for the movement of groups of subpartitions. For movement from the heaviest overloaded node to the least loaded node the invention may allocate as many subpartitions as are needed to bring the least loaded node to the mean weight.
Examples of Specific Embodiments of the Invention As is well known a shared-nothing (SN) parallel database system consists of a set of "nodes" each associated with its own i 2159~69 processing, storage, and communications resources, across which databases are implemented. Such systems employ a partitioned storage model where data belonging to database tables are partitioned across a specified subset of nodes using a default or user-specified partitioning strategy. It is desirable to have a uniform distribution of data across the nodes so that the system resources at each node are equally utilized, thereby resulting in optimal use of the parallel database system. In many practical situations, it is possible that certain data values occur more frequently than others, in a given database. Thus, the use of "value-based" partitioning schemes, such as hash or key-range partitioning, may result in a skew in the distribution of data across nodes. It is, therefore, necessary to provide a means for redistributing data in order to minimize such skews, as much as lS possible. In addition, data can be redistributed to minimize workload skews as well.
The pseudocode discussed here may be used as the basis for data redistribution software in a shared-nothing parallel database system, in order to achieve uniform distribution of data across nodes of the system and also to support the addition and deletion of nodes to or from the parallel database system.
Assuming for this example that the parallel database system supports an "indirect" partitioning scheme using hash or range partitioning. The details on such a scheme are further described under Canadian Patent Application No. 2,150,745 (Method and Apparatus for Implementing Partial Declustering in a parallel Database System). The aspects of that scheme are important to the current discussion are described below.
Database tables are associated with partitioning keys and are created in nodegroups. A nodegroup is a set of nodes. As mentioned above, a node represents storage, processing, and communications resources that are used by the parallel database system. Nodegroups are associated with "partitioning maps". A
partitioning map is a system defined data structure that indicates the mapping of horizontal usually partitions of a database table to nodes in the corresponding nodegroup.
`_ 2159~69 Rowæ are inserted into a table as follows:
1. For a given row, the value of the partitioning key of that row is used as input to the partitioning function ~hash or key-range). This function returns a partition number, p, in some fixed range, say 0 to P-l.
2. The partition number, p, is used as an index into the partitioning map which contains P entries. The node number assigned to location p in the map is the node at which the original row is stored.
The following example illustrates situations in which this invention would be used. Suppose a nodegroup named My_Nodegroup has been defined containing nodes 3 and 5. Also suppose that the partitioning map (PM) associated with this nodegroup has 4 entries, i.e. P=4. Suppose the array, PM, is initialized as follows:
Contents of the Partitioning Map array, PM
Array Entry: PM(0) PM(1) PM(2) PM(3) Array Content: 5 3 5 3 The above PM indicates that horizontal partitions 0 and 2 of tables created in My_Nodegroup will be mapped to node 5 and horizontal partitions 1 and 3 will be mapped to node 3. This mapping works well when each partition has about the same amount of data. However, suppose, partition 2 contains 50% of the data of the tables in the nodegroup. In this case, a desirable mapping of partitions of nodes may be:
Array Entry: PM(0) PM(l) PM(2) PM(3) Array Content: 3 3 5 3 Now, suppose we wish to add a new nodes, say node 4, to the nodegroup and move some data to this node. After adding the new node the PM may now be:
Array Entry: PM(0) PM(1) PM(2) PM(3) Array Content: 3 4 5 3 The pseudocode illustration of the invention described herein will derive a "resultant" or "target partitioning map"
which balances the amount of data at each node, given a "source partitioning map" (i.e. the original map) and other optional inputs such as the distribution of data across the partitions of the partitioning map, a list of nodes to be added to the nodegroup, and/or a list of nodes to be removed from the nodegroup.
The following two redistribution cases are discussed:
1. redistribute the data of all tables in a nodegroup given that the data distribution is uniform across all the subpartitions of the partitioning map (called the UNIFORM
case) 2. redistribute data of all tables in a nodegroup given an input distribution file that describes the distribution of data across the partitions of the partitioning map. This is used when the data is not uniformly distributed (called the NONUNIFORM case) In the above cases, nodes may be added and/or dropped from the nodegroup as part of the redistribution operation.
Since the UNIFORM case is used when every partition in the partitioning map represents the same amount of data, or workload, the redistribution invention treats every partition as being equivalent. Based on this assumption, the invention achieves a uniform allocation of partitions to nodes while minimizing the communications overhead. This is achieved by minimizing the number of communications links (called tablequeue connections) set up during redistribution.
In the NONUNIFORM case, the distribution of data across partitions is provided as input. Some subpartitions may represent more data, or workload, than others. In this case, the invention achieves a uniform data or workload distribution across nodes by moving the minimum number of subpartitions necessary to achieve this goal.
`- 2159269 In the SINGLE_NODE case, the algorithm moves all partitions to the single node.
Design Specifics Inputs 1. Current partitioning map array. Contains a fixed number, P, of entries, indicating the mapping of partitions to nodes, prior to redistribution. For example, the following represents a partitioning map containing P=20 partitions:
Partitioning Map Array:
Partitioning Map Array:
Partition Number = O 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 l9 Node Number = O 1 2 3 0 1 2 3 0 1 2 3 1 2 3 2 3 0 In the above map, for example, partitions 3, 7, 11, 14, and 16 are all mapped to node 3.
2. Data Distribution array. This input is specified only for the NONUNIFORM case. The file contains the same number of entries as the partitioning map, indicating the amount of data or workload represented by each corresponding partition. Each entry is also referred to as the weig~t of the partition. For example, the following is a data distribution array that may be used in conjunction with the above partitioning map array:
Distribution Array:
Partition Number = O 1 2 3 4 5 6 7 8 9 1011 12 13 14 Weight 58 031 64 13 37 99 32 46 0 4 0 13 7 8 For example, considering all partitions that map to node 3, the above array specifies that the weight of partition 3 is 64, partition 7 is 32, partition 11 is 0, partition 14 is 8, and partition 16 is 11. Thus, the total weight of all the partitions ~- 2159269 at node 3 is 64+32+0+8+11 = 115.
In the UNIFORM case, all subpartitions are assumed to have the same weight (=1). We first present the NONUNIFORM case.
Generating the Target Partitioning Map for the NONUNIFORM Case The following pseudocode may be implemented in a program to be executed in the case where the input distribution has been specified. The program retains as many of the largest partitions as possible at each node and moves the remaining partitions in order to achieve a uniform result distribution across nodes. In the process, different nodes may have different number of partitions, even though the amount of data or workload at each node may be about the same.
1. Initialize two lists called, E_List (Empty List) and L_List (Leftover List), to NULL. Details provided in the following steps describe how the E_List and L_List are used.
2. IN denotes the set of all nodes in the source partitioning map. ¦IN¦ = number of nodes in source partitioning map (21).
nodegroup 2, may contain one or more nodes and each node can be a member of zero or more nodegroups. A nodegroup must contain at least one node. Figure 3 illustrates another nodegroup formed from nodes N1, N2, N3, N4.
Partitioning Maps Referring again to Figure 1, partitioning map 3 is a data structure~associated with a nodegroup 2, which indicates the node on which a given row of a given table is stored. Each partitioning map has a unique partitioning map identification (PMID). As indicated in Figure 1, each nodegroup 2 is associated with one partitioning map 3 and each partitioning map 3 belongs only to one nodegroup. During redistribution a nodegroup may have two maps, the original one and the new one reflecting the redistribution plan.
A partitioning map can be generated by allocating node numbers to the partitions using a round robin allocation scheme to assign node numbers in the partitioning map. For example, in a 20 node system if there are three nodes in a nodegroup eg.
nodes 7, 11, 15 and assuming the partition map contains 4K (4096) entries then the partitioning map would be generated as 7, 11, 15, 7, 11, 15....... which would repeat to fill the entire 4K
space. This of course, assumes a uniform distribution of data, allocating an equal number of partitions for each node. Rows of the database are mapped to the nodes in the nodegroup using the partitioning map.
Tables Still referring to Figure 1, a database consists of a set of tables 4. a table 4 in the database is uniquely identified by the creator name and table name, in a typical implementation.
Each table is created within a nodegroup. A nodegroup can contain zero or more (N) tables.
Indexes A table 4 may have zero or more indexes 5 associated with it. Each index is uniquely identified by the name of the creator and the name of index in this implementation. Other identification methods are useful as well. Each index 5 is associated with a single table 4. Typical]y the index 5 consists of a fast access structure to access data within the table. This is well known in the art.
Partitioning Map Referring to Figure 3 the distribution of data of a table across a subset of nodes (nodes 1, 2, 3, 4) in a parallel system is illustrated.
In this illustration, A is a column of table 4 and is used as a partitioning key.
H( ), 15 is a hash function used to obtain a partition number when applied to the partitioning key value.
Partitioning map 3 is an array in which each entry contains a node number of the node in which rows of data that hash to this partition (node) are stored.
In this illustration column B is not used in hashing. The partitioning of data from column B follows that of column A.
From a review of Figure 3 the distribution of data Ai and Bi (i=1 to 6) from table 4 nodes N1, N2, N3 and N4 respectively, is accomplished using partitioning map 3, as may be readily appreciated.
Referring to the parallel database system depicted schematically in Figure 2 it may be seen that the system comprises a number of nodes and N0 to N5 some of which have data (T10, T20, T11, T21, T12, T13, T23) stored in the respective storage of the nodes N0 through N3 in which tables T1 and T2 have been partitioned. It may also be noted that nodes N4 and N5 do contain any partitions of tables T1 and T2. The representation indicates that the data of tables Tl and T2 are not uniformly distributed throughout Nodes N0 to N3. For instance Node N0 appears to be heavily loaded whereas Node N1 and Node N2 are more lightly loaded.
It should be noted as in the normal arrangement of parallel database systems each of the nodes N0 through N5 has associated with it a log L0 through L5. Log reader 20, which can comprise software operating from any node, is connected to each of logs L0 to L5 for the purpose of reading their contents. Files statistics monitor 21, which can be software operating from any node, is coupled to the storage of nodes N0 through N5 in order to monitor the data contained by their respective storage devices 11 .
The logreader 20 is a program that is able to access files at each node of the database system and determine the volume of l~ 2159269 database transactions issued against each table in the database.
This is done by reading database log files (Ll - L5).
The file statistics monitor 21 is a computer program that is able to read the files from each node of the parallel database system and determine the size of the data and index files corresponding at tables in the database.
Figure 4a depicts a specific implementation of the invention in which alternative paths are provided for determining and arranging for the redistribution of resource loading either based on the volume of data present at each node or the workload for instance transaction activity of the nodes. Depending on the potential advantages the most optimal distribution of data may be selected from balancing either workload activity or data volume storage. Depending on the processing capability of the individual nodes of the parallel database system either file size balancing or transaction activity may provide optimal efficiency.
The balancing of data volumes among nodes to achieve uniformity has been found to result insignificant efficiency improvements.
The benefits of the invention herein may be provided by a program product adapted to be operated in conjunction with a parallel database system. In the application of a software embodiment of the invention, a user employing the software operating on the parallel database computer system, initially Z5 determines which table stored on the parallel database system is to be considered for redistribution. Either the software or the user can determine whether redistribution is to be based on workload activity or data volumes. Conveniently an implementation of the invention may provide for periodic or occasional consideration of the tables by the database computer system for initiation of redistribution. Referring to Figure 4b, considering redistribution based on data volumes the apparatus of the invention obtains file size information for all table partitions (including subpartitions) by reading file attributes and obtaining database statistics relating thereto. It generates a current data distribution file which contains the weight of each subpartition (this information is used to compute the mean weight for each node (MNW)) from this information and then generates a partitioning map for the redistribution of the data based on the movement of subpartitions (eg. rows) of data such that the result of the redistribution assures that each node will have as close to the mean weight as possible. This is done by moving subpartitions of data from nodes having excessive loading to nodes having less loading. Priority is given to heavier subpartitions which are not moved from their original nodes.
When subpartitions are moved from nodes having excessive loading the subpartitions to be moved are considered in descending weight order. A Best Fit "greedy" approach is used to determine the node to which such subpartitions are moved.
Referring to Figure 4c an alternative method of the invention which may also be embodied in the software accomplishes redistribution of data based on workload (activity) of the nodes.
Again referring to the data of the table to be distributed the software of the invention obtains transaction activity information for all table partitions by reading the database logs associated with them (LO - L5 in Figure 2), and generates the current workload distribution file which depicts the current distribution of workload among the nodes. The current workload distribution file is then used to assign weights to the subpartitions of the table. With this information a new partitioning map is generated for the redistribution of data based on the movement of subpartitions of data to result in each node having as close to the mean weight of data as possible.
One specific embodiment of the invention advantageously provides for the movement of groups of subpartitions. For movement from the heaviest overloaded node to the least loaded node the invention may allocate as many subpartitions as are needed to bring the least loaded node to the mean weight.
Examples of Specific Embodiments of the Invention As is well known a shared-nothing (SN) parallel database system consists of a set of "nodes" each associated with its own i 2159~69 processing, storage, and communications resources, across which databases are implemented. Such systems employ a partitioned storage model where data belonging to database tables are partitioned across a specified subset of nodes using a default or user-specified partitioning strategy. It is desirable to have a uniform distribution of data across the nodes so that the system resources at each node are equally utilized, thereby resulting in optimal use of the parallel database system. In many practical situations, it is possible that certain data values occur more frequently than others, in a given database. Thus, the use of "value-based" partitioning schemes, such as hash or key-range partitioning, may result in a skew in the distribution of data across nodes. It is, therefore, necessary to provide a means for redistributing data in order to minimize such skews, as much as lS possible. In addition, data can be redistributed to minimize workload skews as well.
The pseudocode discussed here may be used as the basis for data redistribution software in a shared-nothing parallel database system, in order to achieve uniform distribution of data across nodes of the system and also to support the addition and deletion of nodes to or from the parallel database system.
Assuming for this example that the parallel database system supports an "indirect" partitioning scheme using hash or range partitioning. The details on such a scheme are further described under Canadian Patent Application No. 2,150,745 (Method and Apparatus for Implementing Partial Declustering in a parallel Database System). The aspects of that scheme are important to the current discussion are described below.
Database tables are associated with partitioning keys and are created in nodegroups. A nodegroup is a set of nodes. As mentioned above, a node represents storage, processing, and communications resources that are used by the parallel database system. Nodegroups are associated with "partitioning maps". A
partitioning map is a system defined data structure that indicates the mapping of horizontal usually partitions of a database table to nodes in the corresponding nodegroup.
`_ 2159~69 Rowæ are inserted into a table as follows:
1. For a given row, the value of the partitioning key of that row is used as input to the partitioning function ~hash or key-range). This function returns a partition number, p, in some fixed range, say 0 to P-l.
2. The partition number, p, is used as an index into the partitioning map which contains P entries. The node number assigned to location p in the map is the node at which the original row is stored.
The following example illustrates situations in which this invention would be used. Suppose a nodegroup named My_Nodegroup has been defined containing nodes 3 and 5. Also suppose that the partitioning map (PM) associated with this nodegroup has 4 entries, i.e. P=4. Suppose the array, PM, is initialized as follows:
Contents of the Partitioning Map array, PM
Array Entry: PM(0) PM(1) PM(2) PM(3) Array Content: 5 3 5 3 The above PM indicates that horizontal partitions 0 and 2 of tables created in My_Nodegroup will be mapped to node 5 and horizontal partitions 1 and 3 will be mapped to node 3. This mapping works well when each partition has about the same amount of data. However, suppose, partition 2 contains 50% of the data of the tables in the nodegroup. In this case, a desirable mapping of partitions of nodes may be:
Array Entry: PM(0) PM(l) PM(2) PM(3) Array Content: 3 3 5 3 Now, suppose we wish to add a new nodes, say node 4, to the nodegroup and move some data to this node. After adding the new node the PM may now be:
Array Entry: PM(0) PM(1) PM(2) PM(3) Array Content: 3 4 5 3 The pseudocode illustration of the invention described herein will derive a "resultant" or "target partitioning map"
which balances the amount of data at each node, given a "source partitioning map" (i.e. the original map) and other optional inputs such as the distribution of data across the partitions of the partitioning map, a list of nodes to be added to the nodegroup, and/or a list of nodes to be removed from the nodegroup.
The following two redistribution cases are discussed:
1. redistribute the data of all tables in a nodegroup given that the data distribution is uniform across all the subpartitions of the partitioning map (called the UNIFORM
case) 2. redistribute data of all tables in a nodegroup given an input distribution file that describes the distribution of data across the partitions of the partitioning map. This is used when the data is not uniformly distributed (called the NONUNIFORM case) In the above cases, nodes may be added and/or dropped from the nodegroup as part of the redistribution operation.
Since the UNIFORM case is used when every partition in the partitioning map represents the same amount of data, or workload, the redistribution invention treats every partition as being equivalent. Based on this assumption, the invention achieves a uniform allocation of partitions to nodes while minimizing the communications overhead. This is achieved by minimizing the number of communications links (called tablequeue connections) set up during redistribution.
In the NONUNIFORM case, the distribution of data across partitions is provided as input. Some subpartitions may represent more data, or workload, than others. In this case, the invention achieves a uniform data or workload distribution across nodes by moving the minimum number of subpartitions necessary to achieve this goal.
`- 2159269 In the SINGLE_NODE case, the algorithm moves all partitions to the single node.
Design Specifics Inputs 1. Current partitioning map array. Contains a fixed number, P, of entries, indicating the mapping of partitions to nodes, prior to redistribution. For example, the following represents a partitioning map containing P=20 partitions:
Partitioning Map Array:
Partitioning Map Array:
Partition Number = O 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 l9 Node Number = O 1 2 3 0 1 2 3 0 1 2 3 1 2 3 2 3 0 In the above map, for example, partitions 3, 7, 11, 14, and 16 are all mapped to node 3.
2. Data Distribution array. This input is specified only for the NONUNIFORM case. The file contains the same number of entries as the partitioning map, indicating the amount of data or workload represented by each corresponding partition. Each entry is also referred to as the weig~t of the partition. For example, the following is a data distribution array that may be used in conjunction with the above partitioning map array:
Distribution Array:
Partition Number = O 1 2 3 4 5 6 7 8 9 1011 12 13 14 Weight 58 031 64 13 37 99 32 46 0 4 0 13 7 8 For example, considering all partitions that map to node 3, the above array specifies that the weight of partition 3 is 64, partition 7 is 32, partition 11 is 0, partition 14 is 8, and partition 16 is 11. Thus, the total weight of all the partitions ~- 2159269 at node 3 is 64+32+0+8+11 = 115.
In the UNIFORM case, all subpartitions are assumed to have the same weight (=1). We first present the NONUNIFORM case.
Generating the Target Partitioning Map for the NONUNIFORM Case The following pseudocode may be implemented in a program to be executed in the case where the input distribution has been specified. The program retains as many of the largest partitions as possible at each node and moves the remaining partitions in order to achieve a uniform result distribution across nodes. In the process, different nodes may have different number of partitions, even though the amount of data or workload at each node may be about the same.
1. Initialize two lists called, E_List (Empty List) and L_List (Leftover List), to NULL. Details provided in the following steps describe how the E_List and L_List are used.
2. IN denotes the set of all nodes in the source partitioning map. ¦IN¦ = number of nodes in source partitioning map (21).
3. DN denotes the set of all nodes to be dropped during redistribution. ¦DN¦ = number of nodes to be dropped (>0).
4. AN denotes the set of nodes to be added during redistribution. ¦AN¦ = number of nodes to be added (>0).
5. Let ON denotes the set of all output nodes, i.e. all nodes in the target partitioning map. The total number of output nodes N = ¦ON¦ = ¦IN¦ - ¦DN¦ + ¦AN¦ (~1).
6. Let w(i) denote the weight of each partition, as specified in the data distribution array. Let Total Weight, W=SUMi=0~4ogs(w(i))-
7. Compute Mean Node Weight, MNW = LW/N~ (i.e. floor of W/N).
8. Scan the given input distribution information and move all partitions with w(i) = O to the empty_list (E_List), where i=0,4095.
9. For each node in the drop set, DN, insert all partitions of the node in the Leftover List, L_List.
_ 2159269
_ 2159269
10. For each node, i, in the output node set, ON, form a sorted list, L(i), of all partitions with w(j)~O, j=0,4095, that map to that node, in descending order of partition weight.
For newly added nodes, their corresponding L(i) will be empty.
For newly added nodes, their corresponding L(i) will be empty.
11. For each node, i, in the output set, ON, do:
Scan through L(i) in descending weight order and retain the maximum number of partitions in L(i), such that the sum of the weights of the retained partitions is < MNW. The remaining partitions are inserted into L_List. Thus, priority is given to the heavier partitions, i.e. the heavier partitions are not moved from their original nodes.
If the weight of the first partition is itself > MNW, then retain that partition in L(i) and insert the remaining partitions into L_List.
Compute W(i) to reflect the total weights of the retained partitions. Note, for newly added nodes, their corresponding L(i) is NULL and W(i)=O.
Scan through L(i) in descending weight order and retain the maximum number of partitions in L(i), such that the sum of the weights of the retained partitions is < MNW. The remaining partitions are inserted into L_List. Thus, priority is given to the heavier partitions, i.e. the heavier partitions are not moved from their original nodes.
If the weight of the first partition is itself > MNW, then retain that partition in L(i) and insert the remaining partitions into L_List.
Compute W(i) to reflect the total weights of the retained partitions. Note, for newly added nodes, their corresponding L(i) is NULL and W(i)=O.
12. Sort L_List in descending order of weight.
13. Iterate through the following, until L_List is NULL:
Assign the first L_List entry (the heaviest partition) to the best fitted node i (i.e. W(i) + the heaviest L_List entry is closest to MNW, and ~ MNW if possible). Increment the W(i) of selected node by the weight of the current list entry and add current list entry to L(i). Remove the first L_List entry from L_List.
If more than one node is a candidate for assignment then reassign partition to node it came from, if possible, in order to minimize communication overhead. Otherwise randomly pick a node.
Assign the first L_List entry (the heaviest partition) to the best fitted node i (i.e. W(i) + the heaviest L_List entry is closest to MNW, and ~ MNW if possible). Increment the W(i) of selected node by the weight of the current list entry and add current list entry to L(i). Remove the first L_List entry from L_List.
If more than one node is a candidate for assignment then reassign partition to node it came from, if possible, in order to minimize communication overhead. Otherwise randomly pick a node.
14. If E_List is not NULL, iterate through the following until E_List is NULL.
Assign current partition from E_List to the L(i) which satisfies MINL(~ oN(number of partitions) ( means belongs to).
Generating a Target Partitioning Map for the UNIFORM Case A program implementing the following pseudocode may be executed in the UNIFORM case (see Figure 5). The data or workload is assumed to be uniformly distributed across all S subpartitions (ie. each subpartition is the same size); however each partition may contain different numbers of subpartitions.
The program moves subpartitions among nodes to achieve a uniform allocation of subpartitions to nodes while minimizing the amount of communication necessary to do so.
1. IN denotes the set of all nodes in the source partitioning map. ¦IN¦ = number of nodes in source partitioning map (>1 ) .
2. DN denotes the set of all nodes to be dropped during redistribution. ¦DN¦ = number of nodes to be dropped (20).
3. AN denotes the set of nodes to be added during redistribution. ¦AN¦ = number of nodes to be added (20).
4. Let ON denote the set of all output nodes, i.e. all nodes in the target partitioning map. Let number of output nodes N
= ¦ON¦ = ¦IN¦ - ¦DN¦ + ¦AN¦ (21).
5. Let TN denote the set of al l nodes, i.e. input + added nodes. Thus, ¦TN¦ = ¦IN¦ + ¦AN¦.
6. Let W(i) denote the number of partitions mapped to node i.
Let Total Weight, W = SUMi=o IINI(W(i)) = 4096. For newly added nodes, their corresponding W(i) = O.
7. Mean Node Weight, MNW = lW/N~.
8. Let Overflow = W nod N. Overflow > O, indicates that the number of nodes does not exactly divide the number of partitions. In this situation, some nodes will have one more partition than others. To minimize data movement in this case, some of the nodes that already have excess partitions (source nodes) are made to keep an extra partition. In the case where Overflow > number of source nodes, a special logic is provided in Step llc.(4) to assign an extra partition to some of the nodes that have < MNW
partitions (target nodes). The Overflow value is used as a counter to keep track of how many such nodes should retain/receive an extra partition.
9. For each node i in the total node set, TN, do:
a. If node is to be dropped, then set diff(i) = W(i) b. Else If node is to be added, then set W(i) = O, diff(i) = -MNW
c. Else Compute diff(i) = W(i) - MNW (note diff(i) can be < 0, = 0, or > 0).
d. If diff(i) > 0 and Overflow > 0, then diff(i) = diff(i) - 1 and Overflow = Overflow -1.
10. Let S denotes the set of nodes where diff(i) > 0 (called, Source nodes) and T denotes the set of nodes where diff(i) < = O (called, ~arget nodes).
11. Repeat the following until diff(i) = O for all source nodes:
a. Let i denote the source node such that W(i) MAXk=1lSl(diff(k)). This is the "heaviest" node over all source nodes.
b. Let j denote the target node such that W(j) MINk=1lTl(diff(k)). This is the "lightest" node over all target nodes.
c. If diff(i) > ABS(diff(j)) then:
1) move ABS (diff(j)) partitions from node i to node j (i.e. W(i) = W(i) - ABS(diff(j)) and W(j) = W(j) + ABS(diff(j))).
2) diff(i) = diff(i) - ABS(diff(j)).
3) diff(j) = 0.
4) If overflow > 0, then a) move 1 partition from node i to j.
b) diff(i) = diff(i) -1.
c) diff(j) = 1.
d) Overflow = Overflow -1.
d. Else /* diff(i) < = ABS(diff(j)) */
1) move diff(i) partitions from node i to node j (i.e.
W(i) = W(i) - diff(i) and W(j) = W(j) + diff(i)).
2) diff(i) = 0.
3) diff(j) = diff(j) + diff(i).
`-- 2159269 The notation used above corresponds to standard mathematical rotation, well known in the art.
The pseudocode when suitably embodied in a computer program for operating in a computer system takes current data placement into account and minimizes the amount of data movement. It arrives at a resultant data distribution while minimizing communication overhead when moving from the initial to the resultant distribution. It does not require the user to explicitly state which data is to be moved. It derives this information based on the input distribution information provided by the data processing system. The method of the invention is applicable in parallel database systems employing "indirect"
partitioning strategy or a scheme similar to a partitioning map which indicates the mapping of table partition (hash arrange) to nodes.
As will be well recognized by those skilled in the art to which this invention pertains, the invention may be practised in computer systems and in computer programs for the operation of computer systems.
Assign current partition from E_List to the L(i) which satisfies MINL(~ oN(number of partitions) ( means belongs to).
Generating a Target Partitioning Map for the UNIFORM Case A program implementing the following pseudocode may be executed in the UNIFORM case (see Figure 5). The data or workload is assumed to be uniformly distributed across all S subpartitions (ie. each subpartition is the same size); however each partition may contain different numbers of subpartitions.
The program moves subpartitions among nodes to achieve a uniform allocation of subpartitions to nodes while minimizing the amount of communication necessary to do so.
1. IN denotes the set of all nodes in the source partitioning map. ¦IN¦ = number of nodes in source partitioning map (>1 ) .
2. DN denotes the set of all nodes to be dropped during redistribution. ¦DN¦ = number of nodes to be dropped (20).
3. AN denotes the set of nodes to be added during redistribution. ¦AN¦ = number of nodes to be added (20).
4. Let ON denote the set of all output nodes, i.e. all nodes in the target partitioning map. Let number of output nodes N
= ¦ON¦ = ¦IN¦ - ¦DN¦ + ¦AN¦ (21).
5. Let TN denote the set of al l nodes, i.e. input + added nodes. Thus, ¦TN¦ = ¦IN¦ + ¦AN¦.
6. Let W(i) denote the number of partitions mapped to node i.
Let Total Weight, W = SUMi=o IINI(W(i)) = 4096. For newly added nodes, their corresponding W(i) = O.
7. Mean Node Weight, MNW = lW/N~.
8. Let Overflow = W nod N. Overflow > O, indicates that the number of nodes does not exactly divide the number of partitions. In this situation, some nodes will have one more partition than others. To minimize data movement in this case, some of the nodes that already have excess partitions (source nodes) are made to keep an extra partition. In the case where Overflow > number of source nodes, a special logic is provided in Step llc.(4) to assign an extra partition to some of the nodes that have < MNW
partitions (target nodes). The Overflow value is used as a counter to keep track of how many such nodes should retain/receive an extra partition.
9. For each node i in the total node set, TN, do:
a. If node is to be dropped, then set diff(i) = W(i) b. Else If node is to be added, then set W(i) = O, diff(i) = -MNW
c. Else Compute diff(i) = W(i) - MNW (note diff(i) can be < 0, = 0, or > 0).
d. If diff(i) > 0 and Overflow > 0, then diff(i) = diff(i) - 1 and Overflow = Overflow -1.
10. Let S denotes the set of nodes where diff(i) > 0 (called, Source nodes) and T denotes the set of nodes where diff(i) < = O (called, ~arget nodes).
11. Repeat the following until diff(i) = O for all source nodes:
a. Let i denote the source node such that W(i) MAXk=1lSl(diff(k)). This is the "heaviest" node over all source nodes.
b. Let j denote the target node such that W(j) MINk=1lTl(diff(k)). This is the "lightest" node over all target nodes.
c. If diff(i) > ABS(diff(j)) then:
1) move ABS (diff(j)) partitions from node i to node j (i.e. W(i) = W(i) - ABS(diff(j)) and W(j) = W(j) + ABS(diff(j))).
2) diff(i) = diff(i) - ABS(diff(j)).
3) diff(j) = 0.
4) If overflow > 0, then a) move 1 partition from node i to j.
b) diff(i) = diff(i) -1.
c) diff(j) = 1.
d) Overflow = Overflow -1.
d. Else /* diff(i) < = ABS(diff(j)) */
1) move diff(i) partitions from node i to node j (i.e.
W(i) = W(i) - diff(i) and W(j) = W(j) + diff(i)).
2) diff(i) = 0.
3) diff(j) = diff(j) + diff(i).
`-- 2159269 The notation used above corresponds to standard mathematical rotation, well known in the art.
The pseudocode when suitably embodied in a computer program for operating in a computer system takes current data placement into account and minimizes the amount of data movement. It arrives at a resultant data distribution while minimizing communication overhead when moving from the initial to the resultant distribution. It does not require the user to explicitly state which data is to be moved. It derives this information based on the input distribution information provided by the data processing system. The method of the invention is applicable in parallel database systems employing "indirect"
partitioning strategy or a scheme similar to a partitioning map which indicates the mapping of table partition (hash arrange) to nodes.
As will be well recognized by those skilled in the art to which this invention pertains, the invention may be practised in computer systems and in computer programs for the operation of computer systems.
Claims (46)
1. A method of distributing data of a table partitioned across a parallel database system having a number of nodes comprising:
determining resource loading at nodes of the system associated with said table;
comparing resource loading among said nodes;
if said resource loading is distributed in a significantly unbalanced manner;
(a) selecting subpartitions contained within partitions of said table at said nodes having heavy loading for movement to nodes having lower resource loading to obtain a more uniform distribution;
(b) selecting subpartitions for retention at said nodes having heavy loading;
moving said subpartitions selected for movement to said nodes having lower resource loading to balance the resource loading among said nodes containing partitions of said table.
determining resource loading at nodes of the system associated with said table;
comparing resource loading among said nodes;
if said resource loading is distributed in a significantly unbalanced manner;
(a) selecting subpartitions contained within partitions of said table at said nodes having heavy loading for movement to nodes having lower resource loading to obtain a more uniform distribution;
(b) selecting subpartitions for retention at said nodes having heavy loading;
moving said subpartitions selected for movement to said nodes having lower resource loading to balance the resource loading among said nodes containing partitions of said table.
2. The method of claim 1 wherein subpartitions are selected for movement from a node based on weighting in descending weight order.
3. The method of claim 1 wherein the selection of subpartitions for retention at a node includes one or more of subpartitions with the largest weighting contained in said node.
4. The method of claim 1 wherein the subpartition with the largest weighting contained in a node is retained.
5. The method of claim 3 in which a method of best fit is used to determine to which nodes subpartitions selected for movement are distributed to obtain substantially uniform loading distribution.
6. The method of claim 5 wherein said resource loading comprises data volume.
7. The method of claim 5 wherein said loading comprises workload (activity).
8. The method of claims 3 or 4 wherein said manner of distribution of subpartitions is selected to minimize data communication overhead among said nodes.
9. The method of claim 1 wherein said resource loading comprises data volume associated with said table.
10. A method of distributing data of a table partitioned across a parallel database system having a number of nodes comprising:
determining the data volume for nodes of said system associated with said table;
comparing said data volume stored among said nodes;
identifying groups of data in nodes having higher data volumes which may be distributed to nodes having lower data volumes to obtain a more uniform data distribution with minimum required data movement activity;
moving said identified data to said nodes having lower data volumes to balance the data volumes of said nodes across which said data is partitioned.
determining the data volume for nodes of said system associated with said table;
comparing said data volume stored among said nodes;
identifying groups of data in nodes having higher data volumes which may be distributed to nodes having lower data volumes to obtain a more uniform data distribution with minimum required data movement activity;
moving said identified data to said nodes having lower data volumes to balance the data volumes of said nodes across which said data is partitioned.
11. The method of claim 10 in which the group of data with the smallest data volume identified for redistribution from one node to another comprises a subpartition of said data in said node.
12. The method of claim 11 in which subpartitions of data are selected for movement in a descending weight order with subpartitions within each node containing the largest amount of data being selected for retention at said node.
13. The method in claim 12 in which a method of best fit is used to select and distribute subpartitions among the nodes to achieve uniform data distribution.
14. The method of claims 1, 10 or 12 in which data is redistributed among said nodes in a manner which minimizes the number of communication links required to link data of a table across said database system.
15. The method of claim 10 comprising obtaining file size information for table partitions of said nodes by reading file attributes for said files and obtaining database statistics on data group volume and volume of data group usage;
generating a distribution listing file depicting current data distribution;
selecting one or more data groups for redistribution among selected nodes to which data is to be redistributed;
generating a partitioning map for redistribution of said groups of data in which a best fit method has been applied to select data groups and a redistribution plan for redistribution according to descending weight of said data groups in which data will be substantially uniformly distributed among said nodes;
redistributing said data groups among said selected nodes in accordance with said partition map.
generating a distribution listing file depicting current data distribution;
selecting one or more data groups for redistribution among selected nodes to which data is to be redistributed;
generating a partitioning map for redistribution of said groups of data in which a best fit method has been applied to select data groups and a redistribution plan for redistribution according to descending weight of said data groups in which data will be substantially uniformly distributed among said nodes;
redistributing said data groups among said selected nodes in accordance with said partition map.
16. The method of claim 1 wherein transaction activity information is obtained for all table partitions in said nodes by reading transaction logs of said database system;
generating a current workload distribution file;
selecting data groups from nodes having excessive workload distribution for redistribution among selected nodes to which data is to be distributed;
generating a partitioning map describing a plan of redistribution of said groups to achieve uniformity of workload while minimizing the amount of data transferred between said nodes to achieve said redistribution;
redistributing said selected data groups.
generating a current workload distribution file;
selecting data groups from nodes having excessive workload distribution for redistribution among selected nodes to which data is to be distributed;
generating a partitioning map describing a plan of redistribution of said groups to achieve uniformity of workload while minimizing the amount of data transferred between said nodes to achieve said redistribution;
redistributing said selected data groups.
17. A system of distributing data of a table partitioned across a parallel database system having a number of nodes comprising:
means for determining resource loading at nodes of the system associated with said table;
means for comparing resource loading among said nodes;
(a) means for selecting subpartitions contained within partitions of said table at said nodes having heavy loading for movement to nodes having lower resource loading;
(b) means for selecting subpartitions for retention at said nodes having heavy loading;
means for moving said subpartitions selected for movement to said nodes having lower resource loading to balance the resource loading among said nodes containing partitions of said table.
means for determining resource loading at nodes of the system associated with said table;
means for comparing resource loading among said nodes;
(a) means for selecting subpartitions contained within partitions of said table at said nodes having heavy loading for movement to nodes having lower resource loading;
(b) means for selecting subpartitions for retention at said nodes having heavy loading;
means for moving said subpartitions selected for movement to said nodes having lower resource loading to balance the resource loading among said nodes containing partitions of said table.
18. The system of claim 17 wherein said means for selecting subpartitions for movement from a node makes its selection based on weighting in descending weight order.
19. The system of claim 17 wherein the means selection of subpartitions for retention at a node retains one or more of the subpartitions with the largest weighting contained in said node.
20. The system of claim 17 wherein said means for retention retains the largest subpartition with the largest weighting contained in a node.
21. The system of claim 19 in which means of best fit is used to determine to which nodes subpartitions selected for movement are distributed to obtain substantially uniform loading distribution.
22. The system of claim 21 wherein said resource loading comprises data volume.
23. The system of claim 21 wherein said loading comprises workload (activity).
24. The system of claims 19 or 20 wherein said manner of distribution of subpartitions is selected to minimize data communication overhead among said nodes.
25. The system of claim 17 wherein said resource loading comprises data volume associated with said table.
26. A system of distributing data of a table partitioned across a parallel database system having a number of nodes comprising:
means for determining the data volume for nodes of said system associated with said table;
means for comparing said data volume stored among said nodes;
means for identifying groups of data in nodes having higher data volumes which may be distributed to nodes having lower data volumes to obtain a more uniform data distribution with minimum required data movement activity;
means for moving said identified data to said nodes having lower data volumes to balance the data volumes of said nodes across which said data is partitioned.
means for determining the data volume for nodes of said system associated with said table;
means for comparing said data volume stored among said nodes;
means for identifying groups of data in nodes having higher data volumes which may be distributed to nodes having lower data volumes to obtain a more uniform data distribution with minimum required data movement activity;
means for moving said identified data to said nodes having lower data volumes to balance the data volumes of said nodes across which said data is partitioned.
27. The system of claim 26 in which the group of data with the smallest volume identified for redistribution from one node to another comprises a subpartition of said data in said node.
28. The system of claim 27 in which subpartitions of data are selected for movement in a descending weight order with subpartitions within each node containing the largest amount of data being selected for retention at said node.
29. The system in claim 28 in which a method of best fit is used to select and distribute subpartitions among the nodes to achieve uniform data distribution.
30. The system of claim 26 comprising means for obtaining file size information for table partitions of said nodes by reading file attributes for said files and obtaining database statistics on data group volume and volume of data group usage;
means for generating a distribution listing file depicting current data distribution;
means for selecting one or more of data groups for redistribution among selected nodes to which data is to be redistributed;
means for generating a partitioning map for redistribution of said groups of data in which a best fit method has been applied to select data groups and a redistribution plan for redistribution according to descending weight of said data groups in which data will be uniformly distributed among said nodes;
means for redistributing said data groups among said selected nodes in accordance with said partition map.
means for generating a distribution listing file depicting current data distribution;
means for selecting one or more of data groups for redistribution among selected nodes to which data is to be redistributed;
means for generating a partitioning map for redistribution of said groups of data in which a best fit method has been applied to select data groups and a redistribution plan for redistribution according to descending weight of said data groups in which data will be uniformly distributed among said nodes;
means for redistributing said data groups among said selected nodes in accordance with said partition map.
31. The system of claim 17 including means for obtaining transaction activity information for all table partitions in said nodes by reading transaction logs of said database system;
means for generating a current workload distribution file;
means for selecting data groups from nodes having excessive workload distribution for redistribution among selected nodes to which data is to be distributed;
means for generating a partitioning map describing a plan of redistribution of said groups to achieve uniformity of workload while minimizing the amount of data transferred between said nodes to achieve said redistribution;
means for redistributing said selected data groups.
means for generating a current workload distribution file;
means for selecting data groups from nodes having excessive workload distribution for redistribution among selected nodes to which data is to be distributed;
means for generating a partitioning map describing a plan of redistribution of said groups to achieve uniformity of workload while minimizing the amount of data transferred between said nodes to achieve said redistribution;
means for redistributing said selected data groups.
32. A computer readable product having computer readable program code means embodied in said product for use on a computer system for causing said computer system to distribute data of a table partitioned across a parallel database system having a number of nodes comprising:
a recording medium;
means recorded on said medium for causing said computer system to perform the steps of:
determining resource loading at nodes of the system associated with said table;
comparing resource loading among said nodes;
if said resource loading is distributed in a significantly unbalanced manner;
(a) selecting subpartitions contained within partitions of said table at said nodes having heavy loading for movement to nodes having lower resource loading;
(b) selecting subpartitions for retention at said nodes having heavy loading;
moving said subpartitions selected for movement to said nodes having lower resource loading to balance the resource loading among said nodes containing partitions of said table.
a recording medium;
means recorded on said medium for causing said computer system to perform the steps of:
determining resource loading at nodes of the system associated with said table;
comparing resource loading among said nodes;
if said resource loading is distributed in a significantly unbalanced manner;
(a) selecting subpartitions contained within partitions of said table at said nodes having heavy loading for movement to nodes having lower resource loading;
(b) selecting subpartitions for retention at said nodes having heavy loading;
moving said subpartitions selected for movement to said nodes having lower resource loading to balance the resource loading among said nodes containing partitions of said table.
33. The product of claim 32 wherein subpartitions are selected for movement from a node base on weighting in descending weight order.
34. The product of claim 32 wherein the selection of subpartitions for retention at a node includes one or more of the largest subpartitions contained in said node.
35. The product of claim 32 wherein the largest subpartition contained in a node is retained.
36. The product of claim 34 in which a method of best fit is used to determine to which nodes subpartitions selected for movement are distributed to obtain substantially uniform loading distribution.
37. The product of claim 36 wherein said resource loading comprises data volume.
38. The product of claim 36 wherein said loading comprises workload activity.
39. A computer readable product having computer readable program code means embodied to said product for use on a computer system causing said computer system to distribute data of a table partitioned across a parallel database system having a number of nodes comprising:
program code means recorded on said medium for causing said computer system to perform the steps of:
determining the data volume for nodes of said system associated with said table;
comparing said data volume stored among said nodes;
identifying groups of data in nodes having higher data volumes which may be distributed to nodes having lower data volumes to obtain a more uniform data distribution with minimum required data movement activity;
moving said identified data to said nodes having lower data volumes to balance the data volumes of said nodes across which said data is partitioned.
program code means recorded on said medium for causing said computer system to perform the steps of:
determining the data volume for nodes of said system associated with said table;
comparing said data volume stored among said nodes;
identifying groups of data in nodes having higher data volumes which may be distributed to nodes having lower data volumes to obtain a more uniform data distribution with minimum required data movement activity;
moving said identified data to said nodes having lower data volumes to balance the data volumes of said nodes across which said data is partitioned.
40. The product of claim 39 in which the group of data having the smallest data volume identified for redistribution from one node to another comprises a subpartition of said data in said node.
41. The product of claim 40 in which subpartitions of data are selected for movement in a descending weight order with subpartitions within each node containing the largest amount of data being selected for retention at said node.
42. The product in claim 41 in which a method of best fit is used to select and distribute subpartitions among the nodes to achieve uniform data distribution.
43. The product in accordance with claim 40 wherein said program code means includes program code for causing said computer system to perform the steps of:
obtaining file size information for table partitions of said nodes by reading file attributes for said files and obtaining database statistics on data group volume and volume of data group usage;
generating a distribution listing file depicting current data distribution;
selecting one or more data groups for redistribution among selected nodes to which data is to be redistributed;
generating a partitioning map for redistribution of said groups of data in which a best fit method has been applied to select data groups and a redistribution plan for redistribution according to descending weight of said data groups;
a redistribution plan in which data will be uniformly distributed among said nodes;
redistributing said data groups among said selected nodes in accordance with said partition map.
obtaining file size information for table partitions of said nodes by reading file attributes for said files and obtaining database statistics on data group volume and volume of data group usage;
generating a distribution listing file depicting current data distribution;
selecting one or more data groups for redistribution among selected nodes to which data is to be redistributed;
generating a partitioning map for redistribution of said groups of data in which a best fit method has been applied to select data groups and a redistribution plan for redistribution according to descending weight of said data groups;
a redistribution plan in which data will be uniformly distributed among said nodes;
redistributing said data groups among said selected nodes in accordance with said partition map.
44. The product of claim 32 wherein transaction activity information is obtained for all table partitions in said nodes by reading transaction logs of said database system;
generating a current workload distribution file;
selecting data groups from nodes having excessive workload distribution for redistribution among selected nodes to which data is to be distributed;
generating a partitioning map describing a plan of redistribution of said groups to achieve uniformity of workload while minimizing the amount of data transferred between said nodes to achieve said redistribution;
redistributing said selected data groups.
generating a current workload distribution file;
selecting data groups from nodes having excessive workload distribution for redistribution among selected nodes to which data is to be distributed;
generating a partitioning map describing a plan of redistribution of said groups to achieve uniformity of workload while minimizing the amount of data transferred between said nodes to achieve said redistribution;
redistributing said selected data groups.
45. In a parallel database computer system having a number of nodes a method of distributing data of a table partitioned across said database system comprising:
determining resource loading at nodes of the system associated with said table;
comparing resource loading among said nodes;
if said resource loading is distributed in a significantly unbalanced manner;
(a) selecting subpartitions contained within partitions of said table at said nodes having heavy loading for movement to nodes having lower resource loading to obtain a more uniform distribution;
(b) selecting subpartitions for retention at said nodes having heavy loading;
moving said subpartitions selected for movement to said nodes having lower resource loading to balance the resource loading among said nodes containing partitions of said table.
determining resource loading at nodes of the system associated with said table;
comparing resource loading among said nodes;
if said resource loading is distributed in a significantly unbalanced manner;
(a) selecting subpartitions contained within partitions of said table at said nodes having heavy loading for movement to nodes having lower resource loading to obtain a more uniform distribution;
(b) selecting subpartitions for retention at said nodes having heavy loading;
moving said subpartitions selected for movement to said nodes having lower resource loading to balance the resource loading among said nodes containing partitions of said table.
46. A parallel database system having a number of nodes including a system of distributing data of a table partitioned across comprising:
means for determining resource loading at nodes of the system associated with said table;
means for comparing resource loading among said nodes;
(a) means for selecting subpartitions contained within partitions of said table at said nodes having heavy loading for movement to nodes having lower resource loading;
(b) means for selecting subpartitions for retention at said nodes having heavy loading;
means for moving said subpartitions selected for movement to said nodes having lower resource loading to balance the resource loading among said nodes containing partitions of said table.
means for determining resource loading at nodes of the system associated with said table;
means for comparing resource loading among said nodes;
(a) means for selecting subpartitions contained within partitions of said table at said nodes having heavy loading for movement to nodes having lower resource loading;
(b) means for selecting subpartitions for retention at said nodes having heavy loading;
means for moving said subpartitions selected for movement to said nodes having lower resource loading to balance the resource loading among said nodes containing partitions of said table.
Priority Applications (2)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CA002159269A CA2159269C (en) | 1995-09-27 | 1995-09-27 | Method and apparatus for achieving uniform data distribution in a parallel database system |
| US08/665,031 US5970495A (en) | 1995-09-27 | 1996-06-10 | Method and apparatus for achieving uniform data distribution in a parallel database system |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CA002159269A CA2159269C (en) | 1995-09-27 | 1995-09-27 | Method and apparatus for achieving uniform data distribution in a parallel database system |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CA2159269A1 CA2159269A1 (en) | 1997-03-28 |
| CA2159269C true CA2159269C (en) | 2000-11-21 |
Family
ID=4156665
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CA002159269A Expired - Fee Related CA2159269C (en) | 1995-09-27 | 1995-09-27 | Method and apparatus for achieving uniform data distribution in a parallel database system |
Country Status (2)
| Country | Link |
|---|---|
| US (1) | US5970495A (en) |
| CA (1) | CA2159269C (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US11157496B2 (en) | 2018-06-01 | 2021-10-26 | International Business Machines Corporation | Predictive data distribution for parallel databases to optimize storage and query performance |
| US11163764B2 (en) | 2018-06-01 | 2021-11-02 | International Business Machines Corporation | Predictive data distribution for parallel databases to optimize storage and query performance |
Families Citing this family (96)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CA2159269C (en) * | 1995-09-27 | 2000-11-21 | Chaitanya K. Baru | Method and apparatus for achieving uniform data distribution in a parallel database system |
| JP3510042B2 (en) * | 1996-04-26 | 2004-03-22 | 株式会社日立製作所 | Database management method and system |
| US6496823B2 (en) * | 1997-11-07 | 2002-12-17 | International Business Machines Corporation | Apportioning a work unit to execute in parallel in a heterogeneous environment |
| US6125370A (en) * | 1998-04-01 | 2000-09-26 | International Business Machines Corporation | Repartitioning data |
| US6269375B1 (en) | 1998-04-01 | 2001-07-31 | International Business Machines Corporation | Rebalancing partitioned data |
| US6363396B1 (en) * | 1998-12-21 | 2002-03-26 | Oracle Corporation | Object hashing with incremental changes |
| US6691166B1 (en) * | 1999-01-07 | 2004-02-10 | Sun Microsystems, Inc. | System and method for transferring partitioned data sets over multiple threads |
| US6542854B2 (en) | 1999-04-30 | 2003-04-01 | Oracle Corporation | Method and mechanism for profiling a system |
| US6609131B1 (en) * | 1999-09-27 | 2003-08-19 | Oracle International Corporation | Parallel partition-wise joins |
| US6665684B2 (en) * | 1999-09-27 | 2003-12-16 | Oracle International Corporation | Partition pruning with composite partitioning |
| US6549931B1 (en) | 1999-09-27 | 2003-04-15 | Oracle Corporation | Distributing workload between resources used to access data |
| US6470331B1 (en) * | 1999-12-04 | 2002-10-22 | Ncr Corporation | Very large table reduction in parallel processing database systems |
| US6529906B1 (en) | 2000-01-28 | 2003-03-04 | Oracle Corporation | Techniques for DLM optimization with re-mastering events |
| US6751616B1 (en) | 2000-01-28 | 2004-06-15 | Oracle International Corp. | Techniques for DLM optimization with re-mapping responsibility for lock management |
| US7246120B2 (en) | 2000-01-28 | 2007-07-17 | Oracle International Corporation | Techniques for achieving higher availability of resources during reconfiguration of a cluster |
| US6920454B1 (en) | 2000-01-28 | 2005-07-19 | Oracle International Corporation | Techniques for DLM optimization with transferring lock information |
| US6523036B1 (en) * | 2000-08-01 | 2003-02-18 | Dantz Development Corporation | Internet database system |
| CA2319918A1 (en) * | 2000-09-18 | 2002-03-18 | Linmor Technologies Inc. | High performance relational database management system |
| US6944607B1 (en) * | 2000-10-04 | 2005-09-13 | Hewlett-Packard Development Compnay, L.P. | Aggregated clustering method and system |
| IL141599A0 (en) * | 2001-02-22 | 2002-03-10 | Infocyclone Inc | Information retrieval system |
| JP4232357B2 (en) * | 2001-06-14 | 2009-03-04 | 株式会社日立製作所 | Computer system |
| US7024401B2 (en) * | 2001-07-02 | 2006-04-04 | International Business Machines Corporation | Partition boundary determination using random sampling on very large databases |
| US7028054B2 (en) * | 2001-07-02 | 2006-04-11 | International Business Machines Corporation | Random sampling as a built-in function for database administration and replication |
| US6801903B2 (en) * | 2001-10-12 | 2004-10-05 | Ncr Corporation | Collecting statistics in a database system |
| US20030158842A1 (en) * | 2002-02-21 | 2003-08-21 | Eliezer Levy | Adaptive acceleration of retrieval queries |
| US7346690B1 (en) | 2002-05-07 | 2008-03-18 | Oracle International Corporation | Deferred piggybacked messaging mechanism for session reuse |
| US20040003022A1 (en) * | 2002-06-27 | 2004-01-01 | International Business Machines Corporation | Method and system for using modulo arithmetic to distribute processing over multiple processors |
| US20040006622A1 (en) * | 2002-07-03 | 2004-01-08 | Burkes Don L. | Optimized process for balancing load for data mirroring |
| US7020661B1 (en) * | 2002-07-10 | 2006-03-28 | Oracle International Corporation | Techniques for pruning a data object during operations that join multiple data objects |
| US7778996B2 (en) * | 2002-09-25 | 2010-08-17 | Teradata Us, Inc. | Sampling statistics in a database system |
| US7797450B2 (en) * | 2002-10-04 | 2010-09-14 | Oracle International Corporation | Techniques for managing interaction of web services and applications |
| US7293024B2 (en) * | 2002-11-14 | 2007-11-06 | Seisint, Inc. | Method for sorting and distributing data among a plurality of nodes |
| US7657540B1 (en) | 2003-02-04 | 2010-02-02 | Seisint, Inc. | Method and system for linking and delinking data records |
| US7447786B2 (en) | 2003-05-09 | 2008-11-04 | Oracle International Corporation | Efficient locking of shared data that is accessed for reads in a cluster database |
| US20040260745A1 (en) * | 2003-06-18 | 2004-12-23 | Gage Christopher A. S. | Load balancer performance using affinity modification |
| JP4330941B2 (en) | 2003-06-30 | 2009-09-16 | 株式会社日立製作所 | Database divided storage management apparatus, method and program |
| US7379952B2 (en) * | 2004-01-30 | 2008-05-27 | Oracle International Corporation | Techniques for multiple window resource remastering among nodes of a cluster |
| EP1626339B1 (en) * | 2004-08-13 | 2016-02-24 | Sap Se | Data processing system and method for assigning objects to processing units |
| US20060200469A1 (en) * | 2005-03-02 | 2006-09-07 | Lakshminarayanan Chidambaran | Global session identifiers in a multi-node system |
| US7209990B2 (en) * | 2005-04-05 | 2007-04-24 | Oracle International Corporation | Maintain fairness of resource allocation in a multi-node environment |
| US7366716B2 (en) * | 2005-05-06 | 2008-04-29 | Microsoft Corporation | Integrating vertical partitioning into physical database design |
| US8037169B2 (en) * | 2005-05-18 | 2011-10-11 | Oracle International Corporation | Determining affinity in a cluster |
| US7493400B2 (en) | 2005-05-18 | 2009-02-17 | Oracle International Corporation | Creating and dissolving affinity relationships in a cluster |
| US7539661B2 (en) * | 2005-06-02 | 2009-05-26 | Delphi Technologies, Inc. | Table look-up method with adaptive hashing |
| US8326990B1 (en) * | 2005-07-15 | 2012-12-04 | Symantec Operating Corporation | Automated optimal workload balancing during failover in share-nothing database systems |
| US7814065B2 (en) | 2005-08-16 | 2010-10-12 | Oracle International Corporation | Affinity-based recovery/failover in a cluster environment |
| US8027684B2 (en) * | 2005-08-22 | 2011-09-27 | Infosys Technologies, Ltd. | System for performing a task in a communication network and methods thereof |
| US20070162506A1 (en) * | 2006-01-12 | 2007-07-12 | International Business Machines Corporation | Method and system for performing a redistribute transparently in a multi-node system |
| US8005836B2 (en) * | 2006-01-13 | 2011-08-23 | Teradata Us, Inc. | Method and system for performing logical partial declustering |
| JP2007249468A (en) * | 2006-03-15 | 2007-09-27 | Hitachi Ltd | Cpu allocation method, cpu allocation program, cpu allocation device and database management system |
| US7921416B2 (en) * | 2006-10-20 | 2011-04-05 | Yahoo! Inc. | Formal language and translator for parallel processing of data |
| US20080168077A1 (en) * | 2007-01-10 | 2008-07-10 | Eric Lawrence Barsness | Pre-loading of an in memory database |
| US7698529B2 (en) * | 2007-01-10 | 2010-04-13 | International Business Machines Corporation | Method for trading resources between partitions of a data processing system |
| US7769732B2 (en) * | 2007-08-27 | 2010-08-03 | International Business Machines Corporation | Apparatus and method for streamlining index updates in a shared-nothing architecture |
| US8892558B2 (en) | 2007-09-26 | 2014-11-18 | International Business Machines Corporation | Inserting data into an in-memory distributed nodal database |
| US8027996B2 (en) * | 2007-11-29 | 2011-09-27 | International Business Machines Corporation | Commitment control for less than an entire record in an in-memory database in a parallel computer system |
| US8209334B1 (en) * | 2007-12-28 | 2012-06-26 | Don Doerner | Method to direct data to a specific one of several repositories |
| US8266168B2 (en) | 2008-04-24 | 2012-09-11 | Lexisnexis Risk & Information Analytics Group Inc. | Database systems and methods for linking records and entity representations with sufficiently high confidence |
| US20090307329A1 (en) * | 2008-06-06 | 2009-12-10 | Chris Olston | Adaptive file placement in a distributed file system |
| US8285725B2 (en) | 2008-07-02 | 2012-10-09 | Lexisnexis Risk & Information Analytics Group Inc. | System and method for identifying entity representations based on a search query using field match templates |
| US7774311B2 (en) * | 2008-07-17 | 2010-08-10 | International Business Machines Corporation | Method and apparatus of distributing data in partioned databases operating on a shared-nothing architecture |
| US9996572B2 (en) * | 2008-10-24 | 2018-06-12 | Microsoft Technology Licensing, Llc | Partition management in a partitioned, scalable, and available structured storage |
| US9244793B1 (en) | 2008-11-04 | 2016-01-26 | Teradata Us, Inc. | Using target database system statistics in emulation |
| JP5459222B2 (en) * | 2008-12-24 | 2014-04-02 | 富士通株式会社 | Configuration management system, proxy system, and configuration management method |
| US8078825B2 (en) * | 2009-03-11 | 2011-12-13 | Oracle America, Inc. | Composite hash and list partitioning of database tables |
| US9251212B2 (en) * | 2009-03-27 | 2016-02-02 | Business Objects Software Ltd. | Profiling in a massive parallel processing environment |
| US9411859B2 (en) | 2009-12-14 | 2016-08-09 | Lexisnexis Risk Solutions Fl Inc | External linking based on hierarchical level weightings |
| US9171044B2 (en) * | 2010-02-16 | 2015-10-27 | Oracle International Corporation | Method and system for parallelizing database requests |
| US8849749B2 (en) * | 2010-05-14 | 2014-09-30 | Oracle International Corporation | Load balancing in parallel database systems using multi-reordering |
| US8768973B2 (en) * | 2010-05-26 | 2014-07-01 | Pivotal Software, Inc. | Apparatus and method for expanding a shared-nothing system |
| US9189505B2 (en) | 2010-08-09 | 2015-11-17 | Lexisnexis Risk Data Management, Inc. | System of and method for entity representation splitting without the need for human interaction |
| US8583687B1 (en) * | 2012-05-15 | 2013-11-12 | Algebraix Data Corporation | Systems and methods for indirect algebraic partitioning |
| US8903876B2 (en) | 2012-08-15 | 2014-12-02 | Facebook, Inc. | File storage system based on coordinated exhaustible and non-exhaustible storage |
| US9229657B1 (en) | 2012-11-01 | 2016-01-05 | Quantcast Corporation | Redistributing data in a distributed storage system based on attributes of the data |
| US9792295B1 (en) | 2013-02-06 | 2017-10-17 | Quantcast Corporation | Distributing data of multiple logically independent file systems in distributed storage systems including physically partitioned disks |
| US9811529B1 (en) * | 2013-02-06 | 2017-11-07 | Quantcast Corporation | Automatically redistributing data of multiple file systems in a distributed storage system |
| EP2975523A4 (en) * | 2013-03-12 | 2017-02-08 | Toshiba Solutions Corporation | Database system, program, and data processing method |
| CN103336792B (en) * | 2013-06-07 | 2016-11-23 | 华为技术有限公司 | Data partition method and device |
| WO2015025384A1 (en) * | 2013-08-21 | 2015-02-26 | 株式会社東芝 | Database system, program, and data processing method |
| JP6122126B2 (en) * | 2013-08-27 | 2017-04-26 | 株式会社東芝 | Database system, program, and data processing method |
| US9934323B2 (en) * | 2013-10-01 | 2018-04-03 | Facebook, Inc. | Systems and methods for dynamic mapping for locality and balance |
| US9372907B2 (en) * | 2013-11-26 | 2016-06-21 | Sap Se | Table placement in distributed databases |
| US9830346B2 (en) | 2013-11-26 | 2017-11-28 | Sap Se | Table redistribution in distributed databases |
| JP2015170101A (en) | 2014-03-06 | 2015-09-28 | 富士通株式会社 | biometric authentication device, method and program |
| CN105205052B (en) * | 2014-05-30 | 2019-01-25 | 华为技术有限公司 | A kind of data digging method and device |
| US9860316B2 (en) | 2014-09-19 | 2018-01-02 | Facebook, Inc. | Routing network traffic based on social information |
| US10558637B2 (en) * | 2015-12-17 | 2020-02-11 | Sap Se | Modularized data distribution plan generation |
| CN105959419A (en) * | 2016-07-15 | 2016-09-21 | 浪潮(北京)电子信息产业有限公司 | Establishment method and system for distributed storage structure based on consensus tree |
| KR101951999B1 (en) | 2016-08-31 | 2019-05-10 | 재단법인대구경북과학기술원 | Storage system and storing method of relational database for high query performance with low data redundancy and processing method of query based on storing method of relational database |
| US10534765B2 (en) * | 2017-04-07 | 2020-01-14 | Micro Focus Llc | Assigning segments of a shared database storage to nodes |
| US20180322397A1 (en) * | 2017-05-08 | 2018-11-08 | International Business Machines Corporation | Detecting case families with anomalous frequencies in rule-based decision policies |
| US10459810B2 (en) | 2017-07-06 | 2019-10-29 | Oracle International Corporation | Technique for higher availability in a multi-node system using replicated lock information to determine a set of data blocks for recovery |
| US10877801B2 (en) * | 2018-09-28 | 2020-12-29 | Atlassian Pty Ltd. | Systems and methods for scheduling tasks |
| CN110196882B (en) * | 2019-05-27 | 2021-06-08 | 上海达梦数据库有限公司 | Method and device for determining data redistribution mode, server and storage medium |
| US11526500B2 (en) * | 2019-12-12 | 2022-12-13 | Sap Se | System and method for initiating bulk inserts in a distributed database |
| CN111274028B (en) * | 2020-01-15 | 2023-09-05 | 新方正控股发展有限责任公司 | Partitioning method, partitioning device and readable storage medium based on database middleware |
Family Cites Families (13)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US4403286A (en) * | 1981-03-06 | 1983-09-06 | International Business Machines Corporation | Balancing data-processing work loads |
| US4630264A (en) * | 1984-09-21 | 1986-12-16 | Wah Benjamin W | Efficient contention-resolution protocol for local multiaccess networks |
| US5117350A (en) * | 1988-12-15 | 1992-05-26 | Flashpoint Computer Corporation | Memory address mechanism in a distributed memory architecture |
| US5625836A (en) * | 1990-11-13 | 1997-04-29 | International Business Machines Corporation | SIMD/MIMD processing memory element (PME) |
| US5325525A (en) * | 1991-04-04 | 1994-06-28 | Hewlett-Packard Company | Method of automatically controlling the allocation of resources of a parallel processor computer system by calculating a minimum execution time of a task and scheduling subtasks against resources to execute the task in the minimum time |
| US5555404A (en) * | 1992-03-17 | 1996-09-10 | Telenor As | Continuously available database server having multiple groups of nodes with minimum intersecting sets of database fragment replicas |
| US5390283A (en) * | 1992-10-23 | 1995-02-14 | North American Philips Corporation | Method for optimizing the configuration of a pick and place machine |
| US5625811A (en) * | 1994-10-31 | 1997-04-29 | International Business Machines Corporation | Method and system for database load balancing |
| US5687372A (en) * | 1995-06-07 | 1997-11-11 | Tandem Computers, Inc. | Customer information control system and method in a loosely coupled parallel processing environment |
| CA2159269C (en) * | 1995-09-27 | 2000-11-21 | Chaitanya K. Baru | Method and apparatus for achieving uniform data distribution in a parallel database system |
| US5758345A (en) * | 1995-11-08 | 1998-05-26 | International Business Machines Corporation | Program and method for establishing a physical database layout on a distributed processor system |
| US5764905A (en) * | 1996-09-09 | 1998-06-09 | Ncr Corporation | Method, system and computer program product for synchronizing the flushing of parallel nodes database segments through shared disk tokens |
| US5799312A (en) * | 1996-11-26 | 1998-08-25 | International Business Machines Corporation | Three-dimensional affine-invariant hashing defined over any three-dimensional convex domain and producing uniformly-distributed hash keys |
-
1995
- 1995-09-27 CA CA002159269A patent/CA2159269C/en not_active Expired - Fee Related
-
1996
- 1996-06-10 US US08/665,031 patent/US5970495A/en not_active Expired - Lifetime
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US11157496B2 (en) | 2018-06-01 | 2021-10-26 | International Business Machines Corporation | Predictive data distribution for parallel databases to optimize storage and query performance |
| US11163764B2 (en) | 2018-06-01 | 2021-11-02 | International Business Machines Corporation | Predictive data distribution for parallel databases to optimize storage and query performance |
Also Published As
| Publication number | Publication date |
|---|---|
| CA2159269A1 (en) | 1997-03-28 |
| US5970495A (en) | 1999-10-19 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CA2159269C (en) | Method and apparatus for achieving uniform data distribution in a parallel database system | |
| Hua et al. | An Adaptive Data Placement Scheme for Parallel Database Computer Systems. | |
| CA2150745C (en) | Method and apparatus for implementing partial declustering in a parallel database system | |
| US10831758B2 (en) | Partitioning and repartitioning for data parallel operations | |
| Shachnai et al. | On two class-constrained versions of the multiple knapsack problem | |
| US5960431A (en) | Method and apparatus for adding data storage bins to a stored computer database while minimizing movement of data and balancing data distribution | |
| US6101495A (en) | Method of executing partition operations in a parallel database system | |
| US6438562B1 (en) | Parallel index maintenance | |
| EP2212806B1 (en) | Allocation of resources for concurrent query execution via adaptive segmentation | |
| US7558802B2 (en) | Information retrieving system | |
| US7599910B1 (en) | Method and system of database divisional management for parallel database system | |
| US6772163B1 (en) | Reduced memory row hash match scan join for a partitioned database system | |
| Gupta et al. | A two stage heuristic algorithm for solving the server consolidation problem with item-item and bin-item incompatibility constraints | |
| EP1564638B1 (en) | A method of reassigning objects to processing units | |
| US6549931B1 (en) | Distributing workload between resources used to access data | |
| WO2017118335A1 (en) | Mapping method and device | |
| CN110941602A (en) | Database configuration method and device, electronic equipment and storage medium | |
| Halfpap et al. | Workload-driven fragment allocation for partially replicated databases using linear programming | |
| EP1524599B1 (en) | A method of reassigning objects to processing units | |
| CN108776698B (en) | Spark-based anti-deflection data fragmentation method | |
| EP0547992A2 (en) | Method and system for enhanced efficiency of data recovery in balanced tree memory structures | |
| Shachnai et al. | Polynomial time approximation schemes for class-constrained packing problems | |
| Ibrahim et al. | Improvement of data throughput in data-intensive cloud computing applications | |
| US20210216573A1 (en) | Algorithm to apply a predicate to data sets | |
| JP2001022621A (en) | Multidimensional database management system |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| EEER | Examination request | ||
| MKLA | Lapsed |