JP2015210665A - Data management system - Google Patents

Abstract

PROBLEM TO BE SOLVED: To improve a processing speed by effectively utilizing the multi-core of a CPU when an AP server and a DB server are achieved in the same casing.SOLUTION: A data management system 10 includes: a CPU package in which a plurality of CPU cores are incorporated and an external storage device 30 in which a plurality of tables are stored; an AP server 32 activated by the computer 11; and first DB server 36 to sixth DB server 46. A plurality of different CPU cores are exclusively assigned to the AP server 32 and each DB server. The AP server 32 includes: a function for specifying the type of data necessary for the execution of task processing; a function for requesting the extraction of necessary data to each DB server; and a function for performing necessary arithmetic processing to the data acquired from the DB server. When the extraction of data is requested to each DB server, SQL is issued in a state that the CPU core in charge of the processing is concretely designated.

Description

  The present invention relates to a data management system, and more particularly to a technique for coexisting an AP server function in charge of business processing and a DB server function in charge of database management in a single housing.

  Normally, the AP server in charge of business processing and the DB server that stores and manages various data resulting from business processing in a database are configured on separate server computers, and they are connected via a communication network. Has been done. For this reason, communication overhead by Ethernet (registered trademark) occurs between the AP server and the DB server, and high-speed processing cannot be realized (see Non-Patent Document 1).

Of course, by setting up the AP server program and the DP server program in a single server computer, it is possible to form the AP server and the DB server in the same chassis. The elimination itself can be realized.
However, in this case, since the AP server and the DB server compete for computer resources, the benefits of speeding up due to elimination of communication overhead are offset by half, resulting in overall processing speed. Could not be expected to improve dramatically.

  By the way, the improvement in computer processing power has previously depended mainly on the improvement of the CPU clock frequency, but since the problems of heat generation and power consumption have emerged, the CPU clock frequency has peaked around 2003-2005. It is in a state. Instead, in recent years, CPUs have become multi-core, and even if the CPU core has a relatively low number of clocks, it is possible to store 4 to 6 CPU cores in one package and parallelize the processing. It is expected to cope with the ever-increasing amount of data processing.

Certainly, in the case of science and technology calculations that are based on pure arithmetic processing, such as DNA analysis and meteorological analysis, the processing power can be improved relatively easily by linking many CPU cores. .
On the other hand, in the case of business processing that needs to refer to a large amount of data accumulated in the database in a complex combination, it takes time for data extraction processing and calculation processing in the DB server. It has been pointed out that even if the number of CPU cores of the server computer on which the DB server is mounted is increased, the processing speed cannot be improved immediately.

Have you forgotten the overhead? "Internet URL: http://itpro.nikkeibp.co.jp/free/TIS/denwa/20040517/1/ Search date: April 3, 2014

  The present invention has been devised in view of such a current situation. When the AP server and the DB server are realized in the same housing, the multi-core of the CPU is effectively used, so that the AP server and the DB server are The object is to provide a technique for dramatically improving the processing speed.

  In order to achieve the above object, a data management system according to a first aspect of the present invention is a computer including a CPU package including a plurality of CPU cores and an external storage device for storing a plurality of tables; An AP server configured by starting an AP server program and a plurality of DB servers configured by starting a plurality of DB server programs on the computer. A plurality of tables for storing data common to each are stored separately for each DB server, and each of the above AP server and each DB server is assigned a plurality of different CPU cores, respectively, The AP server relies on a function that identifies the type of data required for execution of business processing and the extraction of the necessary data for any of the above DB servers. At least a function to perform the necessary arithmetic processing on the data obtained from the DB server, and a function to request all the DB servers to register the data generated by the business process at the same time When requesting extraction or registration of data from the above DB server, SQL is issued with the CPU core in charge of the processing specifically specified, and in the DB server that receives this, the specified CPU core It is characterized by executing corresponding processing.

  A data management system according to a second aspect is the system according to the first aspect, wherein the computer further comprises a plurality of the CPU packages, and a dedicated local memory is allocated to each CPU package. CPU cores belonging to separate CPU packages are allocated to at least a part of the AP server and the DB server, and the AP server and each DB server are restricted to access only local memory. It is characterized by that.

  The data management system according to claim 3 is the system according to claim 1 or 2, and further, the AP server is only allowed to request each DB server to insert and extract data. In addition, there is a restriction that prohibits requests to update and delete data.

