JP4349463B2 - Storage device management method - Google Patents

Description

The present invention relates to a database partition management method and a parallel database system, and more particularly to a storage device management method for optimizing the number of processors or the number of disks that perform database processing in accordance with the load.

For example, Non-Patent Document 1 proposes a parallel database system.
In this parallel database system, a plurality of processors are connected in tight coupling or loose coupling, and database processing is distributed to the plurality of processors.

David DeWitt and Jim Gray: Parallel Database Systems: The Future of High Performance Database Systems, CACM, Vol. 35, No. 6, 1992

The system configuration of the conventional parallel database system is left to the user and is fixed.
For this reason, the system configuration may be incompatible with the load from the beginning or may become incompatible in the middle, and the expected parallelism cannot be obtained, or high-speed queries cannot be realized.
SUMMARY OF THE INVENTION An object of the present invention is to provide a database partition management method and a parallel database system that can obtain the expected degree of parallelism and realize high-speed queries.

In a first aspect, the present invention accesses a database on the basis of an FES node having a function of executing analysis, optimization, and processing procedure creation of a query from a user, and the processing procedure created by the FES node. In a parallel database system in which a BES node having a function and an IOS node having a disk and a function of storing and managing a database on the disk are connected via a network, an FE is applied according to a load pattern of database processing.
Provided is a database partition management method characterized by determining the number of processors allocated to an S node, the number of processors allocated to a BES node, the number of processors allocated to an IOS node, the number of disks in an IOS node, and the number of disk partitions To do.

In addition, the FE has the function of executing analysis, optimization, and processing procedure creation of queries from users.
A parallel structure in which an S node and a BES node having a function of accessing a database and a function of storing and managing a database on the disk are connected via a network based on a processing procedure created in the FES node. In the database system,
Depending on the load pattern of database processing, the number of processors assigned to the FES node and B
Provided is a database partition management method characterized by determining the number of processors allocated to an ES node, the number of disks of a BES node, and the number of disk partitions.

In the database partition management method according to the first aspect described above, the number of processors, the number of disks, and the number of disks allocated to each node according to the database processing load pattern (one record search process, one record update process, data fetch process, etc.) Determine the number. Therefore, the system configuration is adapted to the load, the expected parallelism can be obtained, and high-speed queries can be realized.

In a second aspect, the present invention accesses a database on the basis of an FES node having a function of executing analysis, optimization, and processing procedure creation of a query from a user, and a processing procedure created by the FES node. In a parallel database system in which a BES node having a function and an IOS node having a disk and a function of storing and managing a database on the disk are connected by a network, the parallel time for which the time required for the database scan is constant Determine the upper limit of the number of accessible pages, and according to the upper limit of the number of pages, the number of processors assigned to the FES node, the number of processors assigned to the BES node, the number of processors assigned to the IOS node, the number of disks of the IOS node, A database characterized by determining the number of disk divisions To provide the split management method.

In addition, the FE has the function of executing analysis, optimization, and processing procedure creation of queries from users.
A parallel structure in which an S node and a BES node having a function of accessing a database and a function of storing and managing a database on the disk are connected via a network based on a processing procedure created in the FES node. In the database system,
Determine the upper limit of the number of pages that can be accessed in parallel with a fixed time required for the database scan, and according to the upper limit of the number of pages, the number of processors assigned to the FES node, the number of processors assigned to the BES node, and the number of BES nodes Provided is a database partition management method characterized by determining the number of disks and the number of disk partitions.

In the database partition management method according to the second aspect, the number of processors, the number of disks, and the number of disk partitions allocated to each node are determined according to the upper limit of the number of pages that can be accessed in parallel with a constant time required for database scanning. To decide. Therefore, high-speed inquiries can be realized.

In a third aspect, the present invention accesses a database on the basis of an FES node having a function of executing analysis, optimization and processing procedure creation of a query from a user, and the processing procedure created by the FES node. In a parallel database system in which a BES node having a function and an IOS node having a disk and a function of storing and managing a database on the disk are connected by a network, an expected parallelism p is calculated from a load pattern, and The number of processors assigned to the FES node, the number of processors assigned to the BES node, the number of processors assigned to the IOS node, the number of disks in the IOS node, and the number of disk divisions are determined according to the expected parallelism p. A database partition management method is provided.

In addition, the FE has the function of executing analysis, optimization, and processing procedure creation of queries from users.
A parallel structure in which an S node and a BES node having a function of accessing a database and a function of storing and managing a database on the disk are connected via a network based on a processing procedure created in the FES node. In the database system,
The expected parallelism p is calculated from the load pattern, and the number of processors assigned to the FES node, the number of processors assigned to the BES node, the number of disks in the BES node, and the number of disk divisions are determined according to the expected parallelism p A database partition management method is provided.

In the database partition management method according to the third aspect, the number of processors, the number of disks, and the number of disk partitions allocated to each node are determined according to the expected parallelism p calculated from the load pattern. Therefore, the expected degree of parallelism can be obtained.

In a fourth aspect, the present invention calculates the optimum page access count m in the database partition management method with the above configuration, and if there is a key range partition, the stored page count s (= m / p) in subkey range units. ) Is calculated, subkey range is divided into s pages, and data is inserted into the disk.
In the database partition management method according to the fourth aspect, the subkey range is divided by the number of stored pages s (= m / p) in subkey range units calculated from the expected parallelism p and the optimum page access number m, and the data is transferred to the disk. Insert. Therefore, the data can be divided and managed substantially equally.

In a fifth aspect, the present invention accesses a database on the basis of an FES node having a function of executing analysis, optimization, and processing procedure creation of a query from a user, and a processing procedure created by the FES node. In a parallel database system in which a BES node having a function and an IOS node having a disk and a function of storing and managing a database on the disk are connected via a network, the number of access pages acquired during query execution processing,
Based on load information such as the number of hit rows and the number of communications, load imbalance is detected, and the number of processors assigned to the FES node, the number of processors assigned to the BES node, and the processor assigned to the IOS node in the direction of eliminating the load imbalance The database partition management method is characterized in that the number and the number of disks of the IOS node are changed.

