Dynamic Write Destination Control for MySQL WAL and Table Data
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Systems using MySQL as a DBMS on Linux OS experience performance deterioration due to high-frequency data writing exceeding the capacity of secondary storage media, leading to increased waiting times and reduced processing performance.
Innovation Solution
A control device that detects access states and I/O usage ratios for table data and write-ahead logs, dynamically setting the writing destination for high-frequency data to either a physical disk or a separate storage medium based on these ratios to optimize data distribution and reduce load on secondary storage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If MySQL stores high-frequency data in a single secondary storage medium (physical disk), then the system structure remains simple, but the writing performance deteriorates when high-frequency data exceeds the storage capacity
Solution Approach 1:
The patent segments the storage system into multiple storage media (first storage medium for table data, second storage medium for WAL, and third storage medium for high-frequency data). This segmentation allows each storage medium to handle specific data types, preventing any single medium from being overwhelmed by high-frequency writes and thus improving overall writing performance.
Solution Approach 2:
The patent extracts high-frequency data from the general storage pool and directs it to a dedicated third storage medium. By separating high-frequency data access paths from the main storage system, the patent reduces contention and improves writing performance for frequently accessed data without significantly increasing overall system complexity.
2Loss of time
If MySQL uses a single secondary storage medium for both table data and WAL, then the storage configuration is simple, but the waiting time increases due to I/O contention
Solution Approach 1:
The patent divides the storage system into distinct segments: first storage medium for table data, second storage medium for WAL, and third storage medium for high-frequency data. This segmentation eliminates I/O contention between different data types by providing dedicated storage paths, thereby reducing waiting time for data writing operations.
Solution Approach 2:
The patent introduces a storage management mechanism that acts as an intermediary, dynamically determining which storage medium to use based on data characteristics and access patterns. This intermediary layer optimizes data placement and access, reducing waiting time without requiring complex manual configuration.
3Productivity
If the system dynamically adjusts writing destinations based on I/O use ratios, then the processing efficiency improves, but the control complexity increases
Solution Approach 1:
The patent implements a feedback mechanism that continuously monitors I/O use ratios of different storage media and dynamically adjusts writing destinations accordingly. When the I/O use ratio of a storage medium exceeds a threshold, the system automatically redirects high-frequency data to alternative storage media, optimizing processing efficiency through adaptive control.
Solution Approach 2:
The patent changes the writing destination parameter dynamically based on I/O use ratio thresholds. By monitoring and adjusting the destination parameter according to real-time storage medium performance, the system maintains optimal processing efficiency without requiring complex permanent reconfiguration.
Data Source
AI summary
A control device according to the present invention includes: a memory; and processing circuitry coupled to the memory and configured to: detect a state of access to table data stored in a first logical disk of a physical disk and to which first data is written and a state of access to a write ahead log (WAL) stored in a second logical disk of the physical disk and to which second data is written or to a WAL stored in a second storage medium different from the physical disk and to which the second data is written, and acquire an I/O use ratio of the WAL and an I/O use ratio of the table data, and set a writing destination of the second data to one of the WAL stored in the second logical disk and the WAL stored in the second storage medium.