In the case of the data management system according to claim 1, since separate CPU cores are assigned exclusively to the AP server and the plurality of DB servers started on the computer, the AP is installed in the same casing. Even if the server and multiple DB servers coexist, there is no contention for CPU resources.
Moreover, each DB server has an independent table, and common data is stored in each table. Therefore, the AP server can request multiple DB servers to extract different data at the same time. The overall processing speed can be improved.
In addition, when issuing SQL for data extraction from the AP server to the DB server, it is done in a form that specifies the CPU core that should be in charge of the processing, so it is possible to draw out the potential of the multi-core CPU to the limit It becomes. In other words, since it is the AP server that knows the types of data required for business processing and the calculation procedure, if different processing can be performed in parallel, specify different CPU cores and request extraction of different data. As a result, the operating rate of the CPU core can be increased.
The AP server has a function to perform arithmetic processing on the data extracted from the DB server, so even if data is extracted separately from multiple DB servers, each data can be combined, sorted and matched Can be executed effectively on the AP server side.

  In the data management system according to the second aspect, the available memory of the AP server and each DB server is limited to the local memory, and access to the remote memory is prohibited, so that further processing speed can be realized.

  In the case of the data management system according to the third aspect, the processing performance on the DB server side is simplified, and as a result, the response performance from each DB server can be further improved.

  FIG. 1 shows a hardware configuration for realizing a data management system 10 according to the present invention. In a server computer 11, a first CPU package 12, a second CPU package 14, and a third CPU A motherboard 20 having a CPU package 16 and a fourth CPU package 18 is accommodated.

Since 15 CPU cores are formed in each CPU package, the server computer 11 has 60 CPU cores in total.
Each CPU package is attached to a CPU socket (not shown) provided on the motherboard 20.

A dedicated memory (local memory) is assigned to each CPU package.
That is, the first memory 22 is included in the first CPU package 12, the second memory 24 is included in the second CPU package 14, the third memory 26 is included in the third CPU package 16, and the fourth memory. The CPU package 18 is exclusively provided with a fourth memory 28.

  The server computer 11 is connected to an external storage device 30 made of SSD (Solid State Drive), and various tables (details will be described later) are stored in the external storage device 30. The external storage device 30 can be configured by a hard disk instead of the SSD.

Each CPU core is assigned a unique function in advance.
That is, the AP server 32 is exclusively assigned to the core (01) to the core (15) formed in the first CPU package 12.

A kernel thread (OS) 34 is exclusively assigned to the core 16 formed in the second CPU package 14.
A first DB server 36 is exclusively assigned to the cores (17) to (23) formed in the second CPU package.
Similarly, the second DB server 38 is exclusively assigned to the core (24) to the core (30) formed in the second CPU package.

A third DB server 40 is exclusively allocated to the cores (31) to (38) formed in the third CPU package 16.
Similarly, the fourth DB server 42 is exclusively allocated to the cores (39) to (45) formed in the third CPU package 16.

A fifth DB server 44 is exclusively allocated to the cores (46) to (53) formed in the fourth CPU package.
Similarly, the sixth DB server 46 is exclusively assigned to the cores (54) to (60) formed in the fourth CPU package.

  As described above, one AP server program and six DB server programs (instances) are started in one server computer 11, and each server has a dedicated number of CPU cores. Assigned to.

As described above, the AP server 32 has the first memory 22, the kernel thread (OS) 34, the first DB server 36, and the second DB server 38 have the second memory 24, and the third A third memory 26 is allocated exclusively to the DB server 40 and the fourth DB server 42, and a fourth memory 28 is allocated exclusively to the fifth DB server 44 and the sixth DB server 46 as local memories. ing.
In this case, each CPU core can access the local memory directly (at high speed). However, in order to access the remote memory allocated to another CPU package, it is necessary to go through the CPU core belonging to the CPU package. There arises a problem that the processing speed decreases accordingly.
Therefore, in this system 10, each CPU core can access only to local memory, and access to remote memory under other CPU packages is prohibited.

  Such allocation and restrictions are realized by inputting the following “NUMA control” command when the OS is started.

