Indexing for evolving large-scale datasets in multi-master hybrid transactional and analytical processing systems

Abstract

According to one or more embodiments of the present invention, a computer-implemented method includes creating a hybrid index for a multi-region data storage system, wherein a first region has data organized for transaction processing and a second region has data organized for analytical processing. Each index entry in the hybrid index includes an index key, the index key including an equality column, a start timestamp column, and a tuple sequence number column. The method also includes constructing an index run to be included in the hybrid index, the index run including a plurality of index entries. The index run is created by scanning data blocks from the first region to select columns, calculating tuple sequence number values ​​for the index entries to be included in the hybrid index, and sorting the index entries according to a predetermined order using the columns to be included in the index run.

Description

Background Art

[0001] The present invention relates generally to computer technology, and more particularly to indexing for evolving large-scale data sets in a multi-master hybrid transaction / analytical processing system.

[0002] The popularity of real-time analytics (e.g., risk analysis, online recommendations, and fraud detection) requires data management systems to handle fast concurrent transactions (e.g., online transaction processing or OLTP) and large-scale analytical queries on fresh data (e.g., online analytical processing or OLAP). OLTP applications facilitate the high-speed ingestion of data and persist them to disk or shared storage devices, and OLAP applications facilitate the simultaneous running of analytical queries on the newly ingested data to quickly derive insights. The necessity of real-time analytics has led to the emergence of hybrid transaction and analytical processing (HTAP) systems. HTAP systems support OLTP and OLAP queries in a single system, allowing real-time analysis of newly ingested data. Fine-grained indexes are mandatory in HTAP systems to support efficient point lookups and range queries for high-speed transaction processing and real-time analytics. Summary of the Invention

[0003] According to one or more embodiments of the present invention, a computer-implemented method includes creating a hybrid index for a multi-region data storage system, wherein a first region is for data organized for transactional processing and a second region is for data organized for analytical processing. Each index entry in the hybrid index includes at least an index key, which also includes an equality column. The index entry also includes a start timestamp column that indicates a timestamp when data corresponding to the index entry was inserted. The index entry further includes a tuple sequence number column that indicates a location of the index entry in a tiered storage device. The method also includes constructing an index run to be included in the hybrid index. The index run includes a plurality of index entries. Constructing the index run includes determining a column created in the index run by scanning data blocks from a first region, the data blocks to be associated with the index run. Constructing the index run also includes calculating a tuple sequence number value for the index entry to be included in the hybrid index. The tuple sequence number value is stored in the tuple sequence number column. Constructing the index run also includes sorting the index entries according to a predetermined order using the column to be included in the index run.

[0004] In one or more examples, building the index run also includes creating a header for the index run and setting the value of the index run pointer to the header. The header includes a pointer set to the current value of the index run pointer. In one or more examples, a summary of the data elements stored in the index run is stored in the header. Additionally, in one or more examples, building the index run includes calculating an offset array for the index run. The offset array can be stored in the header. The offset array maps the most significant n bits of the hash value to an offset of an index entry in the index run.

[0005] In one or more examples, the index entry also includes an ordering column in the index key.

[0006] In one or more examples, each index run is assigned a level number, where the level number indicates a source of data used to construct the index run. In one or more examples, the level number also indicates a region in which the data associated with the index run is stored, which region is one of a plurality of regions in a multi-region data storage system. In one or more examples, a separate thread is assigned to maintain one or more index runs from each level.

[0007] Furthermore, according to one or more embodiments of the present invention, a computer-implemented method includes, in response to receiving a request to merge two index runs, a first index run and a second index run, in a database, creating a third index run to include columns from the first index run and from the second index run, storing data in the database using a hierarchical data storage device. The method also includes globally sorting the columns from the first index run and the columns from the second index run using a merge sort. The method also includes, based on a determination that the third index run belongs to a non-persistent level, storing the third index run only to a cache storage device. The method further includes, based on a determination that the third index run belongs to a persistent level and that a level number of the third index run is lower than a purge level, storing the third index run only to a cache storage device and a shared storage device. The method also includes, based on a determination that the third index run belongs to a persistent level and that a level number of the third index run is equal to or higher than a purge level, storing the third index run only to a shared storage device.

[0008] In one or more examples, the method further includes updating a list of index runs in the database. Updating the list includes changing a pointer of a fourth index run pointing to the address of the first index run to the address of the third index run, and deleting the first index run and the second index run. Furthermore, in one or more examples, after the first index run and the second index run are merged into an index run belonging to the persistence level, the first index run and the second index run are deleted.

[0009] The above features may also be provided by at least one system, a computer program product, and a machine, among other types of implementations.

[0010] Additional technical features and benefits are achieved through the technology of the present invention. Embodiments and aspects of the present invention are described in detail herein and are considered to be part of the claimed subject matter. For a better understanding, reference is made to the detailed description and accompanying drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] The details of the exclusive rights described herein are particularly pointed out and distinctly claimed in the claims at the conclusion of the specification. The foregoing and other features and advantages of the embodiments of the present invention will become apparent from the following detailed description taken in conjunction with the accompanying drawings, in which:

[0012] Figure 1 Describes a cloud computing environment according to an embodiment of the present invention;

[0013] Figure 2 Depicts the abstract model layers according to an embodiment of the present invention;

[0014] Figure 3 Describes the data lifecycle in HTAP-based systems;

[0015] Figure 4 depicts a representative view of a hybrid index according to one or more embodiments of the present invention;

[0016] Figure 5 depicts an exemplary indexing run according to one or more embodiments of the present invention;

[0017] Figure 6 depicts an exemplary run allocation for index maintenance according to one or more embodiments of the present invention;

[0018] Figure 7 depicts a flowchart of an example method for building an index run according to one or more embodiments of the present invention;

[0019] Figure 8 depicts a flow chart of an example method for clearing index runs according to one or more embodiments of the present invention;

[0020] Figure 9 depicts a visual illustration of a clearing operation according to one or more embodiments of the present invention in an example scenario;

[0021] Figure 10 A flowchart depicting an example method for an index merge operation according to one or more embodiments of the present invention is depicted;

[0022] Figure 11 and Figure 12depicts a visual illustration of an index merge operation in accordance with one or more embodiments of the present invention in an example scenario;

[0023] Figure 13 A flowchart illustrating an example method of performing index evolution operations according to one or more embodiments of the present invention is shown;

[0024] Figure 14 An exemplary scenario of evolving indexes according to one or more embodiments of the present invention is shown;

[0025] Figure 15 Providing pseudo code for index maintenance operations according to one or more embodiments of the present invention;

[0026] Figure 16 Pseudo code illustrating a resume run list according to one or more embodiments of the present invention; and

[0027] Figure 17 Depicted is a flow diagram of an example method for executing a range scan query against a single index run in accordance with one or more embodiments of the present invention.

[0028] The figures described herein are illustrative. Many variations may be made to the figures or operations described therein without departing from the spirit of the present invention. For example, actions may be performed in a different order, or actions may be added, deleted, or modified. Furthermore, the term "coupled" and its variations describe the presence of a communication path between two elements and do not imply a direct connection between the elements without an intermediate element / connection between them. All such variations are considered part of this specification.

[0029] In the drawings and the following detailed description of the disclosed embodiments, the various elements shown in the drawings have two or three digit reference numerals. With minor exceptions, the leftmost digit(s) of each reference numeral corresponds to the figure in which the element is first shown. DETAILED DESCRIPTION

[0030] Various embodiments of the present invention are described herein with reference to the accompanying drawings. Without departing from the scope of the present invention, alternative embodiments of the present invention may be designed. In the following description and accompanying drawings, various connections and positional relationships (e.g., above, below, adjacent, etc.) between elements are described. Unless otherwise indicated, these connections and / or positional relationships may be direct or indirect, and the present invention is not intended to be limited in this respect. Therefore, the coupling of an entity may refer to a direct or indirect coupling, and the positional relationship between the entity may be a direct or indirect positional relationship. In addition, various tasks and process steps as described herein may be incorporated into a more comprehensive program or process with additional steps or functionality not described in detail herein.

[0031] The following definitions and abbreviations are used to interpret the claims and description. As used herein, the terms "comprises," "includes," "has," "contains," or any other variations thereof are intended to cover a non-exclusive inclusion. For example, a composition, mixture, process, method, article, or apparatus that comprises a list of elements is not necessarily limited to only those elements but may include other elements not expressly listed or inherent to such composition, mixture, process, method, article, or apparatus.

