A database-based method, device, and medium for stratified processing of hot and cold data.

Abstract

This invention discloses a database-based method, device, and medium for tiered processing of hot and cold data, belonging to the field of database technology. It addresses the technical problems of inaccurate tiered identification of hot and cold page data in existing databases, poor transaction consistency migration, high storage costs, and the potential impact on overall database performance. The method includes: comprehensively calculating page heat based on the write-ahead log status and page access behavior status in the database kernel; determining hot and cold page data in the database based on the comprehensive page heat value; performing pre-migration judgment on the hot and cold page data, and controlling transaction consistency during the migration process based on the LSN consistency control module; migrating cold page data within the hot and cold page data; and restoring the heat of cold page data in the cold page archive results through historical SQL periodic behavior, while simultaneously updating the database buffer.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a method, device and medium for database-based cold and hot data layering processing. Background Technology

[0002] As the scale of database business continues to grow, the amount of data stored in PostgreSQL databases is increasing rapidly. Database systems need to simultaneously meet the following requirements: (1) high-performance hot transaction access; (2) low-cost storage of massive historical data; (3) mixed workload support of Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP); and (4) long-term archived data management.

[0003] Existing hot and cold data tiering solutions are mostly geared towards file systems or big data platforms. They typically identify and migrate hot and cold data based on file access frequency, directory access frequency, or file lifecycle, but lack the ability to perceive internal database transaction behavior, data page update behavior, and database buffer status.

[0004] Currently, common database storage architectures typically employ solid-state drives (SSDs) to store hot data and hard disk drives (HDDs) or object storage to store cold data. However, PostgreSQL itself does not provide a complete database page-level hot and cold data lifecycle management mechanism. PostgreSQL uses a page-based data storage structure, where tables, indexes, and transaction data are managed in the form of data pages. The following kernel behaviors occur during database operation: (1) Write Ahead Log (WAL) writing; (2) Shared Buffer cache hit; (3) Database Checkpoint flushing; (4) Multi-Version Concurrency Control (MVCC) version chain maintenance; and (5) Vacuum Garbage Collection (VACUUM) execution.

[0005] In other words, existing cold and hot data tiering schemes mainly identify cold and hot data based on the number of file accesses or the frequency of table accesses. Since they do not take into account the behavior of the database kernel, this can lead to: low accuracy in cold and hot data identification; potential disruption of transaction consistency during cold and hot data migration; impact on database performance during cold and hot data migration; and high latency in cold data recovery. Summary of the Invention

[0006] This application provides a database-based method, device, and medium for tiered processing of hot and cold data, which addresses the following technical problems: existing databases have insufficient accuracy in identifying the tiers of hot and cold pages, poor transaction consistency migration, high storage costs, and can easily affect the overall performance of the database.

[0007] The embodiments of this application adopt the following technical solutions: On one hand, this application provides a database-based method for tiered processing of hot and cold data, comprising: in the background process of a PostgreSQL database, performing a comprehensive calculation of page heat based on the write-ahead log status and page access behavior status in the database kernel to obtain a comprehensive page heat value; determining hot and cold page data in the database based on the comprehensive page heat value; performing pre-migration judgment on the hot and cold page data according to the background process status of the checkpoint in the database, and performing transaction consistency control on the migration process of the hot and cold page data based on the LSN consistency control module to obtain the migration control result of the hot and cold page data; performing migration processing on the cold page data in the hot and cold page data according to the migration control result to obtain a cold page archive result; and performing heat recovery processing on the cold page data in the cold page archive result through historical SQL periodic behavior, and synchronously updating the database buffer.

[0008] This application's embodiments, through deep integration with the PostgreSQL kernel's operational characteristics, bring substantial technical improvements in four aspects: accuracy (identifying hot pages), security (transaction consistency), stability (minimal performance impact), and efficiency (fast access to cold data). It effectively addresses the pain points of traditional hot / cold data tiering schemes in relational databases, especially PostgreSQL. It significantly improves the accuracy and granularity of hot / cold data identification, ensures strong consistency of database transactions during hot / cold data migration, greatly reduces the impact of hot / cold data migration on online database performance, and significantly improves the efficiency of cold data access and recovery, as well as enabling fine-grained hot / cold data tiering management at the database page level.

[0009] In one feasible implementation, before comprehensively calculating page heat based on the write-ahead log status and page access behavior status in the database kernel, the method further includes: monitoring the PostgreSQL WAL write process in real time through a WAL log collection unit, and parsing the corresponding Relation File Node, Block Number, and Transaction ID in the WAL Record; locating the corresponding data page based on the Block Number, and counting the WAL increment corresponding to the data page per unit time; calculating the WAL growth rate of the data page based on the WAL increment, and generating a page-level WAL heat value; after writing the page-level WAL heat value into a heat cache table, periodically scanning the shared buffer descriptors in the database; reading the buffer reference count based on the shared buffer descriptors, and counting the number of buffer hits, the number of dirty pages, and the buffer replacement behavior; calculating the page-level buffer heat value based on the counted number of buffer hits, the number of dirty pages, and the buffer replacement behavior; wherein, the page-level buffer heat value is the ratio between the number of buffer hits and the statistical period.

