Database asynchronous scheduling control method and device based on plan perception, and medium

Abstract

The application discloses a plan-aware-based database asynchronous scheduling control method and device and medium, relates to the technical field of database asynchronous scheduling, and comprises the following steps: scanning a plan prompt set through a description index to determine a prefetch description of an execution plan; querying a buffer page corresponding to an original page request to obtain a buffer page or a page I / O request identifier; judging whether the page I / O request identifier has the same identifier in a global hash table to obtain an entry creation instruction; calculating a priority score of a pending I / O entry corresponding to the entry creation instruction; distributing a queue quota to the pending I / O entry according to the priority score to determine an I / O batch of the pending I / O entry; and asynchronously submitting the pending I / O entry in the I / O batch to obtain a scheduling state of the pending I / O entry. The application solves the technical problems of distorted request priority and uncontrollable tail delay in the prior art database asynchronous scheduling control.

Description

Technical Field

[0001] This application relates to the field of asynchronous database scheduling technology, and in particular to a plan-aware asynchronous database scheduling control method, device and medium. Background Technology

[0002] Database systems manage data page caching through a buffer manager. When a page is not in the buffer pool, it needs to be read from storage. Asynchronous I / O interfaces allow batch I / O submissions and retrieve results via completion queues or event notifications. During database execution, different I / O requests have varying timeliness and impact. WAL flushing directly affects transaction commit latency, index point lookups affect interactive query tail latency, sequential prefetching affects scan throughput, and checkpoint flushing affects background write pressure.

[0003] Because database asynchronous I / O queues cannot eliminate duplication during the request construction phase, resulting in duplicate physical I / O on the same page, and because requests are not categorized according to database execution semantics, a large number of sequential prefetches or background checkpoint write-backs can crowd out limited commit ring slots, causing WALflush on the critical path and foreground index point lookups to queue at the tail of the queue. Existing technologies for database asynchronous scheduling control suffer from technical problems such as request priority distortion and uncontrollable tail latency. Summary of the Invention

[0004] This application provides a plan-aware database asynchronous scheduling control method, device, and medium, which solves the technical problems of request priority distortion and uncontrollable tail delay in the prior art.

[0005] In a first aspect, embodiments of this application provide a plan-aware asynchronous database scheduling control method, characterized in that the method includes: obtaining a plan hint set corresponding to an execution plan, and scanning the plan hint set through a description index to determine the prefetch description of the execution plan; obtaining the original page request corresponding to an I / O task, and querying the buffer page corresponding to the original page request to obtain the buffer page or page I / O request identifier; determining whether the page I / O request identifier has the same identifier in a preset global hash table to obtain an entry creation instruction; calculating the priority score of the suspended I / O entry corresponding to the entry creation instruction based on the entry creation instruction and the prefetch description; allocating queue quotas to the suspended I / O entries according to the priority score to determine the I / O batch corresponding to the suspended I / O entry; asynchronously submitting the suspended I / O entries in the I / O batch to obtain the scheduling status of the suspended I / O entries.

[0006] In one implementation of this application, the plan hint set is scanned using a description index to determine the prefetch description of the execution plan. Specifically, this includes: performing a sequential scan of the plan hint set and determining a first prefetch description for consecutive pages based on the range of the sequential scan and the prefetch distance; performing a bitmap heap scan of the plan hint set and splitting the set after the bitmap heap scan into discrete intervals to obtain a second prefetch description; marking the root page, intermediate page, and leaf page in the plan hint set through an index scan to obtain a third prefetch description; and determining the prefetch description based on the first, second, and third prefetch descriptions.

[0007] In one implementation of this application, querying the buffer page corresponding to the original page request to obtain the buffer page or page I / O request identifier specifically includes: querying the buffer pool corresponding to the page identifier based on the page identifier in the original page request; if the buffer pool can be found, returning the corresponding buffer page ID to obtain the buffer page; if the buffer pool cannot be found, returning a miss record and extracting the identifier parameters from the miss record to obtain the page I / O request identifier; wherein, the identifier parameters include: tablespace, database, fork, block number, and I / O type.