[0032] In addition, the term "exemplary" is used herein to mean "serving as an example, instance, or illustration," and any embodiment or design described herein as "exemplary" is not necessarily to be construed as preferred or advantageous over other embodiments or designs. The terms "at least one" and "one or more" may be understood to include any integer greater than or equal to one, i.e., one, two, three, four, etc. The term "plurality" may be understood to include any integer greater than or equal to two, i.e., two, three, four, five, etc. The term "connected" may include both indirect and direct "connections."

[0033] The terms "about," "substantially," "approximately," and variations thereof are intended to include the degree of error associated with measurement of a particular quantity based on the equipment available at the time this application is filed. For example, "about" may include a range of ±8%, 5%, or 2% of a given value.

[0034] For the sake of brevity, conventional techniques related to making and using aspects of the present invention may or may not be described in detail herein. In particular, various aspects of computing systems and specific computer programs for implementing the various technical features described herein are well known. Therefore, for the sake of brevity, many conventional implementation details are only briefly mentioned herein or omitted entirely, without providing well-known system and / or process details.

[0035] As mentioned earlier, HTAP systems support both OLTP and OLAP queries in a single system, allowing real-time analysis of newly ingested data. Fine-grained indexing is mandatory in HTAP systems to support efficient point lookups and range queries for high-speed transaction processing and real-time analysis. However, indexing large amounts of data in HTAP systems is non-trivial, at least due to the challenges of shared storage and data evolution.

[0036] First, for large-scale HTAP, storage-only solutions are insufficient, at least due to limitations that prevent analytical queries from interfering with operational workloads. Consequently, most HTAP systems persist data on highly available, fault-tolerant shared storage, including distributed file systems. However, most of these shared storage options do not perform well with random access and in-place updates. For example, HDFS supports only append-only operations and optimizes block-level transfers, and object storage completely disallows random access within objects and updates to them. To accommodate the unique characteristics of shared storage, index operations such as inserts, updates, and deletes must use sequential I / O rather than in-place updates.

[0037] Furthermore, accessing remote shared storage over the network for index lookups is expensive, for example, for accessing object storage via a REST API over HTTP. Consequently, indexing methods on HTAP must fully leverage the storage hierarchy within a distributed cluster environment for caching. In particular, existing solutions can leverage the large memories and solid-state drives (SSDs) available in modern hardware. However, due to the large data volumes in HTAP systems, only the most frequently accessed portions of the index can be cached locally, leaving cold entries in the shared storage. An effective caching mechanism must be developed to facilitate index lookups.

[0038] Furthermore, regarding data evolution, because HTAP systems must effectively support transactional and analytical workloads, data is typically stored in different organizations, with one organization typically favoring transactions for more recent data and another favoring analytics for older data. Here, "organization" refers to a portion of the stored data, determined based on time. Each portion can be stored using a different data structure. This "organization" is used because as data becomes older, it is processed and stored based on the results of that processing. For example, transactional data from the last second (or any other duration) may not yet have been fully analyzed for analytical queries; however, when it is analyzed and stored, it is organized for executing such analytical queries. This document refers to these temporally separated data as different data "zones." As data in the system ages, it evolves from transaction-friendly zones to analytical-friendly zones. Evolving data from one zone to another may include reorganizing the data according to patterns associated with the different data zones. In one or more examples, the different data zones may be stored in different types of storage devices, such as RAM, SSDs, etc.

[0039] Such a region can be implemented in various ways. For example, in one or more embodiments of the present invention, a loosely coupled HTAP solution uses a non-relational repository for operational workloads and periodically copies data from the non-relational repository to columnar files on a shared storage device so that the distributed database engine can efficiently query them. In one or more examples, data is organized into a read-optimized main repository and a write-optimized delta repository. Writes are first buffered into a row-based delta repository, which is further transformed into a columnar main repository to facilitate analytical queries.

[0040] In one or more example systems using HTAP, transactions first append writes to a transaction log, which is then organized into columnar data blocks. This organized data is further periodically post-organized into an analysis-friendly organization by creating query-optimal data versions and partitions. In these HTAP systems, data evolution across different regions is ongoing, posing significant challenges to building and maintaining indexes.

[0041] Existing indexing solutions on multi-region HTAP systems either only support indexing on transaction-friendly regions and loosely coupled HTAP solutions, or support separate indexes on different regions.

[0042] Therefore, in order to address such technical challenges, one or more embodiments of the present invention provide multi-version and multi-region log structure merge-type (LSM-type) indexes. Such indexes help to efficiently query historical data, such as for real-time analysis, especially for analytical queries that are part of transactions in real HTAP scenarios. As a result, historical data needs are covered by such indexes. In addition, having separate indexes on different regions exposes a partitioned view of the data, requiring additional work to combine index query results across multiple regions. In particular, due to the ever-evolving nature of HTAP data, it is necessary to ensure that there is no duplicate or missing data in the final results. The indexes described by one or more embodiments of the present invention provide consistent and unified indexes across different regions in the HTAP system.

[0043] One or more embodiments of the present invention are described herein in the context of a large-scale HTAP system that utilizes complex analytics and columnar data processing to simultaneously implement fast transactions and analytics. However, it should be understood that one or more embodiments of the present invention are not limited to this implementation, and the features described herein may also be applied to other environments using different types of HTAP systems and data processing.

[0044] Now, the following is a brief description of the background technology, followed by a description of specific features of hybrid indexing used by one or more embodiments of the present invention. It is understood that although this disclosure includes detailed descriptions regarding cloud computing, implementation of the teachings described herein is not limited to cloud computing environments. Rather, embodiments of the present invention can be implemented in conjunction with any other type of computing environment now known or later developed.

[0045] Cloud computing is a service delivery model for enabling convenient, on-demand network access to a shared pool of configurable computing resources (e.g., networks, network bandwidth, servers, processing, memory, storage, applications, virtual machines, and services) that can be quickly provisioned and released with minimal management effort or interaction with the service provider. The cloud model can include at least five characteristics, at least three service models, and at least four deployment models.

[0046] Features are as follows:

[0047] On-demand self-service: Cloud consumers can unilaterally and automatically provision computing capabilities, such as server time and network storage, as needed without manual interaction with the service provider.

[0048] Wide Area Network Access: Capabilities are available over the network and are accessed through standard mechanisms that facilitate use by heterogeneous thin or thick client platforms (eg, mobile phones, laptops, personal digital assistants, etc.).

[0049] Resource pooling: A provider's computing resources are pooled to serve multiple consumers using a multi-tenant model, where different physical and virtual resources are dynamically allocated and reallocated based on demand. This is location-independent in the sense that consumers typically do not control or know the exact location of the provided resources, but are able to specify the location at a higher level of abstraction (e.g., country, state, or data center).

[0050] Rapid elasticity: In some cases, the ability to scale out quickly and release quickly can be automatically and elastically provided quickly and elastically. To the consumer, the capacity available for provisioning often appears unlimited and can be purchased in any quantity at any time.

[0051] Metered Services: Cloud systems automatically control and optimize resource usage by leveraging metering capabilities at a level of abstraction appropriate to the type of service (e.g., storage, processing, bandwidth, and active user accounts). Resource usage can be monitored, controlled, and reported, providing transparency to both the provider and consumer of the utilized service.

[0052] The service model is as follows:

[0053] Software as a Service (SaaS): The ability provided to consumers is to use the provider's applications running on a cloud infrastructure. Applications are accessed from a variety of client devices through a thin-client interface such as a web browser (e.g., web-based email). Consumers do not manage or control the underlying cloud infrastructure, including the network, servers, operating systems, storage, or even individual application capabilities, with the possible exception of limited user-specific application configuration settings.

[0054] Platform as a Service (PaaS): The capability provided to consumers is to deploy applications created or acquired by the consumers on cloud infrastructure. The applications created or acquired by the consumers are created using programming languages ​​and tools supported by the provider. The consumers do not manage or control the underlying cloud infrastructure including networks, servers, operating systems or storage devices, but have control over the deployed applications and possibly the configuration of the application hosting environment.

[0055] Infrastructure as a Service (IaaS): The capabilities provided to consumers are processing, storage, networking, and other basic computing resources on which consumers can deploy and run arbitrary software, including operating systems and applications. Consumers do not manage or control the underlying cloud infrastructure, but do have control over the operating system, storage, deployed applications, and possibly limited control over selected networking components (e.g., host firewalls).

[0056] The deployment model is as follows:

[0057] Private cloud: The cloud infrastructure is operated solely for the organization. It can be managed by the organization or a third party and can exist inside or outside the building.

