JP3367140B2 - Database management method - Google Patents

Description

Description: BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a database management method using a plurality of processors and a plurality of storage devices. [0002] A database management system is a computer system that records and holds data. [0003] In particular, among database management systems,
In a relational database management system, a database is composed of a table (or relation) that is viewed from a user in a two-dimensional table format, and this table is composed of a plurality of rows (records or taples). A row is composed of a plurality of columns (attributes or fields), and each column defines a data type and data length indicating characteristics of the column. When using such a relational database system, a user or an application program processes data by issuing a request or command to the database (called an inquiry).
(Select, update, insert or delete). In a data query and processing language of a relational database management system such as SQL, queries are atypical. That is, the user or the application program only needs to specify what is necessary, and does not need to specify the processing procedure for executing it. Further, the user or the application program does not need to be aware of the location where the table accessed by the inquiry is stored. [0005] However, while the user or the application program is unaware of the processing procedure, the burden on the relational database management system (the process of optimizing the database access plan by query) tends to increase. In particular, input / output processing with an external storage device such as a magnetic disk in which a database is stored becomes a burden. Therefore, in recent years, systems have been increasing in which one table is divided and stored in a plurality of external storage devices, and the load on the input / output processing is reduced by parallelizing the input / output processing. Further, there is a system in which table data is divided and stored in each external storage device under a plurality of processors connected by a network in order to parallelize the arithmetic processing of the database. According to this system,
One table can be stored in different external storage devices, and different processors can read them in parallel. This data distribution technology is playing an increasingly important role in relational database management systems. [0008] Incidentally, as a form for storing data in a distributed manner, there are forms such as round robin, hash division, user-specified key range division, and uniform partition. The first division form, round robin,
Data is stored in a plurality of storage devices so that the data amount is uniform. The next hash partition determines the appropriate storage device by applying a hash function to a column of the table. In the key range division, a range of data to be stored in each storage device is specified as a condition for a certain column of the table, and the storage device satisfying the condition by a given data value. Select and store.
When such a key range division is performed based on the user's designated conditions, the designation of the storage location of the divided table is not directly received by the designation of the storage device to be stored, but at least one storage device is designated. 2. Description of the Related Art There is known a system that receives designation of a logical database area composed of devices. The reason for accepting the specification of the storage location of the table divided by the logical database area is to minimize the need for the user (the person who defines the database) to be aware of the physical system configuration. is there. In the last form of uniform division, data is divided into a plurality of storage devices in a round-robin manner in advance when a certain table is initially loaded. Then, the whole of a certain column (item) is sorted, and the data is divided again into a plurality of storage devices in the order of the sorted items so that the data amount becomes equal. Then, the minimum value and the maximum value of the sorted items are obtained for each storage device, and the range between the minimum value and the maximum value is used as the key range of the storage device. These techniques are described in 1986 VLDB by David J. DeWitt et al.
"GAMMA A High Performance Data Flow Database Machine (GAMMA A High
Performance Dataflow Database Machine)]. It also describes the realization of database parallel processing by multiple processors. As described above, by dividing and arranging data in a plurality of different storage devices, parallel processing of database access can be performed. As a data division method, in particular, when data is equally divided into a plurality of storage devices such as round robin, the same processing can be requested for each inquiry request, so that response time is improved by parallel processing. be able to. In addition, when the key range is divided, if the condition (column) subjected to the key range division is specified in the search condition specified in the query request for the table, the condition is satisfied in advance. The database processing may be performed only by the processing device storing the data to be processed. As a result, the load of another processing device is suggested, and the throughput of the entire system can be improved. As an important technique for speeding up database processing, other than this, using statistical information,
There are techniques for optimizing database access methods.
Statistical information is information that the system uses to determine the optimal database access procedure on behalf of the user. Information represented as one of the statistical information includes, for each section, section frequency distribution information in which a frequency of a row having a value included in the section is obtained in a specific column. Thus, when a condition is set in a column having section frequency distribution information by an inquiry, the number of rows satisfying the condition (selection rate) is determined by referring to the section frequency distribution information for the column. Since it can be calculated, an optimal procedure can be selected as an access processing procedure (whether or not to use an index) of the query. The method of obtaining this section frequency distribution information is based on Gregory Piatetsky Shapiro.
ACATERATE ETIMATION OF THE NUMBER OF TUP in 1984 ACM-SIGMOD international conference materials by Piatetsky Shapiro and others.
LES SATISFYING A CONDITION)]. Generally, in a database management system,
In designing a database, a means is provided for database users to achieve the best performance. In particular, when a table constituting a database is stored in an external storage device, if the table is stored in one external storage device, access to the external storage device storing the table is concentrated due to inquiries to the table, and input / output processing becomes a bottleneck. And the throughput of the entire system is reduced. Therefore, it is well known that a plurality of external storage devices are prepared as locations for storing tables, thereby distributing access for input / output processing and preventing a decrease in throughput. In this case, a method of using a plurality of external storage devices includes a method of storing data in the next external storage device when data cannot be simply stored in one external storage device, or a method of storing data described above. A method of dividing data into a plurality of external storage devices using key range division, hash division, and round robin division is well known. However, in the latter case of dividing a table in which storage conditions are specified, only queries that exhibit an effect suitable for the specified storage conditions produce valid results, and
The processing performance of queries that are not suitable for storage conditions was not guaranteed. As described above, in the prior art, in a database management system such as a relational database, inquiries from users are atypical, and only specific effects can be obtained. Satisfactory system performance cannot be required simply by providing the storage conditions to the user. Furthermore, if the applied partitioning condition is changed to another partitioning condition, all data in the table must be reorganized, and the definition of the table partitioning condition must be redefined. ) Burden. It is necessary to consider not only the balance of access to the external storage device for storing the table data but also the load of the processor for processing the data. Another problem in the table division is a key range division condition specified by a user. Today, with the advancement of parallel computer technology, the number of processors has been expanded from several to several thousand. The number of storage devices (such as magnetic disks) connected to the processor is similarly expanded. When a database system is built with such a system, the definition of the table to be divided into key ranges
If the division condition specified by the user exceeds several tens, the burden is large, and the definition is likely to be erroneous. For example, if the user specifies at most several division conditions, the amount of data stored in the storage device increases in proportion to the amount of data in the table, the load on one storage device increases, and I / O It is easy to become a bottleneck. Further, depending on the domain of the column specified in the division condition, an imbalance such as concentration of data in a specific storage device may occur. In this case, the user is required to prepare a new storage device and redefine the table. In redefining the table, the user needs to back up the data already stored, delete the table, redefine the partitioning conditions, and download the backed up data. There was a problem that the time to hinder the operation of the system increased. For this, a uniform parti
According to the option, the user does not specify the division condition for the key range division, but only specifies the item to be subjected to the division condition, and the system pre-equalizes the data amount in a plurality of storage devices. By determining the key range of each storage device based on the minimum value and the maximum value of the items to be stored and specified in each storage device to be divided, the burden on the user is reduced. However, also in this case, addition or deletion of data causes an imbalance in the data amount of each storage device, and there is no need to define the table again. Since it is necessary to equalize the data amount of each storage device and recalculate the key range of each storage device, the operation time of the system may be hindered. Furthermore, even if the data amount of each storage device becomes constant, there may be a situation in which access is concentrated only in a specific key range depending on a query to a table, and the problem of access imbalance is considered for each storage device. To get access status information,
The user must readjust the table partitioning conditions. An object of the present invention is to provide an information processing apparatus comprising a plurality of processing devices and a storage device connected to the processing devices, capable of performing optimal division of data of a table constituting a relational database. It is to provide a system. In order to achieve the above-mentioned object, the present invention provides an M (M ≧ M) which performs input / output between N (N ≧ 1) storage means and the storage means. 1) A processing device composed of a plurality of processors is a minimum constituent unit, and the processing device O
An information processing device in which a hierarchy is formed in which a set of (O> 1) is a cluster, and the set of clusters P (P> 1) is a cluster group, and all of the processing devices are connected via a network In the above, the table constituting the relational database is divided and stored in a certain arbitrary layer in accordance with the specification of the user's division method, and the division definition information specified for the arbitrary layer corresponds to the above-mentioned layer. By storing the information as the made information, data is processed based on the division definition information in response to an inquiry from a user. The table constituting the relational database is divided and stored in a certain hierarchy corresponding to the user's specification of the division method, and the division definition information specified for the arbitrary hierarchy is stored. According to the relational database management system of the present invention in which information is stored as information corresponding to a hierarchy, in response to an inquiry from a user,
Since it is possible to limit the processing devices that process data based on the division definition information and the storage devices that store the data, it is possible to prevent the processing load from increasing without lowering the throughput of the entire system. it can. DESCRIPTION OF THE PREFERRED EMBODIMENTS A database management system according to the present invention
A plurality of processors connected to the network divide the data in the table into external storage devices connected to each processor. FIGS. 2 to 4 show hardware of the database management system according to the present embodiment. In FIG. 2, the minimum structural unit of the database management system according to the present invention comprises a processor 12 and an external storage device 14 connected to the processor 12, which is called a node 15. The node may comprise not only one processor but also a plurality of tightly coupled processors. Also, a plurality of external storage devices 14 connected to the processor 12 may be connected. The nodes are connected to a network 10. A cluster 16 refers to a processing device having a node as a minimum unit as a set of a plurality of nodes. Further, in FIG. 3 and FIG. 4, a group of a plurality of clusters 16 is referred to as a cluster group 18. The cluster 16 and the cluster group 18 are constituent units of a logical system, and do not indicate special hardware. The database management system according to the present invention enables the system configuration to be changed in units of the node 15, the cluster 16, and the cluster group. Next, FIG. 5 shows a system configuration of the database management system according to the present invention using the hardware configuration shown in FIGS. FIG. 5 shows the configuration of each processing unit (hereinafter referred to as a server) of the database management system according to the present invention, the correspondence between the server and the resources, the node configuration, and the positioning of the communication path (network). The front-end server 103 (hereinafter abbreviated as FES) receives an inquiry from the application program 104 (hereinafter abbreviated as AP) and generates a processing procedure. Backend server 1
01 (hereinafter abbreviated as BES) receives the processing procedure from the FES, accesses a database 102 (hereinafter abbreviated as DB), acquires data, and passes it to the FES. The journal server 107 (hereinafter abbreviated as JS) records change history information of a database in which FES or BES occurs, and status information of a transaction. Data dictionary server 10
5 (hereinafter abbreviated as DDS) is FES or BES (or J
Meta information used by S), for example, table definition information in a relational database and column information of each table are stored in a data dictionary (hereinafter abbreviated as DD). Item number 15 in FIG. 5 is the node shown in FIG. Item No. 10 is the network shown in FIG. 2 and is a communication backbone for performing communication between nodes. A node is given a unique node address.
The node address is a physical node identifier, and communication is performed on the network by designating the node address. The network absorbs differences in protocols and network interfaces in the communication system, and allows all servers existing on the network to transmit and receive data by specifying an identifier for identifying the partner server. And Next, FIG. 1 shows an embodiment of dividing a database table according to the present invention. In FIG. 1, a table T120 is defined in a relational database by a CREATE TABLE statement which is an SQL data definition language. The table T120 is stored in the cluster group 1 including the cluster group 18 shown in FIG. Further, Table T1
(20) is divided into a plurality of clusters 16 (Cluster 1 to Cluster O) included in the cluster group 1 and stored. Further, in each cluster, the data is divided and stored in a plurality of nodes 15 included in the cluster, and finally divided and stored in a plurality of external storage devices 14 included in each node. FIG. 6 shows a definition example of Table T1 at this time.
A method of hierarchically designating the designation of the cluster group, cluster, node, and external storage device storing the table T1 and designating a table dividing method in each hierarchy in each hierarchy is adopted. In this case, the table T1 is stored in the cluster group 1, and in the clusters included in the cluster group 1,
The column C1 of the table T1 is divided by the key range, and is divided into clusters 1 to 8. Next, in each cluster, the nodes in the cluster are hash-divided in column C2 of table T1,
Further, each node specifies that the external storage devices in the node are equally divided. As shown in FIG. 7, the configuration information of these cluster groups, clusters, nodes and external storage devices is managed by system configuration information 30 and stored in the DDS in FIG. Further, the division definition information of the defined table is managed by three tables shown in FIG. 8, and these tables are also stored by the DDS in FIG. The cluster group management information table 32 stores information about the cluster groups that divide the table, the cluster management information table 34 stores information about the clusters that divide the table, and the node management information table 36 stores the nodes that divide the table. And information about an external storage device (disk) included in the node. FIG. 9 shows how data is inserted into a table based on the table partition information according to the present invention. 9, the insertion processing analysis processing 40 is performed in the configuration of the database management system shown in FIG.
The request is passed from the AP 104 to the FES 103 by an SQL INSERT statement, and the process starts. The three tables shown in FIG. 8 are referred to in order to determine the divided storage location of the table into which data is to be inserted. When referencing three tables, use FE
A request to acquire table information is transmitted from S103 to the DDS 105, and the received DDS 105 returns a search result of the requested table division information to the FES 103 that has issued the request. First, referring to the cluster group management table 32, the table division information is acquired (step 410). Therefore, if there is division information in the cluster group (step 412), a cluster group and a cluster into which data is to be inserted are determined.
(Step 414). Next, whether there is any division information in the cluster group or not, based on the previously determined cluster information, the cluster management table 34 is referred to and the division information in the cluster of the table is obtained (step 416). Therefore, if there is division information in the cluster (step 418), a cluster and a node into which data is to be inserted are determined (step 420). Next, based on the previously determined node information, whether there is any division information in the cluster, the node management table 36 is referred to, and the division information in the node of the table is acquired (step 422). Therefore, if there is division information at the node (step 424), the node into which data is to be inserted is determined (step 426). Further, when the node is determined, the disk to be inserted is determined by referring to the disk division information in the node (step 428). A data insertion request is transmitted to the corresponding server (BES 101 in FIG. 5) based on the data insertion location (in which cluster, which cluster, which node, which disk, etc.) of the table thus determined (step 430). Next, FIGS. 10 and 11 show how data retrieval processing for a table is performed based on the table division information according to the present invention. In FIG. 10, the optimizing process 50 is performed by the AP 104 in the configuration of the database management system shown in FIG.
The request is passed to FES103 by the SQL SELECT statement,
After the syntax analysis processing is performed, the processing starts. The three tables shown in FIG. 8 are referred to in order to determine the divided storage location of the table from which data is searched. When referring to the three tables, the FES 103 transmits a table information acquisition request to the DDS 105, and the received DDS 105 returns a search result of the requested table division information to the request source FES 103. The optimization process 50 is divided into three phases. First, the table divided storage conditions are referred to the three tables, and the search target range is evaluated from the specified search condition in the inquiry (step 510). Next, a process of determining an access procedure based on the determined table search range is performed (step 520). Then, a processing procedure to be executed by the server (BES) that performs the processing based on the determined access procedure is generated (step 30). The generated procedure is
Sent from FES103 to BES101 to be processed,
BES101 will send a database
Performs access processing and returns the result to FES103. FIG. 11 shows a flow of the table division storage condition evaluation processing in step 510 of FIG. First, the cluster group management table 32
To obtain the partition information of the table (step 51).
10). Therefore, if there is division information in the cluster group (step 5112), a cluster group and a cluster to be searched for data are determined (step 5).
114). In the case of the table T1 (20) in FIG. 1, the cluster group 1 is to be searched. In the cluster group, since the division condition for dividing into clusters is key range division, if a search condition for the column C1 subject to key range division is specified, the search condition is specified. The cluster to be searched is determined based on the comparison value obtained. Next, whether there is any division information in the cluster group or not, based on the previously determined cluster information, the cluster management table 34 is referred to and the division information in the cluster of the table is obtained (step 5116). Therefore, if there is division information in the cluster (step 5118), the cluster and the node from which data is to be searched are determined (step 5120). FIG.
In the case of Table T1, the cluster 11 is assumed to be a search target. In the cluster, since the partitioning condition for partitioning into nodes is hash partitioning, if a search condition for the column C2 subject to hash partitioning is specified, the comparison value specified in the search condition is used. Determine the node to search based on. Next, based on the previously determined node information, whether there is any division information in the cluster or not, the node management table 36 is referred to, and the division information in the node of the table is acquired (step 5122). Therefore, if there is division information at the node (step 5124), a node from which data is to be retrieved is determined (step 5126). In the case of the table T1 in FIG. 1, it is assumed that the cluster Node 111 is a search target.
When the node is determined, the disk to be inserted is determined by referring to the disk division information in the node (step 5128).
In the node, since the division condition when dividing into disks is equal division, in the case of equal division, all disks included in the node are to be searched. In this way, the search processing request range to be processed by the inquiry is determined (step 5130). Although a specific embodiment of the database management method of the present invention has been described, other examples can be implemented without departing from the purpose and spirit of the present invention. In other embodiments, even in a single system including a processor and a plurality of external storage devices connected to the processor, the external storage device is divided and managed in a logical hierarchy, and an arbitrary division method is performed according to the hierarchy of the external storage device. There is a method of realizing it. According to the present invention, since the data of a certain table in the database is divided in consideration of the hierarchy of the hardware constituting the system, uniform distribution of the data can be facilitated. Also, by adopting such a data division method, it is possible to select a data parallel search processing method which is good at each division method. Further, since the table is divided hierarchically, the data can be reorganized at an arbitrary hierarchy even when the data is reorganized, and the range of the reorganization process can be localized.

