System and Methodology Providing Workload Management in Database Cluster

• US 20090037367A1
• Filed: 08/17/2007
• Published: 02/05/2009
• Est. Priority Date: 07/30/2007
• Status: Active Grant

• Current Assignee: Sybase Incorporated (SAP SE)
• Original Assignee: Sybase Incorporated (SAP SE)
• Inventors: Wein, David
• Granted Patent: US 9,141,435 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 11/2035 without idle spare hardware
• CPC Class Codes: G06F 11/2046 where the redundant components share persistent storage G06F11/2043 takes precedence
• CPC Class Codes: G06F 11/2097 maintaining the standby controller/processing unit updated initialisation or re-synchronisation thereof G06F11/1658 and subgroups
• CPC Class Codes: G06F 11/3409 for performance assessment
• CPC Class Codes: G06F 11/3433 for load management allocation of a server based on load conditions G06F9/505; load rebalancing G06F9/5083; redistributing the load in a network by a load balancer H04L67/1029
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 2201/80 Database-specific techniques
• CPC Class Codes: G06F 2209/5019 Workload prediction
• CPC Class Codes: G06F 2209/5022 Workload threshold
• CPC Class Codes: G06F 9/5061 Partitioning or combining of resources
• CPC Class Codes: G06F 9/5083 Techniques for rebalancing the load in a distributed system

Abstract

A system and methodology providing workload management in a database cluster. In one embodiment, for example, a method for allocating workload amongst a plurality of database servers sharing access to data comprises steps of: periodically collecting information about workload at each database server; distributing the workload information to each of the plurality of database servers; in response to a client connected to a first database server requesting that work be performed, examining the workload information to determine whether the requested work should be performed at a second database server having a lower workload than the first database server; if it is determined that the work should be performed at the second database server, automatically transferring the client connection to the second database server and performing the requested work at the second database server; and otherwise, performing the requested work at the first database server.

Claims

1. A method for allocating workload amongst a plurality of database servers sharing access to data, the method comprising:

periodically collecting information about workload at each database server;

distributing the workload information to each of said plurality of database servers;

in response to a client connected to a first database server requesting that work be performed, examining the workload information to determine whether the requested work should be performed at a second database server having a lower workload than the first database server;

if it is determined that the work should be performed at the second database server, automatically transferring the client connection to the second database server and performing the requested work at the second database server; and

otherwise, performing the requested work at the first database server.

2. The method of claim 1, wherein said examining step includes substeps of:

if the workload at the first database server is greater than a pre-defined base threshold, calculating difference between workload at the first database server and workload at the second database server; and

transferring the requested work to the second database server if the difference in workload is calculated to be greater than a dynamic threshold.

3. The method of claim 2, wherein the dynamic threshold is user configurable.

4. The method of claim 1, further comprising:

receiving user input of a load profile including weights to be applied to elements of the workload information in calculating workload; and

calculating workload at the first database server and the second database server using said load profile.

5. The method of claim 1, wherein the plurality of database servers are organized into a plurality of logical clusters, each logical cluster comprising at least one database server and associated with a particular application.

6. The method of claim 5, wherein the first database server and the second database server are associated with a particular logical cluster.

7. The method of claim 5, wherein workload is calculated separately at database servers of each logical cluster, so as to isolate a given logical cluster from other logical clusters.

8. The method of claim 5, further comprising:

receiving user input of a logical cluster load profile including weights to be applied to elements of the workload information in calculating workload at database servers of a given logical cluster.

9. The method of claim 1, wherein said workload information include selected ones of user connection metrics, CPU utilization metrics, run-queue length metrics, Input/Output (I/O) load metrics, and online database engine metrics at each database server.

10. The method of claim 9, wherein said workload information further comprises a user-configurable metric which enables users to specify custom information to be used in calculating workload.

11. The method of claim 9, further comprising:

calculating workload based on applying user configurable weightings to said workload information.

12. The method of claim 11, further comprising:

generating an ordered list of database servers to which a client should connect based, at least in part, upon the calculated workload; and

upon failure of the first database server while the client is connected to the first database server, using the ordered list for selecting a database server to which the client should failover.

13. A computer-readable medium having processor-executable instructions for performing the method of claim 1.

14. The method of claim 1, further comprising:

downloading a set of processor-executable instructions for performing the method of claim 1.

29. The method of claim 15, further comprising:

downloading a set of processor-executable instructions for performing the method of claim 1.

15. In a distributed database system comprising a plurality of servers sharing access to data, a method for directing clients to connect to servers based on workload, the method comprising:

periodically collecting statistics about workload at each of said plurality of servers;

calculating a load score for each server based on the collected statistics;

distributing the load score to each of the plurality of servers;