[1] Allocation for AP server "#numactl
-physcpubind = 1-15 ← Assign cores (01)-(15)
-membind = 0 ← first memory allocation
nice -n -5 ← Specifying process priority
java -jar aloha.jar "<-Starting the AP server process (Java)

[2] Allocation for the first DB server “#numactl
-physcpubind = 17-23 ← Assign cores (17)-(23)
-membind = 1 ← Allocate second memory
nice -n -5 ← Specifying process priority
su -postgres <-Switch the process execution user to postgres (su)
-c "pg_ctl -D data1 start"<-Start of the first DB server process (database for data area data1) "

[3] Allocation for the second DB server “#numactl
-physcpubind = 24-30 ← Assign cores (24)-(30)
-membind = 1 ← Allocate second memory
nice -n -5 ← Specifying process priority
su -postgres <-Switch the process execution user to postgres (su)
-c "pg_ctl -D data2 start"<-Start the second DB server process (database for data area data2) "

[4] Allocation for the third DB server “#numactl
-physcpubind = 31-38 ← Assign cores (31)-(38)
-membind = 2 ← Third memory allocation
nice -n -5 ← Specifying process priority
su -postgres <-Switch the process execution user to postgres (su)
-c "pg_ctl -D data3start"<-Starting the third DB server process (database for data area data3) "

[5] Allocation for the fourth DB server “#numactl
-physcpubind = 39-45 ← Assign cores (39)-(45)
-membind = 2 ← Third memory allocation
nice -n -5 ← Specifying process priority
su -postgres <-Switch the process execution user to postgres (su)
-c "pg_ctl -D data4 start"<-Start the fourth DB server process (database for data area data4) "

[6] Allocation for the fifth DB server "#numactl
-physcpubind = 46-53 ← Assignment of cores (46)-(53)
-membind = 3 ← 4th memory allocation
nice -n -5 ← Specifying process priority
su -postgres <-Switch the process execution user to postgres (su)
-c "pg_ctl -D data5 start"<-Starting the fifth DB server process (database for data area data5) "

[7] Allocation for the sixth DB server "#numactl
-physcpubind = 54-60 ← Assign cores (54)-(60)
-membind = 3 ← 4th memory allocation
nice -n -5 ← Specifying process priority
su -postgres <-Switch the process execution user to postgres (su)
-c "pg_ctl -D data6 start"<-Start of the sixth DB server process (database for data area data6) "

FIG. 2 is a block diagram showing a functional configuration of the data management system 10. In the AP server 32, a screen processing unit 52, a processing content specifying unit 54, a DB processing allocation unit 56, and an arithmetic processing unit 58 are provided. Is formed.
A plurality of client terminals 62 are connected to the screen processing unit 52 via a communication network 60 such as the Internet.

  The screen processing unit 52, the processing content specifying unit 54, the DB processing allocation unit 56, and the arithmetic processing unit 58 are configured such that the CPU core (01) to the core (15) execute predetermined processing in accordance with an application program for business processing. In this case, multitask processing is realized by activating a plurality of threads by the OS or middleware and assigning them to each CPU core.

FIG. 3 shows the correspondence between each CPU core and a plurality of threads 64. Each thread 64 is provided with a thread pool 66, and the threads 64 sequentially process a plurality of tasks 68 arranged there. The image to go is drawn.
The thread 64 in this figure functions as the screen processing unit 52, the processing content specifying unit 54, the DB processing allocation unit 56, and the arithmetic processing unit 58 of the AP server 32, and the specific processing executed by each of these functional components is as follows. Corresponds to task 68.

  Each thread 64 executes the tasks 68 accumulated in the thread pool 66 one after another in the oldest order, and when its own thread pool 66 becomes empty, it is accumulated in the thread pool 66 of the other thread 64 Task 68 is executed in order from the newest.

  The DB processing allocation unit 56 has a function of issuing SQL to the first DB server 36 to the sixth DB server 46 and instructing extraction (selection) and addition (insertion) of data necessary for business processing. .