In addition, the FE has the function of executing analysis, optimization, and processing procedure creation of queries from users.
A parallel structure in which an S node and a BES node having a function of accessing a database and a function of storing and managing a database on the disk are connected via a network based on a processing procedure created in the FES node. In the database system,
The load unbalance is detected based on the load information such as the number of access pages, the number of hit rows, and the number of communications acquired during the query execution process, and the number of processors assigned to the FES node and the BES node in the direction of eliminating the load imbalance There is provided a database partition management method characterized by changing the number of processors allocated to a disk and the number of disks of a BES node.

In the database partition management method according to the fifth aspect, the load imbalance is detected during the query execution process, and the number of processors or the number of disks in each node is changed so as to eliminate the load imbalance. Therefore, even if there is a load change, the system configuration is always adapted to the load, the expected parallelism can be obtained, and high-speed queries can be realized.

In a sixth aspect, the present invention is an addition target if the number of processors allocated to a BES node, the number of processors allocated to an IOS node, or the number of disks is added in the database partition management method configured as described above if online. Block the key range of the database table managed by the processor or disk, allocate a new processor or disk, take over lock information and directory information, rewrite the dictionary information necessary for node allocation control, and then The database partition management method is characterized by releasing the blockage when online.
In addition, when adding the number of processors or disks allocated to the BES node in the database partition management method configured as described above, if online, the key range range of the database table managed by the processor or disk to be added is set. Shut down, assign a new processor or disk, take over lock information and directory information, rewrite the dictionary information necessary for node allocation control, and move data from the disk group to be added to the new disk group Then, the database partition management method is provided, wherein the blockage is released if online.

In the database partition management method according to the sixth aspect, when adding or deleting the number of processors or the number of disks, if it is online, the key range range of the table is blocked, the takeover process is performed, and then the blocking is performed. To release. Thus, overhead can be minimized. In a system configuration with an IOS node, data can be transferred without moving.

In a seventh aspect, the present invention is a deletion target if the number of processors allocated to a BES node, the number of processors allocated to an IOS node, or the number of disks is deleted in the database partition management method configured as described above, if it is online. Block the key range of the database table managed by the processor or disk, determine the processor or disk to be deleted, take over the lock information and directory information, rewrite the dictionary information required for node allocation control, Thereafter, the database partition management method is provided, wherein the blockage is released if online.

In the database partition management method configured as described above, when deleting the number of processors or disks allocated to a BES node, if online, the key range range of the database table managed by the processor or disk to be deleted is set. Shut down, determine the processor or disk to be deleted, take over lock information and directory information,
Database partition management characterized by rewriting dictionary information necessary for node allocation control, moving data from a disk group to be deleted to a succeeding disk group, and then releasing the block if online Provide a method.

In the database partition management method according to the seventh aspect, when adding or deleting the number of processors or the number of disks, if online, the key range range of the table is blocked, the takeover process is performed, and then the blocking is performed. To release. Thus, overhead can be minimized. In a system configuration with an IOS node, data can be transferred without moving.

In an eighth aspect, the present invention provides a parallel database system characterized by dynamically changing the number of processors or the number of disks that perform database processing by the database partition management method configured as described above.
The parallel database system according to the eighth aspect is a scalable parallel database system due to the actions of the fifth to seventh aspects.

According to the database partition management method of the present invention, the system configuration is adapted to the load, the expected parallelism can be obtained, and high-speed queries can be realized.
According to the parallel database system of the present invention, it is possible to obtain a scalable parallel database system in which the system configuration is always changed to one adapted to the load even when the load fluctuates.

Hereinafter, embodiments of the present invention will be described in detail with reference to the drawings. Note that the present invention is not limited thereby.
FIG. 1 is a configuration diagram showing a parallel database system 1 according to an embodiment of the present invention.
This parallel database system 1 includes an FES (front end server) node, BES
(Back-end server) node, IOS (input output server) node, DS
In this configuration, a (dictionary server) node and a JS (journal server) node are connected by a network 90. Each node is also connected to other systems.
The FES node is an FE composed of at least one processor having no disk.
This node is composed of S75, and has a function of a front-end server that executes analysis, optimization, and processing procedure creation of queries from users.
A BES node is a BE composed of at least one processor having no disk.
This node is composed of S73 and has a function of accessing a database based on the processing procedure created by the FES75.
The IOS node is a node composed of an IOS 70 composed of at least one processor and at least one disk 80, and has a function of storing and managing a database on the disk 80. If the BES node has the function of the IOS node, the IO node
S nodes can be omitted. In this case, a disk is connected to the BES 73 and the BES 73
Has a function of storing and managing the database on the disk 80.

The database consists of a plurality of tables. The table is a two-dimensional table format and includes a plurality of rows. One row consists of one or more columns (attributes).
This table is physically divided into fixed-length page units made up of a predetermined number of rows, and the disk 8
Stored on 0. The storage position of each page on the disk 80 can be known using directory information.
The DS node is a node composed of a DS 71 composed of at least one processor and at least one disk 81, and has a function of collectively managing database definition information.
The JS node is a node including a JS 72 composed of at least one processor and at least one disk 82, and has a function of storing and managing database update history information executed in each node.

FIG. 2 shows FES75, BES73, IOS7 in the parallel database system 1 described above.
It is a conceptual diagram of the database partition management method of the present invention for determining the number of processors of 0, the number of disks, and the number of disk partitions.
First, the database processing load pattern is determined by the workload specified by the user. The load pattern includes a single search process, a single update process, and a data fetch process. In accordance with the load pattern, the IOS 70 determines how many disks 80 are to be managed (IO
When the BES has the function of the S node, the BES 73 determines how many disks are to be managed).

