Query dispatching system and method

Patent metadata

Field	Value
Publication number	US-2016171070-A1
Application number	US-201514856664-A
Country	US
Kind code	A1
Filing date	Sep 17, 2015
Priority date	Dec 10, 2014
Publication date	Jun 16, 2016
Grant date	—

Abstract

A computer receives a database request to be executed on a source database or a target database. The source database is configured to efficiently process database queries of a first query type, the target database is configured to efficiently process database queries of a second query type, data changes in the source database are asynchronously replicated to the target database, and all changed data in the source database impacting a result of the database request are replicated to the target database before executing the database request. The computer determines a latency time to asynchronously complete a replication of unreplicated data changes and determines a velocity of replication. The computer predicts, using the latency time and velocity of replication, which of the databases will complete an execution of the database request first and dispatches the database request to the database predicted to complete the execution of the database request first.

First claim

What is claimed is:

1 . A method for dispatching a database request to either a source database or a target database, wherein the source database is configured to efficiently process database queries of a first query type, wherein the target database is configured to efficiently process database queries of a second query type, wherein data changes in the source database are asynchronously replicated to the target database, and wherein all changed data in the source database impacting a result of the database request are replicated to the target database before executing the database request, the method comprising:
receiving, by a computer, a database request to be executed on a source database or a target database; determining, by the computer, a latency time to asynchronously complete a replication from the source database to the target database of, at least, all unreplicated data changes that impact the result of the received database request; determining, by the computer, a velocity of replication to the target database, wherein the velocity of replication is one of:
a capture velocity, the capture velocity being a number or size of data changes applied to the source database that can be identified in a predefined time interval;
an apply velocity, the apply velocity being a number or size of identified data changes that can be applied to the target database in a predefined time interval;
a function of the capture velocity;
a function of the apply velocity; or
a function of both the capture and the apply velocity;
predicting, by the computer, using the determined latency time and determined velocity of replication, which of the source database or the target database will complete an execution of the received database request first, wherein a high latency time and a low velocity lowers a probability that the execution of the received database request will be finished first on the target database; and responsive to predicting which of the source database or the target database will complete the execution of the database request first,
dispatching, by the computer, the database request to the source database or the target database predicted to complete the execution of the database request first.

2 . The method according to claim 1 , wherein determining, by the computer, the velocity of replication further comprises calculating, by the computer, the velocity of replication according to the following formula: Vtotal=(minimum(Vcapture,Vapply))*C1+C2,
wherein Vcapture is the capture velocity, wherein Vapply is the apply velocity, wherein Vtotal is the determined velocity of replication, and wherein C 1 and C 2 are correction constants.

3 . The method according to claim 1 , further comprising:
receiving, by the computer, a first estimated time for executing the received database request on the source database; receiving, by the computer, a second estimated time for executing the received database request on the target database; and wherein predicting, by the computer, which of the source database or the target database will complete the execution of the received database request first further includes using the received first estimated time and second estimated time, wherein a long received first estimated time and a short received second estimated time increases the probability that the execution of the received database request will be finished first on the target database.

4 . The method according to claim 1 , wherein predicting, by the computer, which of the source database or the target database will complete the execution of the received database request first, further comprises:
determining, by the computer, a transfer velocity, the transfer velocity being a number or size of identified data changes applied to the source database that can be transferred to the target database in a predefined time interval during an asynchronous replication to the target database; calculating, by the computer, a total target database time, starting at the receipt of the database request by the computer and ending at the completion of the execution of the received database request on the target database, according to the following formula:

CTD
=

EQ



2

+

(

L
*

VT
V

*
C



3

)

+
CF

,

wherein L is the determined latency time,
wherein VT is the determined transfer velocity,
wherein V is the determined velocity of replication,
wherein C 3 is a correction constant,
wherein CTD is the total target database time,
wherein CF is a constant indicating invariable hardware, software and/or firmware-related delays, and
wherein EQ 2 is an estimated time for executing the received database request on the target database; and

comparing, by the computer, the calculated total target database time with an estimated time for executing the received database request on the source database.

5 . The method according to claim 3 , further comprising:
normalizing, by the computer, the received first estimated time and the received second estimated time.

6 . The method according to claim 1 , further comprising:
identifying, by the computer, the unreplicated data changes in the source database that impact the result of the received database request, the unreplicated data changes including:
data changes most recently committed in the source database by a transaction; and
data changes in the source database, made by a database operation in a same transaction as the received database request, wherein the database operation directly precedes the received database request and wherein the data changes are not committed.

7 . The method according to claim 4 wherein the target database is one of a plurality of target databases, the method further comprising:
requesting, by the computer, an estimated time for executing the received database request from each of the plurality of target databases;
receiving, by the computer, a plurality of estimated times from each of two or more of the plurality of target databases;
wherein determining, by the computer, the latency time further comprises determining, by the computer the latency time to asynchronously complete a replication from the source database to each of the two or more of the target databases;
wherein determining, by the computer, the velocity of replication further comprises determining, by the computer, the velocity of replication to each of the two or more target databases;
wherein calculating, by the computer, the total target database time further comprises calculating, by the computer, the total target database time for each of the two or more target databases;
wherein comparing, by the computer, the calculated total target database time further comprises comparing, by the computer, eac

Assignees

• IBM

Inventors

• Hrle Namik
• Lohrer Andreas
• Martin Daniel
• Tschaffler Matthias

Classifications

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F16/2379

  Updates performed during online database operations; commit processing · CPC title

• G06F16/2453

  Query optimisation · CPC title

• G06F16/2393

  Updating materialised views · CPC title

• G06F16/273 Primary

  Asynchronous replication or reconciliation · CPC title