[0008] In one implementation of this application, determining whether a page I / O request identifier has the same identifier in a preset global hash table to obtain an entry creation instruction specifically includes: calculating the hash value corresponding to the page I / O request identifier and determining whether the hash value has the same hash value in the global hash table; if the hash value has the same hash value in the global hash table, adding the execution context corresponding to the execution plan to the waiting queue of the existing entry and returning a waiting handle pointing to the existing entry; if the hash value does not have the same hash value in the global hash table, determining that a new entry needs to be created to obtain an entry creation instruction.

[0009] In one implementation of this application, the priority score of the pending I / O entry corresponding to the entry creation instruction is calculated based on the entry creation instruction and the prefetch description. Specifically, this includes: assigning the schedule order corresponding to the schedule node in the entry creation instruction based on the type of the prefetch description; calculating the priority parameters corresponding to the entry creation instruction; wherein the priority parameters include: deadline urgency, wait count, page type weight, and WAL commit urgency; and performing a weighted average of the priority parameters to determine the priority score.

[0010] In one implementation of this application, queue quotas are allocated to pending I / O entries based on priority scores to determine the I / O batches corresponding to the pending I / O entries. Specifically, this includes: adding pending I / O entries corresponding to entry creation instructions to each entry queue based on priority scores to obtain an update entry queue; performing quota analysis on the update entry queue to determine the entry allocation result of the update entry queue; and determining the I / O batches based on the entry allocation result.

[0011] In one implementation of this application, the method of asynchronously submitting suspended I / O entries in an I / O batch to obtain the scheduling status of the suspended I / O entries specifically includes: converting the suspended I / O entries in the I / O batch into the submission request structure required by the asynchronous I / O interface to obtain the corresponding asynchronous request; wherein the type of the asynchronous request includes: submission queue entry, I / O control block; binding the batch identifier corresponding to the asynchronous request, and submitting the asynchronous request to the corresponding device queue based on the batch identifier to obtain the scheduling status of the suspended I / O entries; wherein the scheduling status includes: submission successful, submission failed.

[0012] In one implementation of this application, after obtaining the scheduling status of the suspended I / O entries in the asynchronous I / O batch, the method further includes: if the scheduling status is successful, reading or writing data to the corresponding target buffer page and updating the corresponding buffer page flag; if the scheduling status is failed, updating the processing status of the suspended I / O entries according to the error type of the failed submission.

[0013] Secondly, embodiments of this application also provide a plan-aware database asynchronous scheduling and control 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, the instructions being executed by the at least one processor to enable the at least one processor to implement a plan-aware database asynchronous scheduling and control method.

[0014] Thirdly, embodiments of this application also provide a non-volatile computer storage medium for plan-aware asynchronous database scheduling control, which stores computer-executable instructions, characterized in that the computer-executable instructions, when executed, can implement a plan-aware asynchronous database scheduling control method.

[0015] This application provides a plan-aware database asynchronous scheduling control method, device, and medium. By querying plan awareness, request generation, page-level deduplication, and priority scheduling, the database identifies, merges, classifies, and prioritizes requests through internal semantics before page I / O requests enter the operating system's asynchronous interface. After I / O is completed, the corresponding execution context is woken up. This solves the technical problems of request priority distortion and uncontrollable tail delay in the prior art of database asynchronous scheduling control. It enables the database to maintain the ability to submit asynchronous I / O in batches while reducing the probability of critical transactions and checkpoint requests being blocked by background prefetching or checkpoint writes. Attached Figure Description

[0016] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings: Figure 1 A flowchart of a plan-aware asynchronous scheduling control method for databases is provided for embodiments of this application; Figure 2 This is a schematic diagram of the internal structure of a database asynchronous scheduling and control device based on plan awareness, provided as an embodiment of this application. Detailed Implementation

[0017] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0018] This application provides a plan-aware database asynchronous scheduling control method, device, and medium. By querying plan awareness, request generation, page-level deduplication, and priority scheduling, the database identifies, merges, classifies, and prioritizes requests through internal semantics before page I / O requests enter the operating system's asynchronous interface. After I / O is completed, the corresponding execution context is woken up. This solves the technical problems of request priority distortion and uncontrollable tail delay in the prior art of database asynchronous scheduling control. It enables the database to maintain the ability to submit asynchronous I / O in batches while reducing the probability of critical transactions and checkpoint requests being blocked by background prefetching or checkpoint writes.

