WO2018070011A1

WO2018070011A1 - Job specification program, job specification method and job specification device

Info

Publication number: WO2018070011A1
Application number: PCT/JP2016/080358
Authority: WO
Inventors: 純史川井
Original assignee: 富士通株式会社
Priority date: 2016-10-13
Filing date: 2016-10-13
Publication date: 2018-04-19

Abstract

An execution plan unit (14) extracts, on the basis of connection time information associated with a connection time, a query associated with an online batch task. In addition, if an access method of the extracted query is an index search, the execution plan unit (14) accumulates information of the query in a management information storage unit (15). In addition, the execution plan unit (14) specifies, on the basis of accumulated information, a query of an online batch task for which the access method has been changed from an index search to a table all cases search.

Description

Job identification program, job identification method, and job identification apparatus

The present invention relates to a job specifying program, a job specifying method, and a job specifying apparatus.

Businesses that use the database include online business, nighttime batch business, and online batch business. FIG. 7 is a diagram for explaining the online business, the nighttime batch business, and the online batch business.

As shown in FIG. 7, the online business is a business that is executed as an online job by a computer during business hours from 9 o'clock to 17 o'clock, for example. This is the job that requires the most performance. The amount of data handled in online operations is small. As an example of online business, there is a bank online system such as deposit withdrawal.

The nighttime batch job is a job that is executed as a nighttime batch job by a computer outside the business hours such as nighttime, and is a job that requires stable processing rather than performance. The amount of data handled in nighttime batch operations is large. Examples of nighttime batch operations include aggregation processing and backup processing.

The online batch job is a job that is executed as an online batch job by the computer in parallel with the online job for information analysis of the online job, and is a job that requires higher performance than the night batch job.

∙ The amount of data handled in online batch operations varies from small to large. As an example of online batch operations, there is a task of counting sales in the morning in order to determine time sale products in a supermarket. Since sales in the morning may fluctuate, the amount of data for summing up sales in the morning varies from small to large.

In the database management system, when the amount of data is small to medium, index search is used, and when the amount of data is large, all table search is used. The reason is that when the amount of data becomes large, the whole table search becomes faster than the index search.

FIG. 8 is a diagram for explaining index search. In FIG. 8, the TBL # 1 tuple is composed of an ID, a name, and an affiliation, and the ID is used as an index. As shown in FIG. 8, in an index search, index values are searched using a tree structure, and a table is accessed based on position information associated with the searched index values. On the other hand, in the all table search, the tuples in the table are searched one by one.

FIG. 9 is a diagram showing the relationship between business and database access methods. As shown in FIG. 9, in online work, since the amount of data is small to medium, index search is used. In the night batch operation, since the amount of data is large, a table all-case search is used.

In online batch operations, index search is used when the amount of data is small to medium, and full table search is used when the amount of data is large. In other words, in online batch operations, the database access method changes when the amount of data changes from small to medium.

JP 2012-150733 A Japanese Patent Laying-Open No. 2015-141574 JP-A-5-225132

In online batch operations, if the data volume changes from small to medium, the database access method can be changed from index search to full table search to increase the speed. However, even if the access method is changed to all table search, the performance deteriorates due to an increase in the data amount. Therefore, a parallel search is performed in order to speed up the search of all the tables.

FIG. 10 is a diagram for explaining a parallel search in the all table search. In FIG. 10, IDX represents an index, and TBL represents a table. As shown in FIG. 10, in the online batch operation, the processing amount is small and the processing time by the index search is small at the normal time when the data amount is small to medium. However, when the amount of data increases, the processing time for searching all the tables becomes longer, although it is shorter than the processing time for index searching. For this reason, speeding up of the whole table search is performed using parallel search.

In order to speed up the whole table search by using parallel search for online batch operations where the amount of data has increased, the database query is a query for online batch operations and the database access It is necessary to identify that the method has changed. For this reason, the access method is stored as management information for each query.

FIG. 11 is a diagram showing an example of management information. As shown in FIG. 11, the management information is information for associating the query contents with the access method for each query. For example, an index search is performed for a query “SELECT A, B, C FROM T1 WHERE D = 1”.

Here, the same query “SELECT A, B, C FROM T1, WHERE D = 1” is newly issued, and DBMS (Data Base Management System) has determined that all table search is used as the access method. To do. Then, the DBMS refers to the management information, specifies that the access method has changed from index search to full table search, this query is a query for online batch operations, and the database access method is You can identify what has changed.