That is, when the schema is defined, if the number of disks required for storage is determined from the table partitioning method (key value range, number of rows per range (converted page number), etc.) and the unit of blockage and reorganization is determined, BES73 And IOS70 combination (block or reorganization unit is disk, I
Depends on OS and BES).
Thereby, the number of constituents of the BES 73, the IOS 70, and the disk 80 is determined. That is, it is as follows.
Number of existing partitions ... Parallel accessible database partition units managed by all BESs (dynamic B
(ES addition and deletion are performed in this division unit)
Number of disks per division ... Number of disks allocated in each division Figure 2 shows the number of disks is "8"
This is the case where the number of already divided divisions is “4” and the number of disks for each division is “2”.
If the processor performance is n times, the number of volumes used in each division is increased n times without changing the number of divisions (however, there is a limit on the total data transfer rate between the IOS 70 and the disk 80, There is also a limit on the number of disks).
Here, the disk corresponds to one disk device. In the present invention, the “disk” does not necessarily correspond to one disk device on a one-to-one basis.
For example, when a single disk device includes a plurality of disk devices (disk array device), the number of input / output units that can be accessed in parallel may be applied as a “disk device”.

As shown in FIG. 2, FES: BES: IOS: disk = 1: 4: 1: 8, but at the time of initial data loading, only one FES and BES is required, and FES: BES: IOS: disk = 1: 1. 1: 8. Therefore, the BES 731 has directory information of a database stored in the already-divided # 1 to # 4 disks 811 to 842.
When the load of the BES 73 is light and the IOS 70 and the eight disks 811 to 842 can be processed by only one BES 731, the eight disks 811-8 can be processed by only one BES 731.
The database stored in 842 is accessed. Therefore, FES: BES: IOS: disk = 1: 1: 1: 8 remains unchanged.
When the load of the BES 731 increases and the state of 100% utilization continues and a load imbalance is detected, the BES 732 is added. Since the number of divisions is “4”, two BESs 731,
Two divisions are associated with 732. Therefore, the BES 731 contains already divided #
1 to # 2 of the disks 811 to 822 having database directory information. Further, the BES 732 has database directory information stored in the previously divided # 3 to # 4 disks 831 to 842. FES: BES: IOS: disk = 1: 2: 1
: 8.

Further, when the load of BES 731 and 732 increases and the state of 100% utilization continues and load imbalance is detected, BES 733 and 734 are added to BES 731 and 732, respectively. Since the number of divisions is “4”, one division is associated with each of the four BESs 731, 732, 733, and 734. Therefore, the BES 731 has directory information of a database stored in the already-divided # 1 disks 811 to 812. In addition, BES7
32 has database directory information stored in the already-divided # 2 disks 821 to 822. The BES 733 has database directory information stored in the already divided # 3 disks 831 to 832. Further, the BES 734 has database directory information stored in the already divided # 4 disks 841 to 842. FES: B
ES: IOS: disk = 1: 4: 1: 8.

When the load becomes lighter and the usage rate of BES 733, 734 continues to be less than 50%, for example,
It is more effective to use the node assigned to the BES 733 and 734 for other processing. Therefore, BES733 and 734 having a utilization rate of less than 50% are combined. Then FES:
Degenerate to BES: IOS: disk = 1: 2: 1: 8.

As described above, if the BES is increased or decreased according to the load, a scalable system can be realized between FES: BES: IOS: disk = 1: 1: 1: 8 to 1: 4: 1: 8.
The IOS 70 may have as many parallel tasks as the number of disks that can be accessed in parallel, regardless of the correspondence between the BES 73 and the disks 80. Therefore, by moving the directory information between the BESs without moving the data, the correspondence between the BES 73 and the disk 80 can be changed, and access separation and integration can be easily performed.
Next, the number of processors, the number of disks, and the number of disk divisions will be described with numerical examples in the case where the load pattern is a one-case update process and the data extraction process.
The preconditions are assumed as follows.
FES processing (receipt processing) ... 30 [K steps]
BES processing (one update processing) ... 60 [K steps]
(Data retrieval processing) ... 220 [K steps]
Transmission processing ... 6 [K steps]
Reception processing ... 6 + 4 * Number of pages [K steps]
Input / output issuance processing ... 4 + 4 * number of pages [K steps]
Processor performance ... 10 [M steps / sec]
Input / output performance (1 page access) ... 20 [msec]
(10 page access) ... 30 [msec]
A. In case of single update process (1 page access)
(1) In the case of a system configuration with an IOS node When the processor performance of 10 [M steps / second] is divided by 30 [K steps] of FES processing, reception processing can be performed up to 333 times / second.

Also, the execution request reception process 6 [K steps] from the FES + the data extraction request transmission process 6 [K steps] from the BES + the data extraction result reception process 10 [K steps] from the IOS + one item update process 60 [ K step] + FES execution request result transmission process 6 [K step] = 88 [K step] is necessary for one update process in BES, so this divides the processor performance 10 [M step / second]. One update process is possible up to 114 times / second.
Further, input / output request reception processing 6 [K steps] from BES + input / output issuance processing 8 [K steps] + input / output request result transmission processing 6 [K steps] = 20 [K steps] to BES Since it is necessary for accessing the disk, when the processor performance of 10 [M steps / second] is divided by this, the disk can be accessed up to 500 times / second.

In addition, since 20 [msec] is required for random input / output of one page, there are 5 for one disk.
Access is possible up to 0 times / second. If the number of times the IOS can access the disk is divided by 500 times / second, it is possible to mount up to 10 disks in the IOS.
Moreover, if the number of times the disk can be accessed by the IOS is divided by 500 times / second by the number of times that the BES can be updated by 114 times / second, then 4.3 BESs can be handled by one IOS. is there.

Furthermore, if the BES can be received and processed 333 times / second by the BES one-time update processing possible number of 114 times / second, one FES can support three BESs.
From the above, FES: BES = 1: 3, BES: IOS = 4.3: 1, and IOS: disk = 1: 10. Therefore, generally, as shown in FIG. 3, FES: BES: IOS:
If the disk is 1: 4: 1: 8, the mounting is almost balanced (some unbalance occurs between the FES and the disk).

(2) In the case of a system configuration in which the BES node has the function of the IOS node When the processor performance of 10 [M steps / second] is divided by 30 [K steps] of FES processing, reception processing can be performed up to 333 times / second. is there.
Also, the execution request reception process 6 [K steps] + input / output issue process 8 [K steps] + one update process 60 [K steps] + execution request result transmission process 6 [K steps] to FES = Since 80 [K steps] is necessary for one update process in BES, if the processor performance is 10 [M steps / second], the one update process can be performed up to 125 times / second.
In addition, since 20 [msec] is required for random input / output of one page, there are 5 for one disk.
Access is possible up to 0 times / second. Thus, if the number of times of the one-time update process in the BES is divided by 125 times / second, up to 2.5 disks can be mounted on the BES.
Further, by dividing 333 times / second, which can be processed by the FES, by 125 times / second, the number of times that the one-time update process can be performed by the BES, 2.6 BESs can be handled by one FES.

From the above, FES: BES = 1: 2.6 and BES: disk = 1: 2.5. Therefore, generally, as shown in FIG. 4, when FES: BES: disk = 1: 4: 8,
The implementation is almost balanced (some unbalance occurs in FES).
B. For data retrieval (page 10 access)
(1) In the case of a system configuration with an IOS node When the processor performance of 10 [M steps / second] is divided by 30 [K steps] of FES processing, reception processing can be performed up to 333 times / second.
Also, reception processing 6 [K steps] of execution requests from FES + transmission processing 6 [K steps] of data retrieval requests from BES + reception processing 46 [K steps] + data retrieval processing 220 [K of data retrieval results from IOS Step] + FES execution request result transmission process 6 [K step] = 284 [K step] is necessary for the data extraction process in the BES. Therefore, when the processor performance 10 [M steps / second] is divided by this, Data extraction processing is possible up to 35 times / second.
Further, input / output request reception processing 6 [K steps] from BES + input / output issuance processing 44 [K steps] + input / output request result transmission processing 6 [K steps] = 56 [K steps] to BES Since it is necessary for accessing the disk, when the processor performance of 10 [M steps / second] is divided by this, the disk can be accessed up to 179 times / second.

Also, since 10 [10] batch input / output requires 30 [msec], there are 33 on one disk.
Accessible up to times per second. The number of times the disk can be accessed by the IOS 1
By dividing 79 times / second, it is possible to mount up to 5.4 disks.
Further, if the number of times the data can be fetched by the BES is 35 times / second and the number of times the disk can be accessed by the IOS is 179 times / second, a single IOS can deal with 5.1 BESs. .
Further, when the BES data reception processing possible number of times 35 times / second is divided by the FES reception processing possible time 333 times / second, one FES can cope with 9.5 BESs.
From the above, FES: BES = 1: 9.5, BES: IOS = 5.1: 1, IOS: disk = 1: 5.4. Therefore, generally, FES: BES: IOS: disk = 1
: 10: 2: 10 results in an almost balanced mounting (a little unbalance occurs on the disk).

(2) In the case of a system configuration in which a BES node has the function of an IOS node When the processor performance of 10 [M steps / second] is divided by 30 [K steps] of FES processing, 3
Reception processing is possible up to 33 times / second.
Also, the execution request reception processing 6 [K steps] + input / output issue processing 44 [K steps] + data extraction processing 220 [K steps] + FES execution request result transmission processing 6 [K steps] = 276. Since [K step] is necessary for the data extraction process at the BES, the data extraction process can be performed up to 36 times / second by dividing the processor performance of 10 [M steps / second].
Also, since 10 [10] batch input / output requires 30 [msec], there are 33 on one disk.
Accessible up to times per second. Thus, the number of times data can be extracted by the BES 36
If the number of times / second is divided, only one disk can be mounted.

Further, when the BES data receiving process possible number of times 36 times / second is divided by the FES receiving processable number 333 times / second, one FES can cope with 9.2 BESs.
From the above, FES: BES = 1: 9.2 and BES: disk = 1: 1.
Therefore, in general, when FES: BES: disk = 1: 10: 10, the mounting is almost balanced.

FIG. 5 is a configuration diagram of the FES 75.
The FES 75 includes application programs 10 to 11 created by a user, a parallel database management system 20 that manages the entire database system such as query processing and resource management, and an operating system 30 that manages the entire computer system such as data reading and writing. It is equipped with.

The parallel database management system 20 includes a system control unit 21, a logical processing unit 22, a physical processing unit 23, and a database / dictionary 24 that temporarily stores data to be processed. The parallel database management system 20 is connected to the network 90 and other systems.

The system control unit 21 performs input / output management and the like. In addition, a data load process 210 and a dynamic load control process 211 are provided.
The logic processing unit 22 includes a query analysis 220 that performs query syntax analysis and semantic analysis, a static optimization process 221 that generates an appropriate processing procedure candidate, and a code generation that generates code corresponding to the processing procedure candidate. 222. Further, a dynamic optimization process 223 for selecting an optimum process procedure candidate and a code interpretation execution 224 for interpreting and executing the code of the selected process procedure candidate are provided.

The physical processing unit 23 includes a data access process 230 that realizes condition determination, editing, and record addition of accessed data, a database / dictionary buffer control 231 that controls reading / writing of database records, etc., and a resource shared by the system And exclusive control 233 for realizing exclusive control.

FIG. 6 is a configuration diagram of the BES 73.
The BES 73 includes a parallel database management system 20 that manages the entire database system, and an operating system 30 that manages the entire computer system. When having the function of an IOS node, it has a disk, and the database 40 is stored and managed on the disk.

The parallel database management system 20 includes a system control unit 21, a logical processing unit 22, a physical processing unit 23, and a database buffer 2 that temporarily stores data to be processed.
4. The parallel database management system 20 includes a network 9
0 and connected to other systems.

The system control unit 21 performs input / output management and the like. In addition, a data load process 210 for performing data load in consideration of load distribution is provided.
The logic processing unit 22 includes a code interpretation execution 224 that performs code interpretation.
The physical processing unit 23 includes a data access process 230 that realizes condition determination, editing, and record addition of accessed data, a database buffer control 231 that controls reading and writing of database records, and storage of data to be input / output A mapping process 232 for managing the position and an exclusive control 233 for realizing exclusive control of resources shared by the system are provided.

FIG. 7 is a configuration diagram of the IOS 70 and the disk 80.
The IOS 70 includes a parallel database management system 20 that manages the entire database system, and an operating system 30 that manages the entire computer system.
The disk 80 stores a database 40.
The parallel database management system 20 includes a system control unit 21, a physical processing unit 23, and an input / output buffer 24 that temporarily stores data to be processed. The parallel database management system 20 is connected to the network 90 and other systems.

The system control unit 21 performs input / output management and the like. In addition, a data load process 210 for performing data load in consideration of load distribution is provided.
The physical processing unit 23 includes a data access process 230 that realizes condition determination, editing, and record addition of accessed data, and an input / output buffer control 231 that controls reading and writing of database records.

FIG. 8 is a configuration diagram of the DS 71 and the disk 81.
The DS 71 is a parallel database management system 2 that manages the entire database system.
0 and an operating system 30 that manages the entire computer system.
The disk 81 stores a dictionary 50.
The parallel database management system 20 includes a system control unit 21, a logical processing unit 22, a physical processing unit 23, and a dictionary buffer 24.
The parallel database management system 20 is connected to the network 90 and other systems.
The logic processing unit 22 includes a code interpretation execution 224 that performs code interpretation.
The physical processing unit 23 includes a data access process 230 that realizes condition determination, editing, and record addition of accessed data, a dictionary buffer control 231 that controls reading and writing of dictionary records, and an exclusive control of resources shared by the system. And exclusive control 233 for realizing the above.

FIG. 9 is a configuration diagram of the JS 72 and the disk 82.
JS72 is a parallel database management system 2 that manages the entire database system.
0 and an operating system 30 that manages the entire computer system.
A journal 60 is stored in the disk 82.
The parallel database management system 20 includes a system control unit 21, a physical processing unit 23, and a journal buffer 24. The parallel database management system 20 is connected to the network 90 and other systems.
The physical processing unit 23 includes a data access process 230 that realizes condition determination, editing, and record addition of accessed data, and a journal buffer control 231 that controls reading and writing of journal records.

FIG. 10 is a flowchart showing processing of the database management system 20 in the FES 75.
The system control unit 21 checks whether it is a query analysis process (212). If it is a query analysis process, the query analysis process 400 is called, executed, and terminated.
If it is not a query analysis process, it is checked whether it is a query execution process (213).
If it is a query execution process, the query execution process 410 is called, executed, and then terminated.
If it is not a query execution process, it is checked whether it is a data load process (214). If it is a data load process, the data load process 210 is called, executed, and terminated.
If it is not a data load process, it is checked whether it is a dynamic load control process (214). If it is a dynamic load control process, the dynamic load control process 210 is called, executed, and terminated.
If it is not a dynamic load control process, the process ends.

In addition, the flowchart of the process of the database management system 20 in BES73 is as follows.
Steps 212, 215, 400, and 211 are omitted from FIG. IOS
The flowchart of the process of the database management system 20 at 70 is obtained by omitting steps 212, 213, 215, 400, 410, 211 from FIG.

FIG. 11 is a flowchart of the query analysis process 400.
First, the query analysis 220 executes syntax analysis and semantic analysis of the input query sentence.
Next, the static optimization process 221 estimates the ratio of data that satisfies the condition from the conditional expression that appears in the query, and selects an effective access path candidate (especially an index) based on a preset rule. ) To create processing procedure candidates.
Next, the code generation 222 expands processing procedure candidates into executable code. Then, the process ends.

