Workload balancing in a distributed database

• US 9,542,429 B2
• Filed: 02/03/2014
• Issued: 01/10/2017
• Est. Priority Date: 02/05/2013
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Cecil, Roy R., De Buitlear, Ciaran, Abbour, Mohamed A., Matthews, John, Meehan, Patrick A.
• Primary Examiner(s): Uddin, Md. I
• Application Number:
  US14/171,111
• Publication Number:
  US 20140297611A1
• Time in Patent Office: 1,072 Days
• Field of Search: 707/703, 707/718, 707/E17.005, 707/999.008, 707/706, 707/770
• US Class Current: 1/1
• 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;

at least one database client for managing a transaction including two or more queries;

a transaction analyzer for checking if a transaction can be processed by more than one query processor, 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;

a query processor optimizer for locating the 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 new optimum query processor if the transaction can be processed by more than one query processor; and

further comprising 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 a new query processor and using existing global lock and cache data, and the transaction is transferred as a whole.

2. A system according to claim 1, further comprising an application for interacting with the database client and for identifying a distributable transaction with the tag before it is sent to the database client.

3. A system according to claim 1, further comprising a cursor checker for identifying open cursors and aborting a transfer is an open cursor is identified.

4. A system according to claim 1, further comprising a logging engine for logging records of the mapping between the query processor, application and transaction.

5. A system according to claim 4, further comprising a roll back mechanism for rolling back a transaction using the log records.

6. 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, 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;

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

directing each query to the new optimum query processor if the transaction can be processed by more than one query processor; and

locking portions of the common data source, via a locking mechanism, 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 a new query processor and using existing global lock and cache data, and the transaction is transferred as a whole.

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

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

9. The method of claim 6, further comprising identifying and tagging a transaction with a transaction type.

10. The method of claim 6, further comprising identifying open cursors and aborting a transfer is an open cursor is identified.

11. The method of claim 6, further comprising logging records of the mapping between the query processor, application and transaction.

12. The method of claim 11 further comprising rolling back a transaction using the log records.

13. A computer program product for transaction processing in a distributed database management system, the computer program product comprising a 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, 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;

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

directing each query to the new optimum query processor if the transaction can be processed by more than one query processor; and

locking portions of the common data source, via a locking mechanism, 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 a new query processor and using existing global lock and cache data, and the transaction is transferred as a whole.

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

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

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