[0019] The technical solutions proposed in the embodiments of this application will be described in detail below with reference to the accompanying drawings.

[0020] Figure 1A flowchart illustrating a plan-aware asynchronous scheduling control method for databases, provided as an embodiment of this application. Figure 1 As shown in the figure, the asynchronous scheduling control method for databases based on plan awareness provided in this application embodiment specifically includes the following steps: Step 101: Obtain the plan hint set corresponding to the execution plan, and scan the plan hint set through the description index to determine the prefetch description of the execution plan.

[0021] For example, this application scans the plan hint set by describing the index to determine the prefetch description of the execution plan, avoiding invalid reads caused by prefetching by file offset, and keeping the prefetch request relevant to the actual execution plan.

[0022] Specifically, by describing the index, the plan hint set is scanned to determine the prefetch description of the execution plan, including: performing a sequential scan of the plan hint set and determining a first prefetch description of consecutive pages based on the range of the sequential scan and the prefetch distance; performing a bitmap heap scan of the plan hint set and splitting the set after the bitmap heap scan into discrete intervals to obtain a second prefetch description; marking the root page, intermediate page, and leaf page in the plan hint set through an index scan to obtain a third prefetch description; and determining the prefetch description based on the first, second, and third prefetch descriptions.

[0023] In one embodiment, firstly, based on the execution plan generated by the query optimizer, the plan node cost, the expected number of rows, the target relation identifier, and the scan predicate, the plan tree is traversed to identify nodes such as sequential scan, index scan, and bitmap heap scan. Parameters such as relation_id, fork, block_range, access direction, and expected access time are extracted to obtain the corresponding plan hint set.

[0024] Then, for the planned I / O hint set and the current buffer pool hit status, for sequential scans, consecutive page prefetch tasks are generated according to block_range and prefetch_distance; for bitmap heap scans, the data is split into several discrete intervals according to the density of block numbers in the bitmap; for index scans, the root page, intermediate pages, and leaf pages to be accessed are marked first. During processing, a PrefetchDescriptor is generated and written to op_type, sequentiality_score, deadline_hint, page_type_weight, and expected_access_ms.

[0025] Finally, based on the prefetch descriptions obtained after each scan, the prefetch descriptions are determined and organized into a prefetch description queue.

[0026] Step 102: Obtain the original page request corresponding to the I / O task, and query the buffer page corresponding to the original page request to obtain the buffer page or page I / O request identifier.

[0027] For example, this application constructs the page I / O request identifier by querying the buffer page corresponding to the original page request to obtain the buffer page or page I / O request identifier. Since the page entry merging across I / O types is completed before the buffer page is allocated and the request is sent to the operating system's asynchronous interface, the additional fields provide a semantic source for subsequent priority scoring.

[0028] Specifically, the process involves querying the buffer page corresponding to the original page request to obtain the buffer page or page I / O request identifier. This includes: querying the buffer pool corresponding to the page identifier in the original page request; if the buffer pool can be found, returning the corresponding buffer page ID to obtain the buffer page; if the buffer pool cannot be found, returning a miss record and extracting the identifier parameters from the miss record to obtain the page I / O request identifier. The identifier parameters include: tablespace, database, fork, block number, and I / O type.

[0029] In one embodiment, the buffer pool manager first checks whether the page is already in the buffer pool and whether the verification status is valid based on relation_id, forkNum, and blockNum. If a match is found, buffer_id is returned directly, and it is recorded that the request did not enter the asynchronous I / O queue. If a match is found or the page is being loaded, the page-level deduplication process is initiated. For dirty page write-back requests, it is also determined whether the page is in a non-write-back state, such as still being held by an active transaction or the LSN not meeting the flush conditions, to obtain a hit return value or a miss record for constructing a PageIOKey.