FIG. 12 is a flowchart of the query analysis 220.
In step 2200, syntax analysis and semantic analysis of the input query statement are executed. Then, the process ends.

FIG. 13 is a flowchart of the static optimization process 221.
First, the predicate selection rate estimation 2210 estimates the selection rate of the predicate of the conditional expression that appears in the query.
Next, the access path consisting of an index or the like is pruned by the access path pruning 2212.
Next, a processing procedure candidate combining access paths is generated by processing procedure candidate generation 2213.
Then, the process ends.

FIG. 14 is a flowchart of predicate selection rate estimation 2210.
In step 22101, it is checked whether or not a variable appears in the query conditional expression (221).
01). If no variable appears, the process proceeds to step 22102, and if a variable appears, step 2102
Go to 2104.
In step 22102, it is checked whether the conditional expression includes column value distribution information. If there is column value distribution information, the process proceeds to step 22103, and if there is no column value distribution information, the process proceeds to step 22105.
In step 22103, the selectivity is calculated using the column value distribution information, and the process ends.
In step 22104, it is checked whether the conditional expression includes column value distribution information. If there is column value distribution information, the process ends. If there is no column value distribution information, the process proceeds to step 22105.
In step 22105, a default value is set according to the type of conditional expression (22105).
),finish.

FIG. 15 is a flowchart of access path pruning 2212.
In step 22120, the index of the column that appears in the query conditional expression is registered as an access path candidate.
In step 22121, it is checked whether the table to be accessed by the query is divided and stored in a plurality of nodes. If it is not stored separately, the process proceeds to step 22122. If it is stored separately, the process proceeds to step 22123.
In step 22122, the sequential scan is registered as an access path candidate.
In step 22123, parallel scan is registered as an access path candidate.
In step 22124, it is checked whether the selection rate of each conditional expression has already been set. If it has been set, the process proceeds to step 22125. If it has not been set, the process proceeds to step 22126.
In step 22125, the index of the conditional expression that minimizes the selection rate for each table is set as the highest priority of the access path.
In step 22126, the maximum value and the minimum value of the selection rate of each conditional expression are acquired.
In step 22127, a selection criterion for each access path is calculated from system characteristics such as processor performance and IO performance.
In step 22128, only those having a selection rate in an access path combining a single index or a plurality of indexes below the selection criterion are registered as access path candidates.

