Query dispatching system and method

Patent metadata

Field	Value
Publication number	US-10762108-B2
Application number	US-201815944816-A
Country	US
Kind code	B2
Filing date	Apr 4, 2018
Priority date	Dec 10, 2014
Publication date	Sep 1, 2020
Grant date	Sep 1, 2020

Abstract

Dispatching a database request. A database request is received. Latency times are determined for databases managed by the DBMS, the latency times being estimates of the times to complete replications from the other databases of all unreplicated data changes in the other databases. For each database, a capture velocity and an apply are determined, which are a number of data changes applied to the database that can be identified or applied in a predefined time interval. For each database, an estimated time to complete the database request is determined. The database request is dispatched to the database having an estimated minimum time of execution of the database request, wherein the time of execution is function of the estimated time for the database to complete the database request, the latency time of the database, and the capture and apply velocities of the database.

First claim

What is claimed is:

1. A method for dispatching a database request, the method comprising:
receiving, by a database management system (DBMS), a database request;
determining, by the DBMS, a plurality of latency times corresponding to a plurality of respective databases managed by the DBMS, wherein the latency times for each database of the plurality of databases are current estimates of the times to complete replications from the other databases of the plurality of databases of all unreplicated data changes in the other databases;
determining, by the DBMS, for each database of the plurality of databases, a capture velocity, which is a number of data changes applied to the database that can be identified in a predefined time interval, and an apply velocity, which is a number of identified data changes that can be applied to the database in a predefined time interval;
determining, by the DBMS, for each database of the plurality of databases, an estimated time to complete the database request;
identifying, by the DBMS, a database of the plurality of databases having an estimated minimum time of execution of the database request, wherein the time of execution for each database of the plurality of databases is function of the estimated time for the database to complete the database request, the latency time of the database, and the capture and apply velocities of the database; and
dispatching, by the DBMS, the database request to the database of the plurality of databases having the estimated minimum time of execution of the database request.

2. The method according to claim 1 , wherein the latency time for each database of the plurality of databases is a current estimate of the time to complete a replication from the other databases of the plurality of databases of all unreplicated data changes in the other databases that have an impact on the result of the database request.

3. The method according to claim 1 , wherein determining the latency times for each database of the plurality of databases, further comprises:
determining, by the DBMS, for each database of the plurality of databases, the capture velocity;
determining, by the DBMS, for each database of the plurality of databases, the apply velocity;
determining, by the DBMS, for each database of the plurality of databases, the number of unreplicated data changes in the other databases of the plurality of databases that need to be replicated to the database;
calculating a plurality of latency times, corresponding to each of the other databases, wherein each latency time is the sum of the product of the number of unreplicated data changes in the other database with the capture velocity of the other database, and the product of the number of unreplicated data changes in the other database with the apply velocity of the database.

4. The method according to claim 1 , wherein determining the latency times for each database of the plurality of databases, further comprises:
determining, by the DBMS, for each database of the plurality of databases, the capture velocity;
determining, by the DBMS, for each database of the plurality of databases, the apply velocity;
determining, by the DBMS, for each database of the plurality of databases, a plurality of transfer velocities corresponding to each of the other databases, wherein each transfer velocity is a number of captured data changes that can be transferred from another database of the plurality of databases to the database in a predefined time interval;
determining, by the DBMS, for each database of the plurality of databases, the number of unreplicated data changes in the other databases of the plurality of databases that need to be replicated to the database;
calculating a plurality of latency times, corresponding to each of the other databases, wherein each latency time is the sum of the product of the number of unreplicated data changes in the other database with the capture velocity of the other database, the product of the number of unreplicated data changes in the other database with the apply velocity of the database, and the product of the number of unreplicated data changes in the other database with the transfer velocity for the other database.

5. The method according to claim 1 , wherein the latency time for each database of the plurality of databases is determined by applying heuristics on historical data, taking into consideration one or more of: the kind of data to be replicated, the type or status of a replication engine, and currently observed hardware dependent factors.

6. The method according to claim 2 , wherein identifying the unreplicated data changes that impact the result of the database request further comprises evaluating a transaction log of the source database and extracting database operations that introduced the unreplicated data changes.

7. A computer system for dispatching a database request, the computer system comprising:
a plurality of databases;
a database management system (DBMS) that manages the plurality of databases;
the DBMS operated to:
receive a database request;
determine a plurality of latency times corresponding to a plurality of respective databases managed by the DBMS, wherein the latency times for each database of the plurality of databases are current estimates of the times to complete replications from the other databases of the plurality of databases of all unreplicated data changes in the other databases;
determine, for each database of the plurality of databases, a capture velocity, which is a number of data changes applied to the database that can be identified in a predefined time interval, and an apply velocity, which is a number of identified data changes that can be applied to the database in a predefined time interval;
determine, for each database of the plurality of databases, an estimated time to complete the database request;
identify a database of the plurality of databases having an estimated minimum time of execution of the database request, wherein the time of execution for each database of the plurality of databases is function of the estimated time for the database to complete the database request, the latency time of the database, and the capture and apply velocities of the database; and
dispatch the database request to the database of the plurality of databases having the estimated minimum time of execution of the database request.

8. The computer system according to claim 7 , wherein the latency time for each database of the plurality of databases is a current estimate of the time to complete a replication from the other databases of the plurality of databases of all unreplicated data changes in the other databases that have an impact on the result of the database request.

9. The computer system according to claim 7 , wherein determining the latency times for each database of the plurality of databases, further comprises:
determining, by the DBMS, for each database of the plurality of databases, the capture velocity;
determining, by the DBMS, for each database of the plurality of databases, the apply velocity;
determining, by the DBMS, for each database of the plurality of databases, the number of unreplicated data changes in the other databases of the plurality of databases that need to be replicated to the database;
calculating a plurality of latency times, corresponding to each of the other databases, wherein each latency time is the sum of the product of the number of unreplicated data changes in the other database with the capture velocity of the other database, and the product of the number of unreplicated data changes in the other database with the apply velocity of the database.

10. The computer system according to claim

Assignees

• IBM

Inventors

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

Classifications

• G06F16/2453

  Query optimisation · CPC title

• G06F16/273 Primary

  Asynchronous replication or reconciliation · CPC title

• G06F16/2393

  Updating materialised views · CPC title

• G06F16/27

  Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor · CPC title

• G06F16/2379

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