The DB processing allocation unit 56 and each DB server are connected via the number of connection pools 70 corresponding to the number of CPU cores.
For example, since seven CPU cores are assigned to the first DB server 36, seven connection pools 70 are provided, and each connection pool 70 is associated with a specific CPU core in advance.
Further, since eight CPU cores are allocated to the fifth DB server 44, eight connection pools 70 are provided, and each connection pool 70 is associated with a specific CPU core in advance.

In the external storage device 30, for example, a purchase table 72, an order table 73, an order details table 74, an order cancellation table 75, an item table 76, a finished product table 77, a raw material table 78, a raw material inventory table 79, a cost table 80 A plurality of table groups including the supplier table 81 and the like are stored for each DB server.
Here, since the first DB server 36 to the sixth DB server 46 are provided, six sets of table groups A to F are dedicated to each DB server in separate areas in the external storage device 30. Stored.
Also, as will be described later, the same record belonging to each table group stores the same record at the same time.

FIG. 4 illustrates the configuration of records stored in each table.
That is, the purchase table 72 stores records having data items of “purchase ID”, “cost ID”, and “quantity”.
Further, the order table 73 stores a record having data items of “order ID” and “customer ID”.
The order detail table 74 stores records having data items of “order detail ID”, “order ID”, “item ID”, and “quantity”.
The order cancellation table 75 stores a record including a data item of “order details ID”.

The item table 76 stores a record having a data item of “item ID”.
The finished product table 77 stores a record having a data item of “item ID”.
The raw material table 78 stores a record having a data item of “item ID”.
The raw material inventory table 79 stores records having data items of “raw material inventory ID”, “parent item ID”, “child item ID”, and “quantity”.
The cost table 80 stores records having data items of “cost ID”, “supplier item ID”, and “cost”.
The supplier table 81 stores a record including data items of “supplier item ID”, “supplier ID”, and “item ID”.

With respect to each of the tables 72 to 81, the following restrictions (1) to (3) are imposed in advance.
(1) The key item is limited to one.
Therefore, it is prohibited to configure a key item (composite key) by combining a plurality of items.

(2) When the AP server 32 accesses each table, only reference and addition of records are permitted, and deletion and update of existing records are prohibited.

  For this reason, when it becomes necessary to invalidate the existing data, a state equivalent to the deletion is realized by adding a new record to another table instead of deleting the record. For example, when an order is canceled, the status is generally managed by describing a value indicating “cancel” in the order data subject to cancellation. The fact of “cancel” is expressed by adding the record of “order item ID” as a target to the cancel table 75.

Further, when a change occurs in the value of an existing record, a value update is substantially realized by adding a record of a different primary key having a corrected value to the same table. For example, when the quantity of a certain record stored in the order detail table 74 is changed from “3” to “5”, a new record with the quantity “5” is added to the order detail table 74. Each record is stamped with a millisecond precision time stamp, and comparing this makes it clearer between the old and new records (update facts).

(3) A NULL value prohibition constraint, a flag value prohibition constraint, and a partition value prohibition constraint are imposed on the columns of each table.
First, "NULL value prohibition constraint" means that it is prohibited to fill NULL (no value) as the value of the data item, and the setting of the data item assuming such filling of the NULL value itself Is not allowed.
Next, “flag value prohibition constraint” prohibits filling of flag values (binary data such as “1/0”, “ON / OFF”, “TRUE / FALSE”, etc.) as data item values. This means that setting of data items assuming such filling of flag values is not allowed.
“Category value prohibition constraint” means that it is prohibited to fill a data item value with a category value (“1: regular employee”, “2: part”, “3: part-time job”, etc.). Therefore, the setting of the data items assuming such filling of the segment values is not allowed.