FIG. 16 is a flowchart of processing procedure candidate generation 2213.
In step 22130, it is checked whether the table to be accessed by the query is divided and stored in a plurality of nodes. If it is not stored separately, the process proceeds to step 22131. If it is stored separately, the process proceeds to step 22135.
In step 22131, it is checked whether or not the sorting procedure is included in the processing procedure candidate. If it is not included, the process proceeds to step 22132;
Proceed to 5.
In step 22132, it is checked whether the access path of the table to be accessed by the query is unique. If it is unique, go to Step 22133;
Go to 134.
In step 22133, a single processing procedure is created and the process ends.
In step 22134, a plurality of processing procedures are created and the process ends.
In step 22135, the query is broken down into two-way joins that can be joined.
In step 22136, the data reading / data distribution processing procedure and the slot sorting processing procedure are registered as candidates corresponding to the storage node group of the table to be divided and stored.
In step 22137, a slot sort processing procedure corresponding to the combination processing node group, N
The way merge process procedure and the matching process procedure are registered as candidates. Note that the slot sort process refers to a sort process in a page targeted for a page in which each row in the page is managed by a slot positioned with an offset from the top of the page and data is stored,
If read in slot order, rows can be accessed in ascending order. The N-way merge process is a process that uses an N-way buffer to input N sort sequences at each merge stage and finally create one sort sequence by the tournament method.
In step 22138, the request data output processing procedure is registered in the request data output node.
In step 22139, it is checked whether all evaluations have been completed for the decomposition result. If all the evaluations have not been completed, the process returns to step 22136, and if all the evaluations have been completed, the process ends.

