Autonomous Database Defragmentation via Online Index Rebuilding
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database defragmentation techniques are not suitable for large-scale server systems providing highly available web services, as they require downtime, are manual, and lack scalability, making them inefficient and logistically unfeasible for systems with many databases and database objects.
Innovation Solution
An autonomous database defragmentation system that uses a fragmentation analyzer and index rebuilder module to identify and address fragmentation by rebuilding index objects and redefining tables online, without downtime, and is scalable for large-scale systems.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional database defragmentation techniques are used, then storage space can be reclaimed and performance improved, but the database system requires downtime and manual intervention
Solution Approach 1:
The system performs preliminary analysis of database fragmentation levels and identifies defragmentation candidates before executing defragmentation operations. This allows the system to plan and execute defragmentation during scheduled maintenance windows while minimizing impact on database availability.
Solution Approach 2:
The autonomous defragmentation system automatically monitors database fragmentation, selects tables requiring defragmentation, and executes defragmentation operations without manual intervention. This self-service capability eliminates the need for manual DBA involvement while maintaining database availability through automated scheduling and execution.
2Productivity
If manual defragmentation processes are employed, then storage efficiency can be improved, but the complexity of operation increases and scalability is limited
Solution Approach 1:
The system autonomously monitors database fragmentation levels, identifies tables requiring defragmentation, and executes defragmentation operations without manual intervention. This eliminates complex manual operations while maintaining storage efficiency through automated decision-making and execution.
Solution Approach 2:
The system continuously monitors database fragmentation levels and uses this feedback to automatically trigger defragmentation operations when thresholds are exceeded. This closed-loop control simplifies operations by replacing manual assessment with automated monitoring and decision-making based on real-time database state.
3Productivity
If defragmentation is performed on large-scale systems with many databases, then overall system performance improves, but the time required and resources consumed increase significantly
Solution Approach 1:
The system divides the large-scale database environment into manageable segments, analyzing and defragmenting tables based on fragmentation levels, table sizes, and priority assignments. This segmentation allows parallel processing of multiple tables and databases, reducing total execution time while maintaining system-wide performance improvements.
Solution Approach 2:
The system performs defragmentation on a subset of tables that meet specific criteria (fragmentation threshold, size thresholds, priority levels) rather than defragmenting all tables. This partial action approach focuses resources on tables that will provide the greatest performance benefit, reducing overall execution time while maintaining system-wide effectiveness.
Data Source
AI summary
Techniques are disclosed relating to performing database defragmentation operations by autonomously rebuilding index objects stored in one or more tablespaces of a database. In various embodiments, the disclosed techniques include autonomously performing defragmentation operations for one or more tablespaces in a database in an online manner such that a server system may continue to service data access requests while performing the defragmentation operations. In one non-limiting embodiment, for example, the disclosed techniques include selecting a first tablespace to defragment based on its level of fragmentation (e.g., relative to the other tablespaces). The server system may then rebuild index objects, from the first tablespace, to a new tablespace such that the index objects are stored in the new tablespace without fragmentation. The server system may then reclaim at least a portion of the storage space used to store the first tablespace and repeat, as desired, to autonomously defragment multiple tablespaces.