[0058] Community cloud: Cloud infrastructure is shared by several organizations and supports a specific community with shared concerns (e.g., mission, security requirements, policies, and compliance considerations). It can be managed by the organization or a third party and can exist inside or outside a building.

[0059] Public cloud: Cloud infrastructure is available to the general public or large industrial groups and is owned by the organization that sells cloud services.

[0060] Hybrid cloud: A cloud infrastructure is a combination of two or more clouds (private, community, or public) that remain a unique entity but are bound together by standardized or proprietary technologies that enable data and application portability (e.g., cloud bursting for load balancing between clouds).

[0061] The cloud computing environment is service-oriented, with a focus on statelessness, low coupling, modularity, and semantic interoperability. At the core of cloud computing is the infrastructure consisting of a network of interconnected nodes.

[0062] Now refer to Figure 1 , depicts an illustrative cloud computing environment 50. As shown, the cloud computing environment 50 includes one or more cloud computing nodes 10 with which a local computing device used by a cloud consumer can communicate, such as a personal digital assistant (PDA) or cellular phone 54A, a desktop computer 54B, a laptop computer 54C, and / or an automobile computer system 54N. The nodes 10 can communicate with each other. They can be physically or virtually grouped (not shown) in one or more networks, such as a private cloud, community cloud, public cloud, or hybrid cloud, or a combination thereof, as described above. This allows the cloud computing environment 50 to provide infrastructure, platform, and / or software as a service for which the cloud consumer does not need to maintain resources on a local computing device. It should be understood that Figure 1 The types of computing devices 54A-N shown in FIGURE 5 are intended for illustration only, and computing node 10 and cloud computing environment 50 may communicate with any type of computerized device over any type of network and / or network-addressable connection (eg, using a web browser).

[0063] Now refer to Figure 2 , showing the cloud computing environment 50 ( Figure 1 ) provides a set of functional abstraction layers. It should be understood in advance that Figure 2 The components, layers, and functions shown in are intended to be illustrative only, and embodiments of the present invention are not limited thereto. As depicted, the following layers and corresponding functions are provided:

[0064] The hardware and software layer 60 includes hardware and software components. Examples of hardware components include: host 61; server 62 based on RISC (Reduced Instruction Set Computer) architecture; server 63; blade server 64; storage device 65; and network and network components 66. In some embodiments, software components include network application server software 67 and database software 68.

[0065] Virtualization layer 70 provides an abstraction layer from which the following examples of virtual entities can be provided: virtual servers 71 ; virtual storage 72 ; virtual networks 73 , including virtual private networks; virtual applications and operating systems 74 ; and virtual clients 75 .

[0066] In one example, the management layer 80 may provide the functionality described below. Resource provisioning 81 provides for dynamic procurement of computing and other resources for performing tasks within a cloud computing environment. Metering and pricing 82 provides for cost tracking when utilizing resources in a cloud computing environment, as well as billing or invoicing for the consumption of those resources. In one example, these resources may include application software licenses. Security provides authentication for cloud consumers and tasks, as well as protection for data and other resources. A user portal 83 provides access to the cloud computing environment for consumers and system administrators. Service level management 84 provides for allocation and management of cloud computing resources so that required service levels are met. Service level agreement (SLA) planning and fulfillment 85 provides for pre-scheduling and procurement of cloud computing resources, where future demand is anticipated based on the SLA.

[0067] Workload layer 90 provides examples of functionality that can utilize a cloud computing environment. Examples of workloads and functionality that can be provided from this layer include: mapping and navigation 91; software development and lifecycle management 92; virtual classroom education delivery 93; data analysis processing 94; transaction processing 95; and source code versioning 96. It will be understood that these are just some examples and that in other embodiments, these layers may include different services.

[0068] In one or more examples, the HTAP system can be implemented as a multi-master HTAP system with several loosely coupled processes. In addition, in one or more examples, high-speed transactions are supported with weak snapshot isolation and concurrent analytical queries on new data, for example using an application programming interface to achieve this.

[0069] Figure 3 The data lifecycle in an HTAP-based system is described. As can be seen, and as further described, data evolves across multiple areas within system 100, which is an HTAP system. System 100 includes a processing unit 190, which may include multiple processors. In one or more examples, the multiple processors are part of different computer servers in a distributed computing system. Computer servers are also referred to as "nodes." System 100 also includes various types of non-volatile memory devices. Memory usage by processing unit 190 varies depending on the type of memory device.

[0070] Tables in system 100 can be "sharded" using a user-provided shard key (a subset of the primary key). In database computing, sharding is a technique that facilitates scaling a database by splitting data across multiple databases. Table shards are replicated to multiple nodes, with one replica acting as the shard leader and the rest as slaves. Inserted records are routed to different shards based on the shard key. Any replica of a shard can ingest data.

[0071] As part of data ingestion, transactions first append writes to the in-memory transaction log buffer 101. Each shard in the system 100 has a designated collator that runs on the leader replica. The collator is a process executed by one or more of the processors in the processing unit 190, particularly those processors assigned to the leader replica.

[0072] The collator periodically (e.g., every second, every 5 seconds, etc.) merges the transaction logs from the transaction log buffer 101 from the shard replicas in chronological order, sets a monotonically increasing start timestamp (beginTS) for each record, and creates columnar data files, also known as blocks, in shared storage 160 and local cache 155. Shared storage 160 is typically slower to access (higher latency), thus motivating the use of local cache 155. For example, shared storage 160 may be a hard drive, tape, distributed file system, cloud storage, etc., while local cache 155 may be a solid-state drive, non-volatile memory (NVM) device, etc. Local cache 155 is made non-volatile so that cached components 172 of the hybrid index generated by one or more embodiments of the present invention can be persisted. The hybrid index is a combination of the collated index 173 and the post-collated index 174, where cached components 172 are the portion of the hybrid index cached in local cache 155. Various advantages of persisting cached components 172 include that cached components 172 are readily available to system 100 if / when system 100 experiences a failure and must be restarted. By persisting cached components 172 in this manner, the indexing process does not have to be performed again.

[0073] Each collated block is uniquely identified by a monotonically increasing ID called the collated block ID. The commit time of transactions in the system 100 is effectively delayed to the collation time. Another separate process, called the post-collator, periodically (e.g., every 10 minutes, 15 minutes, 30 minutes, etc.) performs post-collation operations on the newly collated blocks in the collated area (103) to make the data more analysis-friendly and move and convert the data from the collated area (103) to the organized (or post-collated) area (104). The post-collation operation performs a series of tasks on the newly collated data from the collated area (103), such as setting the end timestamp of the update record for versioning, partitioning the data based on a user-defined partition key (usually different from the shard key) optimized for queries, and separating the latest version from the historical version to facilitate queries on the latest data. The post-collation operation publishes a file list that includes a monotonically increasing post-collation sequence number, the ID range of the collated blocks from the collated area (103) that evolved in the operation, and a list of the blocks generated in the post-collated area (104). The system 100 organizes data in separate collated areas (103) and post-collated areas (104) to support fast ingestion into the collated areas (103). Simultaneously, the data in the collated areas (103) is optimized for analytical queries in the background by the post-collator using an optimization strategy that delays optimization of a portion of the data until that portion is needed to avoid using computing resources to optimize unused data.

[0074] Depending on the freshness requirement, a query may need to access data in both the post-collation area (104) and the collated area (103). However, the data in the transaction log buffer (101) is not accessed by the query because it contains uncommitted data. To facilitate point lookup and transaction processing, our goal is to create an index on the data in the collated area (103) and the post-collation area (104), such as Figure 1 shown.

[0075] Therefore, the hybrid index provides a unified view for queries in the background as data evolves from one region to another. As data evolves from the sorted region (103) to the post-sorted region (104), the hybrid index is also updated by updating the sorted index 173 and the post-sorted index 174. Figure 3 The combination of blocks 172, 173, and 174 in ) is based on a log-structured merge tree (LSM tree).

[0076] As background on LSM trees: An LSM tree is a persistent index structure optimized for high-volume ingestion. Instead of updating entries in place, which might require random I / O, an LSM tree inserts into memory in batches and flushes the data to disk using sequential I / O when memory is full. Each flush operation creates a new run. LSM tree runs are immutable. Modifications (inserts, updates, and deletes) are handled by inserting new entries into memory. An insert or update simply inserts a new entry with the same key, while a delete places a tombstone entry indicating that the key has been deleted. Queries must reconcile entries with the same key from multiple runs, with entries from newer (i.e., younger) runs taking precedence over entries from older runs. A "run," as used here, can be constructed in various ways, such as using a B-tree, a sorted array with a list of page pointers stored in memory to guide searches, or any other such data structure.

