JP2013235328A - Data management system - Google Patents

Abstract

PROBLEM TO BE SOLVED: To provide a data management system for maintaining data at a plurality of base points, and for validly utilizing a facility at each base point even in a normal time.SOLUTION: The data management system includes: AP servers 16 installed at a plurality of base points; a plurality of DB servers 18 installed at each base point; and a plurality of management servers 12. Each DB server includes a table for storing data which are common at all base points. In the case of adding data, the registration of additional data is commanded from any one management server to the AP server at each base point, and each AP server requests the registration of the additional data to each DB server in the same base point, and each DB server inserts the additional data into the table. In the case of searching data, any one management server transmits search conditions to the AP server at any one base point, and the AP server divides the search conditions into a plurality of portions, and issues the divided search conditions to each DB server in the same base point, and each DB server extracts the corresponding data from the table, and transmits the data to the AP server, and the AP server integrates the data transmitted from each DB server, and transmits the data to the management sever.

Description

  The present invention relates to a data management system, and more particularly to a technique for distributing the risk of losing business data due to the occurrence of a large-scale disaster or the like by storing business data in multiple servers installed at a plurality of locations.

In the case of a large-scale disaster such as an earthquake, tsunami, or eruption, or when a database is physically damaged due to a nuclear disaster or terrorist or other man-made disaster, a corporate database is a backup database in which business data is installed remotely. Duplicate registration has been done for a long time.
For example, Patent Document 1 discloses a technique for registering data in a disk device of a primary control device, and then transmitting the same data to a sub-control device installed at a remote place and requesting the registration.
JP-A-11-85408

In this way, by holding data twice by the primary and secondary control devices, even if an earthquake or the like occurs in one area and the disk device is physically damaged, the other disk device that survived By utilizing the data inside, it becomes possible to ensure the continuity of business processing.
However, in the case of the conventional data maintenance technology, the secondary control device is provided as insurance for emergency situations only, and it is not used effectively in normal times, and its inefficiency has been a problem. .

  The present invention has been devised in view of such a current situation, and important business data can be reliably maintained at a plurality of bases, and facilities at each base are effective not only in an emergency but also in a normal time. It aims to provide a data management system that can be used.

  In order to achieve the above object, the data management system according to claim 1 includes a plurality of AP servers installed at a plurality of bases spaced apart from each other by a predetermined distance, and a plurality of AP servers installed at each of the bases. A DB server connected to the AP server via the network, and a plurality of management servers connected to the AP server at each site via the network. Each AP server includes an additional data replication unit, a plurality of Registration processing unit, search condition dividing unit, multiple search processing units, multiple data processing units, and search result integration unit, each DB server described above holds data common to all sites Each table is provided with a restriction that the key item is limited to one and a restriction that data updating is prohibited. When the additional data is transmitted to the management server, the management server executes processing for instructing the AP server arranged at each site to register the additional data, and the additional data replication unit of each AP server A process of assigning a copy of the transmitted additional data and a data addition request including specific information of the DB server arranged in the same site to each of the above registration processing units, and each of the above registration processing units, Generate a SQL statement that requests registration of the additional data and execute a process that is issued to the DB server assigned to itself, and each DP server executes a process of inserting a record corresponding to the additional data into the table. When a search condition is transmitted from a client terminal to any management server, the management server executes a process of transmitting the search condition to an AP server in any one of the bases. The search condition dividing unit analyzes the search condition transmitted from the management server and divides it into a plurality of search conditions, and executes a process of assigning each search condition and a responsible DB server to the plurality of search processing units, Each search processing unit generates a SQL statement corresponding to the search condition assigned to itself, issues it to the DB server assigned to itself, and sends a certain amount of search result data from the DB server. In addition, each time the required arithmetic processing is assigned to each data processing unit and the partial processing result data is returned from each data processing unit, it is stored in the memory and transmitted from the DB server. When the processing to delete the processed data from the memory and the data transmission from the DB server are completed, and all the partial processing result data based on the partial search result data are prepared, A process of counting and executing the process of outputting the counting result to the search result integrating unit, wherein the search result integrating unit totals the counting result passed from each search processing unit, and transmits it to the management server as a search result And the management server executes a process of transmitting the data transmitted from the AP server to the client terminal as a search result.

  The data management system according to claim 2 is the system according to claim 1, wherein the search condition dividing unit further performs processing when the input search condition includes a temporal range. It is characterized by dividing into shorter time ranges.

  The data management system according to claim 3 is the system according to claim 1, wherein the search condition dividing unit further performs processing when the input search condition includes a regional range. It is characterized by dividing it into narrower regional areas.

  A data management system according to a fourth aspect is the system according to the first to third aspects, further comprising an index server having an index generation unit and an index storage unit, and each of the above DB servers includes: The database management system includes a temporary storage area and a permanent storage area. The database management system stores the additional data transmitted from the AP server in the temporary storage area and temporarily stores the data at a predetermined time interval. A process of moving the additional data in the area to the permanent storage area, and the index generation unit of the index server acquires the additional data stored in the temporary storage area of the DB server at a predetermined time interval. , Generate an index based on this additional data, and execute the process of storing in the index storage unit, Each search processing unit of the AP server refers to the index storage unit of the index server when generating the SQL statement corresponding to the search condition assigned to itself, and determines the primary key of each data corresponding to the search condition. It is characterized by executing the process of acquiring and the process of generating the SQL statement specifying the acquisition target data by this primary key.

  A data management system according to a fifth aspect is the system according to the first to fourth aspects, wherein each of the DB servers further includes an update log storage area provided on a memory, and the database The management system is characterized by executing processing for sequentially storing additional data transmitted from the AP server in the update log storage area.