[0030] The PageIOKey is constructed based on the tablespace node, database node, relation node, forkNum, blockNum, and io_type, with auxiliary fields such as requester_id, backend_id, plan_node_id, page_type, deadline_hint, target_buffer, checksum_expected, is_prefetch, and enqueue_time appended. For WAL flush requests, the PageIOKey can further include the log segment number and LSN range; for checkpoint write-back requests, the PageIOKey can include the dirty page version number to prevent older versions from overwriting newer versions, resulting in a key that can be used for hash-based location and concurrent deduplication.

[0031] Furthermore, the constructed PageIOKey adds an io_type field in addition to the traditional BufferTag fields (spcNode, dbNode, relNode, forkNum, blockNum), enabling the same-page I / O of different semantic types, such as read, WAL flush, checkpoint write-back, and background write-back, to be identified and merged in a unified hash table at the request level.

[0032] Step 103: Determine if the page I / O request identifier has the same identifier in the preset global hash table to obtain the entry creation instruction.

[0033] For example, this application determines whether the page I / O request identifier has the same identifier in a preset global hash table in order to obtain the entry creation instruction, thereby reducing duplicate I / O on the same page.

[0034] Specifically, determining whether a page I / O request identifier has the same identifier in a preset global hash table to obtain an entry creation instruction includes: calculating the hash value corresponding to the page I / O request identifier and determining whether the hash value has the same hash value in the global hash table; if the hash value has the same hash value in the global hash table, adding the execution context corresponding to the execution plan to the waiting queue of the existing entry and returning a waiting handle pointing to the existing entry; if the hash value does not have the same hash value in the global hash table, determining that a new entry needs to be created to obtain an entry creation instruction.

[0035] In one embodiment, a shard lock is selected based on the hash value of the PageIOKey, the corresponding PendingIOMap shard is entered, and it is checked whether a PendingIOEntry with the same PageIOKey already exists.

[0036] If it exists and its status is READY, SUBMITTED, or COMPLETING, it means that the same page is already in the queue, submitted, or completed processing. The current request will no longer generate new physical I / O. The current execution context, waiting reason, timeout time, and wake-up callback will be added to waiter_list.

[0037] If an existing entry is in the ERROR_RETRY state, the system will determine whether to add it to the wait list or switch to a synchronous rollback based on the error type, in order to obtain the corresponding entry creation instruction.

[0038] Furthermore, if no reusable entry is found, a PendingIOEntry is created. This entry is initialized with state=READY, retry_count=0, waiter_list containing the first requester, submit_time empty, completion_time empty, and bound to target_buffer or the buffer page to be allocated. For prefetch requests, waiter_list can be empty, but it still records the source schedule node and failure condition.

[0039] If the prefetched page has been hit by a real request from the executor before submission, it is upgraded to a foreground read request and a waiter is added, resulting in a new PendingIOEntry, which is then inserted into the PendingIOMap.

[0040] Step 104: Based on the entry creation instruction and prefetch description, calculate the priority score of the suspended I / O entry corresponding to the entry creation instruction.

[0041] For example, this application calculates the priority score of the pending I / O entry corresponding to the entry creation instruction based on the entry creation instruction and the prefetch description, so that the scheduling result does not depend on a single FIFO or address ordering.

[0042] Specifically, based on the entry creation instruction and prefetch description, the priority score of the pending I / O entry corresponding to the entry creation instruction is calculated, including: assigning the schedule order corresponding to the schedule node in the entry creation instruction based on the type of the prefetch description; calculating the priority parameters corresponding to the entry creation instruction; wherein, the priority parameters include: deadline urgency, wait count, page type weight, and WAL commit urgency; and performing a weighted average of the priority parameters to determine the priority score.

[0043] In one embodiment, the priority scheduler calculation can be represented as: priority=c1 DeadlineUrgency+c2 WaiterCount+c3 PageTypeWeight+c4 PlanSequentiality+c5 CachePressure+c6 WalCommitUrgency-c7 WriteBackDeferrable-c8 PrefetchDistancePenalty.

[0044] Among them, DeadlineUrgency indicates the urgency of the expected access time relative to the current time, WaiterCount indicates the number of execution contexts waiting for the same page, and PageTypeWeight distinguishes between WAL, index page, data page and backend write-back page to obtain normalized priority scores and category labels.

