Index table based routing for query resource optimization

Patent metadata

Field	Value
Publication number	US-10042885-B2
Application number	US-201514926997-A
Country	US
Kind code	B2
Filing date	Oct 29, 2015
Priority date	Oct 29, 2015
Publication date	Aug 7, 2018
Grant date	Aug 7, 2018

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.

First claim

What is claimed is:

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, a state of each index of the indexes of the database tables for each database replica of the plurality of database replicas, 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;
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 and is projected to consume a lowest amount of electrical power to process the query.

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 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.

7. 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, 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;
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 and is projected to consume a lowest amount of electrical power to process the query.

8. The computer program product of claim 7 , 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.

9. The computer program product of claim 8 , 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.

10. The computer program product of claim 9 , 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.

11. The computer program product of claim 7 , 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 o

Assignees

• IBM

Inventors

• Hamilton Ii Rick A
• Hoy Jeffrey R
• O'Connell Brian M
• Pickover Clifford A
• Walker Keith R

Classifications

• G06F16/2453 Primary

  Query optimisation · CPC title

• G06F16/24564

  Applying rules; Deductive queries · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F1/3234

  Power saving characterised by the action undertaken · CPC title

• G06F16/27

  Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor · CPC title