[0077] In order to keep the number of runs small, which improves query performance, runs are periodically merged into larger runs. In general, merging processes that are commonly used in practice include hierarchical and tiered. These are briefly discussed below, however, it should be understood that any other merging process may be used in one or more embodiments of the present invention. In hierarchical and tiered schemes, runs are organized into levels, where new runs are added to the lowest level 0, and runs are gradually merged into higher levels. The hierarchical strategy optimizes index lookups by limiting only one run to each level. A run in level L is immediately merged with a run in level L+1, however, the tiered strategy allows up to N runs in level L, and these runs are merged together into a new run in level L+1, reducing the amortized insertion cost at the expense of the lookup cost.

[0078] Although LSM-like indexes have been used in the prior art, the technical challenge of utilizing a multi-level storage hierarchy for indexing and data evolution has not been solved, which is what one or more embodiments of the present invention are attempting to accomplish.

[0079] Figure 4 A representative view of a hybrid index according to one or more embodiments of the present invention is depicted. The hybrid index 200 is an LSM-like index in which runs 250 are organized into levels across multiple zones (103, 104). The hybrid index 200 also includes a header block 402 that includes metadata for the hybrid index 200, such as a summary of the data included in the hybrid index 200. In addition, the index runs 250 are organized into multiple levels (e.g., levels 0 through 5). It should be noted that in one or more examples, each level may not exist depending on the age of the data. For example, in Figure 4In the example above, data is organized into levels 0, 1, and 6. In other examples, index runs 250 may be organized into a different number of levels (e.g., 0-4 levels) and / or into different levels (levels 0, 1, 2, and 3). Data ingestion creates a new index run 250. Each index run 250 has a list of sorted rows (i.e., index entries). As further described, the hybrid index 200 is maintained using background index maintenance operations that are non-blocking to readers. Index maintenance operations include merge operations that produce a new index run 250 in the next level, index purge and garbage collection for space management, and index evolution operations that are performed after data is moved or updated from one region to another.

[0080] The hybrid index 200 can be used in both a single run format (250) and a multi-run structure, as will be described herein. Here, an index run 250 (represented by a triangle) is an index file that contains index entries for data from a set of collated chunks. Chunk ID ranges are marked in each index run (in Figure 4 depicted as a triangle in the figure).

[0081] like Figure 4 As shown, the hybrid index 200 may include a plurality of runs 250, where levels 0 to 5 (levels 2 to 5 not shown) are configured for the cleaned area 103, and levels 6 to 9 (levels 7 to 9 not shown) are configured for the post-cleaned area 104. For clarity, each run 250 is labeled with its corresponding cleaned block ID range. In the example shown, the cleaned runs labeled 23-24 and 21-22 belong to level 0, while the runs labeled 16-20 and 11-15 belong to level 1, and the post-cleaned runs labeled 6-10 and 0-5 belong to level 6.

[0082] Index run 250 includes an index key and zero or more included columns 220. Index key 210 is defined as a composite key having zero or more equality columns 212 (for equality predicates) and zero or more sort columns 214 (for range predicates). Included columns 220 are columns included in index run 250 to facilitate query processing by allowing index-only access. Composite index 200 includes at least one equality column 212 or sort column 214 specified in index key 210.

[0083] If (multiple) equality columns 212 are specified, hash values ​​of the equality column values ​​are also stored in the index run 250 to speed up index queries. In this way, the index run 250 is a combination of a hash and a range index. In an example Internet of Things (IoT) application, the device ID is used as the equality column 212, and the message number (msg) is used as the sort column 214. As a special case, the (multiple) equality columns 212 are omitted, which makes the hybrid index 200 a range index. In the above example, the record being ingested may include at least the device ID, msg, and message, as well as other data elements.

[0084] In one or more examples, the index key is the primary key of the table, that is, mixed index 200 is a primary index.

[0085] An index run can be logically viewed as a table of rows. In addition to the columns specified in the index definition above, the table includes two additional columns: a start TS column 230 and a tuple sequence number (TSN) column 235. The start TS column 230 indicates the timestamp when the record was inserted. The start TS in system 100 is generated by the tidy operation. The TSN column 235 specifies the precise location of the index record. In one or more examples, the TSN in system 100 can be a 64-bit integer, where the high-order bits indicate the block ID and the low-order bits specify the record offset within the block.

[0086] Thus, each row in the index run has a hash column 225, an equality column 212, a sort column 214, a start TS 230, an include column 220, and a TSN 235. In one or more examples, the rows in the index run are sorted in descending order 230 of the hash column 225, the equality column 212, the sort column 214, and the start TS.

[0087] As a special case, if the equality column 212 is not specified, the hash column 225 is omitted from the index run. The descending start TS column 230 facilitates access to newer versions. All sort columns, namely the hash column 225, the equality column 212, the sort column 214, and the start column 230, are stored in a lexicographically comparable format so that keys can be compared using memory comparison operations when processing index queries.

[0088] Physically, an index run is stored as a header block plus one or more fixed-cardinality data blocks (except the last data block). The header block contains metadata information about the index run, such as the number of data blocks, the merge level to which the run belongs, and the range of collated block IDs corresponding to the run.

[0089] In one or more embodiments of the present invention, a summary is stored in a header block to remove irrelevant runs during index queries. The summary contains the ranges for each key column stored in the run. If the input value for a key column does not overlap with the range specified by the summary, the index query can skip the run.

[0090] When the equality column 212 is specified in the mixed index 200 definition, the header block includes 2 n An offset array of integers to facilitate index queries. The offset array maps the most significant n bits of the hash value to offsets in the index run. When processing index queries, the offset array can be used to provide more compact start and end offsets for binary searches, effectively limiting the number of blocks to be visited.

[0091] Figure 5 An exemplary indexing operation according to one or more embodiments of the present invention is shown. In the described example scenario, consider the device ID (in Figure 5 ) is the equality column 212, and msg is the sort column 214. For simplicity, the other columns in the index run are not shown, and it is assumed that the hash value only takes one byte. Table a shows the index entries in this run, where the hash value is shown in binary format, and Table b shows the offset array with the most significant 3 bits of the hash value. The physical layout of this index run 250 is shown in Table c, where the index run 250 contains one header block 402 and two data blocks 504.

[0092] According to one or more embodiments of the present invention, hybrid index 200 may include multiple runs organized into multiple levels. Runs at lower levels, such as level 0, correspond to the most recent data and are periodically merged into higher levels to limit the total number of runs. However, data in HTAP systems is typically organized into multiple regions (103, 104) as described herein, and data gradually evolves from one region to another. To illustrate this multi-region data organization in HTAP systems, hybrid index 200 accordingly divides the levels into multiple regions. In one or more examples, hybrid index 200 includes two regions: one for tidying (103) and one for post-tidying (104). Within each region, runs are linked together into a linked list based on their creation time, with the head 402 pointing to the most recent run. Runs can only be merged into the same region. That is, a run in tidying region 103 cannot be merged into a new run in post-tidying region 104. Instead, as data evolves over time in the system, runs are periodically evolved to another region through index evolution operations. Details of index maintenance operations, such as merging and evolution, are discussed further herein.

[0093] In one or more examples, a user may set one or more lower levels to non-persistent to avoid writing too many small files to the shared storage device, since lower level runs are typically smaller and merged more frequently. Runs in the non-persistent level exist only in memory 150 and SSD cache 155, and are not persisted in shared storage 160, which can significantly improve merge performance. However, for recoverability, level 0 must be set to persistent so that the system does not need to re-index runs after a system crash / failure.

[0094] Figure 6 An exemplary run allocation for index maintenance according to one or more embodiments of the present invention is depicted. In the illustration, each run 250 is labeled with the level to which it belongs. To minimize concurrent index modifications caused by index maintenance, each level is assigned to a dedicated index maintenance thread 610. Each thread 610 periodically performs index maintenance operations only on the runs 250 assigned to that thread 610. This ensures that an index run 250 can only be modified by one thread 610 at a time, thereby minimizing locking and contention in concurrent index modifications. Index maintenance operations include index build, purge, merge, and evolve operations. Index maintenance is performed concurrently with index queries and other index modifications. To achieve maximum concurrency for index queries, atomic pointer swaps are used instead of locks. All index maintenance operations result in each index modification, i.e., a pointer swap, resulting in a valid state of the index. As a result, index queries are non-blocking and lock-free.