in response to a client attempting to connect to a particular server, determining whether the client should be redirected to another server based on the load scores;

if it is determined that the client should be redirected to another server, providing the client with an ordered list of servers to which the client may connect; and

otherwise, allowing the client to connect to the particular server.

16. The method of claim 15, wherein the servers comprise database servers sharing access to at least one database.

17. The method of claim 15, wherein said determining step includes substeps of:

determining whether the load score at the particular server is greater than a pre-defined base threshold;

if the load score is greater than a pre-defined base threshold, determining whether difference between load score at the particular server and load score at another server is greater than a login threshold; and

if the difference in the load score is determined to be greater than the login threshold, redirecting the client to another server.

18. The method of claim 17, wherein the login threshold is user configurable.

19. The method of claim 15, further comprising:

receiving user input of a load profile including weights to be applied to the workload statistics in calculating a load score for each server; and

calculating a load score for each server using said load profile.

20. The method of claim 15, wherein the plurality of servers are organized into a plurality of logical clusters, each logical cluster comprising at least one server and associated with a particular application.

21. The method of claim 20, wherein load scores are calculated separately at servers of each logical cluster, so as to isolate a given logical cluster from other logical clusters.

22. The method of claim 20, further comprising:

receiving user input of a load profile for a given logical cluster including weights to be applied to the workload statistics in calculating a load score at servers of the given logical cluster.

23. The method of claim 15, wherein said workload statistics include selected ones of user connection metrics, CPU utilization metrics, run-queue length metrics, Input/Output (I/O) load metrics, and online database engine metrics at each server.

24. The method of claim 22, wherein said workload statistics include a user-configurable metric which enables a user to specify custom information to be used in calculating load scores.

25. The method of claim 22, further comprising:

calculating load scores based on applying user configurable weightings to said workload statistics.

26. The method of claim 15, wherein said ordered list of servers comprises an list of servers ordered based, at least in part, upon the calculated load scores.

27. The method of claim 15, further comprising:

periodically updating the ordered list of servers based on periodic updates to the workload statistics;

providing the ordered list of servers to a client connected to a given server; and

upon failure of the given server while the client is connected to the given server, using the ordered list for selecting a server to which the client should failover.

28. A computer-readable medium having processor-executable instructions for performing the method of claim 15.

30. In a distributed database cluster comprising a plurality of database servers, a system for allocating workload amongst the plurality of database servers, the system comprising:

a statistics collector module which periodically collects workload statistics at each database server;

a load profile module at each database server for calculating a load score for such database server based on the workload statistics and publishing the load score to other database servers in the cluster; and

a workload manager module, responsive to a client requesting that work be performed at the first database server, which automatically transfers the client to a second database server when it determines that the requested work should be performed at a second database server having a lower load score than the first database server.

31. The system of claim 30, wherein said workload manager module responds to an attempt by a client to connect to the first database server.

32. The system of claim 30, wherein said workload manager module responds to submission of a query batch by a client to connected the first database server.

33. The system of claim 30, wherein the client remains connected to the first database server when said workload manager module determines that the load score at the first database server is less than a pre-defined base threshold.

34. The system of claim 33, wherein the workload manager calculates difference between the load score at the first database server and the load score at the second database server when the load score at the first database server is greater than the pre-defined base threshold.

35. The system of claim 34, wherein the workload manager module transfers the client to the second database server when the difference is greater than a user configured threshold.

36. The system of claim 30, wherein said load profile module calculates load scores based on a load profile which includes weights to be applied to elements of the workload statistics.

37. The system of claim 30, further comprising:

a logical cluster module enabling a user to organize the plurality of database servers into a plurality of logical clusters, each logical cluster comprising at least one database server and associated with a particular application.

38. The system of claim 37, wherein said load profile module allows users to create a load profiles for a given logical cluster, the load profile including weights to be applied to elements of the workload statistics in calculating load scores at database servers of the given logical cluster.

39. The system of claim 30, wherein said workload statistics include selected ones of user connection metrics, CPU utilization metrics, run-queue length metrics, Input/Output (I/O) load metrics, and online database engine metrics at each database server.

40. The system of claim 39, wherein said workload statistics further comprises a user-configurable metric which enables users to specify custom information to be used in calculating load scores.

41. The system of claim 39, wherein the load profile module calculates load scores based on applying user configurable weightings to said workload statistics.

42. The system of claim 41, wherein the load profile module provides a client with an ordered list of database servers in the cluster to which the client should connect based, at least in part, upon the calculated load scores.

43. The system of claim 42, wherein the client uses the ordered list of database servers to select a database server to which the client should connect at login time.

44. The system of claim 42, wherein the client uses the ordered list of database servers to select a particular database server to which the client should connect upon failure of the client'"'"'s current connection to a given database server.