However, if management information for all queries is stored in the main memory, there is a problem that the amount of use of the main memory increases. Since the main memory is used as a database cache, it is desired that the amount of main memory used for storing management information is as small as possible.

In one aspect, the present invention aims to reduce the amount of main memory used for storing management information.

In one aspect, the job identification program causes the computer to execute the following processing. That is, the job specifying program causes the computer to execute a process of extracting a query related to the online batch job based on connection time information related to the connection time from a query that makes a search request to the database management system. . Then, the job specifying program causes the computer to execute processing for accumulating information of the query when the access method to the extracted query database is an appropriate access method when the amount of data is small. The job specifying program causes the computer to execute processing for specifying a query of an online batch job whose access method has been changed based on the accumulated information.

In one aspect, the amount of main memory used to store management information can be reduced.

FIG. 1 is a diagram illustrating a functional configuration of the DBMS according to the embodiment. FIG. 2 is a diagram for explaining a method for specifying a query that is an online batch query and whose access method has changed from index search to full table search. FIG. 3 is a diagram illustrating an example of the management information storage unit. FIG. 4 is a diagram for explaining parallel search control. FIG. 5 is a flowchart showing a flow of processing for creating and executing a query execution plan. FIG. 6 is a diagram illustrating a hardware configuration of a computer that executes the execution planning program according to the embodiment. FIG. 7 is a diagram for explaining the online business, the nighttime batch business, and the online batch business. FIG. 8 is a diagram for explaining the index search. FIG. 9 is a diagram illustrating a relationship between a business and a database access method. FIG. 10 is a diagram for explaining parallel search in the table all-case search. FIG. 11 is a diagram illustrating an example of management information.

Hereinafter, embodiments of a job specifying program, a job specifying method, and a job specifying apparatus disclosed in the present application will be described in detail with reference to the drawings. Note that this embodiment does not limit the disclosed technology.

First, the functional configuration of the DBMS according to the embodiment will be described. FIG. 1 is a diagram illustrating a functional configuration of the DBMS according to the embodiment. As illustrated in FIG. 1, the DBMS 1 according to the embodiment includes a syntax analysis unit 11, an analysis unit 12, a rewrite unit 13, an execution plan unit 14, a management information storage unit 15, an execution unit 16, and a DB 20. Have

The syntax analysis unit 11 analyzes a query character string and performs query syntax analysis. The analysis unit 12 checks the presence of a table used in the query and acquires information for processing the query. The rewrite unit 13 rewrites the query including processing such as view.

The execution plan unit 14 creates an optimal execution plan for processing the query. Further, the execution planning unit 14 uses the management information storage unit 15 to identify a query that is an online batch job and whose access method has changed from index search to full table search. That is, the execution planning unit 14 specifies that the job that issued the query is an online batch job, and the query access method has changed from index search to full table search.

FIG. 2 is a diagram for explaining a method for specifying a query that is an online batch job query and whose access method has changed from an index search to an all-table search. As shown in FIG. 2, the execution planning unit 14 determines whether the query is an online business query or an online batch business query using the connection time (t1).

Specifically, the execution plan unit 14 determines that the query is an online business or online batch business query when connection start time + 40 seconds <query start time. Note that 40 seconds is an example, and other values may be used.

Then, the execution plan unit 14 uses the table statistical information to determine whether the query is a query that requires parallel search, that is, a query for online batch operations (t2). Specifically, the execution planning unit 14 determines whether the query is an online batch job by determining whether the table size is 10 GB (gigabytes) or more based on the number of blocks in the table. Determine.

Then, the execution plan unit 14 determines whether or not the current method is non-parallel, that is, the access method of the newly executed query is a full table search, and the access method of the newly executed query is the index search. It is determined using the management information storage unit 15 whether or not there is (t3).

The management information storage unit 15 stores a query for which an index search has been performed among queries for online batch operations. The management information storage unit 15 is provided in the main memory. FIG. 3 is a diagram illustrating an example of the management information storage unit 15. As shown in FIG. 3, the management information storage unit 15 stores the query content and priority information as management information.

The priority information indicates a priority execution right that is given to a query that is an online batch transaction and whose access method has changed from index search to full table search, and is executed preferentially for ad hoc queries. The right to Here, an ad hoc query is a query used on an ad hoc basis. When the access method changes from index search to full table search and the priority execution right is given, the priority information is set to “1” by the execution planning unit 14.