FIG. 17 is a flowchart of the code generation 222.
In step 2220, it is checked whether the processing procedure candidate is unique. If not unique, the process proceeds to step 2221; if unique, the process proceeds to step 2223.
In step 2221, optimization information including column value distribution information and the like is embedded in the processing procedure.
In step 2222, a data structure for selecting a processing procedure is created based on the constant assigned at the time of executing the query.
In step 2223, the processing procedure is expanded into an execution format. Then, the process ends.

FIG. 18 is a flowchart of the query execution process 410.
First, the dynamic execution time optimization 223 determines a processing procedure to be executed in each node group based on the assigned constant.
Next, the code interpretation execution 224 interprets and executes the processing procedure. Then, the process ends.

FIG. 19 is a flowchart of the dynamic optimization process 223.
In step 22300, a dynamic load control process is executed (22300).
In step 22301, it is checked whether the created processing procedure is single. If it is single, the process is terminated. If not, go to step 22302.
In step 22302, the selectivity is calculated based on the assigned constant.
In step 22303, it is checked whether or not parallel processing procedures are included in the processing procedure candidates. If it is included, the process proceeds to step 22304. If it is not included, step 22308 is performed.
Proceed to
In step 22304, optimization information (column value distribution information of joined columns, number of rows in the table to be accessed, number of pages, etc.) is input from the dictionary.
In step 22305, the processing time for data retrieval / data distribution is calculated in consideration of each system characteristic.
In step 22306, the number of nodes p to be allocated to the joining process is determined from this processing time.
In step 22307, data distribution information is created based on the optimization information.
In step 22308, a processing procedure is selected according to the access path selection criteria, and the process ends.

FIG. 20 is a flowchart of the code interpretation execution 224.
In step 22400, it is checked whether or not data extraction / data distribution processing is being performed. If it is a data retrieval / data distribution process, the process proceeds to step 22401. If it is not a data retrieval / data distribution process, the process proceeds to step 22405.
In step 22401, the database is accessed to evaluate the conditional expression.
In step 22402, data is set in the buffer for each node based on the data distribution information.
In step 22403, it is checked whether or not the buffer of the node is full.
If it is full, the process proceeds to step 22404. If it is not full, the process proceeds to step 22420.
In step 22404, the data is transferred to the corresponding node in the page format.
Proceed to 2420.
In step 22405, it is checked whether or not slot sorting processing is performed. If it is slot sort processing, the process proceeds to step 22406. If it is not slot sort processing, step 2240 is executed.
Proceed to step 9.
In step 22406, page format data is received from another node.
In step 22407, slot sort processing is executed.
In step 22408, the slot sort processing result is temporarily stored, and in step 224.
Proceed to 20.
In step 22409, it is checked whether or not an N-way merge process is performed. If it is N-way merge processing, the process proceeds to step 22410. If it is not N-way merge processing, step 2241 is executed.
Go to step 2.

In step 22410, an N-way merge process is executed.
In step 22411, the N-way merge processing result is temporarily saved, and in step 224
Proceed to 20.
In step 22412, it is checked whether or not the matching process is performed. If it is a matching process, it will progress to step 22413, and if it is not a matching process, it will progress to step 22416.
In step 22413, both sort lists are matched, and data is set in the output buffer.
In step 22414, it is checked whether the output buffer is full. If it ’s full,
Proceed to step 22415. If not full, step 22420 follows.
In step 22415, the data is transferred to the request data output node in the page format, and the process proceeds to step 22420.
In step 22416, it is checked whether or not the request data output process is being performed. If it is a request data output process, the process proceeds to step 22417, and if it is not a request data output process, step 2242 is performed.
Go to 0.
In step 22417, it is checked whether there is transfer of page format data from another node. If there is a transfer, the process proceeds to step 22418, and if there is no transfer, the process proceeds to step 22419.
In step 22418, page format data is received from another node.
In step 22419, the query processing result is output to the application program.
In Step 22420, it is checked whether or not the BES is being executed. If it is being executed by BES, the process proceeds to step 22421, and if it is not being executed by BES, the process is ended.
In step 22421, information for estimating the processing load such as the number of access pages, the number of hit rows, the number of communications, etc. is notified to the FES, and the process ends.