[0045] Furthermore, the weighting coefficients c1–c8 can be taken as follows: c1=0.40, corresponding to DeadlineUrgency, prioritizing critical paths; c2=0.15, representing the value of WaiterCount merging; c3=0.20, corresponding to PageTypeWeight, giving WAL and index pages a higher weight than ordinary data pages; c4=0.08, corresponding to PlanSequentiality; c5=0.05, corresponding to CachePressure, representing the overall buffer pool tension; c6=0.10, corresponding to WalCommitUrgency, representing the urgency of WALflush; c7=0.05, a WriteBackDeferrable suppression term; c8=0.05, a PrefetchDistancePenalty suppression term.

[0046] Each coefficient can be adjusted in the [0,1] range according to the load characteristics during implementation, while keeping c1+c2+c3+c4+c5+c6 in the range of 0.95–1.00 and c7+c8 in the range of 0.05–0.15.

[0047] Step 105: Allocate queue quotas to the suspended I / O entries based on priority scores to determine the I / O batches corresponding to the suspended I / O entries.

[0048] Specifically, based on priority scores, queue quotas are allocated to pending I / O entries to determine the I / O batches corresponding to the pending I / O entries. This includes: adding pending I / O entries corresponding to entry creation instructions to each entry queue based on priority scores to obtain an update entry queue; performing quota analysis on the update entry queue to determine the entry allocation results of the update entry queue; and determining the I / O batches based on the entry allocation results.

[0049] In one embodiment, requests are sent to a WAL, IndexLookup, RandomRead, SeqPrefetch, CheckpointWrite, or BackgroundWrite queue based on their io_type, page_type, and op_type. Each queue maintains an independent priority heap, commit quota, aging factor, and maximum number of consecutive services. If a certain type of request remains unserviced for an extended period, its aging_score is increased to prevent background write-backs or low-priority prefetching.

[0050] Step 106: Asynchronously submit the pending I / O entries in the I / O batch to obtain the scheduling status of the pending I / O entries.

[0051] Specifically, asynchronously submitting suspended I / O entries in an I / O batch to obtain the scheduling status of the suspended I / O entries includes: converting the suspended I / O entries in the I / O batch into the submission request structure required by the asynchronous I / O interface to obtain the corresponding asynchronous request; wherein the type of asynchronous request includes: submission queue entry, I / O control block; binding the batch identifier corresponding to the asynchronous request, and submitting the asynchronous request to the corresponding device queue based on the batch identifier to obtain the scheduling status of the suspended I / O entries; wherein the scheduling status includes: submission successful, submission failed.

[0052] In one embodiment, each PendingIOEntry is converted into an io_uringSQE, LinuxAIO control block, or database internal I / O worker task, and the PageIOKey, batch_id, and queue type are bound to the user data fields. Upon successful submission, the state is updated to SUBMITTED, and the submit_time, submit_sequence, and device_queue_id are recorded. If the submission ring space is insufficient, the READY state is retained, and the batch_size for the next round is reduced.

[0053] Furthermore, after obtaining the scheduling status of the suspended I / O entries in the asynchronous I / O batch, the method further includes: if the scheduling status is successful, reading or writing data to the corresponding target buffer page and updating the corresponding buffer page flag; if the scheduling status is failed, updating the processing status of the suspended I / O entries according to the error type of the failed submission.

[0054] In one embodiment, after the queue returns, the PendingIOEntry is located based on the PageIOKey or user data field in the event, and the return length, error code, page header identifier, checksum, and target LSN are checked.

[0055] When a read is successful, the page is copied or mapped to the target_buffer, the buffer page validity flag is set, and the page's most recently accessed time and source plan node statistics are updated. When a write-back is successful, the corresponding dirty page flag is cleared and the write-back progress is advanced.

[0056] Furthermore, when a PendingIOEntry changes from COMPLETING to DONE, the system iterates through the waiter_list, waking up only the execution contexts waiting for that PageIOKey, and returning the buffer_id, completion status, error code, or retry information to each waiter. For waiters that have timed out, been canceled, or had their transactions aborted, the system checks whether their context is still valid before waking them up; invalid waiters are removed from the list and no longer trigger execution. After waking up, the entry is removed from the PendingIOMap or retained as a short-term completion cache.