[0010] In one feasible implementation, in the background process of the PostgreSQL database, a comprehensive page heat value is calculated by combining the write-ahead log status and page access behavior status in the database kernel. Specifically, this includes: in the background process of the PostgreSQL database, reading and statistically analyzing the tuple update frequency and VACUUM execution frequency based on the page modification behavior in the database kernel; determining the proportion of dirty pages in the database based on the number of dirty pages; and performing a comprehensive calculation of page heat value based on preset heat weight parameters for the page-level WAL heat value, the page-level buffer heat value, the dirty page proportion, the tuple update frequency, and the VACUUM execution frequency to obtain the comprehensive page heat value.

[0011] In one feasible implementation, hot and cold page data in the database are determined based on the comprehensive page popularity value. Specifically, if the comprehensive page popularity value is greater than or equal to a preset hot page threshold, the target data is determined to be hot page data; if the comprehensive page popularity value is less than a preset cold page threshold, the target data is determined to be cold page data.

[0012] In one feasible implementation, the pre-migration determination of the hot and cold page data is performed based on the background process status of the Checkpoint in the database. Specifically, this includes: monitoring the background process of the Checkpoint in the database through the Checkpoint collaborative scheduling module to obtain the execution result of the Checkpoint; if the execution result is a Checkpoint in progress status, then the migration control of the hot and cold page data in the pre-migration state is paused; if the execution result is a Checkpoint completed status, then the migration control of the hot and cold page data in the pre-migration state is started; and the migration status of the hot and cold page data is updated based on the Checkpoint status in the execution result.

[0013] In one feasible implementation, based on the LSN consistency control module, transaction consistency control is performed on the migration process of the hot and cold page data to obtain the migration control result of the hot and cold page data. Specifically, this includes: through the LSN consistency control module, reading the PageLSN of the page to be migrated and the current WALFlushLSN of the hot and cold page data under the start migration control; if the PageLSN of the page to be migrated is less than or equal to the current WAL FlushLSN, the hot and cold page data under the start migration control is determined to be in an allowed migration state; if the PageLSN of the page to be migrated is greater than the current WAL FlushLSN, the hot and cold page data under the start migration control is determined to be in a waiting WAL Flush state; wherein, after the waiting WAL Flush state changes to the WAL Flush complete state, the hot and cold page data is determined to be in the allowed migration state; wherein, the migration control result includes: the allowed migration state and the waiting WALFlush state; after obtaining the migration control result, the migration processing of the hot and cold page data is executed, and the page metadata is updated.

[0014] In one feasible implementation, based on the migration control result, the cold page data in the hot and cold page data is migrated to obtain a cold page archiving result. Specifically, this includes: if the migration control result is in a migration-allowed state, reading the cold page list in the hot and cold page data and generating a cold page migration task; based on the cold page migration task, copying the cold page data and archiving the copied cold page data to complete the migration; after the cold page data is archived, updating the page mapping relationship of the cold page data and synchronizing the page status of the migrated cold page data with the current hot page data; updating page metadata based on the updated page mapping relationship of the cold page data and the synchronized hot and cold page data status; wherein, the cold page archiving result includes: the archived storage of the cold page data, the page mapping relationship of the cold page data, the synchronized update status of the page status, and the synchronized update status of the page metadata.

[0015] In one feasible implementation, the cold page data in the cold page archive results is subjected to hot page recovery processing based on historical SQL periodic behavior, and the database buffer is updated synchronously. Specifically, this includes: collecting historical SQL logs through a cold page pre-warming module, identifying periodic behavior in the historical SQL logs to obtain the historical SQL periodic behavior; predicting the cold page access window based on the historical SQL periodic behavior; generating a warming task related to the recovery of the cold page data according to the cold page access window; restoring the target cold page data in the cold page archive results to the database's solid-state drive through the warming task, and synchronously updating the database buffer based on the recovery result of the target cold page data.

[0016] Secondly, embodiments of this application also provide a database-based hot and cold data stratification processing device, the device comprising: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor to enable the at least one processor to execute a database-based hot and cold data stratification processing method as described in any of the above embodiments.

[0017] Thirdly, embodiments of this application also provide a non-volatile computer storage medium, which is a non-volatile computer-readable storage medium storing at least one program. Each program includes instructions, which, when executed by a terminal, cause the terminal to perform a database-based hot and cold data tiering processing method as described in any of the above embodiments.