FIG. 21 is a flowchart of the data load process 210.
Before explaining each step, the concept will be explained.
Data loading methods include target response time-oriented data placement that reduces the time required to scan the entire table within a fixed time, expected parallelism-oriented data placement optimized for m-page access, and volume partitioning completely by the user. There is user-specified data arrangement by specified user control.
In the target response time emphasis data arrangement, first, the number of pages necessary for storing the rows of the entire table is obtained. Next, the upper limit of the number of pages stored in each of the divided disks that can be accessed in parallel is determined. Access is premised on batch input (for example, 10 pages) if necessary. Load distribution is determined according to the combination of the number of disks, the number of IOS, and the number of BES. When there is a key range division, the key range division section is subdivided by the upper limit number of pages, and stored in each divided disk. This key range division will be described in detail later with reference to FIG.
In the data arrangement in which the expected parallelism is emphasized, it is desirable that the data arrangement is considerably large although it depends on the size of m. When there is a key range division, the number of sub key range storage pages s (= m / p) for each key range division unit is determined from the size of m and the expected parallelism p, and each s page is stored in each division disk. .

The expected parallelism p is calculated by the square root of the ratio obtained by dividing the response time by the overhead for each node. This ratio is 10 when the expected parallelism is 3, 100 when the expected parallelism is 10, 1000 when the expected parallelism is 32, and 10000 when the expected parallelism is 100. When the calculated expected degree of parallelism p exceeds the number of divisions, the number of divisions is adopted (because the maximum number of disks that can be processed with the number of divisions is determined). In the opposite case, the expected parallelism p is adopted as the number of divisions with the number of already divided numbers as the upper limit.
Specifically, a data arrangement optimized for 100 page access is estimated. As a premise, the batch input is 10 pages. One I / O time (10 page access) is 300 ms, 1
Since the number of I / O overheads is 5.6 msec (56 ks is required for 10 MIPS performance), the expected parallelism p is about 7 (= √ {300 / 5.6}). Therefore, s = 14 (= 100
/ 7) Divide the sub key range for each page.
The user-specified data arrangement is the same data arrangement as the conventional database management system, and is managed according to the setting parameters.

In step 21000, it is checked whether or not the target response time emphasis data is arranged.
If it is not the target response time emphasis data arrangement, the process proceeds to step 21001, and if it is the target response time emphasis data arrangement, the process proceeds to step 21003.
In step 21001, it is checked whether the expected parallelism-oriented data arrangement is present. If it is not the expected parallelism-oriented data arrangement, the process proceeds to step 21002, and if it is the expected parallelism-oriented data arrangement, the process proceeds to step 21010.
In step 21002, it is checked whether or not there is a user designation. If there is a user designation, the process proceeds to step 21016, and if there is no user designation, the process ends.
In step 21003, the number of pages necessary to store the entire table row is obtained.
In step 21004, an upper limit of the number of pages to be stored in a disk that can be accessed in parallel with a fixed time required for table scanning is determined.
In step 21005, a BES, IOS, and disk group satisfying the above requirements are determined.
In step 21006, it is checked whether there is a key range division. If there is a key range division, the process proceeds to step 21007, and if there is no key range division, the process proceeds to step 21009.
In step 21007, the key range division section is subdivided by a certain upper limit page number.
In step 21008, data insertion is performed corresponding to the key range division section, and the process ends.
In step 21009, data is inserted by dividing by the upper limit number of pages, and the process ends.
In step 21010, the optimum page access number m is calculated based on the estimated workload.
In step 21011, the expected parallelism p is calculated, and the BES is calculated according to the expected parallelism p.
, IOS, and disk group are determined.
In step 21012, it is checked whether there is a key range division. If there is a key range division, the process proceeds to step 21013. If there is no key range division, the process proceeds to step 21015.
In step 21013, the number of stored pages s (= m / p) in subkey range units is calculated.
In step 21014, the sub key range is divided in units of s pages, data is inserted into each disk, and the process ends.
In step 21015, data is inserted by dividing by the number of s pages, and the process ends.
In step 21016, data is inserted into the disk managed by the user-specified IOS, and the process ends.

FIG. 22 is a flowchart of the dynamic load control process 211.
In step 21100, the presence / absence of load imbalance (access concentration / discretization) is detected. That is, the DB processing load executed per unit time per node (number of processing steps (
DB processing, I / O processing, communication processing), processor performance (converted to processing time), I
/ O count (converted to I / O time)) resource that becomes a bottleneck (processor (BES)
, IOS) and disk), the DB process is expanded into an SQL statement, and the access status to each resource is classified in a table unit. If a load imbalance is detected, the process proceeds to step 21101. If a load imbalance is not detected, the process ends.

In step 21101, BES is added or deleted from the access distribution information,
It is determined whether IOS or disk pair is added or deleted. If addition or deletion is necessary, the process proceeds to step 21102, and if not necessary, the process ends.
In step 21102, it is checked whether or not it is added. If it is added, the process proceeds to step 21103. If it is not added, the process proceeds to step 2112.
In step 21103, it is checked whether online. If online, go to step 21104. If it is not online, the process proceeds to step 21105.
In step 21104, the key range range of the table managed by the target BES group is blocked.
In step 21105, a new BES is assigned.
In step 21106, lock information and directory information are taken over.
In step 21107, the DS 71 is instructed to rewrite dictionary information necessary for node allocation control.
In step 21108, it is checked whether IOS exists. If it does not exist, the process proceeds to step 21109, and if it exists, the process proceeds to step 21110. This step is inserted in order to cope with both the system configuration in which the IOS exists and the system configuration in which the IOS does not exist with the same software.
In step 21109, data is moved from the target BES group to a new BES group.
In step 21110, it is checked whether online. If online, go to step 21111. If it is not online, the process is terminated.
In step 21111, the block of the key range of the table managed by the target BES group is released, and the process ends.
In step 21112, it is checked whether online. If online, go to step 21113. If it is not online, the process proceeds to step 21114.
In step 21113, the key range range of the table managed by the target BES group is blocked.
In step 21114, the BES to be degenerated is determined.
In step 21115, lock information and directory information are taken over.
In step 21116, the DS 71 is instructed to rewrite dictionary information necessary for node allocation control.
In step 21117, it is checked whether IOS exists. If it does not exist, the process proceeds to step 21118, and if it exists, the process proceeds to step 21119.
In step 21118, data is expelled from the degenerate BES group.
In step 21119, it is checked whether online. If online, go to step 21120. If it is not online, the process is terminated.
In step 21120, the block of the key range of the table managed by the target BES group is released, and the process ends.