BRIEF DESCRIPTION OF THE DRAWINGS FIG. 1 is an explanatory diagram showing a divided example of a table according to the present embodiment. FIG. 2 is a block diagram showing a configuration from a cluster to a node having a hardware configuration of the embodiment. FIG. 3 is a block diagram showing a configuration from a cluster group to a node in a hardware configuration of the embodiment. FIG. 4 is a block diagram showing the maximum configuration of the hardware configuration of the embodiment. FIG. 5 is a block diagram illustrating a configuration of a database management system according to the embodiment; FIG. 6 is an explanatory diagram illustrating an example of a table division definition according to the embodiment; FIG. 7 is an explanatory diagram showing system configuration information of a hardware configuration according to the embodiment; FIG. 8 is an explanatory diagram showing table division information according to the embodiment; FIG. 9 is a processing flowchart of an analysis process of inserting data into a table according to the embodiment; FIG. 10 is a flowchart of an optimization process in a table search process according to the embodiment; FIG. 11 is a processing flowchart of table division storage condition evaluation processing in table search processing according to the embodiment; [Description of Signs] 10 network, 12 processor, 14 storage device, 15 node, 16 cluster, 18 cluster group.

Continuation of front page (56) References DEWITT, D. GRAY, J, Parallel datasystems: the feature of high performance datasystems, Communications of the ACM, 1992, Vol. 6, p. 85-98 GHANDHARIZADEH, S.M. DEWITT, D.E. J. , Hybrid-Range Partitioning Strategies: A New Destructing Strategies for Multiprocesor Database Machinery, Inc., 16th International News Agency, Canada. 481-492 (58) Field surveyed (Int. Cl. ⁷ , DB name) G06F 17/30 G06F 12/00 JICST file (JOIS)

Claims (1)

(57) [Claim 1] A processing apparatus comprising M (M ≧ 1) processors for performing input / output between N (N ≧ 1) storage means and the storage means. The processing unit O (O
> 1) sets as clusters, and P (P> 1) sets of the clusters as a cluster group to form a hierarchy, wherein all of the processing devices are connected via a network. A table constituting a relational database is divided and stored in a certain hierarchy corresponding to a user's specification of a division method, and division definition information specified for the arbitrary hierarchy is made to correspond to the hierarchy. A database management method characterized by storing data as information and processing data based on the division definition information in response to an inquiry from a user.