JPH0695936A - Method for managing buffer - Google Patents

Abstract

PURPOSE:To provide a priority-added buffer managing method for detecting a data access type inherent in each transaction previously or at the time of execution, determining the priority of buffer substitution and executing buffer allocation and substitution based upon the priority. CONSTITUTION:In the case of mixedly executing on-line transactions 1 for retrieving or updating an objective line in a table by means of an index and a batch transaction 2 for searching and retrieving or updating an objective line by sequentially reading out respective lines in the table and judging conditions, the buffer managing method is characterized by detecting an access characteristic inherent in any one of the transactions 1, 2 and determining priority for buffering based upon the detected access characteristic.

Description

Detailed Description of the Invention

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention buffers tables and indexes of a relational database stored in a secondary storage device such as a disk device into a primary storage device such as a high speed and small capacity semiconductor memory. More specifically, the present invention relates to a buffer management method in a data processing device for processing, and in particular, to determine the priority of buffer management based on the access characteristics of a transaction and the execution priority of the transaction or the difference in data attributes such as tables and indexes. And a buffer management method with priority.

[0002]

2. Description of the Related Art Databases are used in many business applications such as financial systems, ticket reservation systems, inventory management systems, personnel management systems, etc., and large-scale database systems have been realized. Most of these systems are used in such a way that a batch transaction performs statistical processing on a database updated by an online transaction to create a sales strategy. Here, in general, an online transaction utilizes an index to search and update a specific row (tuple) of a table (batch table), whereas a batch transaction requires a full scan or join of the entire table. , The processing time tends to be lengthened. Therefore, in the conventional database management system (DBMS),
I used to use separate time zones for running online transactions and batch transactions (for example, online at daytime and batch at night). Taking the financial system as an example, the deposit balance of each individual account must be updated online with the deposit / withdrawal operation, and the fluctuation of the total deposit balance at each branch must be aggregated on a daily basis. I won't.

The former is a typical example of online transaction, and the latter is a typical example of batch transaction. Conventionally, the batch was processed at night when the online was finished. However, in a large-scale network management system that provides 24-hour service and a system that conducts transactions on a global scale, it is not possible to stop the 24-hour service a day, making it difficult to use the above-mentioned time zones separately, and statistical processing It is required to execute a batch transaction for performing the above and the like in parallel with the online transaction. The table to be processed by the database management system and the index generated from the table are a collection of an extremely large amount of data, and must be stored permanently with high reliability. Etc. are stored in the secondary storage device. In addition, since a magnetic disk device has a low access speed, generally, in a database processing device, a part of the database stored in the magnetic disk device is buffered in a primary storage device such as a semiconductor storage device that can be accessed at high speed. Ring and process.

FIG. 3 shows a configuration example of a conventional DBMS having a buffer management mechanism. In the figure, 1 is an online transaction, 2 is a batch transaction, 3 is a magnetic disk device for storing database tables and indexes, and 31 is a database management system.
(DBMS) is shown. Here, the DBMS 31 is
Execution control unit 32 for consistently executing the online transaction 1 and the batch transaction 2 described above
And a buffer subsystem 33 for buffering data such as tables and indexes stored in the magnetic disk device 3. Further, the buffer subsystem 33 includes a semiconductor memory device 36 for temporarily holding the data stored in the magnetic disk 3, and a buffer management table for managing the data currently held by the semiconductor memory device 36. 35, and a buffer control unit 34 for controlling buffer allocation and replacement. DBMS
The transaction 1 or 2 input to 31 is decoded by the execution control unit 32 to create an execution plan, and the buffer subsystem 33 is requested to read necessary data according to the execution plan. If the transaction involves data update, the read data is updated or new storage of new data is requested to the buffer subsystem 33.

The buffer subsystem 33 stores the requested data if the requested data has already been buffered in the semiconductor memory device 36, or the free space of the semiconductor memory device 36 from the magnetic disk device 3 if not buffered. Then, the requested data is read and the data is returned to the execution control unit 32. At that time, if there is no free area in the semiconductor memory device 36, the semiconductor memory device 3
Any data buffered in 6 must be evicted to create free space and replace the buffer. Therefore, the challenge of buffer management is to achieve allocation and replacement of buffers with high hit rates. A conventional buffer management method in a database processor is described in "Principles o" by W. Effelsberg and T. Hearder.
f Database Base Management ", ACM Trans.o
n Database Systems, Vol.9, No.4, pp.560-595, D
ec. Organized in 1984.