In the case of the data management system according to claim 1, when data is added, the addition of data to a plurality of DB servers arranged in the same base is executed simultaneously via an AP server installed in the plurality of bases. Because it has a mechanism, even if one of the sites suffers catastrophic damage due to a large-scale disaster, business processing can be continued seamlessly based on the data in the DB server located at the other site It becomes.
Since a plurality of management servers having the same function are also prepared, risk distribution can be achieved even at the management server level by installing each of them at a location that is geographically separated by a sufficient distance.

  In addition, there is no difference between primary and secondary equipment at each base, and the management server can select a base freely in consideration of the latest processing results and distance during the search process. Effective utilization of the installed facilities can be achieved.

  In addition, the normalization of each table managed by the DB server is pursued to the limit, and the structure is simplified, while the processing of data extracted from the table (sorting, matching, control break, etc.) is performed on the AP server side Since the DB server can concentrate on data in / out, even if the number of SQL issues increases as the number of tables increases and the relationship between tables increases, the burden on the DB server can be suppressed. It becomes possible.

  In addition, since multiple DB servers with the same data are physically prepared and the search conditions are distributed to each DB server during search processing, data extraction processing in each DB server is reduced. As a result, the processing speed of the entire system can be improved.

  Moreover, in the AP server, the input search condition is divided into a plurality of parts, and a search request for data is transmitted to the DB server via each of the plurality of search processing units, and the DB server sends each search processing unit to each search processing unit. The returned search result data is also processed in parallel by multiple data processing units in a fixed unit, so even if a large amount of data is sent from the DB server, many CPU cores By using, efficient processing becomes possible. In addition, without waiting until all the search result data from the DB server is complete in this way, by shifting to partial processing when a certain amount of data is complete, the delay due to DISK / IO on the DB server side Can be relaxed.

Until now, unlike scientific and engineering calculations that are purely computational, such as DNA analysis and meteorological analysis, in the case of business processing that needs to refer to a large amount of data stored in the database, from the DB server DISK / IO that waits for the response to the bottleneck becomes a bottleneck, so even if the number of CPU cores on the AP server side is increased, there is a problem that the processing speed is not immediately improved.
On the other hand, by providing the above mechanism in the AP server, it became possible to cover the increase in the burden on the DB server side due to the pursuit of table normalization by improving the efficiency of processing on the AP server side. .

In the case of the data management system according to the fourth aspect, since the index (secondary index) creation process is transferred to an external independent index server, the burden on the DB server side associated therewith can be eliminated.
In addition, when the data is referenced, an SQL server that specifies the primary key of each data is issued from the AP server, so there is no need to sort the table on the DB server side, which can reduce the load on the DB server. it can.

In the case of the data management system according to claim 5, since the update log of the DB server is stored in the memory, the DISK / on the DB server side is compared with the case where the update log is stored on the disk. IO can be reduced.
In addition, a plurality of DB servers are physically prepared, and the same additional data is stored in the update log storage area as an update log in each DB server. Even if the update log disappears in, it can be easily recovered by the update log stored in another DB server.

