System and Method for SQL Query Load Balancing

• US 20110119251A1
• Filed: 11/16/2009
• Published: 05/19/2011
• Est. Priority Date: 11/16/2009
• Status: Active Grant

• Current Assignee: Dell Products LP (Dell Technologies Inc.)
• Original Assignee: Dell Products LP (Dell Technologies Inc.)
• Inventors: Yu, Patrick
• Granted Patent: US 8,176,037 B2
• Time in Patent Office: Days
• US Class Current: 707/719
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 2209/5022 Workload threshold
• CPC Class Codes: G06F 9/505 considering the load
• CPC Class Codes: H04L 67/1001 for accessing one among a plurality of replicated servers
• CPC Class Codes: H04L 67/1097 for distributed storage of data in networks, e.g. transport arrangements for network file system [NFS], storage area networks [SAN] or network attached storage [NAS]

Abstract

Systems and methods for SQL query load balancing are disclosed. A method may include establishing a ranking scheme, assigning a rank to an SQL query from the ranking scheme based on a plurality of rank assignment parameters, determining a threshold rank based on a plurality of gathered operational characteristics, comparing the assigned rank to the threshold rank, and, if the assigned rank is greater than the threshold rank, executing the SQL query on a database server. Otherwise, if the assigned rank is not greater than the threshold rank, executing the SQL query on a centralized storage system.

Claims

1. A method for allocating the execution of a SQL query in a system comprising a database server and a storage system communicatively coupled to the database server via a communication network, comprising:

establishing a ranking scheme comprising a plurality of rank values;

assigning a first rank from the plurality of rank values to the SQL query;

determining a threshold rank from the plurality of rank values;

comparing the first rank to the threshold rank;

if the first rank is greater than the threshold rank, communicating the SQL query to the database server for execution; and

if the first rank is not greater than the threshold rank, communicating the SQL query to the storage system for execution.

2. The method of claim 1, further comprising:

monitoring a plurality of operational characteristics of the system; and

adjusting the threshold rank based at least on a change in the plurality of operational characteristics.

3. The method of claim 1, wherein assigning the first rank to the SQL query is based at least on a predetermined estimate of the number of database reads that the SQL query will require.

4. The method of claim 1, wherein the plurality of rank values are based at least on a ratio of the number of rows that may result from the SQL query to the number of rows of the database needed to be scanned by the SQL query.

5. The method of claim 1, wherein the plurality of operational characteristics comprises a current CPU use level of the database server.

6. The method of claim 1, wherein the plurality of operational characteristics comprises a current CPU use level of the storage system.

7. The method of claim 1, wherein the plurality of operational characteristics comprises a current network load level of the communication network.

8. The method of claim 1, wherein assigning the first rank to the SQL query comprises:

determining whether the SQL query uses an index scan; and

if the SQL query uses an index scan, assigning the highest value in the ranking scheme to be the value of the first rank of the SQL query.

9. The method of claim 1, wherein assigning the first rank to the SQL query comprises:

determining whether the SQL query comprises at least an outer table query and an inner table query;

if the SQL query comprises at least an outer table query and an inner table query, assigning the first rank to an outer table query of the SQL query and assigning a second rank to the inner table query of the SQL query.

10. The method of claim 9, wherein assigning the second rank to the inner table query of the SQL query comprises:

determining whether the inner table query uses an index scan; and

if the inner query uses an index scan, assigning the highest value in the ranking scheme to be the value of the second rank; and

if the inner query does not use an index scan, assigning the second rank from a second plurality of rank values, wherein the second plurality of rank values are based at least on the number of rows resulting from the outer query of the SQL query.

11. A system for allocating the execution of a SQL query between a database server and a storage system coupled to the database server via a communication network, comprising a load balancing module communicatively coupled to the database server, the load balancing module configured to:

establish a ranking scheme comprising a plurality of rank values;

assign a first rank from the plurality of rank values to the SQL query;

determine a threshold rank from the plurality of rank values;

compare the first rank to the threshold rank;

if the first rank is greater than the threshold rank, communicate the SQL query to the database server for execution; and

if the first rank is not greater than the threshold rank, communicate the SQL query to the storage system for execution.

12. The system of claim 11, wherein the load balancing module is further configured to:

monitor a plurality of operational characteristics of the system; and

adjust the threshold rank based at least on a change in the plurality of operational characteristics.

13. The system of claim 11, wherein the load balancing module is further configured to assign the first rank to the SQL query based at least on a predetermined estimate of the number of database reads that the SQL query will require.

14. The system of claim 11, wherein the plurality of rank values are based at least on a ratio of the number of rows that may result from the SQL query to the number of rows of the database needed to be scanned by the SQL query.

15. The system of claim 11, wherein the plurality of operational characteristics comprises a current CPU use level of the database server.

16. The system of claim 11, wherein the plurality of operational characteristics comprises a current CPU use level of the storage system.

17. The system of claim 11, wherein the plurality of operational characteristics comprises a current network load level of the communication network.

18. The system of claim 11, wherein the load balancing component is further configured to:

determine whether the SQL query uses an index scan; and

if the SQL query uses an index scan, assign the highest value in the ranking scheme to be the value of the first rank of the SQL query.

19. The system of claim 11, wherein the load balancing component is further configured to:

determine whether the SQL query comprises at least an outer table query and an inner table query; and

if the SQL query comprises at least an outer table query and an inner table query, assign the first rank to an outer table query of the SQL query and assign a second rank to the inner table query of the SQL query.

20. The system of claim 19, wherein the load balancing module is further configured to:

determine whether the inner table query uses an index scan; and

if the inner query uses an index scan, assign the highest value in the ranking scheme to be the value of the second rank; and

if the inner query does not use an index scan, assign the second rank from a second plurality of rank values, wherein the second plurality of rank values are based at least on the number of rows resulting from the outer query of the SQL query.