Index Table Routing for Distributed Database Resource Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database query optimization methods often neglect power consumption and resource utilization, leading to inefficient performance and increased energy use due to redundant indexes in distributed database systems, where multiple copies of indexes consume more memory and power when not all can fit in system memory.
Innovation Solution
A method that identifies and removes redundant indexes from memory, routing queries to the database replica with the index in memory, optimizing resource usage and reducing power consumption by ensuring only one instance of each index resides in memory across servers, allowing more indexes to be held in memory and reducing disk access.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If multiple copies of indexes are maintained in distributed database replicas, then query availability and fault tolerance are improved, but memory consumption and power usage increase
Solution Approach 1:
The patent extracts redundant index copies from memory by identifying which indexes are already loaded into memory on other database replicas. When a database replica needs to load an index, the system checks whether the same index is already in memory on another replica. If it is, the loading operation is skipped, thereby removing the redundant copy from memory and reducing overall memory consumption and power usage while maintaining query availability through the existing index on another replica.
2Reliability
If multiple copies of indexes are maintained in distributed database replicas, then query availability is improved, but memory resources are consumed excessively
Solution Approach 1:
The system extracts redundant index copies by implementing a memory state tracking mechanism. Before loading an index into memory on a database replica, the system queries the memory state of other replicas to determine if the index is already present. This extraction of redundant copies reduces the total quantity of index data stored in memory across the distributed system while preserving query availability through the remaining index copies.
Solution Approach 2:
The patent applies multi-functionality by enabling any database replica to serve as a source for index queries. When an index is needed, the system can retrieve it from any replica that has loaded it, not just the local replica. This universal access mechanism allows the system to reduce local index storage by relying on remote replicas, thereby reducing overall memory consumption while maintaining query availability.
3Speed
If indexes are loaded into memory for faster query processing, then query response time is improved, but power consumption increases
Solution Approach 1:
The system extracts the unnecessary action of loading redundant indexes into memory by implementing a check-before-load mechanism. Before loading an index, the system determines whether the index is already in memory on another replica. If it is, the loading operation is extracted (removed), preventing the power consumption that would result from duplicating the index in memory while still enabling fast query processing through the existing index copy.
Data Source
AI summary
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.