[0057] If I / O fails, the error recovery module categorizes and handles the error according to its type. For temporary resource shortages, EAGAIN, or commit ring congestion, the status is changed to ERROR_RETRY and the device is re-enqueued exponentially. For short reads, checksum errors, or page header mismatches, a reread attempt is prioritized, and bad page diagnostic information is recorded. For device unavailability or permission errors, the status is changed to FAILED, and a definitive error is returned to the waiters in the waiter_list. If retry_count exceeds the threshold, the synchronous I / O path can be switched or the database error handling process can be triggered.

[0058] In one embodiment, before the request is sent to the operating system's asynchronous submission interface, a PageIOKey containing fields such as tablespace, database, relation, forkNum, blockNum, and io_type is constructed for each I / O request, and page-level deduplication is performed in PendingIOMap using this PageIOKey for cross-read, WAL flush, and checkpoint write-back.

[0059] For subsequent requests on the same PageIOKey that already have a PendingIOEntry, the execution context, waiting reason, and timeout are recorded by appending to the waiter_list. After the I / O completion event returns, the waiters that are still valid in the waiter_list are precisely woken up according to the PageIOKey index.

[0060] Extract operator types, scan ranges, and expected access times from the physical execution plan and construct a PrefetchDescriptor so that the scheduling results are directly driven by the semantics of the database execution plan, rather than relying on file offsets, historical access statistics, single heat indicators, or NUMA access costs.

[0061] Separate priority heaps and commit quotas are defined for WAL, IndexLookup, RandomRead, SeqPrefetch, CheckpointWrite, and BackgroundWrite, with minimum quotas such as wal_reserved_ratio reserved for WAL and IndexLookup. At the same time, based on a set of feedback metrics consisting of runtime p95 / p99 I / O latency, SQL wait time, completion queue backlog, WALflushwait, and prefetch hit rate, io_depth, batch_size, prefetch_distance, and quotas for each type of queue are adjusted in a closed loop.

[0062] In one embodiment, in a PostgreSQL-like database, a user executes a query that includes index conditions and range filtering. The execution plan contains two types of nodes: IndexScan and BitmapHeapScan. The plan parsing module first identifies the root and intermediate pages of the index as time-sensitive pages, generating a PrefetchDescriptor with a high deadline_hint; simultaneously, it generates a prefetch description with high orderliness but deferred access for the data page ranges obtained from the bitmap heap scan. When multiple backend processes access the same index leaf page simultaneously, the constructed PageIOKeys are identical, only one PendingIOEntry is retained, and the remaining requesters are added to the waiter_list. Based on this, only one physical read occurs on the device side, waking up multiple execution contexts waiting for that index page upon completion.

[0063] When storage devices experience brief congestion, both completion queue backlog and p99 latency increase simultaneously. The feedback controller determines that this state is not a normal I / O increase caused by a decrease in buffer pool hit rate, but rather an excessively deep queue on the device side. Therefore, it reduces io_depth and batch_size, and imposes a higher PrefetchDistancePenalty on the SeqPrefetch queue.

[0064] Once the p99 delay is resolved and the device utilization rate falls below the target range, the prefetch distance and batch size are gradually restored. This embodiment illustrates that the control object of the present invention is not only a single I / O request, but also the closed-loop operating state between the database execution layer and the device layer.

[0065] Furthermore, for metric verification, the following are observed: First, duplicate I / O elimination rate, i.e., the ratio of the number of same-page requests merged by PendingIOMap to the original number of same-page requests; Second, waiter merging effect, i.e., the average and maximum length of waiter_list for each PendingIOEntry; Third, tail latency improvement effect, i.e., the p95 / p99 latency change of critical SQL; Fourth, transaction commit path improvement effect, i.e., the changes in WALflushwait and commitwait; Fifth, prefetch effectiveness, i.e., the proportion of prefetched pages that are hit by the executor before being evicted; Sixth, error recovery stability, i.e., the proportion of retry errors that return from ERROR_RETRY to READY and eventually DONE.

[0066] By simulating a mixed database I / O workload with high concurrency and near I / O queue saturation, the effects of PageIOKey deduplication, multi-category semantic queues, waiter_list merging, and feedback control on the amount of physical I / O and tail latency are verified.