[0018] This application provides a database-based method, device, and medium for layered processing of hot and cold data. Compared with the prior art, the embodiments of this application have the following beneficial technical effects: 1. It can prevent hot data from being mistakenly migrated to cold storage, thereby ensuring that high-performance solid-state drive (SSD) resources are truly used for core hot data, optimizing the balance between storage cost and performance.

[0019] 2. It ensures PostgreSQL's WAL-based fault recovery capabilities, the visibility and correctness of multi-version concurrency control (MVCC) snapshots, and the integrity and recoverability of data pages, solving the transaction consistency problem that traditional file-level migration solutions are powerless in database scenarios.

[0020] 3. It effectively avoids the migration task competing for I / O resources with critical background processes (such as Checkpointer), eliminating potential problems such as disk I / O jitter, checkpoint delay, and soaring transaction response time, thus ensuring stable throughput of the database during the migration process.

[0021] 4. It solves the high latency problem of traditional solutions when accessing cold data for the first time, greatly shortens the response time of analytical SQL or historical data queries, and improves the user experience in complex query scenarios.

[0022] 5. It enables optimal granularity of hot and cold data management, maximizes the utilization efficiency of high-performance storage media, and significantly reduces overall storage costs. Attached Figure Description

[0023] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings: Figure 1 A flowchart of a database-based method for layering hot and cold data is provided for embodiments of this application; Figure 2 A schematic diagram of the overall architecture of a database-based hot and cold data tiered processing system is provided for embodiments of this application; Figure 3 This is a schematic diagram of a database-based hot and cold data stratification processing device provided in an embodiment of this application. Detailed Implementation

[0024] To enable those skilled in the art to better understand the technical solutions in this application, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this application.

[0025] It should be noted that for PostgreSQL object-relational databases, the traditional hot / cold tiering scheme has the following problems: (1) Inability to accurately identify the real hot data in the database: Existing technologies usually use the number of file accesses or the frequency of directory accesses as the basis for judging hot and cold data. However, hot data in the PostgreSQL database is not only related to the number of reads, but also closely related to database kernel behaviors such as data page update frequency, write-ahead log (WAL) growth rate, dirty page ratio, and shared buffer hit rate. The number of accesses alone cannot accurately reflect the real hot data pages.

[0026] (2) Lack of transaction consistency control in the cold and hot migration process: Existing technologies mainly target the file system for cold and hot data migration, without involving database transaction consistency issues. Directly migrating data pages in the PostgreSQL database may lead to inconsistent log sequence numbers (LSN), abnormal transaction snapshot visibility, and failure of multi-version concurrency control (MVCC) status.

[0027] (3) Cold and hot migration can easily cause database checkpoint jitter: Traditional cold and hot migration is usually performed at any time. The migration process and the PostgreSQL database checkpoint flushing process compete for resources, which can easily lead to disk input / output (I / O) jitter, buffer pollution and increased transaction response latency.

[0028] (4) Low efficiency of cold data recovery: Existing technologies usually trigger recovery operations only when cold data is accessed, resulting in high recovery latency when Structured Query Language (SQL) or historical queries are accessed for the first time, which affects database query performance.

[0029] In other words, existing hot and cold data tiering schemes mainly identify hot and cold data based on file access counts or table access frequencies. Since they don't consider database kernel behavior, this leads to: 1) Low accuracy in hot and cold data identification: Database hotspots are not only characterized by frequent reads, but also by high-frequency updates, high-frequency transaction commits, high-frequency dirty page writes, and high-frequency WAL generation. Therefore, access counts alone cannot accurately identify truly hot data pages. This results in: incorrect migration of hot data, wasted SSD resources, and decreased performance of hot transactions. 2) Hot and cold migration may disrupt transaction consistency: PostgreSQL databases use WAL logs and MVCC mechanisms to maintain transaction consistency. If, during hot and cold migration, the page state is inconsistent with the LSN, the WAL is not yet persisted, or the MVCC version chain is not synchronized, it may lead to: unrecoverable data pages, abnormal transaction visibility, and data consistency errors. 3) Hot and cold migration impacts database performance: Existing migration mechanisms typically perform data migration operations directly. The migration process may involve resource contention with: Database Checkpoint flushing, Buffer Flush, and VACUUM garbage collection, further leading to: I / O jitter, Checkpoint delays, and decreased database throughput. 4) High latency in cold data recovery: Existing technologies typically recover cold data only when a query occurs; therefore, the initial query latency is high, analytical SQL response is slow, and the performance of large-scale historical queries is poor.