[0006] In the above literature, as a buffer replacement algorithm, FIFO (First In First Out), LRU is used.
(Least Recent Used), LFU (Least Frequentl)
y Used) etc. are shown. These conventional replacement algorithms are methods of replacing the data that will be accessed next with a high probability of being retained in the buffer based on the past access history. It was controlled using order, frequency, and data attributes. Therefore, although the buffer management has a feature that it can be configured as an independent subsystem, the following problems occur when the online transaction 1 and the batch transaction 2 are executed in a mixed manner. (1) Pages that have been scanned by batch processing are unlikely to be used afterwards, but with LRU,
Existence in the buffer with a high probability. (2) Since the intermediate result generated in the batch is not the data that is frequently used, the FIFO and LFU algorithms must be expelled from the buffer at an early stage.

[0007]

With respect to the above problems, the method of introducing the priority into the buffer management is MJC.
arey, R. Jauhari and M. Livny "Priority in D"
BMS Resource Scheduling ", 15th Int'l Conf.
on Very Vartage Data Bases, Amsterdam, 1989. Here, a method of inheriting the execution priority given to a transaction as the priority of buffer management is shown for real-time database processing. The buffer management unit registers the data requested for access in the LRU list for each priority, manages the data, and controls the replacement of the buffer so as to expel the low priority first, thereby executing the data with high priority. The data used by the transaction is prevented from being accidentally ejected. However, when different types of transactions such as online transactions and batch transactions are mixedly executed, if the conventional buffer management method is applied, the buffer is subdivided for a plurality of online users, resulting in division loss. Or a problem that a large amount of buffer is allocated to the batch transaction and online performance is degraded.

When a page frequently accessed by an online transaction is evicted from the buffer by executing batch transactions in a mixed manner, the online transaction needs to be re-read, which increases the amount of calculation and disk access. There is also a problem that the response performance deteriorates due to the waiting. On the other hand, by inheriting the execution priority given to the transaction as the priority of buffer management, the conventional method that reflects the transaction priority in the buffer replacement order is applied when executing online transactions and batch transactions together. Then, since there are only two levels of priority, online and batch, it is clear that there is a problem that sufficient priority classification is not possible, and that high-priority transactions are completed and will not be used in the near future. A problem arises that even data is not flushed from the buffer. The present invention has been made in view of the above circumstances, and its object is to:
The above-mentioned problems in the prior art are solved, the data access type unique to the transaction is detected in advance or at the time of execution, the priority of the buffer replacement is determined, and the buffer allocation and replacement based on the priority are performed. It is to provide a buffer management method with priority that is performed.

[0009]

The above objects of the present invention are as follows:
A secondary storage means for storing a database table and an index created from the table; a primary storage means that can be accessed faster than the secondary storage means; and an execution control means for executing a plurality of transactions without contradiction. And a buffer management method in a database processing device for buffering and processing a part of a table and an index stored in the secondary storage means in the primary storage means. The online transaction for retrieving or updating the target row and the batch transaction for retrieving or retrieving or retrieving the target row are mixed by sequentially reading each row in the table and performing the condition determination. When running
This is achieved by a buffer management method characterized by detecting an access characteristic unique to the online transaction and the batch transaction and determining a priority for buffering based on the detected access characteristic.

[0010]

In the buffer management method with priority according to the present invention, since the execution control means creates an execution plan for a transaction, the order of data access to the buffer subsystem and the data once accessed will be in the near future. Moreover, it is possible to easily detect the possibility of being used by the own transaction or another transaction processing in the database management system. For example, if the index data is used by an online transaction, it has a high probability of being used by another online transaction in the near future. On the other hand, it can be said that the table data scanned by the conditional search by the batch transaction is not used by at least the own transaction, and has a low probability of being used by other transactions. On the other hand, it can be said that the intermediate result data in which the result retrieved by the batch transaction is stored in the buffer as a temporary table is always used once in the subsequent processing in the own transaction. It can also be guaranteed that the intermediate results generated for joins etc. will be reused in the near future. As described above, it is extremely easy for the execution control unit to detect the access characteristic of each transaction, and the access characteristic detection unit can be realized with a simple circuit. Therefore, the priority of the buffer management is determined based on the access characteristic detected by the access characteristic detecting means, and the index or some intermediate result determined to be used by the access characteristic detecting means with a high probability in the near future is high. Buffer management is performed by assigning a priority, otherwise assigning a low priority. That is, it is possible to improve the utilization efficiency of the buffer by controlling the buffer so that the buffered data is pushed out earlier as the data having lower priority.

[0011]