[0067] The simulation generates 5 sets of random seed workloads, each containing 30,000 logical requests, including WAL, IndexLookup, RandomRead, SeqPrefetch, and CheckpointWrite.

[0068] The simulation experiment sets up three sets of comparison schemes: the first set is FIFO_async_queue, which is a normal asynchronous I / O FIFO queue without page-level deduplication and semantic scheduling; the second set is batch_address_queue, which is a batch asynchronous queue sorted according to page address locality, but without PageIOKey deduplication, waiter_list merging, and feedback control; the third set is proposed_page_dedup_semantic_feedback, which is the technical solution adopted in this application, including PageIOKey deduplication, waiter_list merging, multi-category semantic queues, priority scoring, and feedback control.

[0069] Compared to a typical FIFO asynchronous queue, this application reduces the number of physical I / O operations from 30,000 to approximately 20,053, a reduction of approximately 33.16%; the p99 latency for full requests is reduced from 16.82ms to 6.71ms, a reduction of approximately 60.12%; the p99 latency for foreground requests is reduced from 16.40ms to 5.78ms, a reduction of approximately 64.74%; and the p99 latency for WAL requests is reduced from 14.54ms to 1.40ms, a reduction of approximately 90.38%. These results demonstrate that the improvements of this invention do not solely stem from asynchronous I / O or batch submissions, but rather from a combination of page-level deduplication, waiter merging, semantic queue quotas, and feedback control.

[0070] The above are embodiments of the method proposed in this application. Based on the same inventive concept, embodiments of this application also provide a plan-aware database asynchronous scheduling and control device, the structure of which is as follows: Figure 2 As shown.

[0071] Figure 2 This is a schematic diagram of the internal structure of a database asynchronous scheduling and control device based on plan awareness, provided as an embodiment of this application. Figure 2 As shown, the device includes: At least one processor 201; And a memory 202 that is communicatively connected to at least one processor; The memory 202 stores instructions that can be executed by at least one processor. The instructions are executed by at least one processor 201 to enable at least one processor 201 to implement a database asynchronous scheduling control method based on a plan awareness.

[0072] Some embodiments of this application provide corresponding to Figure 1 A non-volatile computer storage medium for asynchronous database scheduling control based on plan awareness is disclosed, which stores computer-executable instructions that, when executed, can realize a method for asynchronous database scheduling control based on plan awareness.

[0073] 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 embodiments for IoT devices and media are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0074] The systems, media, and methods provided in this application are one-to-one correspondences. Therefore, the systems 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 systems and media will not be repeated here.

[0075] 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.

[0076] 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.

[0077] 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.

[0078] These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable apparatus 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.

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

[0080] 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.

[0081] Computer-readable media include both permanent and non-permanent, removable and non-removable media that can store information by 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.

[0082] 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 process, method, article, or apparatus. Unless otherwise specified, 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 that element.

[0083] The above are merely embodiments of this application and are 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 the claims of this application.

Claims

1. A plan-aware asynchronous scheduling control method for databases, characterized in that, The method includes: Obtain the set of plan hints corresponding to the execution plan, and scan the set of plan hints using the description index to determine the prefetch description of the execution plan; Obtain the original page request corresponding to the I / O task, and query the buffer page corresponding to the original page request to obtain the buffer page or page I / O request identifier; Determine whether the page I / O request identifier has the same identifier in a preset global hash table to obtain the entry creation instruction; Based on the entry creation instruction and the prefetch description, calculate the priority score of the suspended I / O entry corresponding to the entry creation instruction; Based on the priority score, queue quotas are allocated to the suspended I / O entries to determine the I / O batches corresponding to the suspended I / O entries; The pending I / O entries in the I / O batch are submitted asynchronously to obtain the scheduling status of the pending I / O entries.

2. The database asynchronous scheduling control method based on plan awareness according to claim 1, characterized in that, The plan hint set is scanned using the description index to determine the prefetch description of the execution plan, specifically including: The planned prompt set is sequentially scanned, and the first prefetch description of consecutive pages is determined based on the range of the sequential scan and the prefetch distance; A bitmap heap scan is performed on the plan hint set, and the set after the bitmap heap scan is split into discrete intervals to obtain a second prefetch description; By using an index scan, the root page, intermediate page, and leaf page in the plan hint set are marked to obtain a third prefetch description; The prefetch description is determined based on the first prefetch description, the second prefetch description, and the third prefetch description.