[0095] In addition, if Figure 6 As shown, the allocation of threads 610 is shifted by one run 250, ie, thread 610 for level L processes all but the first run in level L plus the first run in level L+1.

[0096] Figure 7 A flowchart depicts an example method for building an index run according to one or more embodiments of the present invention. Index maintenance includes an index build operation that creates a new index run 250 on the data blocks in the decluttered area 103, making the decluttered data searchable from the index. To build the index run 250, at 702, the decluttered blocks in the decluttered area 103 are scanned to determine which columns, namely, the equality column 212, the sort column 214, and the include column 220, are to be included in the index run 250. Additionally, at 704, the TSN 235 is calculated. Additionally, at 706, a hash value 225 of the equality column 212 (if any) is calculated.

[0097] Furthermore, at 708, the index entries are sorted in ascending order of hash value 225, sort column 214, equality column 212, and descending order of start TS 230. The sorted entries are sequentially written to the index data block at 708. Furthermore, offset array 510 is calculated substantially simultaneously.

[0098] After all entries are written out, at 710, a header block 402 is created, including writing out the offset array 510 with other necessary fields. At 712, the new index run 250 is added to the tidied run list by setting the new index run 250 to point to the created header 402 and resetting the header 402 to the new index run 250. Note that the order of the pointer swapping is important to ensure correctness for concurrent readers.

[0099] Figure 8 A flowchart of an example method for clearing index operations according to one or more embodiments of the present invention is depicted. Index maintenance includes an index clearing operation that removes some index entries from cache 155 to free up cache space. Typically, a large-scale HTAP system includes a multi-layer storage hierarchy ( Figure 3 ). Cache 155, which may be an SSD, is used as a cache layer for shared storage 160 to accelerate index queries. Typically, index runs 250 fit in SSD cache 155, allowing shared storage 160 to be used for backup. However, when SSD cache 155 is full, index purging removes some index entries from SSD cache 155 to free up cache space. In one or more examples, it is assumed that recent data is accessed more frequently. Therefore, as index 200 grows, older runs, i.e., higher-ranked runs, are purged from SSD cache 155 to free up cache space.

[0100] Index clearing operations are referenced above Figure 6 The described index maintenance thread 610 process keeps track of the current purge level to ensure that runs are purged in the correct order, i.e., older runs are purged first. The current purge level separates purged and cached runs. In other words, all runs 250 above the purge level are purged, while runs below the purge level are cached.

[0101] Only threads at the current purge level are allowed to purge indexed runs 250. When the SSD cache space is full, the thread 610 associated with the current purge level purges its assigned runs 250 by discarding data blocks from the SSD cache 155, at 802, 804. If all assigned runs have been purge, the thread 610 decrements the purge level, transferring ownership to the next lower level thread, 806.

[0102] Figure 9 Depicted is a visual illustration of a purge operation according to one or more embodiments of the present invention in an example scenario. In the depicted situation, index run 250 of block IDs 0-18 and 19-36 are discarded from cache 155 and are therefore stored only in shared storage 160.

[0103] On the other hand, if the SSD cache 155 has free space, the most recent run 250 is loaded (in the opposite direction of the flush) into the SSD cache 155 to accelerate the index query at 808. In the case of a load, the thread 610 assigned to the current flush level loads the corresponding run 250 from the current flush level by caching the data block from the shared storage device 160 into the SSD cache 155, 810. Furthermore, at 812, 814, when all assigned runs have been cached, the thread 610 increments the flush level, transferring ownership to the next higher level thread.

[0104] Index maintenance operations also include index merges. Index runs 250 are periodically merged to form larger index runs 250 to limit the number of runs 250 and improve query performance. As described earlier in this document, merges can be performed using a hierarchical strategy that optimizes index queries by limiting only one index run 250 in each level. In this case, when the size of the first index run 250 exceeds a predetermined threshold, the first index run 250 in level L is merged with the second index run 250 in the next level (L+1). Therefore, the first index run 250 is merged multiple times before it is filled and merged into the next level. In contrast, the tiered strategy optimizes write amplification by allowing multiple index runs 250 in each level. In this case, the index run in level L is merged into a new index run in level L+1. One or more embodiments of the present invention balance the tradeoff between write amplification and query performance by using a hybrid merge that combines hierarchical and tiered merge techniques. Similar to the tiered strategy, we allow multiple index runs 250 in each level. On the other hand, similar to the hierarchical strategy, merging runs in level L does not always create a new run in level L+1. If the first run in level L+1 is not large enough (as a configurable parameter), it is merged with the run in level L. Otherwise, the run in level L is merged into the new run in level L+1. Therefore, the index maintenance thread of level L is responsible for all runs except the first index run 250 of level L plus the first index run 250 in level L+1, which results in the following: Figure 6 One run shift assignment shown.

[0105] Figure 10A flowchart depicts an example method for an index merge operation according to one or more embodiments of the present invention. Initially, the method includes determining, at 902, columns 250 to be created in a new index run, which is the merged index run, by scanning columns in a first index run and a second index run to be merged. During the merge operation, a new index run 250 is formed that, when compared to creating an index run 250 from a collated block in a collated region 103, has the difference that the entries in each index run being merged are already sorted. Therefore, at 904, in the case of a merge, a merge sort must be performed to globally sort the two index runs 250 being merged.

[0106] The write destination of the new run depends on the current index purge level and non-persistent level of the hybrid index 200. Specifically, if the new run 250 belongs to the non-persistent level, it is only written to the SSD cache 155 at 906 and 908. Otherwise, if its merge level is lower than the current index purge level, the new index run 250 is written to the SSD cache 155 and the shared storage 160 at 906, 910, and 912, which alleviates cache misses after the index merge. Finally, if the new index run 250 is retained and is above the current index purge level, it is only written to the shared storage 160 at 906, 910, and 908.

[0107] The new index run 250 then replaces the merged index run in the run list. Specifically, at 914, the new index run 250 is set to point to the index run 250 originally pointed to by the last merged run, and then the index run preceding the first merged run is set to point to the new index run 250. Locking of the run list is required to ensure the correctness of the run replacement because two pointers are read and set, which is not an atomic operation, and the pointers can be modified simultaneously by lower-level threads. However, the locking overhead is negligible because it only affects infrequent index maintenance operations, such as every second or every minute, and these locks do not block any index queries.

[0108] Furthermore, at 916, when the last reader exits, the merged runs 250 are deleted. However, index runs in the first level before the non-persistent level cannot be removed immediately after being merged, because otherwise the index runs may be lost in a system crash. Instead, these runs are tracked and deleted only after they are merged into the first level after the non-persistent level. For example, assume that the non-persistent levels are configured as levels 1 to 2; that is, when runs in level 0 are merged into level 1, they cannot be immediately deleted for recovery. The system tracks these runs and deletes them only after they have been merged into level 3, which is persisted again on shared storage 160.

[0109] Index maintenance also involves evolving the index as data evolves from blocks in the cleaned area 103 to blocks in the post-cleaned area 104. As previously mentioned, the post-cleaner periodically moves data blocks from the cleaned area 103 to the post-cleaned area 104, marking them as deprecated and eventually deleting them. Accordingly, index entries must also evolve from the cleaned running list to the post-cleaned running list so that deprecated cleaned blocks are no longer referenced. However, index evolution is important due to at least the following challenges.

[0110] First, in one or more HTAP systems, data evolution is handled by separate processes. For example, the post-processor is a separate process running on a different node from the indexing process. Therefore, one challenge is minimizing communication and coordination between multiple processes during index evolution operations. Furthermore, index evolution operations themselves apply multiple modifications to the index, such as adding new runs to the post-processing run list and clearing old runs from the post-processing run list. This poses another challenge in achieving non-blocking index queries.

[0111] Figure 13 A flowchart depicts an example method for performing an index evolution operation according to one or more embodiments of the present invention. To handle data evolution handled by separate processes, index evolution operations in one or more embodiments of the present invention are performed asynchronously by the indexing process without any coordination. After each post-processing operation, the post-processor publishes and maintains a post-processing sequence number (PSN) for that operation. The PSN is an incrementing sequence number that uniquely identifies each run of the post-processing program. Simultaneously, the indexing process tracks the largest post-processing sequence number that has been indexed, i.e., the IndexedPSN, and keeps polling the most recent PSN. If the IndexedPSN is less than the PSN, at 1002, 1010, and 1020, the indexing process performs an index evolution operation on IndexedPSN+1, which ensures that the index evolves in the correct order and increments the IndexedPSN when the operation is complete. It should be noted that asynchronous index evolution has no impact on index queries, as the post-processing operation merely moves data from one area to another without generating any new data. For queries, there is no difference between accessing records from the sorted area 103 or the post-processed area 104.