[0030] This application provides a database-based method for layering hot and cold data, such as... Figure 1 As shown, the database-based method for stratifying hot and cold data specifically includes steps S101-S105: As a feasible implementation method, this application provides a PostgreSQL hot and cold page tiering method based on Write-Ahead Log (WAL) and page access behavior analysis. This application targets the PostgreSQL database kernel and achieves page-level hot and cold data lifecycle management by analyzing WAL logs generated during database operation, shared buffer access behavior, dirty page changes, database checkpoint execution status, and Multi-Version Concurrency Control (MVCC) transaction behavior. Furthermore, this application does not identify hot and cold data based on traditional file access counts, but rather comprehensively judges data popularity based on the transaction activity level of database pages, WAL log growth rate, page modification frequency, and shared buffer hit behavior.

[0031] It should be noted that, as Figure 2As shown, the core technical system framework of this application includes: a WAL analysis module; a Shared Buffer heat analysis module; a page heat calculation module; a hot and cold page classification module; a Checkpoint collaborative scheduling module; an LSN consistency control module; a hot and cold page migration module; a cold page archiving module; a cold page pre-warming module; and a hot and cold page metadata management module. Specifically, the WAL analysis module connects to the PostgreSQL WAL Writer background process to collect WAL growth information in real time; the Shared Buffer heat analysis module connects to the Buffer Manager to calculate the buffer hit rate and dirty page ratio; the Checkpoint collaborative module connects to the Checkpointer background process to monitor the database checkpoint execution status; the hot and cold page migration module is responsible for hot and cold page data migration and hot and cold page metadata updates; and the LSN consistency control module is used to verify the WAL persistence state before and after hot and cold page migration.

[0032] S101. In the background process of the PostgreSQL database, the page heat value is calculated by combining the write-ahead log status and page access behavior status in the database kernel.

[0033] Specifically, based on the WAL analysis module, the WAL log collection unit first monitors the PostgreSQL WAL write process in real time and parses the corresponding Relation File Node, Block Number, and TransactionID in the WAL Record. Then, based on the Block Number, the corresponding data page is located, and the WAL increment corresponding to the data page is calculated per unit time. Afterward, based on the WAL increment, the WAL growth rate of the data page is calculated, and a page-level WAL heat value is generated.

[0034] In one embodiment, the WAL analysis module is used to identify frequently updated hot pages in the database. All data modification operations in the PostgreSQL database generate corresponding WAL logs, therefore the WAL growth rate reflects the activity level of data page updates. This WAL analysis module mainly includes: (1) a WAL log collection unit; (2) a WAL page mapping unit; (3) a WAL popularity statistics unit; and (4) a page-level WAL popularity caching unit. The specific implementation process is as follows: Step 1: The WAL log collection unit monitors the PostgreSQL WAL write process in real time; Step 2: Parse the corresponding Relation File Node, Block Number, and Transaction ID in the WAL Record; Step 3: Locate the corresponding data page based on the Block Number; Step 4: Calculate the WAL increment corresponding to the data page within a unit of time; Step 5: Calculate the WAL growth rate of the data page; Step 6: Generate page-level WAL heat values; Step 7: Write the WAL heat value into the heat cache table.

[0035] In one embodiment, the WAL heat value is calculated as follows: WALHeat = WALSize / TimeWindow; where WALHeat represents the page-level WAL heat value; WALSize represents the WAL increment per unit time; and TimeWindow represents the statistical time window.

[0036] Furthermore, after writing the page-level WAL heat value to the heat cache table, the Shared Buffer heat analysis module periodically scans the shared buffer descriptors in the database. Then, based on the shared buffer descriptors, the buffer reference count is read, and the number of buffer hits, the number of dirty pages, and buffer replacement behavior are counted. Based on the counted number of buffer hits, the number of dirty pages, and the buffer replacement behavior, the page-level buffer heat value is calculated. The page-level buffer heat value is the ratio between the number of buffer hits and the statistical period.

[0037] In one embodiment, the Shared Buffer hotspot analysis module is used to analyze the access status of data pages in the PostgreSQL Shared Buffer. This Shared Buffer hotspot analysis module mainly includes: (1) a Buffer hit statistics unit; (2) a dirty page statistics unit; (3) a page evictation statistics unit; and (4) a Buffer hotspot calculation unit. The specific implementation process is as follows: Step 1: Periodically scan the Shared Buffer Descriptor; Step 2: Read the Buffe reference count; Step 3: Count the number of buffer hits; Step 4: Count the number of Dirty Pages; Step 5: Analyze Buffer Replacement behavior; Step 6: Calculate the page-level buffer heat value; Step 7: Output the Buffer heat result.

[0038] In one embodiment, the formula for calculating the Buffer heat value is: BufferHeat = HitCount / ScanWindow; where BufferHeat represents the Buffer heat value; HitCount represents the number of Buffer hits; and ScanWindow represents the statistical period.