As described above, deletion and update of records by the AP server 32 is prohibited, so that data at any time in the past will be preserved in each table, and if it is necessary to reproduce the change history in the future There is an advantage that can also respond.
However, if enough time has passed and the need for data retention is completely gone, records can be deleted by logging in directly to each DB server and issuing a delete SQL.

  In the case of this system 10, the individual records stored in each table are normalized to the limit as described above, and arithmetic processing between data is performed by the arithmetic processing unit 58 of the AP server 32, and on the DB server side Since redundant intermediate data is not generated by the arithmetic processing, even if it is handled by inserting a record instead of deleting or updating a record, the disk capacity is not squeezed compared to the conventional case. In addition, the cache memory hit rate is improved, which contributes to speeding up the processing of the DB server.

  These constraint rules are checked for suitability by the database management system of each DB server at the time of designing a new table or issuing an SQL statement, and the execution of processing that violates the constraint rules is rejected, ensuring its effectiveness. Is done.

In a situation where business data is managed by such a group of tables, a user who wishes to register new data transmits data through a data input screen (not shown) transmitted from the screen processing unit 52 to the client terminal 62. input.

The processing content identification unit 54 that has received this input data from the screen processing unit 52 requests the DB processing allocation unit 56 to register the data.
The DB process allocation unit 56 issues a SQL requesting registration of the input data to the first DB server 36 to the sixth DB server 46. At this time, the DB processing allocation unit 56 issues a SQL to each DB server through a connection pool associated with a specific CPU core, thereby designating a responsible CPU core in each DB server.

In each DB server that has received this, the CPU core specified by the DB process allocation unit 56 inserts records into the corresponding tables all at once.
As a result, the same record is stored in duplicate in the same name table managed independently by each DB server.

  Next, a processing procedure when an inquiry request is transmitted from the client terminal regarding the number of stocks of a certain finished product (for example, the item ID “NRI-0001”) will be described with reference to the flowcharts of FIGS.

  First, when the user specifies an inventory search for the item ID “NRI-0001”, which is a finished product, on the search condition specification screen transmitted from the screen processing unit 52, the processing necessary for realizing this search request is performed by the processing content specifying unit. 54 (S10 in FIG. 5).

Specifically, after the following processing content is specified, it is sequentially executed.
(01) All records having the item ID “NRI-0001” are extracted from the order details table (S12).
(02) The cumulative order quantity of the item ID “NRI-0001” is calculated (S14).
(03) All records having the item ID “NRI-0001” are extracted from the order cancellation table (S16).
(04) The cumulative order cancellation quantity of the item ID “NRI-0001” is calculated (S18).
(05) The cumulative order cancellation quantity is subtracted from the cumulative order quantity of the item ID “NRI-0001” to calculate the current cumulative actual order quantity (S20).
(06) Referring to the raw material inventory table, the child item ID having the item ID “NRI-0001” as the parent item ID and its quantity are acquired (S22). This child item ID indicates the raw material of the item ID “NRI-0001” which is a finished product.
(07) The supplier item ID is obtained from the supplier table using each child item ID as a key (S24 in FIG. 6).
(08) The cost ID is acquired from the cost table using the supplier item ID as a key (S26).
(09) All records relating to the raw material are extracted from the purchase table using the cost ID as a key (S28).
(10) The cumulative purchase quantity is calculated for each raw material (S30).
(11) The producible quantity of the item ID “NRI-0001” is calculated based on the cumulative purchase quantity of each raw material (S32).
(12) By subtracting the accumulated actual order quantity from the production possible quantity, the current inventory quantity of the finished product is calculated (S34 in FIG. 5).
(13) Finally, a processing result screen describing the inventory quantity of “NRI-0001” is transmitted to the client terminal 62 (S36).

Among these processes, the data extraction process is executed by issuing an SQL from the DB process allocation unit 56 to each DB server. At this time, the DB process allocation unit 56 transmits the SQL to each DB server through a connection pool associated with a specific CPU core. As a result, the record extraction process in the DB server is executed by the designated CPU core.
Further, the arithmetic processing unit 58 performs necessary arithmetic processing such as sorting, merging, matching, and control break on the data transmitted from each DB server.