[0112] To clear old runs from the post-collation run list and achieve non-blocking index queries, one or more embodiments of the present invention decompose the index evolution operation into a sequence of atomic sub-operations. Each sub-operation is an atomic modification of the index state. This further ensures that any sub-operation results in the correct index state, thereby achieving non-blocking and lock-free index queries. For example, for a given post-collation sequence number, the index evolution operation is performed as follows. First, an index run 250 is created for the data blocks generated by this post-collation operation and then added to the post-collation run list 1012. It should be noted that the run header block still contains the range of collated block IDs corresponding to this run. Next, at 1014, the maximum collated block ID covered by the runs in the post-collation run list is updated. At this point, all runs in the collated run list with an end-of-collation block ID no greater than this updated value are automatically discarded and ignored by the index query because the entries in these runs have already been covered by the post-collation list. Finally, at 1016, the discarded runs in the collated run list are garbage collected by each index maintenance thread. In one or more examples, garbage collection is performed once the number of runs in the collated list exceeds a predetermined threshold. Before performing other index maintenance operations, the thread first removes the deprecated runs from the run list to avoid concurrent modifications with other index maintenance operations on these runs.

[0113] In the above, each step modifies the hybrid index 200 only once and is therefore atomic. Between any two of the above steps, the hybrid index 200 may contain duplicate entries, that is, records with the same version may be indexed by both the sorting run and the post-sorting run. In addition, even after the last step of the index evolution operation, the hybrid index 200 may still contain duplicates because the sorted blocks consumed by the post-sorting operation in the sorted area 103 may not be completely aligned with the boundaries of the index run 250. However, duplicates are harmless to index queries. During query processing, they are removed immediately to ensure that only one version is returned for each matching key, and the remaining versions including duplicates are discarded. The details of query processing are further described herein.

[0114] Figure 11 and Figure 12 Depicted is a visual illustration of an index merge operation according to one or more embodiments of the present invention in an example scenario.Here, the index runs 250 being merged are those for block IDs 67-70 and 71-72, block IDs 55-60 and 61-66, and block IDs 0-18 and 19-36. Figure 11 Depicts the creation of a merged index run 250. Figure 12The second phase of the merge operation is depicted, wherein an atomic pointer update is performed after creating the merged index run 250. Additionally, the older index run, now merged into the newly created index run, is garbage collected. As can be seen, the merge operation can be performed at any level, and further, the merged run can be assigned to a different level than the index run 250 that was merged to create the merged index run.

[0115] Figure 14 An example scenario of evolving indexes according to one or more embodiments of the present invention is shown. The specific example shown shows the resulting index after the sorted blocks 11 to 18, where the original index is Figure 4 . Here, a new index run 1110 for chunk ID 11-18 is created for the newly collated data. Run-1 250 for chunk ID 11-15 is ignored by subsequent queries because its entries are completely overwritten by the new index run 1110. It is further garbage collected by index maintenance thread 610 when thread 610 next wakes up. Also, note that index run-2 250 for chunk ID 16-20 still contains duplicate entries with the new index run 1110. These duplicates are removed during query processing.

[0116] Figure 15 Pseudo code for index maintenance operations according to one or more embodiments of the present invention is provided. As previously described, index maintenance operations are performed by index maintenance threads 610, each of which is assigned to an index run list 250 from a particular level. Thread 610 first garbage collects the assigned index runs 250 if any of them have evolved to another area. Subsequently, if the current purge level is equal to the assigned level, the thread purges or loads index runs based on the current cache space. Finally, if the number of runs in the level exceeds a predefined threshold, thread 610 merges the assigned index runs 250.

[0117] In one or more embodiments of the invention, building new index runs after a tidy or post-tidy operation is not handled by the index maintenance thread 610. Instead, building new index runs 250 on data in the tidy area 103 is part of the tidy operation and is handled by the tidy thread. Indexing data in the post-tidy area 104 is performed by a dedicated thread of the indexing process that periodically reads the latest PSN to see if any new post-tidy operations have been performed.

[0118] As described herein, the hybrid index 200 according to one or more embodiments of the present invention is a persistent index that facilitates efficient recovery of the system without having to perform the indexing again, such as in the event of a system failure. This is because all index runs 250, except those in the non-persistent level, are securely persisted in the shared storage device 160. After each index run 250 is built and added to the hybrid index 200, the maximum tidied block ID of the tidied / post-tidied run list is updated and persisted. However, the indexing process may crash, losing all data structures in the local node. Therefore, in order to recover the hybrid index 200, the run list must be reconstructed based on the index runs 250 stored in the shared storage device 160, and if there are garbage and incomplete runs, the garbage and incomplete runs must be cleared.

[0119] Figure 16 Pseudocode for recovering a run list according to one or more embodiments of the present invention is depicted. In the example shown, the function RecoverRunList takes as input the maximum sorted block ID (maxID) and the minimum sorted block ID (minID) of the run list. Starting at maxID, the function finds the run in the highest level where the ending block ID is maxID. The function then searches backward by setting maxID to the run's starting block ID minus one. This process repeats until maxID is less than minID.

[0120] To restore both run lists, call the RecoverRunList function with the appropriate maxID and minID. For both lists, maxID is set to the persisted corresponding maximum tidy chunk ID. However, the minID for the tidy run list is set to the maximum tidy chunk ID for the post-tidy run list plus one, since all index entries below it have evolved to the post-tidy run list. Although the post-tidy run list's minID is simply set to 0, assuming that tidy chunk IDs start at 0, after restoring both run lists, all unused run chunk files are simply deleted, as they correspond to merged or incomplete runs.

[0121] After the running list is restored, the index is ready to process index queries. If the IndexedPSN is less than the current PSN, index evolution operations are performed asynchronously as described above until the IndexedPSN eventually catches up.

[0122] Compared to the prior art, the hybrid index 200 created and maintained in this manner facilitates processing queries in an efficient manner and, as described herein, facilitates multi-region queries. Because the hybrid index 200 is a multi-version index, queries must specify a query timestamp (QueryTS) and only the latest version of each matching key, i.e., the version with the largest StartTS 230 such that StartTS ≤ QueryTS, is returned.

[0123] Generally, two types of index queries are supported. First, a range scan query specifies the values ​​of all equal columns 212 (if any) and the bounds of the sort column 214, and returns the latest version of each matching key. Second, a point lookup query specifies the entire index key (i.e., the primary key) and returns at most one matching record.

[0124] To process an index query, candidate runs are initially collected by iterating over the run list and examining the run profile. An index run 250 is considered a candidate only if all column values ​​specified in the query satisfy the column ranges in the profile. Note also that all index runs 250 are read from the SSD cache 155. In the event that a query requires access to a run to be purged, the index run is first transferred from shared storage 160 to the SSD cache 155 on a block-by-block basis, i.e., an entire run data block 402 is transferred at once to facilitate future access. After the query is completed, the cached data block is released and further discarded in the event of a cache replacement.

[0125] Figure 17 A flowchart of an example method for performing a range scan query on a single index run is depicted in accordance with one or more embodiments of the present invention. At 1402, a single run is searched to return the latest version of each matching key in the index run 250 for the received query. At 1408, the first matching key in the single run is first located. Because the index run 250 is a table of sorted rows, in one or more embodiments of the present invention, a binary search with connected lower bounds (i.e., lower bounds on hash values, equal column values, and sort column values) is used. If an offset array is available, then at 1404, 1406, the initial search range can be narrowed by calculating the most significant n bits of the hash value (denoted as i) and taking the i-th and i+1-th values ​​in the offset array.

[0126] After determining the first matching key, the index entries are iterated until the upper bound of the join is reached, i.e., the join of the upper bound of the hash value, the equality column value, and the sort column value. At 1410, during this iteration, entries failing the timestamp predicate Start TS ≤ Query TS are filtered out. For the remaining entries, at 1412, for each key, the entry with the largest Start TS is returned, since the entries are sorted in descending order of the index key and Start TS.

