INDEX TABLE BASED ROUTING FOR QUERY RESOURCE OPTIMIZATION

• US 20170124148A1
• Filed: 10/29/2015
• Published: 05/04/2017
• Est. Priority Date: 10/29/2015
• Status: Active Grant

• Current Assignee: Kyndryl Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: Hamilton, II, Rick A., Hoy, Jeffrey R., O'Connell, Brian M., Pickover, Clifford A., Walker, Keith R.
• Granted Patent: US 10,042,885 B2
• Time in Patent Office: Days
• CPC Class Codes: G06F 1/3234 Power saving characterised by the action undertaken
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24564 Applying rules; Deductive 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
• CPC Class Codes: G06F 16/951 Indexing; Web crawling techniques
• CPC Class Codes: G06F 16/953 Querying, e.g. by the use of web search engines

Abstract

Embodiments of the present invention disclose optimizing resources of distributed database replicas in which one or more processors receive resource information associated with a plurality of database replicas hosted on a plurality of servers, the resource information includes identification of indexes of database tables of each database replica. The instances of indexes residing in memory of the plurality of servers is determined, based on the state of each index, which indicates whether the index resides in memory. The instances of indexes that reside in memory and are redundant among the plurality of database replicas are determined, and in response to receiving a query directed to a database replica, if an index of a database table having the query-requested data resides in memory, the query is routed to the database replica on which the index resides in memory.

Claims

1. A method for optimizing resources of distributed database replicas, the method comprising:

receiving, by one or more processors, resource information associated with a plurality of database replicas hosted on a plurality of servers, the resource information including identification of indexes of database tables and a state of each index of the indexes of the database tables for each database replica of the plurality of database replicas;

determining, by one or more processors, whether at least one instance of the indexes of the database tables reside in memory of the plurality of servers, based on the state of each index of the indexes of the database tables, wherein the state of an index indicates whether the index resides in memory;

responsive to determining at least one instance of the indexes of the database tables resides in memory, determining, by one or more processors, whether the at least one instance of the indexes of the database tables that reside in memory are redundant among the plurality of database replicas; and

in response to receiving a query that is directed to at least one database replica of the plurality of database replicas, determining, by one or more processors, whether the query is directed to data for which an index resides in memory, and if so, routing the query to the at least one database replica of the plurality of database replicas, on which the index resides in memory.

2. The method of claim 1, further comprising:

receiving, by one or more processors, input to optimize resources of the plurality of database replicas, based on the resource information; and

removing from memory, by one or more processors, the at least one instance of the indexes of the database tables that reside in memory and are redundant among the plurality of database replicas.

3. The method of claim 2, wherein receiving input to optimize resources, further comprises:

saving, by one or more processors, a copy of instructions to generate an instance of each index of the indexes of the database tables that is removed from memory, to a file that is accessible to a database management application of the plurality of database replicas.

4. The method of claim 3, wherein receiving, by one or more processors input to optimize resources, further comprises:

removing from storage disks, by one or more processors, one or more instances of indexes that are redundant among the storage disks of the plurality of database replicas hosted on the plurality of servers.

5. The method of claim 1, wherein receiving, by one or more processors, the resource information associated with the plurality of database replicas hosted on the plurality of servers, further comprises:

receiving resource information associated with a type of storage device of each database replica on each of the plurality of servers; and

routing the query that is directed to at least one of the plurality of database replicas, to a database replica of the plurality of database replicas, based on the type of storage device of the database replica.

6. The method of claim 1, wherein receiving, by one or more processors, the resource information associated with the plurality of database replicas hosted on the plurality of servers, further comprises:

receiving, by one or more processors, electrical power consumption data of a plurality of storage devices on which is stored the plurality of database replicas, and electrical power consumption data of the plurality of servers hosting the plurality of database replicas on the plurality of storage devices, and the power consumption data of maintaining one or more of the plurality of indexes in memory of the plurality of servers; and

in response to receiving a query directed to at least one of the plurality of database replicas, routing, by one or more processors, the query to a database replica of the plurality of database replicas projected to consume a lowest amount of electrical power to process the query.

7. The method of claim 1, wherein receiving input to optimize resources, further comprises:

routing the query, by one or more processors, to a database replica of the plurality of database replicas hosted on the plurality of servers, based on a combination of factors included within the resource information that is received, wherein the combination of factors includes one or more of;

whether there is an index of a corresponding database table residing in memory, relative electrical power consumption of a transaction of the query, or security features, associated with each database replica.

8. A computer program product for optimizing resources of distributed database replicas, the computer program product comprising:

one or more computer readable storage media wherein the computer readable storage medium is not a transitory signal per se, and program instructions stored on the one or more computer readable storage media, the program instructions comprising;

program instructions to receive resource information associated with a plurality of database replicas hosted on a plurality of servers, the resource information including identification of indexes of database tables and a state of each index of the indexes of the database tables for each database replica of the plurality of database replicas;

program instructions to determine whether at least one instance of the indexes of the database tables reside in memory of the plurality of servers, based on a state of each index of the indexes of the database tables, wherein the state of an index indicates whether the index resides in memory;

responsive to determining at least one instance of the indexes of the database tables resides in memory, program instructions to determine whether the at least one instance of the indexes that reside in memory are redundant among the plurality of database replicas; and