3. The database asynchronous scheduling control method based on plan awareness according to claim 1, characterized in that, Query the buffer page corresponding to the original page request to obtain the buffer page or page I / O request identifier, specifically including: Based on the page identifier in the original page request, query the buffer pool corresponding to the page identifier; If the buffer pool can be found, return the corresponding buffer page ID to obtain the buffer page; If the buffer pool cannot be found, a miss record is returned, and the identifier parameters in the miss record are extracted to obtain the page I / O request identifier; wherein, the identifier parameters include: tablespace, database, fork, block number, and I / O type.

4. The database asynchronous scheduling control method based on plan awareness according to claim 1, characterized in that, Determining whether the page I / O request identifier has the same identifier in a preset global hash table to obtain an entry creation instruction, specifically includes: Calculate the hash value corresponding to the page I / O request identifier, and determine whether the hash value has the same hash value in the global hash table; If the hash value has the same hash value in the global hash table, the execution context corresponding to the execution plan is added to the waiting queue of the existing entry, and a waiting handle pointing to the existing entry is returned; If the hash value does not have the same hash value in the global hash table, it is determined that a new entry needs to be created in order to obtain the entry creation instruction.

5. The database asynchronous scheduling control method based on plan awareness according to claim 1, characterized in that, Based on the entry creation instruction and the prefetch description, the priority score of the pending I / O entry corresponding to the entry creation instruction is calculated, specifically including: Based on the type of the prefetch description, assign the plan order corresponding to the plan node in the entry creation instruction; Calculate the priority parameters corresponding to the entry creation instruction; wherein, the priority parameters include: deadline urgency, wait count, page type weight, and WAL submission urgency; The priority parameters are weighted and averaged to determine the priority score.

6. The database asynchronous scheduling control method based on plan awareness according to claim 1, characterized in that, Based on the priority score, queue quotas are allocated to the suspended I / O entries to determine the I / O batches corresponding to the suspended I / O entries, specifically including: Based on the priority score, the pending I / O entries corresponding to the entry creation instruction are added to each entry queue to obtain the update entry queue; Quota analysis is performed on the update entry queue to determine the entry allocation result of the update entry queue, and the I / O batch is determined based on the entry allocation result.

7. The database asynchronous scheduling control method based on plan awareness according to claim 1, characterized in that, Asynchronously submitting pending I / O entries in the I / O batch to obtain the scheduling status of the pending I / O entries specifically includes: The pending I / O entries in the I / O batch are converted into the submission request structure required by the asynchronous I / O interface to obtain the corresponding asynchronous request; wherein, the type of the asynchronous request includes: submission queue entry, I / O control block; Bind the batch identifier corresponding to the asynchronous request, and submit the asynchronous request to the corresponding device queue based on the batch identifier to obtain the scheduling status of the suspended I / O entry; wherein the scheduling status includes: submission successful, submission failed.

8. The database asynchronous scheduling control method based on plan awareness according to claim 7, characterized in that, After asynchronously submitting the pending I / O entries in the I / O batch to obtain the scheduling status of the pending I / O entries, the method further includes: If the scheduling status is "commit successful", read or write data to the corresponding target buffer page and update the corresponding buffer page flag. If the scheduling status is "commit failed", the processing status of the suspended I / O entry is updated according to the error type of the commit failure.

9. A database asynchronous scheduling and control device based on plan awareness, characterized in that, The device includes: At least one processor; And, a memory communicatively connected to the at least one processor; The memory stores instructions that can be executed by the at least one processor, which are executed by the at least one processor to enable the at least one processor to implement a plan-aware database asynchronous scheduling control method as described in any one of claims 1-8.

10. A non-volatile computer storage medium based on plan-aware asynchronous scheduling control of a database, storing computer-executable instructions, characterized in that, When the computer-executable instructions are executed, they can implement a plan-aware database asynchronous scheduling control method as described in any one of claims 1-8.

Images