Embodiments of the present invention will now be described in detail with reference to the drawings. FIG. 1 is a configuration diagram of a database management system (DBMS) according to an embodiment of the present invention. Symbols 1 to 3 in the figure indicate the same components as those shown in FIG. 3, and 11 indicates a DBMS. DBMS11
Is an execution control unit 1 for executing the above-mentioned online transaction 1 and batch transaction 2 without contradiction.
2 and a buffer subsystem 14 for buffering data such as tables and indexes stored in the magnetic disk device 3. Execution control unit 12
Reads the processing contents of the transaction input to the DBMS 11 to create an execution plan, requests the buffer subsystem 14 to read out necessary data according to the execution plan, and at the same time, causes the access characteristic determination unit 13 to read the buffer subsystem. The access priority is determined based on the access order of the data requested to 14 and the possibility of reuse.

The buffer subsystem 14 includes a semiconductor memory device 18 for temporarily holding the data stored in the magnetic disk device 3, and a buffer management table 16 for managing the data currently held by the semiconductor memory device 18. A data attribute determination unit 17 for determining the attribute of the data, a priority based on the data attribute obtained by the data attribute determination unit 17, and a priority based on the access characteristic obtained by the access characteristic determination unit 13 described above. The buffer management priority from
It is composed of a priority control unit 15 which allocates and swaps buffers based on the priority. FIG. 2 is an operation flow chart for explaining the buffer management procedure in the embodiment shown in FIG. The operation of the embodiment will be described below with reference to both drawings. Step 21: The execution control unit 12 decodes the processing contents of the online transaction 1 and the batch transaction 2 input to the DBMS 11 to create an execution plan. Step 22: The buffer subsystem 14 is requested to read necessary data according to the created execution plan.
Further, when updating the data, the buffer subsystem is requested to read out the data to be updated or additionally store new data.

Step 23: Buffer subsystem 14
Determines whether the requested data is already buffered in the semiconductor memory device 18. If the data is buffered, the data is used for database processing described later. If not, the next data read operation is performed. Step 24: It is judged whether or not there is an unused area in the buffer realized on the semiconductor memory device 18. If there is an unused area, the data reading operation of step 26 is performed, and if there is no unused area, the next buffer exchange operation is performed. Step 25: If all the buffers are used and there is not enough space, one of the buffered data is evicted to create a free area. At this time, referring to the buffer management table 16, the data having the lowest priority and the least recently used is expelled. Therefore, in the buffer management table 16, information for determining at least the priority and the order in which the data is buffered is registered. The buffer management table 16 for this is
This can be easily realized by configuring the LRU list for each priority.

Step 26: As a result of the above operation, since there is a free area in the buffer, the requested data is read. At the same time, information for identifying the read data is registered in the buffer management table 16. Step 27: Here, an independent priority value used in the operation of step 28 of determining the priority of buffer management is obtained. Step 27-1: The execution control unit 12 uses the data attributes of the buffered data to perform processing based on the execution plan. At that time, in the access characteristic determination unit 13,
Determine priority based on access characteristics. At this time, as shown in the section of action, the probability that the once accessed data will be reused is obviously different between the online transaction and the batch transaction depending on the access characteristics such as the order of data access and the bias, so the priority is Can be given. Step 27-2: Convert the priority given to the transaction into a priority used in buffer management. The user who issues a transaction sets an expected value as the execution priority when executing each transaction, so that the execution priorities are uniformly assigned and operations are performed, except for variations among users. That is, normally
Among a plurality of users who share a DBMS, the authority to operate for each user is limited, and a priority when issuing a transaction is given. A large number of authorities and high execution priority are given to the administrator who maintains and manages the DBMS, and a beginner user is given limited authority and low execution priority. Here, it has a role of reflecting the execution priority given to each user in the execution of the transaction.

Step 27-3: On the other hand, the priority of buffer management is determined based on the buffered data attribute. In general, since (capacity of index) <(capacity of table), high priority can be given to the index. Furthermore, it is possible to buffer the schema information for managing the storage status of the database, or to divide the structured index into subdivided priorities for individual data items. As a basic principle of giving a priority, if a data attribute that has a small capacity and is frequently accessed by many transactions is given a higher priority, the hit ratio of the buffer can be improved. Step 28: The priority based on the access characteristics, the priority based on the execution priority of the transaction, and the priority based on the data attribute determined in steps 27-1, 27-2 and 27-3 are integrated to manage the buffer. Determine the priority. If the data is already buffered, a predetermined value is set in the buffer management table 16, so a new priority is determined by the set value and the three types of priority obtained above. Then, the priority value of the buffer management table 16 is updated. The buffer eviction in step 25 is controlled by using the priority value registered in the buffer management table 16.