[0039] Furthermore, in the background process of the PostgreSQL database, based on the page modification behavior in the database kernel, it is necessary to read and statistically analyze the tuple update frequency and VACUUM execution frequency. Then, based on the number of dirty pages, the proportion of dirty pages in the database is determined. Subsequently, according to preset popularity weight parameters, and through the page popularity calculation module, a comprehensive calculation of page-level WAL popularity value, page-level buffer popularity value, dirty page proportion, tuple update frequency, and VACUUM execution frequency is performed to obtain a comprehensive page popularity value.

[0040] In one embodiment, the page popularity comprehensive calculation module is used to calculate a comprehensive popularity value by combining WAL popularity, Buffer popularity, and page modification behavior. This page popularity calculation module includes: (1) a popularity index reading unit; (2) a weight calculation unit; (3) a comprehensive popularity generation unit; and (4) a hot and cold page classification unit. The specific process is as follows: Step 1: Read the WAL heat value; Step 2: Read the Buffer heat value; Step 3: Read the Dirty Page ratio; Step 4: Read the Tuple update frequency; Step 5: Read the VACUUM execution frequency; Step 6: Perform comprehensive heat calculation; Step 7: Output the overall heat score (HeatScore); Step 8: Divide the data into hot and cold pages based on the threshold.

[0041] The formula for calculating the overall heat score is: HeatScore = α × WALRate + β × BufferHitRate + γ × DirtyPageRate + δ × TupleUpdateRate + ε × VacuumFrequency; where HeatScore represents the overall page heat score; WALRate represents the WAL growth rate; BufferHitRate represents the Shared Buffer hit rate; DirtyPageRate represents the proportion of dirty pages; TupleUpdateRate represents the tuple update frequency; VacuumFrequency represents the VACUUM execution frequency; and α, β, γ, δ, and ε are heat score weighting parameters.

[0042] S102. Based on the overall page popularity value, determine the hot and cold page data in the database.

[0043] Specifically, based on the hot / cold page classification module, if the overall page popularity value is greater than or equal to the preset hot page threshold, the target data is determined to be hot page data. If the overall page popularity value is less than the preset cold page threshold, the target data is determined to be cold page data.

[0044] In one embodiment, the hot / cold page classification module determines a page as a hot page if its overall page heatscore is greater than or equal to a preset hot page threshold, and as a cold page if its overall page heatscore is less than a preset cold page threshold.

[0045] S103. Based on the background process status of the Checkpoint in the database, pre-migration judgment is performed on the cold and hot page data, and based on the LSN consistency control module, transaction consistency control is performed on the migration process of the cold and hot page data to obtain the migration control result of the cold and hot page data.

[0046] Specifically, the Checkpoint coordination and scheduling module first monitors the background processes of Checkpoints in the database to obtain their execution results. If the result indicates "Checkpoint in progress," the migration of cold and hot page data in the pre-migration state is paused. If the result indicates "Checkpoint complete," the migration of cold and hot page data in the pre-migration state is initiated. Finally, based on the Checkpoint status in the execution results, the migration status of the cold and hot page data is updated.

[0047] In one embodiment, the Checkpoint co-scheduling module is used to reduce the impact of hot and cold page migration on database I / O. PostgreSQL databases flush dirty pages during Checkpoint execution. If hot and cold page migrations are performed simultaneously, a large amount of I / O contention may occur. Therefore, this application adopts a hot and cold page migration scheduling mechanism based on the Checkpoint cycle. This Checkpoint co-scheduling module includes: (1) a Checkpoint status monitoring unit; (2) a migration task suspension unit; (3) a migration task triggering unit; and (4) an I / O load control unit. The co-scheduling mechanism in the Checkpoint co-scheduling module can effectively reduce: Checkpoint jitter, buffer flush contention, SSD write amplification, and database transaction latency. The specific execution process is as follows: Step 1: Monitor the status of the Checkpointer background process; Step 2: Check if the current checkpoint has been executed; Step 3: If Checkpoint is in progress, pause the hot and cold page migration; Step 4: If Checkpoint is complete, initiate the hot and cold page migration; Step 5: Limit the I / O throughput of the migration thread; Step 6: Update the hot and cold page migration status.

[0048] Furthermore, the LSN consistency control module is needed to read the PageLSN of the page to be migrated and the current WAL FlushLSN of the cold and hot page data under migration initiation control. If the PageLSN of the page to be migrated is less than or equal to the current WAL FlushLSN, the cold and hot page data under migration initiation control is determined to be in an allowable migration state. If the PageLSN of the page to be migrated is greater than the current WAL FlushLSN, the cold and hot page data under migration initiation control is determined to be in a waiting WAL Flush state. After the waiting WAL Flush state changes to the WAL Flush complete state, the cold and hot page data is determined to be in an allowable migration state. The migration control result includes: allowable migration state and waiting WAL Flush state; after obtaining the migration control result, the migration processing of the cold and hot page data is executed, and the page metadata is updated.