The execution plan unit 14 includes an extraction unit 14a, a storage unit 14b, and a specification unit 14c. The extraction unit 14a extracts a query for online batch jobs using the connection time and the table size. The accumulating unit 14b accumulates, in the management information storage unit 15, information on a query that has been index-searched among the queries extracted by the extracting unit 14a. The specifying unit 14c refers to the management information storage unit 15 and specifies a query whose access method has changed from index search to full table search.

The execution unit 16 executes a query based on the execution plan. The execution unit 16 controls the parallel processing so that it is executed preferentially with respect to the query to which the priority execution right is given. The DB 20 stores a table. A search is performed on the table stored in the DB 20.

FIG. 4 is a diagram for explaining parallel search control. In FIG. 4, the parallel processing framework indicates that n parallel search workers # 1 to #n parallel search workers #n perform searches in parallel. As shown in FIG. 4 (a), online batch transaction queries are processed in parallel by parallel search worker # 1 to parallel search worker #n when the access method changes from index search to full table search. The

Further, as shown in FIG. 4B, even when an online batch job query is executed in parallel, even if an ad hoc query processing request is issued, the ad hoc query completes the online batch job query processing. Will not be searched in parallel until

Also, as shown in FIG. 4C, if there is a parallel processing request for online batch business queries during parallel execution of ad hoc queries, parallel search worker # 1 to parallel search worker #n are returned, The ad hoc query is processed in non-parallel. Then, the parallel search worker # 1 to the parallel search worker #n are assigned to the online batch job query, and the online batch job query is preferentially executed.

Next, the flow of processing to create and execute a query execution plan will be described. FIG. 5 is a flowchart showing a flow of processing for creating and executing a query execution plan. As shown in FIG. 5, the execution plan unit 14 creates an execution plan for the target query (step S1). Here, the target query is a query requested to be processed.

Then, the execution planning unit 14 determines whether or not the target query corresponds to an online job or an online batch job (step S2). The unit 16 executes the target query (step S7).

On the other hand, when the target query corresponds to an online job or an online batch job, the execution planning unit 14 determines whether the size of the table to be accessed by the target query is 10 GB or more (step S3). . As a result, when the size of the table to be accessed by the target query is not 10 GB or more, since the target query is not an online batch job query, the execution unit 16 executes the target query (step S7).

On the other hand, when the size of the table to be accessed by the target query is 10 GB or more, since the target query is an online batch job query, the execution plan unit 14 stores information on the target query in the management information storage unit 15. It is determined whether or not there is (step S4).

As a result, when there is information on the target query in the management information storage unit 15, the target query has been processed by the index search before, so the execution plan unit 14 determines that the current access method of the target query is a full table search. It is determined whether or not there is (step S5). As a result, if the current access method of the target query is not a full table search, the index search is still being performed, so the execution unit 16 executes the target query (step S7).

On the other hand, when the current access method of the target query is a full table search, since the access method is changed from an index search to a full table search, the execution planning unit 14 performs parallel search on the target query. A priority execution right is granted (step S6), and the priority execution right is set in the priority information of the management information storage unit 15. Then, the execution unit 16 executes the target query (step S7).

On the other hand, if there is no information on the target query in the management information storage unit 15 in step S4, the execution plan unit 14 determines whether or not the access method of the target query is an index search (step S8). As a result, when the access method of the target query is not index search, the execution unit 16 executes the target query (step S7).

On the other hand, when the access method of the target query is index search, the execution plan unit 14 stores the information of the target query in the management information storage unit 15 (step S9), and the execution unit 16 executes the target query. (Step S7).

As described above, when the target query is an online batch business query and the access method is a query in which the access method is changed from the index search to the all-table search, the priority execution right of the parallel search is executed. Is granted. Therefore, the DBMS 1 can suppress performance degradation when the data amount of online batch operations increases and the access method is changed from index search to full table search.

As described above, in the embodiment, the execution planning unit 14 extracts a query related to the online batch job based on the connection time information related to the connection time. Then, when the access method of the extracted query is an index search, the execution plan unit 14 accumulates information on the query in the management information storage unit 15. Then, the execution planning unit 14 identifies a query for an online batch job whose access method has been changed from an index search to a full table search based on the accumulated information.

Accordingly, the DBMS 1 can limit the query for storing information in the management information storage unit 15 to a query for an online batch job and for which an index search has been performed. The thickness can be reduced. For this reason, the usage amount of the main memory can be suppressed.

