Workload balancing in a distributed database

• US 10,108,654 B2
• Filed: 10/28/2016
• Issued: 10/23/2018
• Est. Priority Date: 02/05/2013
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Abbour, Mohamed A., De Buitlear, Ciaran, Cecil, Roy R., Matthews, John, Meehan, Patrick A.
• Primary Examiner(s): Uddin, Md I
• Application Number:
  US15/337,878
• Publication Number:
  US 20170046375A1
• Time in Patent Office: 725 Days
• Field of Search: 707703, 707718, 707E17005, 707999008
• CPC Class Codes: G06F 16/2343 Locking methods, e.g. distributed locking or locking implementation details
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor

Abstract

A distributed database management system balances the load on query boundaries and to achieve a more evenly balanced load. The system includes two or more query processors with a common data source. A database client manages a transaction including two or more queries. A transaction analyzer checks if a transaction can be processed by more than one query processor. A query processor optimizer locates the optimum query processor for each query in the transaction based on workload of each query processor, while a workload balancer directs each query to the optimum query processor if the transaction can be processed by more than one query processor. This approach is particularly well suited to a clustered database environment that includes query processor nodes that work off a shared disk to provide database access to clients that connect to the node.

Claims

1. A distributed database management system for transaction processing comprising:

two or more query processors with a common data source;

a transaction analyzer for checking if a transaction can be processed by more than one query processor;

a query processor optimizer for locating an optimum query processor for each query in the transaction based on workload of each query processor;

a workload balancer for directing each query to the optimum query processor based on an idleness factor for each query processor if the transaction can be processed by more than one query processor, wherein the idleness factor is based on a relative capacity for each query processor, a transaction type, and transaction length; and

a lock mechanism for locking portions of the common data source at a query level to a new query processor, wherein the lock mechanism can be transferred to the new query processor when a transaction is transferred to the new query processor and using existing global lock and cache data, and the transaction is transferred as a whole;

wherein the workload balancer balances a workload for each query processor in the more than one query processors, using the lock mechanism, such that a processing speed and the idleness factor of each query processor is balanced with respect to the other query processors in more than one query processors;

a logging engine for log records of events pertaining to changes to the common data source; and

wherein the transaction analyzer identifies open cursors and aborting a transfer if an open cursor is identified; and

wherein the transaction analyzer is configured for determining that a tag indicating that the transaction is distributable is associated with the transaction, and for determining that a tag indicating that the transaction is a long running or a short running transaction.

2. The system of claim 1, further comprising an at least one database client and an application for interacting with the database client, wherein the application is configured for identifying a distributable transaction with the tag before it is sent to the database client.

3. The system of claim 1, wherein the logging engine is further configured to log records of a mapping between the query processor, application, and transaction.

4. The system of claim 3, further comprising a roll back mechanism for rolling back a transaction using the log records.

5. A method for transaction processing in a distributed database management system comprising two or more query processors with a common data source and at least one database client for managing a transaction including two or more queries, said method comprising:

checking if a transaction can be processed by more than one query processor;

locating an optimum query processor for each query in the transaction based on workload of each query processor;

directing each query to the optimum query processor based on an idleness factor for each query processor if the transaction can be processed by more than one query processor, wherein the idleness factor is based on a relative capacity for each query processor, a transaction type, and transaction length;

locking portions of the common data source at a query level to a new query processor, wherein a lock mechanism can be transferred to the new query processor when a transaction is transferred to a new query processor and using existing global lock and cache data, and the transaction is transferred as a whole;

balancing a workload for each query processor in the more than one query processors, using the lock mechanism, such that a processing speed and an idleness factor of each query processor is balanced with respect to the other query processors in more than one query processors;

storing log records of events pertaining to changes to the common data source;

identifying open cursors and aborting a transfer if an open cursor is identified; and

wherein a tag indicating that the transaction is distributable is associated with the transaction, and a tag indicating that the transaction is a long running or a short running transaction is associated with the transaction.

6. The method of claim 5, further comprising interacting with the database client and identifying a distributable transaction with the tag before it is sent to the database client.

7. The method of claim 5, further comprising updating a list of workload availability of each query processor and updating the query processor optimizer with the list of workload availability.

8. The method of claim 5, further comprising identifying and tagging a transaction with a transaction type.

9. The method of claim 5, further comprising storing log records of a mapping between the query processor, application and transaction.

10. The method of claim 9 further comprising rolling back a transaction using the log records.

11. A computer program product for transaction processing in a distributed database management system, the computer program product comprising a non-transitory computer-readable storage medium having computer-readable program code embodied therewith, the computer-readable program code configured to perform:

checking if a transaction can be processed by more than one query processor;

locating an optimum query processor for each query in the transaction based on workload of each query processor;

directing each query to the optimum query processor based on an idleness factor of each query processor if the transaction can be processed by more than one query processor, wherein the idleness factor is based on a relative capacity for each query processor, a transaction type, and transaction length;

locking portions of a common data source at a query level to a new query processor, wherein a lock mechanism can be transferred to the new query processor when a transaction is transferred to the new query processor and using existing global lock and cache data, and the transaction is transferred as a whole;

balancing a workload for each query processor in the more than one query processors, using the lock mechanism, such that a processing speed and an idleness factor of each query processor is balanced with respect to the other query processors in more than one query processors;

storing log records of events pertaining to changes to the common data source;

identifying open cursors and aborting a transfer if an open cursor is identified; and

wherein a tag indicating that the transaction is distributable is associated with the transaction, and a tag indicating that the transaction is a long running or a short running transaction is associated with the transaction.

12. The computer program product of claim 11, further comprising interacting with a database client and identifying the distributable transaction with the tag before it is sent to the database client.

13. The computer program product of claim 11, further comprising updating a list of workload availability of each query processor and updating the query processor optimizer with the list of workload availability.

14. The computer program product of claim 11, further comprising identifying and tagging a transaction with a transaction type.