[0049] In one embodiment, the LSN consistency control module is used to ensure transaction consistency during the cold and hot page migration process. Since PostgreSQL implements transaction recovery and transaction visibility based on WAL and MVCC, cold and hot page migration must satisfy WAL persistence consistency. The LSN consistency control module of this application includes: (1) a page LSN reading unit; (2) a WAL persistence status reading unit; (3) an LSN comparison unit; (4) a transaction consistency verification unit; and (5) a migration permission control unit. Through LSN consistency control, the following can be guaranteed: WAL recovery consistency, MVCC snapshot consistency, data page transaction visibility correctness, and data page recoverability. The specific process is as follows: Step 1: Read the PageLSN of the page to be migrated; Step 2: Read the current WAL FlushLSN; Step 3: Compare PageLSN and FlushLSN; Step 4: If PageLSN is less than or equal to FlushLSN, then migration is allowed; Step 5: If PageLSN is greater than FlushLSN, then wait for WAL Flush; Step 6: Update page metadata after migration is complete; Step 7: Record the migration log.

[0050] S104. Based on the migration control results, migrate the cold page data in the cold and hot page data to obtain the cold page archiving results.

[0051] Specifically, in the cold and hot page migration module, if the migration control result indicates that migration is allowed, the cold page list in the cold and hot page data is read, and a cold page migration task is generated. Then, based on the cold page migration task, the cold page data is copied, and the copied cold page data is archived and stored through the cold page archiving module to complete the cold page data migration. After the cold page data is archived and stored, the page mapping relationship of the cold page data is updated, and the page status of the migrated cold page data is synchronized with the current hot page data. Finally, based on the updated page mapping relationship of the cold page data and the synchronized cold and hot page data status, the page metadata is updated. The cold page archiving result includes: the archived storage of cold page data, the page mapping relationship of the cold page data, the synchronized update status of the page status, and the synchronized update status of the page metadata.

[0052] In one embodiment, the hot and cold page migration module is used to migrate cold pages to low-cost storage. It includes: (1) a hot and cold page selection unit; (2) a page migration task generation unit; (3) a page replication unit; (4) a page mapping update unit; and (5) a page status synchronization unit. The specific process is as follows: Step 1: Read the list of cold pages; Step 2: Generate migration task; Step 3: Copy cold pages to archive storage; Step 4: Update the page mapping; Step 5: Synchronize hot and cold page status; Step 6: Update page metadata.

[0053] The archive storage in the cold page archiving results includes: HDD, network storage, object storage, and distributed archive nodes.

[0054] S105. Through historical SQL periodic behavior, perform hot page recovery processing on cold page data in the cold page archive results, and synchronously update the database buffer.

[0055] Specifically, the cold page pre-warming module first collects historical SQL logs and identifies periodic behaviors in these logs to determine historical SQL periodic behaviors. Then, based on these historical SQL periodic behaviors, the cold page access window is predicted. Following this cold page access window, a warming-up task for recovering cold page data is generated. Finally, the warming-up task restores the target cold page data from the cold page archive results to the database's solid-state drive, and the database buffer is synchronously updated based on the recovery results of the target cold page data.

[0056] In one embodiment, the cold page pre-warming module is used to reduce cold page recovery latency and includes: (1) an SQL behavior analysis unit; (2) an access cycle prediction unit; (3) a warming task generation unit; and (4) a page recovery unit. The specific process is as follows: Step 1: Collect historical SQL logs; Step 2: Analyze SQL cycle behavior; Step 3: Predict cold page access window; Step 4: Generate a warm-up task; Step 5: Pre-emptively restore cold pages to the SSD; Step 6: Update the Buffer state.

[0057] In one embodiment, if the system detects that a certain analytical SQL statement is executed at the end of each month, the system can automatically restore the corresponding cold page before the end of the month. This reduces: initial query latency, cold page restoration waiting time, and analytical SQL response latency.

[0058] As a feasible implementation method, the metadata management module is used to manage the lifecycle information of hot and cold pages, including: (1) a page status management unit; (2) a hot and cold page mapping management unit; (3) a migration log management unit; and (4) a recovery status management unit. The metadata tables mainly include: (1) PageHeatTable; (2) PageArchiveTable; (3) MigrationLogTable; and (4) WarmupTaskTable. Among them, PageHeatTable is used to record page heat values; PageArchiveTable is used to record page archive locations; MigrationLogTable is used to record migration logs; and WarmupTaskTable is used to record pre-warmup tasks.