FIG. 1 shows an overall configuration of a data management system 10 according to the present invention, which includes a management server 12, an AP server 16, and a plurality of DB servers 18 respectively arranged at a plurality of bases.
The management server 12 and the AP server 16 and the AP server 16 and the DB server 18 in the same base are connected via a communication network such as a LAN.
In addition, the management server 12 and the AP server 16 at different bases are connected via a communication network 20 such as a WAN.

A client terminal 22 operated by a user is connected to each management server 12 via a communication network 20 such as the Internet.
The client terminal 22 is composed of a computer such as a PC, and is loaded with a program such as an OS and a web browser.
Each client terminal 22 is basically set on the Web browser to access the management server 12 installed at the nearest base, but the base falls into malfunction due to the occurrence of a large-scale disaster, etc. When there is no response within a predetermined time, the access destination is provided with a function of automatically or manually switching to the management server 12 in another base.

Each base corresponds to a data center or the like provided at a geographical location at a predetermined distance. For example, in FIG. 1, a first base 14a is provided in Tokyo and a second base 14b is provided in Osaka.
The number of bases is not limited to two, and it is desirable that more bases (Sapporo, Nagoya, Fukuoka, Naha, etc.) are provided from the viewpoint of risk distribution due to disasters.

For convenience of illustration, one management server 12 and one AP server 16 are depicted in each of the bases 14a and 14b, but actually, a plurality of management servers 12 and a plurality of AP servers 16 are installed, The load is balanced by the load balancer. Further, the number of DB servers 18 installed at each base 14a, 14b is not limited to three, and actually four or more DB servers 18 are installed.
Each DB server 18 includes a plurality of table groups storing data common to all the DB servers 18 and a database management system (RDBMS).

  FIG. 2 shows a specific example of a table group stored in each DB server 18, and includes a customer management table 24, a reservation management table 26, a sales management table 28, a reservation cancellation management table 30, and a billing management table. 32, the sales of each store are managed.

The customer management table 24 includes data items of “customer ID (primary key)” and “region”.
The reservation management table 26 includes data items of “reservation ID (primary key)”, “store ID”, “customer ID (external key)”, “amount”, and “reservation date”.
The sales management table 28 includes data items of “reservation ID (primary key / foreign key)” and “sales date”.
The reservation cancellation management table 30 includes data items of “reservation ID (primary key / foreign key)” and “reservation cancellation date”.
The billing management table 32 includes data items of “billing ID (primary key)”, “billing date”, and “reservation ID (foreign key)”.

The following restrictions (1) to (3) are imposed on the respective tables 24 to 32 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) Only addition of records is allowed in each table, and updating of existing record values and deletion of records are prohibited.
Therefore, information having different generation timings is stored in a separate table, such as when a value has changed.