It should be noted that the DBMS specifications are determined by how the priority levels are integrated. For example, a system that emphasizes priority based on data attributes has buffer management similar to the data attribute-based management method shown in the conventional method. In general, the transaction execution priority pT1, pT2, ...
.. When the priorities pD1, pD2, ... by the data attribute and the priorities pA1, pA2, .. by the access characteristic are given, the combination of these is (pT1, pD1, pA1)> (pT1 , pD1, pA
2) ＞ ・ ・ ・ ・ ＞ (pT1, pD2, pA1) ＞ (pT1, pD2, pA2) ＞ ・ ・ ・
> (PT2, pD1, pA1)> (pT2, pD1, pA2)> ... Step 29: The above operations of Steps 22 to 28 are repeated until the transaction processing is completed.

According to the above embodiment, by determining the buffer management priority based on the data access characteristic, the data required by the online transaction is inadvertently ejected by executing the batch transaction. In addition, the buffer subsystem of the database can be used to hold intermediate results generated by batch transactions.
As a result, for example, when a batch transaction that searches the database under the specified conditions and returns the results one by one is processed by the process for searching and the process for returning the results, the search results passed between the two processes Can be held by using the buffer subsystem, which has the advantage of eliminating the need to provide a new buffer mechanism exclusively for passing results. Further, even in a join process in which a large amount of intermediate results must be held, the buffer subsystem can be used to control the priority of buffering for each bucket, so that even with a small buffer capacity,
It can be used effectively.

It is needless to say that the above embodiment shows an example of the present invention and the present invention should not be limited to this. For example, in the above embodiment, the method of determining the priority of the buffer management based on the data access characteristic and the data attribute has been described, but the priority of the buffer management can be determined using only the data access characteristic. Further, when the execution priority can be specified for the transaction itself, for example, even when the online transaction is set to execute the processing in preference to the batch transaction, the priority and the transaction based on the data access characteristic are given to the transaction. It is also possible to manage the buffer by using the priority in which the priority is integrated. Although the structure of the buffer management table has not been described in detail in the above description, priority may be given to other structures such as an LFU list based on access frequency other than the LRU list for each priority shown as an example. If the relationship between the degree and the order of access can be maintained, it can be realized without depending on the configuration of the buffer management table. In addition, even in cases other than mixed execution of online transactions and batch transactions, when transactions of different types are mixed and executed, and the access characteristics of each transaction can be determined in advance or at the time of execution The buffer management method with priority using access characteristics according to the present invention can be applied.

[0019]

As described above in detail, according to the present invention, the data access type peculiar to a transaction is detected in advance or at the time of execution to determine the priority of the buffer replacement, and the priority is based on the priority. This has the remarkable effect of realizing a buffer management method with priorities that allocates and replaces buffers.

[0020]

[Brief description of drawings]

FIG. 1 is a configuration diagram of a database management system according to an embodiment of the present invention.

FIG. 2 is an operation flow diagram for explaining a buffer management procedure in the embodiment shown in FIG.

FIG. 3 is a configuration diagram of a conventional database management system using buffer management.

[Explanation of symbols]

1: Online transaction, 2: Batch transaction, 3: Magnetic disk device, 11: DBMS, 1
2: execution control unit, 13: access characteristic determination unit, 14: buffer subsystem, 15: priority control unit, 16: buffer management table, 17: data attribute determination unit, 18: semiconductor memory device.

Claims (3)

[Claims] 1. A secondary storage means for storing a table of a database and an index created from the table, a primary storage means capable of high-speed access from the secondary storage means, and a plurality of transactions for consistent execution. And a buffer management method in a database processing device for buffering and processing a part of the table and the index stored in the secondary storage means in the primary storage means. An online transaction that retrieves or updates a target row in the table by using it, and a batch transaction that searches for and retrieves or updates the target row by sequentially reading each row in the table and performing condition determination When executing a mixture of A buffer management method comprising detecting a unique access characteristic and determining a priority at the time of buffering based on the detected access characteristic. 2. A priority at the time of execution is specified for each of the online transaction and the batch transaction executed in a mixed manner, and a priority at the time of buffering is specified based on the specified execution priority and the access characteristic. The buffer management method according to claim 1, wherein the degree is determined. 3. The buffer according to claim 1, wherein the priority at the time of buffering is determined based on the data attribute of the table and index stored in the secondary storage device and the access characteristic. Management method.