in response to receiving a query that is directed to at least one database replica of the plurality of database replicas, program instructions to determine whether the query is directed to data for which an index resides in memory, and if so, routing the query to the at least one database replica of the plurality of database replicas, on which the index resides in memory.

9. The computer program product of claim 8, further comprising:

program instructions to receive input to optimize resources of the plurality of database replicas, based on the resource information; and

program instructions to remove from memory the at least one instance of the indexes of the database tables that reside in memory and are redundant among the plurality of database replicas.

10. The computer program product of claim 9, wherein receiving input to optimize resources, further comprises:

program instructions to save a copy of instructions to generate an instance of each index of the indexes of the database tables that is removed from memory, to a file that is accessible to a database management application of the plurality of database replicas.

11. The computer program product of claim 10, wherein program instructions to receive input to optimize resources, further comprises:

program instructions to remove from storage disks, one or more instances of indexes that are redundant among the storage disks of the plurality of database replicas hosted on the plurality of servers.

12. The computer program product of claim 8, wherein program instructions to receive the resource information associated with the plurality of database replicas hosted on the plurality of servers, further comprises:

program instructions to receive resource information associated with a type of storage device of each database replica on each of the plurality of servers; and

program instructions to route the query that is directed to at least one of the plurality of database replicas, to a database replica of the plurality of database replicas, based on the type of storage device of the database replica.

13. The computer program product of claim 8, wherein program instructions to receive the resource information associated with the plurality of database replicas hosted on the plurality of servers, further comprises:

program instructions to receive electrical power consumption data of a plurality of storage devices on which is stored the plurality of database replicas, and electrical power consumption data of the plurality of servers hosting the plurality of database replicas on the plurality of storage devices, and the power consumption data of maintaining one or more of the plurality of indexes in memory of the plurality of servers; and

in response to receiving a query directed to at least one of the plurality of database replicas, program instructions to route the query to a database replica of the plurality of database replicas projected to consume a lowest amount of electrical power to process the query.

14. The computer program product of claim 8, wherein program instructions to receive input to optimize resources, further comprises:

program instructions to route the query to a database replica of the plurality of database replicas hosted on the plurality of servers, based on a combination of factors included within the resource information that is received, wherein the combination of factors includes one or more of;

whether there is an index of a corresponding database table residing in memory, relative electrical power consumption of a transaction of the query, or security features, associated with each database replica.

15. A computer system for optimizing resources of distributed database replicas, the computer system comprising:

one or more computer processors, one or more computer readable storage media, program instructions stored on the computer readable storage media for execution by at least one of the one or more processors, the program instructions comprising;

program instructions to receive resource information associated with a plurality of database replicas hosted on a plurality of servers, the resource information including identification of indexes of database tables and a state of each index of the indexes of the database tables for each database replica of the plurality of database replicas;

program instructions to determine whether at least one instance of the indexes of the database tables reside in memory of the plurality of servers, based on the state of each index of the indexes of the database tables, wherein the state of an index indicates whether the index resides in memory;

responsive to determining at least one instance of the indexes of the database tables resides in memory, program instructions to determine whether the at least one instance of the indexes of the database tables that reside in memory are redundant among the plurality of database replicas; and

in response to receiving a query that is directed to at least one database replica of the plurality of database replicas, program instructions to determine whether the query is directed to data for which an index resides in memory, and if so, routing the query to the at least one database replica on which the index resides in memory.

16. The computer system of claim 15, further comprising:

program instructions to receive input to optimize resources of the plurality of database replicas, based on the resource information; and

program instructions to remove from memory, the at least one instance of the indexes of the database tables that reside in memory and are redundant among the plurality of database replicas.

17. The computer system of claim 16, wherein receiving input to optimize resources, further comprises:

program instructions to save a copy of instructions to generate an instance of each index of the indexes of the database tables that is removed from memory, to a file that is accessible to a database management application of the plurality of database replicas.

18. The computer system of claim 15, wherein program instructions to receive the resource information associated with the plurality of database replicas hosted on the plurality of servers, further comprises:

program instructions to receive resource information associated with a type of storage device of each database replica on each of the plurality of servers; and

program instructions to route the query that is directed to at least one of the plurality of database replicas, to a database replica of the plurality of database replicas, based on the type of storage device of the database replica.

19. The computer system of claim 15, wherein program instructions to receive the resource information associated with the plurality of database replicas hosted on the plurality of servers, further comprises:

program instructions to receive electrical power consumption data of a plurality of storage devices on which is stored the plurality of database replicas, and electrical power consumption data of the plurality of servers hosting the plurality of database replicas on the plurality of storage devices, and the power consumption data of maintaining one or more of the plurality of indexes in memory of the plurality of servers; and

in response to receiving a query directed to at least one of the plurality of database replicas, program instructions to route the query to a database replica of the plurality of database replicas projected to consume a lowest amount of electrical power to process the query.

20. The computer system of claim 15, wherein program instructions to receive input to optimize resources, further comprises:

program instructions to route the query to a database replica of the plurality of database replicas hosted on the plurality of servers, based on a combination of factors included within the resource information that is received, wherein the combination of factors includes one or more of;

whether there is an index of a corresponding database table residing in memory, relative electrical power consumption of a transaction of the query, or security features, associated with each database replica.