(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.

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

As apparent from FIG. 2, each table has a primary key (PK) constituted by a single data item.
In addition, data relating to “reservation cancellation” is usually provided with data items such as “reservation cancellation flag” in the reservation management table 26, and each record is filled with a flag value such as “1/0”. Although the reservation cancellation management table 30 is provided separately from the reservation management table 26, reservation cancellation status management is performed. That is, the reservation ID registered in the reservation cancellation management table 30 is in the “reservation cancellation” state, and the presence or absence of the reservation cancellation is expressed by the presence or absence of the record.

Since each table has the restriction (2) (restriction of value update), for example, even if there is a change in the “region” of a customer, the “region” of the corresponding record in the customer management table 24 The value is never rewritten.
Although illustration is omitted, in such a case, for example, a “customer region change management table” including data items such as “customer ID”, “change region”, “change date” is newly provided, Stores the customer ID of the customer, the area after the change, and the date of change.

  As a result, if it is necessary to aggregate sales by customer region, AP server 16 refers to customer management table 24 to obtain each customer's region information, and then refers to customer region change management table to determine the region. A process for identifying a customer who has changed and replacing it with the area after the change is executed.

FIG. 3 shows a functional configuration in a situation where a data addition request transmitted from the client terminal 22 to any one of the management servers 12 is stored in each DB server 18 in each base 14a, 14b. The server 12 includes an additional data receiving unit 34 and a plurality of additional data transmitting units 36 corresponding to the number of locations.
Each AP server 16 includes an additional data replication unit 38 and a plurality of registration processing units 40 corresponding to the number of DB servers 18.

FIG. 4 shows a situation in which a search request transmitted from the client terminal 22 to any management server 12 is transmitted to each DB server 18 in the same site via the management server 12 and the AP server 16 at one site. The management server 12 includes a search condition transmission unit 42.
The AP server 16 also includes a search condition dividing unit 44 and a plurality of search processing units 46 corresponding to the number of DB servers 18.

FIG. 5 shows a functional configuration in a case where a search result is transmitted from the DB server 18. The AP server 16 includes a plurality of data processing units 47 assigned to each search processing unit 46, and a search result integrating unit. Has 48.
The management server 12 also includes a search result transmission unit 50.

  The additional data reception unit 34, the additional data transmission unit 36, the search condition transmission unit 42, and the search result transmission unit 50 of each management server 12 have a predetermined number of CPU cores mounted on the management server 12 according to a dedicated application program. This process is realized by executing the process. At this time, a plurality of threads are activated by the OS or dedicated middleware, and a task is assigned to each CPU core, thereby realizing multitask processing.

  In addition, the additional data replication unit 38, the registration processing unit 40, the search condition division unit 44, the search processing unit 46, the data processing unit 47, and the search result integration unit 48 of each AP server 16 are installed in each AP server 16. CPU cores are realized by executing predetermined processing according to a dedicated application program. At this time, multiple threads are started by the OS or dedicated middleware, and tasks are assigned to each CPU core. Thus, multitask processing is realized.

FIG. 6 shows the correspondence between the CPU core 52 and the thread 54. Each thread 54 has a thread pool 56, and the thread 54 sequentially processes a plurality of tasks 58 arranged there. Is drawn.
The thread 54 in this figure functions as the additional data reception unit 34, additional data transmission unit 36, search condition transmission unit 42, search result transmission unit 50 of the management server 12, or additional data replication unit 38 of the AP server 16, registration The specific processing executed by each of these functional components corresponds to the task 58, which functions as the processing unit 40, the search condition dividing unit 44, the search processing unit 46, the data processing unit 47, and the search result integration unit 48.

  Each thread 54 executes the tasks 58 accumulated in the thread pool 56 one after another from the oldest, and when its own thread pool 56 becomes empty, it is accumulated in the thread pool 56 of the other thread 54 Tasks 58 are executed in order from the newest.

Next, according to the flowchart of FIG. 7 and the block diagram of FIG.
First, when the management server 12 receives a data addition request transmitted from the client terminal 22 (S10), the additional data receiving unit 34 copies the additional data for the number of locations (S12), and each additional data transmitting unit 36 To pass.
Each additional data transmission unit 36 transmits the received additional data to the AP server 16 at the site that it is in charge of (S14).

Upon receiving this, the additional data replication unit 38 of each AP server 16 (S16) copies the additional data as many as the number of DB servers 18 (S18), and passes each to the registration processing unit 40.
Each registration processing unit 40 issues an SQL statement for requesting registration of the additional data to the DB server 18 that it is in charge of (S20).
Receiving this (S22), each DB server 18 adds corresponding records to the corresponding table all at once (S24). As a result, the identity of the data managed by all DB servers 18 at all bases is ensured.

Next, a processing procedure at the time of data search of the system 10 will be described according to the flowcharts of FIGS. 8 to 10 and the block diagrams of FIGS.
First, when the management server 12 in the first base 14a receives the search condition transmitted from the client terminal 22 (S30 in FIG. 8), the search condition transmission unit 42 determines one AP server 16 in charge of the search process. Then, the search condition is transmitted to the AP server 16 (S34).
Normally, the AP server 16 located in the same base 14a is selected, but if the AP server 16 is not operating for some reason, the search condition transmission unit 42 of the management server 12 In consideration of the physical distance, the congestion status of the communication network, the past results, etc., the AP server 16 of the base that has been determined that the search result returns the earliest at the present time is selected.

  When the AP server 16 at the first site 14a receives the above search conditions (S36), the search condition dividing unit 44 analyzes the search conditions and divides them into a plurality of search conditions according to the contents of the search conditions (S38). .

Here, the criteria for the division are those that can logically divide the search target data into a plurality of data such as time (day, week, month, year, etc.) and region (prefecture, municipality, etc.).
For example, if the search condition “aggregate sales of all chain stores in 2010” is given, “sales of all chain stores for January 2010”, “sales of all chain stores in February 2010” ”,“ Sales of all chain stores in March 2010 ”, etc., and the year is divided into 12 units per month.
Focusing on the location data of each chain store, “Sales of chain stores in Tokyo in 2010”, “Sales of chain stores in Hokkaido in 2010”, “Sales of chain stores in Okinawa in 2010”… In addition, dividing the whole country into 47 prefectures is also applicable.
Of course, it can also be divided into 564 by month × prefectural unit, such as “sales of chain stores in Tokyo for January 2010” and “sales of chain stores in Hokkaido in January 2010”.

  These division criteria are determined in advance by the program designer in consideration of the search condition predicted to be issued from the client terminal 22, the amount of target data, and the like. It is coded in the program to realize 44.

Here, the description will be continued on the assumption that the client terminal 22 has transmitted a search request with the content “Aggregate bills for August of store A for each region”.
First, according to the calendar information “August = 31 days”, the search condition dividing unit 44 “for August 1”, “for August 2”, “for August 3” ... “for August 31” The search condition is divided into 31 parts.

  Next, the search condition dividing unit 44 selects each of the divided search conditions (such as “Aggregate the amount billed for August 1 of store A for each region”) and the corresponding DB server 18 to be in charge of 31 The search processing units 46 are assigned (S40).

  On the other hand, each search processing unit 46 generates an SQL statement for acquiring the billing data from the billing management table 32 specifying the billing date assigned to itself, and issues it to the DB server 18 in charge of the search processing unit 46 ( S42 in FIG. 9).

  Then, when the billing data including the billing date of the corresponding day is transmitted from the DB server 18, the search processing unit 46 does not wait for all the search result data to be transmitted from the corresponding DB server 18, When a predetermined amount of preset data (for example, equivalent to 1,000 records) is transmitted (S44 / Y), necessary arithmetic processing is assigned to a plurality of data processing units 47 (S46).

Specifically, the following processing is executed by the data processing unit 47.
(1) An SQL statement specifying the “reservation ID” of each billing data is generated and issued to the DB server 18, and the corresponding reservation data is acquired from the “reservation management table 26”.
(2) In the transmitted reservation data, only the data having the “store ID” of the corresponding store is extracted, and the data of other store IDs are excluded.
(3) A SQL statement specifying the “customer ID” of each reservation data is generated and issued to the DB server 18, and the corresponding customer data is acquired from the customer management table 24.
(4) For each “region” of customer data, the “amount” value in the reservation data is aggregated.
Specifically, the processes (1) to (4) are arranged in the thread pool 56 of each data processing unit 47 as a task 58.

The search processing unit 46 stores the processing result per unit data amount in the memory when the processing result data in units of 1, OOO is returned from the data processing unit 47 (S48 / Y), and the DB server 18 The data transmitted from is deleted from the memory (S50).
Each search processing unit 46 repeats the processing of S46 to S50 every time the data transmitted from the DB server 18 reaches a certain amount until the data transmission from the DB server 18 is completed (S52 / N, S44 / Y). .
Then, when the data transmission from the DB server 18 is completed and the last processing of S46 to S50 is completed and the processing result data for one day is prepared (S52 / Y), the search processing unit 46 has executed the previous processing. The total values of the partial processing results are totaled (S54), and the total results are output to the search result integration unit 48 (S56).

Receiving this, the search result integrating unit 48 (S58 in FIG. 10) checks whether or not the total results from all the search processing units 44 have been prepared each time the total results are transmitted from each search processing unit 44. (S60) When all of them have been prepared, the total results of all the search processing units 44 are totaled (S62).
Then, the final count result is transmitted to the management server 12 (S64).
Receiving this, the search result transmission unit 50 of the management server 12 (S66 in FIG. 8) generates a Web file including the total result and transmits it to the client terminal 22 (S68).

  In the case of this system 10, the AP server 16 does not wait until the fetch from the DB server 18 is completed, and the process by the plurality of data processing units 47 is started each time a certain amount of data is received. The bottleneck due to DISK / IO of the DB server 18 can be resolved.

  Also, every time the partial aggregation in the AP server 16 is completed, the memory occupied by the data sent from the DB server 18 is released, and only the aggregation result data with a much smaller amount of data is stored in the memory. Therefore, it can be avoided that the memory of the AP server 16 is continuously occupied by large data.

  Furthermore, as shown in FIG. 11, each DB server 18 holds the same data, and each search processing unit 46 of the AP server 16 receives “8/1 (for August 1) from the DB server 18a. "8/2 (for August 2)" from the DB server 18b, "8/3 (for August 3)" from the DB server 18c, ... In this way, it is possible to distribute and issue SQL statements for acquiring data for one day allocated to itself from one DB server 18 allocated to itself. Therefore, the processing load on each DB server 18 is inevitably reduced, and the overall processing speed can be increased. Further, when the assigned DB server 18 is allocated to each search processing unit 46, it can be mechanically associated without considering the characteristics (the range of retained data, etc.) for each DB server 18.

From the viewpoint of improving the processing speed, it is desirable to prepare the same number of search processing units 46 and DB servers 18 as the divided search conditions, but the numbers are limited to the search processing units 46 and DB servers 18. It is not something.
For example, if there are 31 divided search conditions and 31 search processing units 46 are provided, but only 10 DB servers 18 are physically prepared, each DB server 18 has On the other hand, 3 to 4 search conditions are allocated. Even in this case, a significant increase in speed can be expected compared to a case where only one DB server 18 is used for processing.

  As described above, the management server 12, the AP server 16, and the plurality of DB servers 18 having the same functional configuration are arranged in the plurality of bases. When adding data, the management server 12 in one base Data addition commands are sent to the AP server 16 at the same time, and data addition SQL is issued from each AP server 16 to each DB server 18 at the same location. Even if one of the bases is devastated by a natural disaster, the client terminal 22 immediately accesses the management server 12 of the other base, so that the AP server 16 placed in the base In addition, processing such as search and addition of business data can be continued through the DB server 18.

  Even during normal times, the management server 12, AP server 16, and DB server 18 at each site provide normal business services to neighboring client terminals 22 without interruption and are not waiting in a casual manner. Effective utilization of owned equipment and load distribution at each site are realized.

  The management server 12 is not necessarily arranged in the same base as the AP server 16 and the DB server 18, and each management server 12 can naturally be installed in another location.

As described above, a plurality of AP servers 16 are actually installed in each site, and load is distributed by a load balancer (not shown), so an AP lock server (not shown) is installed in each site, Exclusive control for each site between the AP servers 16 is executed.
In addition, when it is necessary to ensure data consistency across bases (auction bid requests, etc.), although not shown, a management lock server is provided between the management servers 12, and between the management servers 12. Exclusive control in is performed.

In the present invention, as the structure of each table managed by the DB server 18 is simplified to the limit as described above, the number of tables increases and the number of SQL statements issued increases, but the arithmetic processing is performed. Performed on the AP server 16 speeded up by parallel processing using a large number of CPU cores, the DB server 18 can concentrate on simplified data management (insert and select), freeing from data updates and deletions Therefore, the burden on the DB server 18 is greatly reduced.
In addition, a plurality of DB servers 18 are prepared at the chassis level, and data extraction processing is distributed to each when searching.
For this reason, it is possible to effectively avoid a decrease in the processing speed on the DB server 18 side in pursuit of table normalization.

  In addition, as a result of prohibiting the storage of values based on special business rules such as flag values and division values, it is possible to expect an effect that the prospect of the data model is improved and test data creation efficiency is improved.

As shown in FIG. 12, by providing an index server 60 between each DB server 18 and the AP server 16, the search process in the system 10 can be further accelerated.
In this case, each DB server 18 includes a database management system (RDBMS) 61, a data storage area (provisional storage area) 62 for the day, and a data storage area (permanent storage area) 63 for the past.

The index server 60 includes an index generation unit 64, an index storage unit 65, and an index provision unit 66.
The index generating unit 64 and the index providing unit 66 are realized by the CPU of the index server 60 operating according to a dedicated program, and the index storage unit 65 is provided in an SSD (Solid State Drive) of the index server 60. .

The additional data transmitted from the registration processing unit 40 of the AP server 16 is temporarily stored in the data storage area 62 for the current day by the database management system 61 of the DB server 18 and then transferred to the past data storage area 63 by nighttime batch processing. It is.
In addition, the index generation unit 64 of the index server 60 obtains additional data stored in the data storage area 62 for the current day from the database management system 61 by nighttime batch processing, creates an additional index, Store in part 65.

  In this case, each search processing unit 46 of the AP server 16 refers to the index storage unit 65 via the index providing unit 66 of the index server 60 when issuing the SQL statement to each DB server 18, and the past data As for the data stored in the storage area 63, after acquiring the primary key of each data included in the range of the search condition, an SQL sentence specifying each primary key is generated.

  For example, if the search condition transmitted from the management server 12 is to acquire sales data for the past year in a specific store, the search processing unit 46 sets the primary key of all data that matches this condition to the index server. After obtaining from 60, describe these primary keys in the SQL statement and issue them to the DB server 18.

For this reason, the database management system 61 of the DB server 18 directly retrieves data designated by the primary key from each table stored in the past data storage area 63 without performing data retrieval processing, It becomes possible to return to 46 quickly.
Moreover, since the index is stored in an SSD that can operate at a higher speed than the hard disk, DISK / IO when the AP server 16 refers to the index can be reduced.

  Since the index is generated in the night batch as described above, no index is prepared for the data for the day. For this reason, when it is necessary to acquire data for the current day, the search processing unit 46 issues an SQL statement specifying a search condition to the DB server 18 without specifying a primary key.

  In this system 10, as described above, there is no update or deletion of individual records, and only a new record is allowed to be added. Data can be clearly separated.

FIG. 13 shows the internal structure of each DB server 18 in more detail. A buffer / cache area 70 provided in the memory, a database management system (RDBMS) 61, and an OS (Linux / registered trademark) 72. A table storage area 74 and an update log storage area 76 are drawn.
Here, the table storage area 74 is provided in the hard disk (HDD), and stores the customer management table 24, the reservation management table 26, and the like. The update log storage area 76 is provided in the memory (tmpfs) by the OS.

As shown in this figure, in the DB server 18, generally, data in a table is fetched from the table storage area 74 to the buffer cache area 70 and written to the buffer cache area 70 in units of blocks 78. The data is stored in the table storage area 74 in units of blocks 78.
Therefore, if the structure of the records stored in each table is simplified to the limit as described above, the number of records that can be stored in one block 78 can be increased, and the processing in the DB server 18 correspondingly. It becomes possible to realize efficiency improvement.

  Further, by storing the update log 79 in the update log storage area 76 provided on the memory that operates faster than the hard disk, it becomes possible to reduce DISK / IO correspondingly, and further processing in the DB server 18 High speed can be realized.

By the way, the update log 79 is an important component that will be the last base for data recovery in the event of any trouble in the DB server 18, so data is usually lost when the power is turned off. Is not provided.
On the other hand, in the case of this system 10, as described above, a plurality of DB servers 18 are physically provided for each site, and each DB server 18 has a mechanism for storing the same data. Therefore, it is possible to effectively disperse the risk of data loss.

  In addition, since the rule that data update or deletion is not allowed is applied to each table, the update log of one DB server 18 is lost, and data is stored based on the update log of another DB server 18. Even if it is necessary to restore, it is not necessary to ensure consistency with the existing data stored on the hard disk, and only the newly added data for the current day needs to be added. There is also an advantage. Incidentally, until this recovery is completed, the DB server 18 is placed in a WRITE ONLY state (data can be written / not readable).

  The update log storage area 76 is set on the memory by the OS of each DB server 18 as described above, and the update log storage process is also realized by the function of the OS. Even if an application program crashes, it can be recovered in the DB server as long as the OS does not go down.

It is a schematic diagram which shows the whole structure of the data management system which concerns on this invention. It is a figure which illustrates the table stored in DB server. It is a block diagram which shows the function structure in the scene where the data addition request | requirement transmitted from the client terminal is transmitted to DB server. It is a block diagram which shows the function structure in the scene where a search request is transmitted to DB server. It is a block diagram which shows the function structure in the scene where a search result is transmitted from DB server. It is a schematic diagram which shows the correspondence of a CPU core and a thread. It is a flowchart which shows the process sequence at the time of data addition. It is a flowchart which shows the process sequence at the time of data search. It is a flowchart which shows the process sequence at the time of data search. It is a flowchart which shows the process sequence at the time of data search. It is a schematic diagram which shows a mode that each separate data is separately acquired from several DB server holding the common data. It is a block diagram which shows the example which added the index server to the data management system which concerns on this invention. It is a schematic diagram which shows the internal structure of DB server.

10 Data management system
12 Management server
14a locations
14b locations
16 AP server
18 DB server
20 Communication network
22 Client terminal
24 Customer management table
26 Reservation management table
28 Sales management table
30 Reservation cancellation management table
32 Billing management table
34 Additional data reception
36 Additional data transmitter
38 Additional data replica
40 Registration Processing Department
42 Search condition transmitter
44 Search processing section
44 Search condition division
46 Search processing section
47 Data processing section
48 Search Results Integration Department
50 Search result sending part
52 CPU cores
54 threads
56 Thread Pool
58 tasks
60 Index server
61 Database management system
62 Data storage area for the day
63 Past data storage area
64 Index generator
65 Index storage
66 Index provision department
70 Cache area
74 Table storage area
76 Update log storage area
78 blocks
79 Update log

Claims (5)

AP servers installed at a plurality of bases separated from each other by a predetermined distance, a plurality of DB servers installed at the respective bases and connected to the AP server at the same base via a network, and the APs of the bases A plurality of management servers connected to the server via a network;
Each AP server includes an additional data duplicating unit, a plurality of registration processing units, a search condition dividing unit, a plurality of search processing units, a plurality of data processing units, and a search result integrating unit,
Each of the above DB servers has a plurality of tables each holding data common to all the bases. Each table is restricted to one key item and data updating is prohibited. Restrictions have been established,
When additional data is transmitted from the client terminal to any of the management servers, the management server executes processing for instructing the AP server arranged at each site to register the additional data,
The additional data replication unit of each AP server performs a process of assigning a copy of the transmitted additional data and a data addition request including specific information of the DB server arranged in the same site to each of the registration processing units. Run,
Each of the above registration processing units generates an SQL statement for requesting registration of the additional data, and executes a process to be issued to the DB server assigned to itself.
Each DP server executes a process of inserting a record corresponding to the additional data into the table,
When the search condition is transmitted from the client terminal to any management server, the management server executes a process of transmitting the search condition to the AP server in any one of the bases,
The search condition dividing unit of the AP server analyzes the search condition transmitted from the management server and divides it into a plurality of search conditions, and assigns each search condition and the responsible DB server to the plurality of search processing units. Run,
Each of the above search processing units generates a SQL statement corresponding to the search condition assigned to itself, issues it to the DB server assigned to itself, and a certain amount of search result data is transmitted from the DB server. Each time the necessary arithmetic processing is assigned to each data processing unit and each partial processing result data is returned from each data processing unit, it is stored in the memory and from the DB server. When the process of deleting the sent data from the memory and the data transmission from the DB server is completed, and when all the partial processing result data based on the partial search result data are collected, these are totaled Execute the process to output the aggregation results to the search result integration unit,
The search result integration unit totalizes the aggregation results passed from each search processing unit, and executes a process of transmitting to the management server as a search result,
The data management system, wherein the management server executes a process of transmitting data transmitted from the AP server to the client terminal as a search result.   2. The data management system according to claim 1, wherein the search condition dividing unit divides the input search condition into a shorter time range when the input search condition includes a time range.   2. The data management system according to claim 1, wherein, when the input search condition includes a regional range, the search condition dividing unit divides the search condition into a narrower regional range. The data management system according to any one of claims 1 to 3,
An index server having an index generation unit and an index storage unit;
Each DB server described above includes a database management system, a temporary storage area, and a permanent storage area.
The database management system executes a process of storing additional data transmitted from the AP server in the temporary storage area and a process of moving additional data in the temporary storage area to the permanent storage area at predetermined time intervals. ,
The index generation unit of the index server obtains additional data stored in the temporary storage area of the DB server at predetermined time intervals, generates an index based on the additional data, and stores the index in the index storage unit Execute the process to
Each search processing unit of the AP server refers to the index storage unit of the index server when generating the SQL statement corresponding to the search condition assigned to itself, and determines the primary key of each data corresponding to the search condition. A data management system characterized by executing a process of acquiring and a process of generating an SQL statement in which data to be acquired is specified by the primary key. Each of the above DB servers has an update log storage area provided on the memory,
The data management system according to any one of claims 1 to 4, wherein the database management system executes a process of sequentially storing additional data transmitted from the AP server in the update log storage area.

Images