[0059] Meanwhile, the overall execution flow of this application is as follows: Step S1: Collect WAL logs; Step S2: Statistically analyze SharedBuffer status; Step S3: Statistically analyze Dirty Page status; Step S4: Statistically analyze Tuple update frequency; Step S5: Calculate page heat; Step S6: Identify hot and cold pages; Step S7: Monitor Checkpoint status; Step S8: Perform LSN consistency verification; Step S9: Migrate cold pages; Step S10: Update page metadata; Step S11: Archive cold pages; Step S12: Analyze historical SQL behavior; Step S13: Perform cold page pre-warming. This enables database page-level hot and cold data lifecycle management and effectively improves the accuracy of database hot and cold data stratification and transaction access performance.

[0060] In addition, embodiments of this application also provide a database-based cold and hot data stratification processing device, such as... Figure 3 As shown, the database-based hot and cold data stratification processing device 300 specifically includes: At least one processor 301; and a memory 302 communicatively connected to the at least one processor 301; wherein the memory 302 stores instructions executable by the at least one processor 301, enabling the at least one processor 301 to execute: In the background process of the PostgreSQL database, the page heat value is calculated by combining the write-ahead log status and page access behavior status in the database kernel. Based on the overall page popularity value, hot and cold page data in the database are determined; Based on the background process status of the Checkpoint in the database, pre-migration judgment is performed on the cold and hot page data, and based on the LSN consistency control module, transaction consistency control is performed on the migration process of the cold and hot page data to obtain the migration control result of the cold and hot page data. Based on the migration control results, the cold page data in the cold and hot page data is migrated to obtain the cold page archive results; By utilizing historical SQL periodic behavior, the cold page data in the cold page archive results is restored to its original state, and the database buffer is updated synchronously.

[0061] This application, by deeply integrating with the runtime characteristics of the PostgreSQL kernel, brings substantial technical improvements in four aspects: accuracy (identifying hot pages), security (transaction consistency), stability (minimal performance impact), and efficiency (fast access to cold data). It effectively addresses the pain points of traditional hot / cold data tiering schemes in relational databases, especially PostgreSQL. It significantly improves the accuracy and granularity of hot / cold data identification, ensures strong consistency of database transactions during hot / cold data migration, greatly reduces the impact of hot / cold data migration on online database performance, and significantly improves the efficiency of cold data access and recovery, as well as enabling fine-grained hot / cold data tiering management at the database page level.

[0062] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the device and medium embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the description of the method embodiments.

[0063] The devices and media provided in this application are one-to-one with the methods. Therefore, the devices and media also have similar beneficial technical effects as their corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the devices and media will not be repeated here.

[0064] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0065] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart... Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0066] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0067] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0068] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0069] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0070] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0071] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0072] The above description is merely an embodiment of this application and is not intended to limit the scope of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the scope of this specification.

Claims

1. A database-based method for stratified processing of hot and cold data, characterized in that, The method includes: In the background process of the PostgreSQL database, the page heat value is calculated by combining the write-ahead log status and page access behavior status in the database kernel. Based on the comprehensive page popularity value, hot and cold page data in the database are determined; Based on the background process status of the Checkpoint in the database, the cold and hot page data are pre-migrated and judged. Based on the LSN consistency control module, the migration process of the cold and hot page data is subject to transaction consistency control to obtain the migration control result of the cold and hot page data. Based on the migration control results, the cold page data in the cold and hot page data is migrated to obtain the cold page archiving results; By using historical SQL periodic behavior, the cold page data in the cold page archive results is processed for hot page recovery, and the database buffer is updated synchronously.

2. The database-based method for layered processing of hot and cold data according to claim 1, characterized in that, Before performing a comprehensive calculation of page heat based on the write-ahead log status and page access behavior status in the database kernel, the method further includes: The WAL log collection unit monitors the PostgreSQL WAL write process in real time and parses the corresponding Relation File Node, Block Number, and Transaction ID in the WAL Record. Based on the Block Number, locate the corresponding data page and calculate the WAL increment corresponding to the data page per unit time. Based on the WAL increment, calculate the WAL growth rate of the data page and generate a page-level WAL heat value. After writing the page-level WAL heat value into the heat cache table, the shared buffer descriptors in the database are periodically scanned. Based on the shared buffer descriptor, the Buffer reference count is read, and the number of Buffer hits, the number of DirtyPages, and the Buffer Replacement behavior are calculated. Based on the statistically analyzed number of buffer hits, number of dirty pages, and buffer replacement behavior, a page-level buffer popularity value is calculated; wherein, the page-level buffer popularity value is the ratio between the number of buffer hits and the statistical period.