FIG. 23 is a conceptual diagram of data loading processing using key range division.
The already divided number is “4”. In addition, the column values v1 to v6 in the database are assumed to appear as shown in FIG.
At the time of initial data loading, the required BES may be one of 731 units. When the number of pages to be stored is associated with each partition 810 to 840 disk up to the upper limit of the number of pages, the column values v1 to v2 are stored in the partition 810 disk, and the column values v2 to v3 are stored in the partitions 820 and 830 disks. Stored, the column values v3 to v5 are stored in the partition 840 disk,
Column values v5 to v6 are stored in other disk groups. When initial data is loaded, directory information for each disk is created in order to manage pages stored in each disk.
When accessing the database, if BES732-734 is used according to the load, each B
The database is accessed using directory information for each disk corresponding to the ES.
When implementing each of the above processes, it may be combined with the following embodiment.

In order to facilitate the movement of a row between nodes, position information such as BES is not included in the row identifier. In BES, a physical position of a row is specified by combining directory information for specifying a table division position and a row identifier. Row movement is handled by rewriting directory information. A structure corresponding to reorganization or row movement is provided, and even if a BES is dynamically added, processing can be divided by taking over directory information and lock information.
In addition, in the case of replica management of a database, twice the storage area is required.
Regardless of whether or not the primary copy and backup copy are managed by the same IOS and BES, the access load to the disk is almost doubled. Therefore, the number of volumes for each division managed by the existing number of divisions is halved. And it is sufficient.

Furthermore, when a failure occurs in a disk, IOS, BES, etc., it is disconnected from the online processing and connected to online after recovery. The blocking management method differs depending on each node. When a disk failure occurs, the key range range stored on this disk is blocked. If a backup copy exists (the same IOS (mirror disk), it is necessary to acquire a backup copy under the management of another IOS (data replica)), the processing is distributed. When an IOS failure occurs, the key range range stored in the IOS is blocked. If a backup copy exists (the backup copy needs to be acquired under the management of another IOS (data replica)), the process is distributed. When a BES failure occurs, the key range managed by this BES is blocked. If IOS exists, B
After the ES is allocated, the lock information is taken over, and the dictionary information necessary for node allocation control is rewritten, the processing is continued.

The present invention is not limited to the combination of the rule using the statistical information and the cost evaluation. For example, the cost evaluation only, the rule use only, the cost evaluation and the like can be used as long as a processing procedure that provides appropriate database reference characteristic information is obtained. It can also be applied to a database management system that performs optimization processing such as combined use of rules.
The present invention can be realized via a tightly coupled / loosely coupled multiprocessor system software system of a large-scale computer, or a tightly coupled /
It can also be realized via a loosely coupled complex processor system. Further, even a single processor system is applicable if a parallel process is assigned for each processing procedure.
The present invention can also be applied to a configuration in which a plurality of processors share a plurality of disks with each other.

It is a block diagram which shows the parallel database system of one Embodiment of this invention. It is a conceptual diagram which shows the database division | segmentation management method of this invention. It is a conceptual diagram of optimal node allocation (when there is IOS) by the database division | segmentation management method of this invention. It is a conceptual diagram of optimal node allocation (when there is no IOS) by the database division | segmentation management method of this invention. It is a block diagram of FES. It is a block diagram of BES. It is a block diagram of IOS. It is a block diagram of DS. It is a block diagram of JS. It is a flowchart of a process of a system control part. It is a flowchart of an inquiry analysis process. It is a flowchart of a process of inquiry analysis. It is a flowchart of a static optimization process. It is a flowchart of the process of predicate selection rate estimation. It is a flowchart of the process of access path pruning. It is a flowchart of the process of a process procedure candidate production | generation. It is a flowchart of a process of code generation. It is a flowchart of an inquiry execution process. It is a flowchart of the process of dynamic optimization. It is a flowchart of the process of code interpretation execution. It is a flowchart of a data load process. It is a flowchart of a dynamic load control process. It is a conceptual diagram of dynamic load control.

Explanation of symbols

1 ... Parallel database system 10, 11 ... Application program,
20 ... Database management system 21 ... System control unit,
210 ... Data load processing, 210 ... Dynamic load control processing, 22 ... Logic processing section,
220 ... query analysis, 221 ... static optimization processing, 222 ... code generation,
223 ... Dynamic optimization processing, 224 ... Code interpretation execution 30 ... Operating system, 40 ... Database 70 ... IOS, 71 ... JS
72 ... DS 73 ... BES
75 ... FES, 80, 81, 82 ... Disc 90 ... Interconnection network

Claims (2)

A database division management system that associates a data storage area of a storage device with a plurality of data processing devices,
Means for dividing into a plurality of data storage areas based on the key value;
Means for associating a data processing device with the data storage area;
Means for creating directory information of the data storage area;
The correspondence between the data processing apparatus and the data storage area is changed by moving directory information corresponding to the data storage area between the corresponding data processing apparatuses in accordance with a change in the load of the data processing apparatus. Means
A database partition management system comprising: A database division management method for a database system that associates a data storage area of a storage device with a plurality of data processing devices,
The database system includes:
Divide into multiple data storage areas based on key values,
A data processing device is associated with the data storage area,
Create directory information for the data storage area,
The correspondence between the data processing apparatus and the data storage area is changed by moving directory information corresponding to the data storage area between the corresponding data processing apparatuses in accordance with a change in the load of the data processing apparatus.
A database partition management method characterized by the above .

Images