For example, suppose that the number of queries including online business and online batch business is 5000, the ratio of online batch business queries is 1%, the number is 50, the number of ad hoc queries is 500, and the number of connections is 1000. In addition, the size of a conventional storage area that stores information in which an access method is added to the query contents and the size of a storage area that stores information in which priority information is added to the query contents are set to 70 bytes.

Conventionally, the main memory necessary for storing the management information is 70 bytes × 5500 queries × 1000 connections = 385 MB (megabytes). In the embodiment, 70 × 50 queries × 1000 connections = 3.5 MB. .

In the embodiment, the execution planning unit 14 further extracts a query related to the online batch operation based on the size of the table accessed by the query. Therefore, the execution planning unit 14 can accurately extract queries related to online batch operations.

In the embodiment, since the connection time information includes the connection start time and the query start time, the execution planning unit 14 can accurately extract queries related to the online business and the online batch business.

In addition, although the execution plan part 14 was demonstrated in the Example, the execution plan program which has the same function can be obtained by implement | achieving the structure which the execution plan part 14 has with software. Therefore, a computer that executes the execution planning program will be described.

FIG. 6 is a diagram illustrating a hardware configuration of a computer that executes the execution plan program according to the embodiment. As shown in FIG. 6, the computer 50 includes a main memory 51, a CPU 52, a LAN (Local Area Network) interface 53, and an HDD (Hard Disk Drive) 54. The computer 50 includes a super IO (Input Output) 55, a DVI (Digital Visual Interface) 56, and an ODD (Optical Disk Drive) 57.

The main memory 51 is a memory for storing a program and a program execution result. The management information storage unit 15 illustrated in FIG. 1 is provided in the main memory 51. The CPU 52 is a central processing unit that reads a program from the main memory 51 and executes it. The CPU 52 includes a chip set having a memory controller.

The LAN interface 53 is an interface for connecting the computer 50 to another computer via a LAN. The HDD 54 is a disk device that stores programs and data. The DB 20 illustrated in FIG. 1 is provided in the HDD 54. The super IO 55 is an interface for connecting an input device such as a mouse or a keyboard. The DVI 56 is an interface for connecting a liquid crystal display device, and the ODD 57 is a device for reading / writing a DVD.

The LAN interface 53 is connected to the CPU 52 by PCI Express (PCIe), and the HDD 54 and ODD 57 are connected to the CPU 52 by SATA (Serial Advanced Technology Attachment). The super IO 55 is connected to the CPU 52 by LPC (Low Pin Count).

The execution plan program executed in the computer 50 is stored in the DVD, read from the DVD by the ODD 57, and installed in the computer 50. Alternatively, the execution plan program is stored in a database or the like of another computer system connected via the LAN interface 53, read from these databases, and installed in the computer 50. The installed execution plan program is stored in the HDD 54, read into the main memory 51, and executed by the CPU 52.

1 DBMS
DESCRIPTION OF SYMBOLS 11 Syntax analysis part 12 Analysis part 13 Rewrite part 14 Execution plan part 15 Management information storage part 16 Execution part 20 DB
50 Computer 51 Main memory 52 CPU
53 LAN interface 54 HDD
55 Super IO
56 DVI
57 ODD

Claims

On the computer,
Based on the connection time information related to the connection time, the query related to the online batch job is extracted from the query that makes a search request to the database management system.
When the access method to the database of the extracted query is a suitable access method when the amount of data is small, the information of the query is accumulated,
A job specifying program for executing a process for specifying a query of an online batch job whose access method has been changed based on the accumulated information.
The job specifying program according to claim 1, wherein the extracting process further extracts a query related to an online batch job based on a size of a table accessed by the query.
3. The job specifying program according to claim 1, wherein the connection time information includes a connection start time and a query start time.
Computer
Based on the connection time information related to the connection time, the query related to the online batch job is extracted from the query that makes a search request to the database management system.
When the access method to the database of the extracted query is a suitable access method when the amount of data is small, the information of the query is accumulated,
A job specifying method comprising: executing a process for specifying a query of an online batch job whose access method has been changed based on the accumulated information.
An extraction unit that extracts a query related to an online batch job based on connection time information related to connection time from a query that makes a search request to the database management system;
When the access method to the database of the query extracted by the extraction unit is an access method suitable when the amount of data is small, a storage unit that stores information on the query;
And a specifying unit that specifies a query of an online batch job whose access method has been changed based on the information stored by the storage unit.