Database Memory Tuning to Reduce I/O Activity
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database performance tuning techniques do not efficiently utilize memory capacity, leading to high input/output (I/O) activity and prolonged response times, which are critical issues for time-sensitive applications like healthcare and finance.
Innovation Solution
A method that sets a database-allowed core value between 500 MB and half of available memory, declares small and heavily accessed data structures as memory resident, analyzes for read-ahead and reblocking factors, associates system buffers, and consolidates unused data structures to optimize memory usage and reduce I/O requests.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If existing database performance tuning techniques are used, then some database parameters are optimized, but memory capacity is not efficiently utilized leading to high I/O activity
Solution Approach 1:
The patent applies parameter changes by systematically adjusting multiple database parameters including setting the database-allowed core value to between 500 MB and half of available memory, configuring memory resident limit values, adjusting reblocking factors between 1 and 60, and associating system buffers ranging from 1000 to 20000 with data structures. These coordinated parameter changes transform the database's memory utilization pattern, converting inefficient I/O-bound operations into efficient memory-bound operations, thereby reducing I/O activity while maintaining or improving database performance
Solution Approach 2:
The patent transitions the database operation from a disk-I/O dimension to a memory dimension by declaring small and heavily accessed data structures as memory resident. This dimensional shift moves frequently accessed data from slow disk storage to fast memory, fundamentally changing the performance characteristics by operating in a faster dimension (memory) rather than the slower dimension (disk I/O), thus reducing I/O activity while enhancing productivity
2Productivity
If existing database performance tuning techniques are used, then certain parameters are adjusted, but response time for transaction and batch processing remains prolonged
Solution Approach 1:
The patent applies preliminary action by pre-loading frequently accessed data structures into memory before they are needed for transaction processing. By declaring small and heavily accessed data structures as memory resident and pre-associating system buffers with these structures, the database system has data readily available in memory when transactions arrive, eliminating wait time for disk I/O operations and thus reducing response time while improving transaction throughput
Solution Approach 2:
The patent ensures continuity of useful action by maintaining data structures in memory rather than repeatedly reading from disk. Once data is loaded into memory, it remains available for continuous access by multiple transactions without interruption for I/O operations. This continuous availability of data in memory sustains high transaction throughput and minimizes response time by eliminating repeated I/O wait periods
3Quantity of substance
If memory capacity is increased, then performance can be optimized, but existing techniques do not efficiently utilize the available memory
Solution Approach 1:
The patent applies parameter changes by configuring the database-allowed core value as a specific range (500 MB to half of available memory) and setting memory resident limit values within this range. These parameter adjustments ensure that the database system utilizes a substantial portion of available memory for caching data structures, transforming underutilized memory capacity into active working memory that directly supports transaction processing, thereby improving both memory utilization efficiency and overall productivity
Solution Approach 2:
The patent applies universality by using memory for multiple purposes: storing frequently accessed data structures, maintaining system buffers for various operations, and supporting both transaction processing and batch processing workloads. By configuring parameters that allow memory to serve these multiple functions simultaneously, the system achieves efficient utilization of available memory capacity across different operational scenarios, maximizing the return on memory investment
Data Source
AI summary
A method for performance tuning a database is described. The method includes setting a database-allowed core value between 500 megabytes and half size of memory for the database. Further, the method declares small and heavily accessed structures as memory resident structures, and sets a memory resident limit value between 0 and the database-allowed core value for the database. In addition, the method analyzes the database to determine whether read-ahead should be enabled for the data structures. The method also analyzes the database to apply a reblocking factor between 1 and 60. The method associates system buffers ranging from 1000 to 20000 with the data structures, and stores the system buffers in memory. Moreover, the method consolidates the data structures having unused space.