By the way, in each of the above-mentioned processes, there are a process that cannot be executed unless the preceding process is completed, and a process that can be executed in parallel.
For example, as shown in FIG. 5, the process of S12 for extracting the record of the corresponding item ID from the order detail table 74, the process of S16 for extracting the record of the corresponding item ID from the order cancellation table 75, and the raw material inventory table 79 , And the process of S22 for acquiring the child item ID and the quantity having the corresponding item ID as the parent item ID can be performed simultaneously when the item ID is specified.
For this reason, the DB process allocation unit 56 designates a separate CPU core for each of the above processes and requests an extraction process from any DB server.

In addition, when the types of raw materials necessary for producing a finished product are specified by the processing of S22, as shown in FIG. 6, data extraction processing for calculating the purchased quantity of each raw material is performed. However, S24 to S28 are also processed in parallel for each raw material.
For example, when the number of raw materials is 50, the DB processing allocation unit 56 designates 50 different CPU cores and requests each DB server to extract records.
As described above, data can be efficiently referred to by directly allocating specific record extractions to a specific CPU core.

In addition, when the number of types of raw materials reaches several hundreds or more, processing of raw material units cannot be assigned to different CPU cores at once.
In such a case, the DB process assigning unit 56 requests each CPU core to process each raw material in order, and repeats assigning the remaining raw material unit processes in order to the CPU core when the process is completed.

  Since the application program of the AP server 32 recognizes the types of data and extraction procedures necessary to implement a specific business process, a specific record extraction is performed by specifying the CPU core for each DB server. By making a request, it is possible to extract the potential of the multi-core CPU most effectively.

  Note that the OS has a function to deliberately reduce the number of CPU clocks in the middle of processing in order to achieve data processing efficiency and power saving based on statistical information. It is desirable to disable this kind of extra service in advance because it is an obstacle to finely control the processing contents in units of CPU cores.

It is a schematic diagram which shows the hardware constitutions for implement | achieving the data management system which concerns on this invention. It is a block diagram which shows the function structure of the data management system which concerns on this invention. 3 is a schematic diagram showing a correspondence relationship between each CPU core of an AP server and a plurality of threads 64. FIG. It is a figure which shows the structural example of the record stored in each table. It is a flowchart which shows the process sequence in this system. It is a flowchart which shows the process sequence in this system.

10 Data management system
11 Server computer
12 First package
14 Second package
16 Third package
18 Fourth package
20 Motherboard
22 First memory
24 Second memory
26 Third memory
28 Fourth memory
30 External storage
32 AP server
36 First DB server
38 Second DB server
40 Third DB server
42 Fourth DB server
44 Fifth DB server
46 6th DB server
52 Screen processor
54 Processing content identification part
56 DB processing allocation part
58 Arithmetic processing section
60 Communication network
62 Client terminal
64 threads
66 Thread Pool
68 tasks
70 Connection pool
72 Purchase table
73 Order table
74 Order details table
75 Order cancellation table
76 Material table
77 Finished product table
78 Raw material table
79 Raw material inventory table
80 Cost table
81 Supplier table

Claims (3)

A computer package having a CPU package incorporating a plurality of CPU cores and an external storage device for storing a plurality of tables;
An AP server configured by starting an AP server program on this computer;
A plurality of DB servers configured by starting a plurality of DB server programs on the computer;
In the above external storage device, a plurality of tables storing data common to each other are stored separately for each DB server,
A plurality of different CPU cores are assigned exclusively to the above AP server and each DB server,
The AP server has a function to identify the type of data required for execution of business processing,
A function to request extraction of necessary data from any of the above DB servers;
A function for performing necessary arithmetic processing on the data acquired from the DB server;
It has at least the function to request the registration of data generated by business processing to all DB servers at the same time
When requesting extraction or registration of data from the above DB server, issue a SQL with the CPU core in charge of the processing specified specifically,
A data management system in which the specified CPU core executes the corresponding processing in the DB server that receives this. The computer includes a plurality of the CPU packages,
Each CPU package is assigned a dedicated local memory,
CPU cores belonging to separate CPU packages are allocated to at least a part of the AP server and the DB server,
2. The AP server and each DB server are restricted to be able to access only local memory and not to access local memory dedicated to other CPU packages. The data management system described.   The AP server is only allowed to request insertion and extraction of data from each of the above DB servers, and has a restriction that prohibits requests to update and delete data. The database management system according to claim 1 or 2.

Images