[0127] Consider again Figure 5 , where device is the equality column 212 and Msg is the sort column 214. Consider a range scan query with device=4, the bounds of msg are [1, 3], and query TS=100. Here, the most significant 3 bits of hash (4)=10010001, i.e., 100, are used to obtain the initial search range, i.e., 2 to 6, from the offset array 510. In this example, the first matching key is entry 2 after a binary search with the input lower bound (10010001, 4, 1). The index entries are then iterated starting from entry 2, returning entry 2 because it is the latest version of key (4, 1), while entry 3 is filtered out because it is an older version of entry 2, however, entry 4 is filtered out because its start TS 102 exceeds the query TS. Iteration stops at entry 5, which exceeds the input upper bound (10010001, 4, 3).

[0128] Therefore, a single-run index is used to efficiently identify matching entries for range scan queries.

[0129] In the case of multiple run indexes, each run is scanned independently to identify matching entries, as described above. Furthermore, after searching each index run independently, the results returned from the multiple runs are reconciled to ensure that only the most recent version is returned for each matching key. For example, two methods can be used to perform the reconciliation.

[0130] In the first set-based approach, searches are performed sequentially from the latest index run 250 to the oldest run, and a key set is maintained for the keys that have been returned to the query. If a key has not been returned before, that is, it is not in the set, it is added to the set and the corresponding entry is returned to the query; otherwise, the entry is ignored because a newer version from a newer run has already been returned.

[0131] Alternatively, in a priority queue approach, multiple index runs are searched together, and the results returned from each index run 250 are fed into a priority queue to maintain a global ordering of the keys. Once the keys are sorted, the latest version of each key is selected and the remaining versions are discarded without remembering intermediate results.

[0132] Point lookup queries can be viewed as a special case of range scan queries where the entire primary key is specified so that at most one entry is returned. As a special optimization, the search is performed sequentially from the newest run to the oldest run, and the search is stopped early as soon as a match is found. Thus, the method used to search a single run ( Figure 17 ), where the lower and upper bounds of the sort column values ​​are the same.

[0133] For large-scale batch point lookups, i.e., as an access path from the secondary index to the primary index, the input keys are first sorted by the hash value 225, the equality column value 212, and the sort column value 214, rather than evaluating each point lookup directly. Furthermore, each input key is associated with a Boolean value indicating whether the input has been found so far. The sorted input keys are then searched sequentially against each index, one run at a time, from newest to oldest, until all Boolean values ​​are set to true, so that each run block is accessed at most once.

[0134] Thus, one or more embodiments of the present invention promote hybrid index structures to improve the performance of HTAP systems. The hybrid index described herein provides a distributed log-structured, multi-versioned, multi-region index that is easily recoverable / restartable. Portions of the index can be easily moved within the storage hierarchy while still facilitating fast recovery. According to the structure of the index described herein, information is not held solely in memory; instead, index levels can be either persistent or non-persistent. If levels are non-persistent, their contents can be recovered from the last previously persisted level or committed log.

[0135] In addition, the hybrid index structure described in this article facilitates efficiently returning the correct version of data from a distributed log-structured multi-version index. Large-scale hybrid transaction / analytic processing systems organize data differently across multiple zones, typically with newer data in data zones that are transaction / insert / update / delete friendly zones, and older data in analysis / scan friendly zones. There can even be multiple analysis-friendly zones. In this system, data evolves from one zone to another as the data ages. In order to find and return the correct version of a key / tuple, a priority queue is used, and multiple index runs (from the same data zone or different data zones) are searched simultaneously to maintain a global ordering of the keys. Once the keys are sorted, the latest version of each key can be selected, and the remaining versions can be discarded without having to remember intermediate results.

[0136] It should be noted that although one or more embodiments of the present invention are described using an HTAP system with two data zones, the features described herein can also be used to support other HTAP systems with multi-zone data organization. To support multiple zones, rather than just two as presented herein, the hybrid index is constructed with multiple run lists, each corresponding to a data zone. As data evolves from one zone to another, the corresponding index entries also evolve from one run list to another through index evolution operations.

[0137] One or more embodiments of the present invention thus facilitate a recoverable distributed multi-version multi-region index that utilizes LSM trees to obtain a recoverable index structure that can be optimized for both transaction processing and analytics.

[0138] The present invention may be a system, method and / or computer program product at any possible level of technical detail integration. The computer program product may include a computer-readable storage medium (or multiple media) having computer-readable program instructions thereon, the computer-readable program instructions being used to cause a processor to perform various aspects of the present invention.

[0139] A computer-readable storage medium can be a tangible device that can retain and store instructions used by an instruction execution device. A computer-readable storage medium can be, for example, but not limited to, an electronic storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination thereof. A non-exhaustive list of more specific examples of computer-readable storage media includes the following: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a static random access memory (SRAM), a portable compact disc read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanical encoding device such as a punch card or a raised structure in a groove on which instructions are recorded, and any suitable combination thereof. As used herein, a computer-readable storage medium should not be interpreted as a temporary signal itself, such as a radio wave or other freely propagating electromagnetic wave, an electromagnetic wave propagated by a waveguide or other transmission medium (e.g., a light pulse by an optical fiber cable), or an electrical signal transmitted by a wire.

[0140] The computer-readable program instructions described herein can be downloaded from a computer-readable storage medium to a corresponding computing / processing device, or downloaded to an external computer or external storage device via a network, such as the Internet, a local area network, a wide area network, and / or a wireless network. The network can include copper transmission cables, optical transmission fibers, wireless transmission, routers, firewalls, switches, gateway computers, and / or edge servers. The network adapter card or network interface in each computing / processing device receives the computer-readable program instructions from the network and forwards the computer-readable program instructions to be stored in a computer-readable storage medium within the corresponding computing / processing device.

[0141] The computer-readable program instructions for performing the operations of the present invention may be assembly instructions, instruction set architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state setting data, configuration data for an integrated circuit, or source code or object code written in any combination of one or more programming languages ​​(including object-oriented programming languages, such as Smalltalk, C++, etc.) and procedural programming languages ​​(such as "C" programming language or similar programming languages). The computer-readable program instructions may be executed entirely on the user's computer, partially on the user's computer, as an independent software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider). In some embodiments, to perform various aspects of the present invention, an electronic circuit comprising, for example, a programmable logic circuit, a field programmable gate array (FPGA), or a programmable logic array (PLA) may execute the computer-readable program instructions to personalize the electronic circuit by utilizing the state information of the computer-readable program instructions.

[0142] Aspects of the present invention are described herein with reference to the flowcharts and / or block diagrams of the methods, apparatus (systems) and computer program products according to embodiments of the present invention. It will be understood that each block of the flowcharts and / or block diagrams and the combination of blocks in the flowcharts and / or block diagrams can be implemented by computer-readable program instructions.

[0143] These computer-readable program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device to produce a machine, such that the instructions executed by the processor of the computer or other programmable data processing device create a device for implementing the functions / actions specified in one or more blocks of the flowchart and / or block diagram. These computer-readable program instructions can also be stored in a computer-readable storage medium, which can direct the computer, programmable data processing device, and / or other equipment to operate in a specific manner, such that the computer-readable storage medium having the instructions stored therein includes an article of manufacture, which includes instructions for implementing various aspects of the functions / actions specified in one or more blocks of the flowchart and / or block diagram.

[0144] Computer-readable program instructions may also be loaded onto a computer, other programmable data processing apparatus, or other device to cause a series of operational steps to be performed on the computer, other programmable apparatus, or other device to produce a computer-implemented process, so that the instructions executed on the computer, other programmable apparatus, or other device implement the functions / actions specified in one or more boxes of the flowchart and / or block diagram.

[0145] The flow charts and block diagrams in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present invention. In this regard, each frame in the flow chart or block diagram can represent a module, segment or part of an instruction, which includes one or more executable instructions for realizing the specified logical function. In some alternative embodiments, the function noted in the frame may not occur in the order noted in the figure. For example, the two frames shown in succession can actually be performed substantially simultaneously, or these frames can sometimes be performed in reverse order, depending on the function involved. It will also be noted that the combination of each frame of the block diagram and / or flow chart illustration and the frame in the block diagram and / or flow chart illustration can be realized by a dedicated hardware-based system that performs a specified function or action or performs a combination of special-purpose hardware and computer instructions.

[0146] The description of various embodiments of the present invention has been provided for the purpose of illustration, but is not intended to be exhaustive or limited to the disclosed embodiments. Many modifications and variations will be apparent to those of ordinary skill in the art without departing from the scope and spirit of the described embodiments. The terminology used herein was chosen to best explain the principles of the embodiments, practical applications, or improvements over existing technologies in the marketplace, or to enable others of ordinary skill in the art to understand the embodiments described herein.