3. The database-based method for layered processing of hot and cold data according to claim 2, characterized in that, In the background process of the PostgreSQL database, a comprehensive page heat value is calculated by combining the write-ahead log status and page access behavior status in the database kernel. This comprehensive page heat value includes: In the background process of the PostgreSQL database, based on the page modification behavior in the database kernel, the Tuple update frequency and VACUUM execution frequency are read and counted. Based on the number of Dirty Pages, the proportion of Dirty Pages in the database is determined; Based on preset heat weight parameters, a comprehensive calculation of page heat is performed on the page-level WAL heat value, the page-level Buffer heat value, the DirtyPage ratio, the Tuple update frequency, and the VACUUM execution frequency to obtain the comprehensive page heat value.

4. The database-based method for layered processing of hot and cold data according to claim 1, characterized in that, Based on the comprehensive page popularity value, the hot and cold page data in the database are determined, specifically including: If the overall page popularity value is greater than or equal to the preset hot page threshold, the target data is determined to be hot page data; If the overall page popularity value is less than the preset cold page threshold, the target data is determined to be the cold page data.

5. The database-based method for layered processing of hot and cold data according to claim 1, characterized in that, Based on the background process status of the Checkpoint in the database, a pre-migration determination is performed on the cold and hot page data, specifically including: The Checkpoint collaborative scheduling module monitors and processes the Checkpoints in the database as background processes to obtain the execution results of the Checkpoints. If the execution result is a Checkpoint execution status, then the cold and hot page data in the pre-migration state will be paused for migration control. If the execution result is Checkpoint complete, then the cold and hot page data in the pre-migration state will be started for migration control. Based on the Checkpoint status in the execution result, update the migration status of the hot and cold page data.

6. The database-based method for layered processing of hot and cold data according to claim 5, characterized in that, Based on the LSN consistency control module, transaction consistency control is performed on the migration process of the cold and hot page data to obtain the migration control result of the cold and hot page data, specifically including: The LSN consistency control module reads and processes the cold and hot page data under the start migration control by comparing the PageLSN of the page to be migrated with the current WAL FlushLSN. If the PageLSN of the page to be migrated is less than or equal to the current WAL FlushLSN, then the cold and hot page data under the start migration control are determined to be in a state where migration is allowed. If the PageLSN of the page to be migrated is greater than the current WAL FlushLSN, then the cold and hot page data under the start migration control are determined to be in the waiting WAL Flush state; wherein, after the waiting WAL Flush state changes to the WAL Flush completed state, the cold and hot page data are determined to be in the allowed migration state. The migration control results include: the allowed migration status and the waiting WAL Flush status; After obtaining the migration control result, the migration process of the hot and cold page data is performed, and the page metadata is updated.

7. The database-based method for layered processing of hot and cold data according to claim 1, characterized in that, Based on the migration control results, the cold page data in the cold and hot page data is migrated to obtain the cold page archiving results, specifically including: If the migration control result is in the allowed migration state, then read the cold page list from the cold and hot page data and generate a cold page migration task; Based on the cold page migration task, the cold page data is copied and the copied cold page data is archived and stored to complete the migration of the cold page data. After the cold page data is archived and stored, the page mapping relationship of the cold page data is updated, and the page status of the migrated cold page data is synchronized with the current hot page data. Update the page metadata based on the updated page mapping relationship of cold page data and the synchronized cold and hot page data status. The cold page archiving results include: the archived storage of the cold page data, the page mapping relationship of the cold page data, the synchronous update status of the page status, and the synchronous update status of the page metadata.

8. The database-based method for layering hot and cold data according to claim 1, characterized in that, By utilizing historical SQL periodic behavior, the cold page data in the cold page archive results is subjected to hot page restoration processing, and the database buffer is updated synchronously. Specifically, this includes: The cold page pre-warming module collects historical SQL logs and identifies periodic behavior in the historical SQL logs to obtain the historical SQL periodic behavior. Based on the historical SQL cycle behavior, the cold page access window is predicted; and according to the cold page access window, a warm-up task related to the recovery of the cold page data is generated. The warm-up task restores the target cold page data from the cold page archive results to the solid-state drive of the database, and updates the database buffer synchronously based on the restoration results of the target cold page data.

9. A database-based cold and hot data stratification processing device, characterized in that, The device includes: At least one processor; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor to enable the at least one processor to perform a database-based hot and cold data stratification processing method according to any one of claims 1-8.

10. A non-volatile computer storage medium, characterized in that, The storage medium is a non-volatile computer-readable storage medium that stores at least one program, each program including instructions that, when executed by a terminal, cause the terminal to perform a database-based hot and cold data tiering processing method according to any one of claims 1-8.

Images