Claims

1. A computer-implemented method comprising: A hybrid index is created for a multi-region data storage system comprising a plurality of regions, wherein a first region comprises data organized for transactional processing and a second region comprises data organized for analytical processing, each index entry in the hybrid index comprising at least: an index key, the index key including equality columns; a start timestamp column, the start timestamp column indicating a timestamp when data corresponding to the index entry is inserted; a tuple sequence number column indicating a location of the index entry in the tiered storage device; and Constructing an index run to be included in the mixed index, the index run comprising a plurality of index entries, wherein constructing the index run comprises: determining columns to be created in the index run by scanning data blocks from the first region, the data blocks to be associated with the index run; calculating a tuple sequence number value for the index entry to be included in the mixed index, the tuple sequence number value being stored in the tuple sequence number column; and The index entries are sorted according to a predetermined order using the columns to be included in the indexing run.

2. The computer-implemented method of claim 1 , wherein building the index run further comprises: creating a header for an index run, the header including a pointer set to a current value of an index run pointer; as well as The value of the index run pointer is set to point to the head. 3 . The computer-implemented method of claim 2 , wherein a summary of the data elements stored within the index run is stored in the header.

4. The computer-implemented method of claim 1 , wherein: The index entry further includes a hash column, and constructing the index operation further includes: A hash value of the equal column is calculated according to the index entry, and the hash value is stored in the hash column.

5. The computer-implemented method of claim 2 , wherein building the index run further comprises: An offset array is calculated for the index run, the offset array being stored in the header, the offset array mapping the most significant N bits of a hash value to offsets of the index entries in the index run. The computer-implemented method of claim 1 , wherein the index entry further comprises an ordering column in the index key.

7. The computer-implemented method of claim 1, wherein the indexing run is assigned a level number, wherein the level number indicates a source of data used to construct the indexing run.

8. The computer-implemented method of claim 7, wherein the level number further indicates a region in which the data associated with the index run is stored, the region being one of the plurality of regions in the multi-region data storage system.

9. The computer-implemented method of claim 7, further comprising allocating a separate thread for maintaining one or more index runs from each level.

10. A system comprising: A storage system includes a hierarchical data storage device; as well as One or more processing nodes coupled to the storage system, the one or more processing nodes configured to create and maintain a hybrid index for a multi-region data storage system, the multi-region data storage system comprising a plurality of regions, a first region comprising data organized for transactional processing and a second region comprising data organized for analytical processing, each index entry in the hybrid index comprising at least: an index key, the index key including equality columns; a start timestamp column, the start timestamp column indicating a timestamp when data corresponding to the index entry is inserted; a tuple sequence number column indicating a location of the index entry in the tiered storage device; and The one or more processing nodes are configured to construct an index run to be included in the hybrid index, the index run comprising a plurality of index entries, wherein constructing the index run comprises: determining columns to be created in the index run by scanning data blocks from a first region, the data blocks to be associated with the index run; calculating a tuple sequence number value for the index entry to be included in the mixed index, the tuple sequence number value being stored in the tuple sequence number column; and The index entries are sorted according to a predetermined order using the columns to be included in the indexing run.

11. The system according to claim 10, wherein: Building the index run also includes: creating a header for the index run, the header including a pointer set to the current value of the index run pointer; and The value of the index run pointer is set to point to the head.

12. The system of claim 11, wherein a summary of the data elements stored within the index run is stored within the header.

13. The system of claim 11, wherein the index entry further comprises a hash column, and constructing the index run further comprises: Calculating a hash value of the equal column according to the index entry, wherein the hash value is stored in the hash column; as well as An offset array is calculated for the index run, the offset array stored in the header, the offset array mapping the most significant N bits of the hash value to offsets of the index entries in the index run.

14. The system of claim 10, wherein the index runs are assigned levels, wherein: The level number indicates a source of data used to construct the index run, wherein the level number further indicates a region in which the data associated with the index run is stored, the region being one of the plurality of regions in the multi-region data storage system.

15. A computer program product comprising a computer-readable storage medium having program instructions embodied therewith, the program instructions being executable by a processing circuit to perform a method for creating and maintaining a hybrid index for a multi-region data storage system: in, The multi-region data storage system includes a plurality of regions, wherein a first region includes data organized for transactional processing and a second region includes data organized for analytical processing; Each index entry in the mixed index includes at least: an index key, the index key including equality columns; a start timestamp column, the start timestamp column indicating a timestamp when data corresponding to the index entry is inserted; a tuple sequence number column indicating a location of the index entry in the tiered storage device; and The method includes constructing an index run to be included in the mixed index, the index run including a plurality of index entries, wherein constructing the index run includes: determining columns to be created in the index run by scanning data blocks from the first region, the data blocks to be associated with the index run; calculating a tuple sequence number value for the index entry to be included in the mixed index, the tuple sequence number value being stored in the tuple sequence number column; and The index entries are sorted according to a predetermined order using the columns to be included in the indexing run.

16. The computer program product of claim 15, wherein: Building the index run also includes: creating a header for the index run, the header including a pointer set to a current value of an index run pointer; and The value of the index run pointer is set to point to the head.

17. The computer program product of claim 16, wherein the index entries further comprise hash columns, and constructing the index entry further comprises: Calculating a hash value of the equal column according to the index entry, wherein the hash value is stored in the hash column, wherein building the index operation further comprises: An offset array for the index run is calculated, the offset array being stored in the header, the offset array mapping the most significant N bits of the hash value to offsets of the index entries in the index run.

18. The computer program product of claim 15, wherein the index runs are assigned levels, wherein the level number indicates the source of data used to construct the index runs, and wherein, The level number also indicates a region in which the data associated with the index run is stored, the region being one of the plurality of regions in the multi-region data storage system.

19. A computer-implemented method comprising: In response to receiving a request to merge two index runs, a first index run and a second index run, in a database, data is stored in the database using a hierarchical data storage device: creating a third index run to include columns from the first index run and from the second index run; globally sorting the columns from the first index run and the columns from the second index run using a merge sort; Based on determining that the third index run belongs to a non-persistent level, storing the third index run only in a cache storage device; Based on determining that the third index run belongs to the persistence level and the level number of the third index run is lower than the purge level, storing the third index run in the cache storage device and the shared storage device; as well as Based on determining that the third indexing run belongs to the persist level and the level number of the third indexing run is equal to or higher than the purge level, the third indexing run is stored only to the shared storage device.

20. The computer-implemented method of claim 19, further comprising: Update the list of index runs in the database, which includes: changing a pointer of a fourth index run to point to the address of the first index run, the pointer being changed to the address of the third index run; and The first index run and the second index run are deleted.

21. The computer-implemented method of claim 20, wherein the first index run and the second index run are deleted after the first index run and the second index run are merged into an index run belonging to the persistence level.

22. A system comprising: storage systems, including tiered data storage devices; as well as One or more processing nodes coupled to the storage system, the one or more processing nodes being configured to: creating and maintaining a hybrid index for a multi-region data storage system comprising a plurality of regions, a first region of the plurality of regions comprising data organized for transactional processing and a second region comprising data organized for analytical processing, wherein the hybrid index comprises a plurality of index runs, and wherein each index run comprises index entries ordered according to a predetermined order using one or more columns to be included in the index run; as well as Merging two index runs, a first index run and a second index run, by performing a method, the method comprising: creating a third index run to include columns from the first index run and from the second index run; globally sorting the columns from the first index run and the columns from the second index run using a merge sort; Based on determining that the third index run belongs to a non-persistent level, storing the third index run only in a cache storage device; Based on determining that the third index run belongs to the persistence level and the level number of the third index run is lower than the purge level, storing the third index run in the cache storage device and the shared storage device; and Based on determining that the third indexing run belongs to the persist level and the level number of the third indexing run is equal to or higher than the purge level, the third indexing run is stored only to the shared storage device.

23. The system of claim 22, wherein the method further comprises: Updates the list of index runs in the database, which includes: changing a pointer of a fourth index run to point to the address of the first index run, the pointer being changed to the address of the third index run; and The first index run and the second index run are deleted.

24. The system of claim 23, wherein the first index run and the second index run are deleted after the first index run and the second index run are merged into the index run belonging to the persistence level.

25. The system of claim 22, wherein each index run is assigned a level, wherein the level number indicates a source of data used to construct the index run, and wherein the level number also indicates a region in which the data associated with the index run is stored, the region being one of the plurality of regions in the multi-region data storage system.

Images