Selective data compression of in-memory databases

Abstract

This invention relates to selective data compression for databases in memory. A technique is provided for persistently maintaining data in one format, but making that data available to the database server in more than one format. Data in a format independent of the disk format can be specifically maintained in volatile memory to reduce the overhead associated with keeping the data synchronized with disk-based format copies. The selection of data to be maintained in volatile memory can be based on various factors. Once selected, the data can also be compressed to save space in the volatile memory. The compression level can depend on one or more factors evaluated for the selected data. The compression level of the data and the selected factors can be evaluated periodically, and based on this evaluation, selected data can be removed from volatile memory or the compression level of the selected data can be changed accordingly.

Description

[0001] This application is a divisional application of the PCT application filed on June 30, 2016, with national application number 201680057698.8 and entitled "Selective Data Compression of Database in Memory", which has entered the Chinese national phase. Technical Field

[0002] This invention relates to database systems, and more particularly to selective data compression of in-memory databases. Background Technology

[0003] Given that volatile memory (sometimes also called "main memory") is becoming increasingly cheaper and larger, more data can be cached from disk storage devices into volatile memory. This caching allows for faster data access and enables applications that use data to perform tasks more quickly.

[0004] However, making data accessible in volatile memory remains a challenge. First, the amount of data typically used by applications has increased significantly. In particular, fully caching large amounts of data (commonly known as "big data") in volatile memory would require an extremely large amount of volatile memory. Therefore, regardless of the size of the volatile memory, there may still be data that cannot be cached in volatile memory simultaneously (and in some cases, this may be a significant portion of the data). This data will be accessed from disk storage as needed and loaded into the cache (replacing other data in the cache).

[0005] When a database system needs to perform operations on non-cached data, it must first read the data from disk storage into the database system's volatile memory. Once loaded into volatile memory, the database system can perform operations on the data. However, reading data from disk storage typically results in a significant performance penalty compared to retrieving data already residing in volatile memory. Therefore, when a database system needs to perform operations on non-cached data, it cannot experience significant performance benefits from having a large amount of volatile memory.

[0006] One way to fit more data onto volatile memory is to compress the data before storing it. Once compressed, the data is resized to take up less space in volatile memory. However, not all data can be significantly compressed. Furthermore, if the compressed data is frequently accessed for operations, it will need to be decompressed frequently. This frequent decompression uses computational resources that would otherwise be used for data operations, slowing down data operations and thus slowing down applications that request those operations. Therefore, indiscriminate data compression of data cached in volatile memory has significant drawbacks.

[0007] Furthermore, regardless of the level of compression at which data is copied to volatile memory, at some point, the database system will still exhaust the space in volatile memory to store more data. Therefore, when volatile memory is fully loaded and the database system needs to perform operations on data stored only on disk storage, some data already in volatile memory will need to be replaced to make room for data from disk storage. The more frequent this replacement, the more computational resources are wasted on moving data in and out of volatile memory. Therefore, minimizing the frequency of data replacement will contribute to the high performance of the database system.

[0008] The methods described in this section are feasible, but not necessarily methods that have been previously conceived or implemented. Therefore, unless otherwise stated, no method described in this section should be assumed to qualify as prior art simply by virtue of its inclusion in this section. Attached Figure Description

[0009] In the attached diagram:

[0010] Figure 1 This is a block diagram of a database system that simultaneously maintains mirror-formatted data in volatile memory and persistent-formatted data on persistent storage, according to an embodiment.

[0011] Figure 2A This is a block diagram of the table used as an example;

[0012] Figure 2B This is a block diagram illustrating how, according to an embodiment, data items of a table can be maintained simultaneously in two formats, one of which is a memory format.

[0013] Figure 3A This is a block diagram illustrating a process for selecting candidate data portions for mirroring, according to one or more embodiments.

[0014] Figure 3B This is a block diagram illustrating a process for specifying a portion of mirrored data to be removed from volatile memory, according to one or more embodiments.

[0015] Figure 4 This is a block diagram depicting a process for selecting a compression level for a selected portion, according to one or more embodiments; and

[0016] Figure 5 This is a block diagram illustrating a computer system that can be used to implement the techniques described herein. Detailed Implementation

[0017] In the following description, numerous specific details are set forth for purposes of explanation in order to provide a thorough understanding of the invention. However, it will be apparent that the invention may be practiced without these specific details. In other instances, well-known structures and devices are shown in block diagram form to avoid unnecessarily obscuring the invention.

[0018] General Overview

[0019] Different data formats offer different advantages. Therefore, the techniques described herein are used to persistently maintain data in one format, but make that data available to the database server in more than one format. In one embodiment, one format that makes the data available for query processing is an on-disk format, while another format that makes the data available for query processing is independent of the on-disk format.

[0020] The format corresponding to the disk-based format is referred to herein as a "persistent format" or "PF". Data in the persistent format is referred to herein as PF data. The in-memory format, independent of the disk-based format, is referred to herein as a "mirror format" or "MF". Data in the mirror format is referred to herein as MF data. Further details regarding database systems utilizing MF and PF data are described in U.S. Patent Application 14 / 337,179, filed July 21, 2014, entitled "Mirroring, In Memory, Data From Disk To Improve Query Performance," entitled "Mirroring Data Application," the entire contents of which are incorporated herein by reference.

[0021] According to one embodiment, the mirror format is completely independent of the persistent format. However, the MF data is initially constructed in volatile memory based on persistent PF data rather than on any persistent MF structure. Since a persistent MF structure is not required, users of existing databases do not need to migrate data or structures from their existing databases to another format. Therefore, conventional database systems using uncompressed data in disk blocks can continue to use these disk blocks to persistently store data in the database system without performing any data migration, while still gaining the memory space benefits from having a compressed representation of the data available in faster volatile memory.

[0022] MF data mirrors data that already exists in PF data. However, while all items in MF data are mirror versions of their corresponding items in PF data (albeit organized in different formats), not all items in PF data need to be mirrored in MF data. Therefore, MF data can be a subset of PF data.

[0023] Since not all PF data needs to be mirrored in MF data, selection criteria are used to automatically select the data portions, such as columns, to be mirrored from the PF data into the MF data. In this embodiment, various factors regarding the data portions in the PF data are used to determine which portions of the PF data should be mirrored. For example, if a table has columns A, B, and C, and column A has the most frequent read accesses, then column A can be selected for mirroring from the PF data into the MF data. In this example, queries using column A will see a performance improvement because access to the MF data in volatile memory is faster than access to the PF data in non-volatile memory. In addition to considering access statistics for the data portions (such as in the example above), other factors such as data statistics, operational statistics, and data type of the data portions can also be considered.

[0024] Even when a query may require data that can only be satisfied by PF data, MF data can still be used to (a) satisfy a portion of the query and / or (b) speed up the retrieval of the required data from the PF data. For example, MF data can be used to identify specific rows that must be retrieved from the PF data.

[0025] In embodiments, various factors can be used to determine whether to compress data portions of the MF data and the compression level for those data portions. The compression level indicates how much volatile memory space a data portion occupies and the resources the database system needs to decompress it when retrieving it. For example, some data types may require less computational overhead to decompress compared to others. Therefore, the database system can automatically determine that data portions with this data type should have a higher compression level than other data types. On the other hand, different data portions may be accessed at different frequencies. To conserve database system resources, data portions accessed more frequently can be compressed at a lower level than those accessed less frequently. Besides access statistics and data type, other factors may include historical performance statistics of similar data portions in the MF data.

[0026] In embodiments, MF data can be in column-major or row-major format and can differ from PF data. For example, the PF format is column-major, while the MF format is row-major, and vice versa. Regardless of the specific mirror format used, mirror format data is created in volatile memory based on existing PF structures (e.g., tables and indexes) without causing any changes to the format of these structures.

[0027] General system architecture

[0028] Figure 1 This is a block diagram of a database management system according to one embodiment. (Reference) Figure 1 Database system 100 includes volatile memory 102 and persistent storage device 110. Volatile memory 102 generally refers to random access memory used by database system 100 and can be implemented by any number of memory devices. Typically, data stored in volatile memory 102 is lost in the event of a failure.

[0029] Persistent storage device 110 generally refers to any number of persistent storage devices, such as disks, flash memory, and / or solid-state drives. Unlike volatile memory 102, data stored in persistent storage device 110 is not lost in the event of a failure. Therefore, after a failure, data in persistent storage device 110 can be used to reconstruct data lost in volatile memory 102.

[0030] Database system 100 can receive queries from one or more database applications (not shown) for execution on database server 120. To execute the queries, using query optimizer 126, database system 100 can first transform the queries into an ordered set of operators for accessing PF data 112 or MF data 104 in database server 120. Each operator may contain output data of another operator or one or more data operations on PF data 112 or MF data 104. Query optimizer 126 can generate a permutation of the set of operators, referred to herein as an “execution plan.” In embodiments, to ensure the selection of the optimal execution plan, a statistics collector 128 of query optimizer 126 collects and maintains statistics about database system 100. The term “statistic” herein refers to any numerical representation describing the database management system and the data stored in database system 100. The various types of statistics collected by statistics collector 128 are further discussed in the “Statistics” section.

[0031] PF data 112 resides on persistent storage device 110 within PF data structure 108. PF structure 108 can be a structure of PF data 112 at any organizational level, such as a table, column, row, row-major disk block, column-major disk block, etc.

[0032] The volatile memory 102 also includes a cache 106 for PF data. Within the cache 106, data is stored in a format based on the format in which the data resides within the PF data structure 108. For example, if the persistent format is row-major disk blocks, the cache 106 may contain a cache copy of the row-major disk blocks.

[0033] On the other hand, MF data 104 is in a format independent of the persistent format. For example, in the case of a row-major uncompressed disk block in the persistent format, the mirror format can be a column-major compressed unit. Since the mirror format is different from the persistent format, MF data 104 is generated by performing a transformation on PF data 112.

[0034] The mirror data generator 124 can perform these transformations, which can occur either when the volatile memory 102 is initially filled with MF data 104 (whether at startup or as needed) or when the volatile memory 102 is refilled with MF data 104 after a failure. In an embodiment, the mirror data generator 124 can select data from PF data 112 and mirror that data to generate MF data 104 based on one or more factors described below.

[0035] In this embodiment, the transaction manager 122 maintains the MF data 104 in memory in a transactionally consistent manner with the PF data 112. The MF data 104 is transactionally consistent because any data item provided to a transaction from the MF data 104 will be the same version that would be provided if that data item were provided from the PF data 112. Furthermore, this version reflects all changes committed before the transaction's snapshot time, and there are no changes committed after the transaction's snapshot time. Therefore, when a transaction that makes changes to data items mirrored in the MF data 104 is committed, those changes are visible relative to both the PF data 112 and the MF data 104. On the other hand, if a transaction that makes changes is aborted or rolled back, those changes are rolled back relative to both the PF data 112 and the MF data 104.

[0036] In one embodiment, the transaction manager 122 ensures consistency not only between reading and writing PF data 112, but also between reading and writing MF data 104. Since MF data 104 maintains its current state in a transactionally consistent manner, database operations can be satisfied either from MF data in memory or from PF data 112 if the MF data in memory includes data required for database operations.

[0037] Importantly, the existence of MF data 104 is transparent to database applications that submit database commands to the database server that uses MF data 104. For example, the same applications designed to interact with database systems that operate specifically on PF data 112 can interact with the database server that maintains MF data 104 without modification, except for PF data 112. Furthermore, transparently to those applications, the database server can use MF data 104 to process some or all of those database commands more efficiently.

[0038] Mirror format data

[0039] MF data 104 can mirror all or a subset of PF data 112. Users can specify which portion of PF data 112 is "in-memory enabled." This specification can be made at any level of granularity. For example, the specification of what is in-memory enabled can be made at at least the following levels of granularity:

[0040] • The entire database

[0041] ·Specified table

[0042] ·Specified columns

[0043] • Specified line range

[0044] • The specified partition

[0045] • The specified segment

[0046] • The specified extension

[0047] • Any combination of the above (e.g., specified columns and partitions)

[0048] Any data at any granularity level listed above is referred to herein as a “data portion”. In an embodiment, the mirror data generator 124 may recommend to the user that the data portion of the PF data 112 be enabled in memory, or may automatically recognize the data portion as enabled in memory.

[0049] As described below, data enabled in memory is converted into a mirror format by the mirror data generator 124 and stored as MF data 104 in volatile memory. Therefore, when a query requires data enabled in memory, the database server has the option to obtain data from either PF data 112 or MF data 104. The conversion and loading can occur at database startup, or in a lazy or on-demand manner. Data not enabled in memory is not mirrored in MF data 104. Therefore, when a query requires such data, the database server does not have the option to obtain that data from MF data 104.

[0050] For the purposes of explanation, it will be assumed that PF data structure 108 includes Figure 2A Table 200 is shown in the diagram. Table 200 includes three columns c1-c3 and six rows r1-r6. Although in Figure 2A The illustration in Table 200 depicts how data is logically organized in persistent storage device 110, but the actual format in which the data is physically stored may be completely different.

[0051] Specifically, refer to Figure 2B , Figure 2B The illustration shows how data residing in table 200 can be physically organized in persistent storage device 110. In this example, the data of table 200 is stored in three row-major disk blocks 202, 204, and 206. Block 202 stores the values ​​of all columns for row r1, followed by the values ​​of all columns for row r2. Block 204 stores the values ​​of all columns for row r3, followed by the values ​​of all columns for row r4. Finally, block 206 stores the values ​​of all columns for row r5, followed by the values ​​of all columns for row r6.

[0052] Copies of some of these disk blocks can be temporarily stored in cache 106. Figure 2BIn the illustrated example, cache copy 212 of block 204 resides in cache 106. Cache 106 can be managed using any of a variety of cache management techniques, and the embodiments described herein are not limited to any particular cache management technique. Generally, these techniques attempt to retain copies of disk blocks in volatile memory 102 that are most likely to be requested in the near future. Therefore, when cache 106 runs out of space, copies of more likely-to-be-requested blocks replace cache copies of less likely-to-be-requested disk blocks.

[0053] Unlike the data in cache 106, mirrored data 104 is not formatted in a persistence-based manner. In the illustrated example, mirrored data 104 comprises two column vectors 220 and 222. Each column vector stores a consecutive series of values ​​from a single column of table 200. In this example, column vector 220 stores values ​​from column 1 of table 200, and column vector 222 stores values ​​from column 3 of table 200. In this example, MF data 104 mirrors a subset of PF data 112 because MF data 104 does not include the column vector for column 2 of table 200.

[0054] Database system statistics

[0055] In one embodiment, statistics collector 128 collects various types of statistics about data and data access in database system 100. Statistics collector 128 also collects statistics about the performance of database system 100. In one embodiment, statistics are collected for various types of objects and at different granular levels, such as partitions, columns, tables, views, and even indexes.

[0056] Database system statistics are generally categorized into data statistics, access statistics, and system statistics. Each of these statistical categories will be described in more detail below.

[0057] Data statistics

[0058] The term "data statistics" in this document refers to a numerical representation used to quantify the data distribution and storage characteristics of data stored in a database system. In this embodiment, data statistics are collected for any granularity of data portion.

[0059] Non-restricted examples of data statistics include:

[0060] • Row count - Describes the number of rows in the data section.

[0061] • Number of blocks - describes the number of data blocks in the data section.

[0062] • Average row length - describes the average row length in the data section.

[0063] • Number of distinct values ​​in a column (NDV) - describes the number (cardinality) of unique values ​​in a column within the data section.

[0064] • Number of blank values ​​in the column - describes the number of blank values ​​in the column of the data section.

[0065] • Data distribution statistics – describe the distribution of values ​​in a column of data. Data distribution statistics include the minimum, maximum, average, and median values ​​in the column. In one embodiment, in addition to the median, distribution statistics may also include more complex statistics regarding the frequency of values, referred to herein as histogram statistics. Histogram statistics for a column are generated by arranging the values ​​in the column and then storing these values ​​in buckets based on the values ​​themselves or based on the number of values ​​in each bucket. Based on histogram statistics, hot (frequent) values ​​and non-hot (infrequent) values ​​and value ranges in the column can be identified, and these values ​​and value ranges can be tracked as part of the data distribution statistics.

[0066] • Index Statistics - When the data portion is indexed, index statistics contain information about the index, such as the number of index levels, the number of index blocks, and the relationship between the index and the data blocks.

[0067] Other data statistics may include any combination of the above statistics applied by one or more statistical functions, such as the minimum, maximum, mean, median, and standard deviation of one or more of the above statistics.

[0068] Access statistics

[0069] The term "access statistics" herein refers to a numerical value associated with various types of access (such as read or write (modify)) to data portions in a database system. The term "heat map" in this application refers to a statistical value reflecting the comparative frequency of access to various data portions in database system 100: data portions with recent and more frequent access are "hot," while data portions without recent access and less frequent access are "cold." In embodiments, access statistics for data portions are calculated based on the number of accesses to a data portion and how recent these accesses are in time. Accordingly, more recent accesses to a data portion are given more weight than earlier accesses. Based on the calculated access statistics for data portions, some data portions have "hotter" accesses than others. For example, if data from rows in one data portion has been accessed five times on the last day, while rows in another data portion have only been accessed once, then the first data portion will be "hotter" than the second data portion.

[0070] In this embodiment, to differentiate access levels of the heatmap, different ranges of access statistics are designated as "hot" access and "cold" access. If the access statistics for a data portion fall within the "hot" range, that data portion is represented as hot. Similarly, if the access statistics for a data portion fall within the "cold" range, that data portion is represented as cold.

[0071] In this embodiment, access statistics for a data portion are calculated based on access statistics of multiple data portions that are part of a data portion. The access statistics of the multiple data portions are aggregated to produce the access statistics for the data portion. This aggregation can be based on any one or a combination of aggregation functions such as average, median, minimum, and maximum values. For example, a heatmap of a database includes an aggregation of access statistics for data portions that are part of the database.

[0072] While this paper describes “hot” and “cold” access levels for data portion access statistics, the method described herein envisions other embodiments with other access levels having access statistics. For example, a range of access statistics could be added to represent a “warm” level, meaning that the data portion has more access than a “cold” level but fewer accesses than a “hot” level.

[0073] In this embodiment, separate heatmaps are collected for read access types and write access types. The same data portion can have different access statistics based on the access type. For example, a table containing log entries may have hot write access statistics because applications using the table frequently send commands to database system 100 to add new log entries. However, if the log table is primarily for historical purposes and applications in database system 100 are not actively requesting table data, then the log table may have cold read access statistics.

[0074] In this embodiment, access statistics are also collected for each operation type of the data portion. The term "operation statistics" refers to a numerical representation of a measurement describing the different types of operations performed on the data portion. When a query is processed by query optimizer 126, query optimizer 126 selects a specific execution plan for the query. The specific execution plan includes one or more operations that the database system 100 will perform on the data portions involved in the query. Therefore, query optimizer 126 records information about the types of operations performed on each data portion in the query. Using this information from each query execution, statistics collector 128 calculates the total number of specific types of operations performed on a particular data portion, such as the number of index-based scans on table 200.

[0075] For example, database system 100 uses a table "EventLog" with an integer type column "EventID", a variable-length string type column "RecordText", and a date / time type column "Timestamp" to manage the database. For each of these data portions, statistics collector 128 collects operation statistics. For example, when database system 100 receives the query: "select * from EventLog where EventID = 5", query optimizer 126 can generate different query execution plans to traverse the table, identify the row corresponding to the EventID value of 5, and return that row. In one case, the EventLog table supports index-based traversal using the EventID column as the index for traversal. Therefore, query optimizer 126 generates and selects an execution plan that includes a unique index scan operation on the EventID column to select the row corresponding to the value of 5. In response to this operation, statistics collector 128 increments the operation statistics for the unique index scan operation on the EventID column data portion. In another scenario, if the EventLog table supports a full index scan but is not based on the EventID column, the query optimizer 126 selects an execution plan that includes a full index scan of the EventID column. Accordingly, the statistics collector 128 increases the operation statistics for the full index scan operation on the EventID column. In yet another scenario, the EventLog is not indexed at all for index-based scans, and therefore the query optimizer 126 generates and selects a full scan operation on the EventID column. Therefore, the operation statistics for the full scan of the EventID column will be increased.

[0076] Other non-limiting examples of operations (for which operational statistics are collected) include: join operations, in which columns of a data portion are joined to create another data portion; sort operations, in which the column is sorted based on each value in the column of the data portion; and group operations, in which values ​​in a column are grouped and / or aggregated based on a function.

[0077] In this embodiment, operation statistics are relative and measured in comparison with other types of operations performed on the same data portion. For example, query optimizer 126 maintains a count of all types of operations performed on the EventID column and recalculates the operation statistics for each operation type based on the percentage of all operations performed on that column. Alternatively or additionally, operation statistics are absolute and measured in the absolute number of operations performed on the data portion. For example, as described above, each type of operation on the EventID column results in an increase in the corresponding operation statistics.

[0078] System statistics

[0079] In this paper, the term "system statistics" refers to the numerical representation of various system resource measurements of a database system.

[0080] Unrestricted examples of system statistics include:

[0081] • CPU usage - describes the CPU consumption.

[0082] • CPU speed - CPU speed is described by the average number of CPU cycles per second.

[0083] • I / O (Input / Output) Lookup Time - Describes the time required to locate data read from persistent storage.

[0084] • I / O transfer rate - describes the rate at which a database system reads data from persistent storage in a single read request.

[0085] • Maximum I / O throughput - describes the maximum data rate at which a database system reads and writes data to persistent storage.

[0086] • Parallel I / O throughput - describes the average rate at which a database system reads and writes data to persistent storage in parallel.

[0087] • Single block read time - describes the average time to randomly read a single block from persistent storage.

[0088] • Multi-block read time - describes the average time to read multiple blocks sequentially from persistent storage.

[0089] • Multi-block read count - describes the average number of sequential blocks in a multi-block read.

[0090] • Any combination of the above examples also includes the application of one or more statistical functions based on the minimum, maximum, mean, median, standard deviation, or other statistical values ​​mentioned above.

[0091] The term "performance statistic" herein refers to a numerical representation of the resource consumption of a database system when performing operations on data portions loaded into volatile memory. Database system 100 uses computational resources such as CPU, memory, and I / O to perform operations on data portions of MF data. The database system uses the aforementioned system statistics to measure this resource consumption for the operations. In an embodiment, performance statistics are collected for each operation type for one or more data portions of the MF data. The performance statistics are then associated with the data portion for which the performance statistics were collected and the operation type. The performance statistics are also associated with the compression level at which the data portion is compressed into volatile memory 102 when the operation is performed (for which the performance statistics are collected). Other information is also associated with the performance statistics, such as the data statistics for the data portion and the data type (for which the performance statistics are collected).

[0092] As a non-limiting example, the statistics collector 128 collects CPU usage performance statistics for sorting operations on columns stored in volatile memory 102. When the database system 100 performs a sorting operation on a column, the statistics collector 128 records a measurement of the CPU usage for that operation and averages the collected measurement with the average CPU usage already present for sorting operations on that column. If the column is recompressed at different levels in volatile memory 102, the statistics collector 128 also creates new instances of CPU usage performance statistics for that column. Thus, in this case, individual performance statistics will be associated with different compression levels of the same column. Other examples of performance statistics are based on I / O lookup time, I / O transfer rate, or block read time measured for each specific type of scan operation (such as a full table scan or an index scan) on a portion of the data.

[0093] Triggers for collecting statistical values

[0094] Statistics can be collected in many different ways. For example, depending on the implementation, statistics may be collected during the compilation phase of query processing, during query execution, and / or when no query is being processed. In one embodiment, some statistics collection is triggered by database changes: for example, changes to database values ​​(new entries, rows, columns, tables) or changes to system resources (additional processing power or memory) trigger statistics collection.

[0095] One method for collecting access statistics is to log information about accesses to data portions as they are accessed. When a query is executed and retrieves or modifies data in a data portion, the execution also results in records of usage information being associated with that data portion. Usage information may include access timestamps and the type of access / operation. To collect access statistics in database system 100, statistics collector 128 then reads the access information about the data portions and generates heatmaps or operation statistics based on that information. Alternatively, access statistics can be updated during the query compilation phase. For example, before executing a query, query optimizer 126 determines the types of accesses and operations in the query, as well as the data portions that the operations will access. Therefore, query optimizer 126 can directly update the access statistics for the data portions affected by query execution.

[0096] Choose which PF data to mirror

[0097] In this embodiment, the decision of which PF data to mirror and when to load this PF data is based on various factors. For example, if the system has a large amount of volatile memory 102 and a relatively small database, the mirror data generator 124 can mirror the entire database. In such an example, all PF data will also be mirrored in MF data 104. On the other hand, if there is a relatively small amount of volatile memory 102 relative to the size of the database, it is optimal to mirror only a small portion of the database. Therefore, in this embodiment, the amount of available space in volatile memory 102 is a factor in selecting the data portion to be mirrored from the PF data 112.

[0098] In another embodiment, other factors used to determine whether to mirror a data portion of PF data 112 to MF data 104 in volatile memory 102 include the data type and statistics associated with the data portion. The term "candidate portion" refers to a data portion of PF data 112 for which statistics and data type are evaluated to determine whether the data portion should be mirrored. In embodiments, a candidate portion is a data portion enabled in memory or a sub-portion of a data portion enabled in memory. Sub-portions of a data portion can be specified at any granularity level described above for a data portion. According to such an embodiment, if a table is selected or automatically identified as a data portion enabled in memory, then sub-portions of columns or rows constituting that table are also data portions enabled in memory.

[0099] In an embodiment, candidate portions are evaluated to generate “qualification data” for each candidate portion based on factors. The term “qualification data” refers to any information used to evaluate whether a candidate portion should be mirrored in volatile memory and reflects the estimated benefit of mirroring a candidate data portion in volatile memory. In the example, qualification data is a numerical score from 0 to 100, where the score increases for mirroring when the factor is evaluated positively and decreases when the factor is evaluated negatively. If a factor is evaluated as favorable to mirroring a candidate portion, the qualification data for that candidate portion is said to be “positively modified.” On the other hand, if a factor is evaluated as unfavorable to mirroring a candidate portion, the qualification data is negatively modified. Accordingly, based on the modified qualification data, the candidate portion that will maximize the improvement in the overall performance of the system given the available storage capacity is selected for mirroring. The candidate portion selected for mirroring is referred to herein as a “selected portion.” The qualification data for the selected portion can change based on changes in factors. In an embodiment, database system 100 determines to remove one or more selected portions from volatile memory 102 based on the changed qualification data.

[0100] Figure 3A This is a block diagram depicting a process for selecting candidate data portions for mirroring, according to one or more embodiments. One or more blocks in the following description may be omitted, repeated, and / or executed in a different order. Therefore, Figure 3A The specific arrangement of the boxes shown should not be interpreted as limiting the scope of the method.

[0101] At box 305, candidate portions to be evaluated for mirroring are selected from PF data 112. Initial qualification data is generated for the candidate portions. The initial qualification data may be neither favorable nor unfavorable to mirroring the candidate portions, or it may be based on qualification data previously evaluated for the candidate portions. At box 310, access statistics for the candidate portions are evaluated. If the access statistics indicate that the candidate portion has a hot heatmap of read access, the candidate portion is frequently accessed for reading. Therefore, mirroring the candidate portions in volatile memory 102 will improve the performance of database system 100, and the qualification data for the candidate portions is positively modified to reflect this. On the other hand, if the candidate portion has a cold heatmap of read access, the qualification data is negatively modified.

[0102] Alternatively or additionally, the write access heatmap of the candidate portion is also evaluated at block 310. If the candidate portion has a hot write access heatmap, the candidate portion is written to frequently and therefore, once mirrored, it will have to be re-mirrored frequently to avoid becoming obsolete in volatile memory 102, thus consuming database system 100 resources. Therefore, in this case, the eligibility data of the candidate portion is negatively modified. On the other hand, if the candidate portion has a cold write access heatmap, the eligibility data is positively modified to reflect that once mirrored, the candidate portion is less likely to become obsolete and less likely to waste database system 100 resources on re-mirroring.

[0103] For example, the previously described EventLog table and EventID column are represented as data portions enabled in memory, and the EventLog table and EventID column are evaluated based on a score of 0 to 100 for the corresponding qualifying data for loading into volatile memory. Initially, both the EventLog table and EventID column are assigned a default score of 50, and the database system 100 is configured to favor mirroring for any data portion having a qualifying data score greater than 75.

[0104] Access statistics for the EventLog table and EventID column are retrieved from statistics collector 128. The retrieved access statistics indicate that both data portions have hot read access. Accordingly, the qualifying data scores for the EventID and EventLog data portions increase by 10 to a value of 60. However, unlike the access statistics for the EventID column, the access statistics for the EventLog table indicate hot write access. Therefore, the score for EventLog decreases by 10, returning to a value of 50, while the score for EventID remains unchanged.

[0105] At box 315, the process evaluates the data type of the candidate portion. The data type of the candidate portion can affect the degree to which it is compressed in volatile memory 102, and therefore can affect the memory space occupied by the candidate portion when it is mirrored into volatile memory 102 (as discussed further below). If the data type of the candidate portion is well compressed, it occupies less memory space in volatile memory 102, and thus the eligibility data of the candidate portion is positively modified. On the other hand, if the data type is poorly compressed, the candidate portion will occupy a large amount of memory space if mirrored. Therefore, the eligibility data of candidate portions with this data type is negatively modified. For example, large object data types (LOBs) (such as binary large object types (BLOBs) or character large object types (CLOBs)) are less compressible and have very high data size limitations. Therefore, even when compressed, LOB data type data will occupy a large amount of memory space. Therefore, if the candidate portion contains LOB data types, the eligibility data is negatively modified. Similarly, variable-length string data types can also consume significant amounts of memory due to their large size limitations. Therefore, while variable-length string data types compress better than LOB data, candidate parts with this data type may be negatively evaluated. Conversely, date and time-related data types typically have fixed size limitations and are generally well-compressed. Therefore, if a candidate part is a date and time-related data type, the qualifying data for that candidate part is positively modified. Similarly, other data types can be evaluated against the qualifying data.

[0106] Continuing with the evaluation example for the EventLog table and EventID column, we evaluate the data types of the data portion. For the EventLog table, all columns (including the EventID column) are evaluated to modify the EventLog table's eligibility data, while for the EventID column, the data type is evaluated to modify the EventID eligibility data. Since the EventID column is an integer type that does not affect the compression level, the evaluation does not modify the EventID eligibility data score. In addition to the EventID column, the EventLog table also contains a date / time type column (Timestamp) and a string type column (Record). Accordingly, since date / time and string type columns compress well, while integer types do not affect compression, the eligibility data scores for these columns in the EventLog table are averaged. The average score is calculated to be 6.66 ((10+10+0) / 3), and the EventLog eligibility data score increases accordingly to 66.66.

[0107] At box 320, operational statistics are evaluated for candidate portions. If a candidate portion is accessed in a way that mirroring it would improve access speed, the eligibility data for that portion is positively modified. For example, if the most common operations on a candidate data portion are I / O-intensive, such as full table scans and sorting operations, based on operational statistics, mirroring the candidate portion significantly improves performance, and the eligibility data for that candidate data is positively modified. On the other hand, if common operations on a candidate portion require loading other data portions from the PF data, mirroring the candidate portion may not significantly improve the performance of such operations, and the eligibility data is negatively modified. For example, join or grouping operations are often performed on more than one data portion, so this processing is disadvantageous to candidate portions with these operations, and the eligibility data for those candidate portions is negatively evaluated.

[0108] Continuing with the evaluation example of the EventLog table and EventID column, operation statistics for the EventLog table and EventID column are retrieved from Statistical Value Collector 128 for further evaluation of the eligibility data. In this example, based on the operation statistics of the EventLog, the most frequent operation in the EventLog is sorting. Therefore, the eligibility data score for the EventLog further increases from a value of 66.66 to a value of 76.66.

[0109] On the other hand, in this example, the most frequent operation on the EventID column is a join operation. Because join operations may require loading another portion of data into volatile memory for evaluation, the qualifying data score for the EventID column decreases from a value of 60 to a value of 50.

[0110] At box 325, the data statistics of the candidate portion are evaluated as another factor for mirroring. If the candidate portion has a large amount of data that will occupy a significant amount of memory space in volatile memory 102, the eligibility data is negatively modified. On the other hand, if the candidate portion has less data or data that can be well compressed, the eligibility data is positively modified. For example, the process evaluates the statistics of the number of rows, the number of blocks, and the average row length to determine the size of the data. The number of distinct values ​​and the number of null values ​​in the columns are evaluated to determine the degree to which the candidate portion will be compressed if it is mirrored. If the number of distinct values ​​is low, the candidate portion will be better compressed, and therefore the eligibility data will be positively modified. If the number of null values ​​is high, the candidate portion will also be better compressed, and therefore the eligibility data will also be positively modified. Similarly, the data distribution statistics of the candidate portion are evaluated to determine whether the candidate portion will be well compressed. If the candidate portion has many common values / ranges of values, the candidate portion will be well compressed, and therefore the eligibility data will be positively modified. Conversely, if the candidate part has many uncommon values / ranges of values, the candidate part will be poorly compressed, and the eligibility data will therefore be negatively modified.

[0111] Continuing with the evaluation example of the EventLog table and EventID column, data statistics for the EventLog and EventID columns are retrieved from the statistics collector 128 for further evaluation of the eligibility data. The data statistics for the EventLog table and EventID column indicate the row count, which is approximately equal to the average row count of other data portions managed by the database system 100 for which the statistics collector 128 is collecting data statistics. However, because the EventID column contains a unique integer for each row, it has a high cardinality, while the other columns of the EventLog have very low cardinality. Accordingly, the eligibility data score for the EventID column is reduced by 10, while the eligibility data for the EventLog table is modified based on the average score evaluated using the data statistics for each column. The data statistics for the Timestamp and Record columns are favorable for mirroring, while the data statistics for the EventID column are unfavorable for mirroring. Therefore, the eligibility data score for the EventLog table can be increased by: (10 + 10 - 10) / 3 = 3.33, to have a value of 80.

[0112] At box 327, the eligibility data for the candidate portion is stored in association with the candidate portion. At box 330, if the eligibility data for the candidate portion is favorable to mirroring the candidate portion, the process proceeds to box 335 to evaluate the system statistics of database system 100. Otherwise, if the eligibility data for the candidate portion is unfavorable to mirroring the candidate portion, the process then proceeds to box 305, where the next candidate portion is selected for evaluating the factors in question.

[0113] Continuing with the evaluation example of the EventLog table and EventID column, the qualifying data score for the EventLog table is evaluated as 80, while the qualifying data score for the EventID column is evaluated as 40. The EventLog qualifying data score exceeds 75, and therefore the EventLog table is favorable for mirroring to volatile memory 102, while the EventID column is unfavorable for mirroring to volatile memory 102.

[0114] At block 335, system statistics for database system 100 are evaluated to determine whether the system is capable of mirroring candidate portions to and accessing them from volatile memory 102. While accessing MF data is faster, additional system resources may be required due to the additional decompression step of MF data, as discussed further below. If the data portion becomes obsolete, the system further expends resources to compress the data portion. Accordingly, at block 335, based on system statistics, this process determines whether database system 100 has the additional resources for accessing candidate portions from volatile memory 102. For example, if CPU usage is low and CPU speed is sufficient to access candidate data portions from volatile memory 102, the candidate portion is selected for mirroring and access from volatile memory 102. Conversely, if CPU usage is high and / or CPU speed is insufficient to access candidate data portions from volatile memory, the system statistics will be re-evaluated at a later point in time. Similarly, in this embodiment, the process evaluates system I / O and read time-related statistics to determine if disk access speed is insufficient and therefore more PF data needs to be mirrored to accelerate data access. For example, if the disk's maximum I / O throughput and / or parallel throughput is low and various disk read and lookup times are long, mirroring more PF data will improve system performance because data will be accessed from volatile memory 102 rather than persistent storage 110. However, if statistics such as maximum I / O throughput are high and multi-block / single-block times are low, the database system 100 is serving data from persistent storage 110 at a sufficient rate, and therefore mirroring is not necessary and is postponed to avoid consuming system resources. If the system statistics evaluated at block 335 are favorable for mirroring, then at block 340, the process proceeds to block 345 to determine the available space in volatile memory 102.

[0115] At block 345, the space in volatile memory 102 is evaluated to determine whether a candidate portion can be stored in the available space in volatile memory 102. If at block 345 the process determines that there is no available memory space for a candidate portion, the process continues to select the next candidate portion at block 305. Otherwise, the process continues to block 350 to specify the candidate portion for mirroring in volatile memory 102.

[0116] In a related embodiment, if at block 345 the process determines that there is no available memory space in volatile memory 102 for a candidate portion, the process determines whether any data portion already in volatile memory 102 can be removed to make memory space available for a candidate portion. If the qualifying data of the data portion has become unfavorable for mirroring or the qualifying data of the data portion is smaller than the qualifying data of the candidate portion, then the data portion is removed from volatile memory 102. Figure 3B The flowchart for removing the data section is further described below.

[0117] In an embodiment, the data portion includes sub-portions of data, which are evaluated differently based on the eligibility factors discussed for mirroring. Candidate portions may be evaluated as to be mirrored to volatile memory 102, while sub-portions of the candidate portions may be evaluated as not to be mirrored to volatile memory 102 based on eligibility data for the sub-portions. According to one embodiment, the candidate portions are modified to exclude sub-portions evaluated as not to be mirrored. For example, if a table has been evaluated as to be mirrored based on the factors discussed above, and a specific column of the table has been evaluated as not to be mirrored, then the table is mirrored without the specific column; that is, all columns of the table except the specific column are mirrored.

[0118] Continuing with the example of the EventLog table and the EventID column, the EventLog table can be mirrored into volatile memory 102 without the EventID column. Since the EventID column has been evaluated as unfavorable for mirroring, while the EventLog table (of which the EventID column is a sub-part) has been evaluated as favorable for mirroring, the EventLog table can be mirrored without the EventID column.

[0119] According to other embodiments, sub-parts of the candidate portion are evaluated as to be mirrored, while the candidate portion itself is evaluated as not to be mirrored. In such an embodiment, sub-parts of the candidate portion are mirrored to volatile memory 102, while other portions of the candidate portion are not mirrored.

[0120] In this embodiment, the following is executed periodically on the database system 100: Figure 3A The described process can be performed at a time specified by the user of database system 100 or at a time determined by database system 100 based on system statistics. Alternatively, the process can be performed based on a trigger that can be specified by the user. The trigger may include the availability of space in volatile memory 102 or any of the aforementioned specified statistics of database system 100 crossing one or more thresholds.

[0121] Figure 3B This is a block diagram depicting a process for specifying the removal of a portion of mirrored data from MF data, according to one or more embodiments. One or more blocks in the following description may be omitted, repeated, and / or executed in a different order. Therefore, Figure 3B The specific arrangement of the boxes shown should not be interpreted as limiting the scope of the method.

[0122] At box 365, the mirror portion to be evaluated for removal from volatile memory 102 is selected from MF data 104. Initial qualification data is generated for this mirror data portion. The initial qualification data may be neither favorable nor unfavorable to the removal of the mirror data portion, or it may be based on qualification data previously evaluated for the mirror data portion. At box 370, similar to... Figure 3A The evaluation described in box 310 assesses the access statistics for the mirrored data portion. Similar to... Figure 3A The qualification data described in box 310 has been modified, and the qualification data in the mirrored data section has also been modified.

[0123] At box 375, similar to... Figure 3A The evaluation described in box 315 assesses the data type of the mirrored data portion. Similar to... Figure 3A The qualification data described in box 315 has been modified, and the qualification data in the mirrored data section has also been modified.

[0124] At position 380, similar to... Figure 3A The evaluation described in box 320 assesses the operational statistics of the mirrored data portion. Similar to... Figure 3A The qualification data described in box 320 has been modified, and the qualification data in the mirrored data section has also been modified.

[0125] At box 385, similar to... Figure 3A The evaluation described in box 325 assesses the statistical values ​​of the mirrored data portion. Similar to... Figure 3A The qualification data described in box 325 has been modified, and the qualification data in the mirrored data section has also been modified.

[0126] At block 387, qualification data for the mirrored data portion is stored in association with the mirrored data portion. At block 390, if the qualification data for the mirrored data portion is favorable for removal from volatile memory 102, then at block 395 the mirrored data portion is designated for removal. In one embodiment, the qualification data for the mirrored data portion is compared with threshold qualification data to determine whether the qualification data for the mirrored data portion is favorable for removal from volatile memory 102. In another embodiment, the qualification data for the mirrored data portion is compared with one or more selected portion qualification data. If the qualification data for the mirrored data portion is less favorable for mirroring than the selected portion qualification data, then the mirrored data portion is designated for removal from volatile memory 102. On the other hand, if the qualification data for the mirrored data portion is more favorable for mirroring than the selected portion qualification data, then the mirrored data portion is retained in volatile memory 102 as part of MF data 104.

[0127] In an embodiment, the data portion includes sub-portions of data, which are evaluated differently based on the eligibility factors discussed for removal from volatile memory. Candidate portions may be evaluated as to be removed from volatile memory 102, while sub-portions of the candidate portions may be evaluated as to be retained in volatile memory 102 based on the eligibility data of the sub-portions. According to one embodiment, the mirror portion is modified to exclude sub-portions evaluated as to be retained in volatile memory 102. For example, if a table in volatile memory 102 has been evaluated as to be removed based on the factors discussed above, and a specific column of that table has been evaluated as to be retained in volatile memory 102, then the table is removed without the specific column; that is, all columns of the table except the specific column are removed from volatile memory 102.

[0128] According to other embodiments, a sub-part of the candidate portion is evaluated to be removed from volatile memory 102, while the candidate portion itself is evaluated to be retained in volatile memory 102. In such an embodiment, a sub-part of the candidate is removed from volatile memory 102, while other portions of the candidate portion are retained in volatile memory.

[0129] In this embodiment, the following is executed periodically on the database system 100: Figure 3A The described process can be performed at a time specified by the user of database system 100 or at a time determined by database system 100 based on system statistics. Alternatively, the process can be performed based on a trigger that can be specified by the user. The trigger may include the availability of space in volatile memory 102 or any of the aforementioned specified statistics of database system 100 crossing one or more thresholds.

[0130] Organization of MF data

[0131] According to one embodiment, as part of MF data 104, a selected portion is formatted differently from a copy of the selected portion in PF data 112. Even if MF data 104 uses a different format than PF data 112, MF data 104 is organized in a manner corresponding to the organization of PF data 112. For example, in persistent storage device 110, PF data 112 can be stored in blocks residing in an extension area, where the extension area is further organized into segments. In these cases, within volatile memory 102, MF data 104 can be organized based on the extension area and / or segment to which MF data 104 belongs. Therefore, column vector 220 can be divided into vector portions, where each vector portion corresponds to a specific range of extension area and / or segment.

[0132] Within the extended area, data is typically sorted by rowid (row identifier). Similarly, in one embodiment, MF data 104 is sorted based on rowid. For example, the values ​​in column vector 220 are sorted based on the same rowid used to sort the PF data 112 in blocks 202, 204, and 206. Specifically, rowid r1 precedes rowid r2, so in column vector 220 r1c1 precedes r2c1, and in block 202 r1c1 through r1c3 precede r2c1 through r2c3.

[0133] In an alternative embodiment, some or all of the data items in MF data 104 are not sorted by rowid within MF data 104. Storing data items in a different order can be useful, for example, if a different sorting produces much better compression. As another example, the column vector can be initially sorted by rowid. However, when new updates are "incorporated" into the column vector (as will be discussed in more detail below), the updated values ​​can be appended to the end of the existing column vector to avoid the need to decompress and recompress the existing column vector.

[0134] When data items within a column vector are not sorted by rowid, an in-memory index can be created on the rowid to quickly locate the data item associated with any given rowid within MF data 104. Regardless of whether data items within a column vector are sorted based on rowid, a rowid-to-item mapping can be established by maintaining a vector of rowids associated with the column vector. Other embodiments of the organization of MF data 104 are described in the context of mirrored data applications.

[0135] When database system 100 receives a query to be executed, both MF data 104 and PF data 112 can be used to satisfy the query. In embodiments where the organization of MF data 104 corresponds to the organization of PF data 112, it is easier for the database server to separate database operations between MF data 104 and PF data 112. For example, the database server may determine that MF data 104 should be used to satisfy a query relative to one range of extended regions (e.g., extended regions 1 to 10), while PF data should be used to satisfy a query relative to another range of extended regions (e.g., extended regions 11 to 20). Other embodiments of using MF data 104 to satisfy queries are further described in mirrored data applications.

[0136] compression

[0137] As described above, MF data 104 can be compressed. However, according to one embodiment, not all MF data needs to be compressed in the same way or to the same degree. For example, if it is determined that data from column c1 of table 200 is frequently used and data from column c3 is not frequently used, then data in column vector 220 can be slightly compressed or uncompressed, while data in column vector 222 can be highly compressed.

[0138] The compression algorithm and the compression level used by the algorithm to compress each part of the MF data 104 can be specified by the user or determined automatically by the database server based on various factors. Possible compression algorithms include, but are not limited to, dictionary-based compression, run-length encoding (RLE), Ozip compression, etc. Ozip compression is described in U.S. Patent Application 14 / 337 / 113, filed July 21, 2014, entitled "OZIP Compression And Decompression," the contents of which are incorporated herein by reference.

[0139] Factors used by the database server to determine how each portion of the MF data 104 is compressed may include, for example, the frequency with which each portion is accessed, how much data is in that portion, and how much volatile memory is available. Generally, the more frequently a portion of the MF data 104 is accessed, the less data is compressed. As another general rule, the less volatile memory is available to store the MF data 104 and / or the larger the size of that portion of the MF data 104, the more compression is applied.

[0140] Figure 4This is a block diagram depicting a process for selecting a compression level for a selected portion according to one or more embodiments. One or more blocks in the following description may be omitted, repeated, and / or executed in a different order. Therefore, Figure 4 The specific arrangement of the boxes shown should not be interpreted as limiting the scope of the method.

[0141] At block 410, in this embodiment, access statistics for a selected portion are evaluated. If the access statistics indicate that the selected portion has a hot heatmap of read accesses, then the selected portion is frequently accessed from volatile memory 102. To improve the performance of database system 100, the compression level for the selected portion is reduced, so that database system 100 will spend fewer resources decompressing the selected portion on each access. On the other hand, if the candidate portion has a cold heatmap of read accesses, the compression level is increased to save space in volatile memory 102.

[0142] At block 415, in this embodiment, the process evaluates the data type of the candidate portions. The data type of the selected portions may affect how well the selected portions can be compressed in volatile memory 102. Some data types are better compressed than others, and therefore, data portions of this data type occupy less memory for the same amount of information. Therefore, by evaluating the data type of the selected portions, the compression level can be modified to be optimal for the data type of the selected portions. For example, large object data types (LOBs) (such as binary large object types (BLOBs) or character large object types (CLOBs)) are less compressible, and therefore the compression level of selected portions with LOB data types is reduced. Conversely, date and time-related data types are generally well compressed. Therefore, if the selected portions are date and time-related data types, the compression level of the selected portions is increased. Other data types can be similarly evaluated to modify the compression level of the selected portions.

[0143] At block 420, in this embodiment, operation statistics are evaluated for a selected portion. If most operations on the selected portion based on the operation statistics are computationally intensive, compressing the selected portion at a higher compression level may degrade the performance of database system 100. Decompressing the selected portion from a higher compression level will further increase the resource burden on database system 100, and resource-intensive operations can be expected to further increase the resource burden on database system 100. On the other hand, if a lower compression level is used for the selected portion, the performance of database system 100 is less affected. Accordingly, if the operation statistics at block 420 indicate resource-intensive operations for the selected portion, the compression level for the selected portion is modified to a lower value. Operation statistics indicating that most operations on the selected portion are non-resource-intensive may lead to an increase in the compression level. For example, if the most common operations based on the operation statistics of the selected portion are resource-intensive grouping or sorting operations, the compression level is modified to a lower value. However, if, based on the operation statistics, most operations on the selected portion are index-based scans that are not CPU-intensive compared to a full scan, the compression level is increased.

[0144] At block 425, in an embodiment, performance statistics of one or more data portions similar to the selected portion are evaluated. Similar data portions are those data portions that database system 100 considers similar to the selected portion based on the similarity in data type and / or data statistics between those data portions and the selected portion. For example, if the selected portion is a datetime data type, and database system 100 includes performance statistics of other data portions of the datetime data type, then the performance statistics of those other similar data portions are selected for evaluation. Similarly, if other data portions have a similar number of null values, the performance statistics of other similar data portions can be evaluated to determine the compression level of the selected portion. In various embodiments, similarity can vary. In some embodiments, data type similarity is established if the data types match, while in other embodiments, similarity is established if the data types share a considerable amount of commonality (e.g., char data types of different lengths (including varchar) are considered similar). Furthermore, in some embodiments, numerical percentages are used to determine similarity. For example, different data portions are considered similar if the data statistics of different data portions match within 10% of the values ​​of the respective data statistics.

[0145] The performance statistics of similar data portions are then evaluated to determine the historical performance of the operation at various compression levels. In an embodiment, the evaluation yields an optimal compression level if the performance statistics are available for different compression levels. The optimal compression level can be obtained by comparing the performance statistics of similar data portions at different compression levels. Once the optimal compression level is determined, the compression level of the selected portion is increased, remains unchanged, or decreased to more closely or precisely match the optimal compression level. For example, suppose the selected portion has a similar number of null values ​​to two data portions with associated performance statistics in database system 100. These two data portions have compression levels three (3) and nine (9), respectively. Based on the performance statistics, if the additional resources consumed by the first similar data portion with compression level 9 compared to the second similar data portion with compression level 3 are negligible, then database system 100 selects compression level 9 as the optimal compression level. The compression level of the selected portion is then increased, remains unchanged, or decreases to closely or precisely match the optimal compression level 9.

[0146] In other embodiments, performance statistics of the similar data portion are compared to a predetermined threshold within the performance statistics. If the performance statistics do not meet the threshold, the compression level of the selected portion is adjusted relative to the compression level of the similar data portion in a way that adjusts the resource consumption of the selected portion to more closely meet the threshold. For example, the threshold may represent the maximum CPU usage consumed by the operation type. If the CPU usage statistics of the performance statistics of the similar data portion are lower than the CPU usage threshold, the compression level of the selected portion is adjusted to be higher than the compression level of the similar data portion or remains unchanged. Conversely, if the CPU usage statistics are higher than the maximum CPU usage threshold, the compression level of the selected portion is adjusted to be lower than the compression level of the similar data portion to consume fewer resources in the volatile memory 102.

[0147] At block 430, once the compression level of the selected portion is determined based on the evaluation in the previous blocks, the selected portion is compressed at the determined compression level. The compressed selected portion is then loaded into volatile memory 102. In various embodiments, the decision regarding when to create MF data 104 is based on various other factors. For example, if sufficient time is available at system startup, the compressed selected portion is preloaded into volatile memory 102 at startup. In other embodiments, as further described in mirrored data applications, the selected compressed portion is loaded on demand.

[0148] In the embodiments, the selection can be repeated for any number of parts. Figure 4 Boxes 410-430 determine the appropriate compression level and load the selected portion into volatile memory 102.

[0149] In some embodiments, execution is performed periodically on the database system 100. Figure 4 The described process can be performed at a time specified by the user of database system 100 or at a time determined by database system 100 based on its system statistics. Alternatively, the process can be performed based on a trigger that can be specified by the user. The trigger may include the availability of space in volatile memory 102 or any of the aforementioned specified statistics of database system 100 crossing one or more thresholds.

[0150] Repeat the selected portion of MF data 104 Figure 4 The processing may cause a change in the compression level of a selected portion that has been loaded into volatile memory 102. If the processing determines that the selected portion of the MF data 104 from volatile memory 102 needs to have a different compression level than the current compression level, the selected portion is recompressed at the new compression level and then loaded back into volatile memory 102.

[0151] Even if data items can be compressed within MF data 104, it may not be necessary to decompress MF data 104 to use the MF data. For example, as described in U.S. Patent Application 13 / 708,054, filed December 7, 2012, the entire contents of which are incorporated herein by reference. As also described in that application, decompression may be performed on-chip after the compressed column vector values ​​have been transmitted to the CPU.

[0152] In some embodiments where MF data 104 is compressed, the MF data 104 is organized into “compression units in memory” (IMCUs) within volatile memory 102. Each IMCU stores a different set of MF data, which may or may not correspond to a selected portion. The data-to-IMCU mapping indicates which selected portion is included in each IMCU. In one embodiment, the data-to-IMCU mapping may be part of the metadata of the MF data. IMCUs are further described in mirrored data applications.

[0153] To determine whether MF data 104 contains the data required to process the query, and if so, to locate the MF data 104 required for query processing, the database server needs to know which PF data is mirrored in MF data 104, and specifically, which particular PF data is mirrored for each IMCU. Therefore, according to one embodiment, metadata of MF data 104 is maintained in volatile memory 102, and this metadata includes the mapping of data to IMCUs. The metadata of MF data 104 is further described in the context of mirrored data applications.

[0154] In some embodiments, mechanisms are provided for keeping mirrored data 104 synchronized with PF data 112 when updates, inserts, and deletes are performed on PF data 112. The mechanism for keeping MF data 104 synchronized with PF data 112 is further described in mirrored data applications.

[0155] To reduce the amount of decompression operations and decompression required to maintain synchronization with MF data 104, one embodiment utilizes a log to implicitly update MF data 104. The log is further described in the mirrored data application.

[0156] Because MF data 104 is merely a mirror image of some data in PF data 112 (albeit in a different format), all data items contained in MF data 104 are also in PF data 112. Therefore, for any query requiring access to data items mirrored in MF data 104, the database server has the option to obtain the data from MF data 104, from PF data 112, or a combination of both. Various embodiments for determining where to obtain data in response to a query are described in the context of mirrored data applications.

[0157] Loading and clearing MF data

[0158] Before serving queries based on MF data 104, MF data 104 can be loaded into volatile memory 102 based on one or more "load" events in database system 100. In one embodiment, MF data 104 is preloaded into volatile memory when the database system starts. For example, preloading can be performed by a background process before any database operations are performed on an in-memory enabled data structure containing data items that will be mirrored by MF data 104.

[0159] MF data 104 can be created into one IMCU at a time. In a multi-instance environment, persistent storage metadata can be used to determine which MF data is preloaded into which database instance. Such metadata can include, for example, MF-data-to-IMCU mappings and IMCU-to-instance mappings.

[0160] Instead of simply preloading the MF data 104, some or all of the MF data 104 can be generated when the corresponding PF data is accessed by a database operation. For example, suppose database instance 1 is assigned to host the column vectors of columns c1 and c2 of table 200. Instead of building and loading these column vectors at startup, database instance 1 can initially not generate the MF data. Instead, database instance 1 can wait until a database command requires a scan of table 200. Because the MF data has not yet been created, the scan is performed entirely based on the PF data 112. During this scan, the values ​​needed to build the column vectors of c1 and c2 will be accessed. Therefore, the column vectors of c1 and c2 can be built at this time without incurring any additional disk access.

[0161] On-demand loading of MF data can be used in conjunction with preloading. For example, some of the MF data 104 to be hosted on instance 1 can be created when instance 1 starts. Other parts of MF data 104 can be built when the data is accessed by a query.

[0162] In one embodiment, a user can configure options to indicate which MF data to preload and which to load on demand. In an alternative embodiment, the database server automatically determines which portions of the MF data 104 are preloaded and which are loaded on demand. Generally, the more frequently a data item is used, the more likely the database server will automatically preload the data item into the MF data, so that even the first database operation requiring the data item has the option to obtain the data from the MF data 104.

[0163] Ultimately, the MF data 104 is cleared from volatile memory 102 when database system 100 loses power. However, according to one or more embodiments, the data portion of the MF data 104 designated for removal is removed from volatile memory 102 in one or more "clear" events other than a power outage. For example, database system 100 may have a scheduled task to periodically remove designated data portions from volatile memory 102. In another embodiment, the actual designation of the data portion for removal may result in a clear event that removes that data portion. In yet another embodiment, a clear event is triggered when the available memory space (or any other system statistic) of volatile memory 102 crosses a threshold. For example, if volatile memory 102 has only 5% free memory, a clear event is triggered to remove the data portion designated for removal from volatile memory 102.

[0164] Database Management System

[0165] A Database Management System (DBMS) manages a database. A DBMS may include one or more database servers. A database consists of database data and a database dictionary stored on a persistent storage mechanism (such as a set of hard disks). Database data can be stored in one or more data containers. Each container contains records. The data within each record is organized into one or more fields. In a relational DBMS, data containers are called tables, records are called rows, and fields are called columns. In an object-oriented database, data containers are called object classes, records are called objects, and fields are called attributes. Other database architectures may use different terminology.

[0166] In this embodiment, the DBMS may connect to or include a cluster of nodes capable of storing one or more tables. The DBMS may manage tables stored on the node cluster in a manner similar to managing tables stored in persistent storage.

[0167] Users interact with the DBMS database server by submitting commands to the database server, causing the database server to perform operations on the data stored in the database. A user can be one or more applications running on a client computer that interact with the database server. In this document, multiple users may also be collectively referred to as users.

[0168] As used herein, a "query" refers to a database command and can be in the form of a database statement conforming to a database language. In one embodiment, the database language used to express a query is Structured Query Language (SQL). Many different versions of SQL exist; some are standard and some are proprietary, and various extensions exist. Data Definition Language ("DDL") commands are issued to the database server to create or configure database objects, such as tables, views, or complex data types. SQL / XML is a common extension of SQL used when manipulating XML data in object-relational databases. While the term "SQL" is used herein to describe embodiments of the invention, the invention is not limited to this particular database query language and can be used in conjunction with other database query languages ​​and constructs.

[0169] A client can send a series of requests to a database server, such as requests to execute queries, by establishing a database session (referred to as a "session" in this document). A session involves a specific connection established for the client to the database server (such as a database instance), through which the client can send a series of requests. The database server can maintain session state data about the session. Session state data reflects the current state of the session and may include the user's identity (for which the session was established), the services used by the user, instances of object types, language and character set data, statistics on the session's resource usage, temporary variable values ​​generated by processes executing within the session, and storage of pointers, variables, and other information. Session state data may also include execution plan parameters configured for the session.

[0170] A multi-node database management system consists of interconnected nodes that share access to the same database. Typically, nodes are interconnected via a network and share access to shared storage devices to varying degrees, such as shared access to a set of disk drives and blocks of data stored on those disk drives. Nodes in a multi-node database system can take the form of a group of computers (e.g., workstations, personal computers) interconnected via a network. Alternatively, the node can be a grid of nodes, where the grid consists of nodes interconnected in the form of server blades with other server blades on a rack.

[0171] In a multi-node database management system, each node hosts a database server. A server, such as a database server, is a combination of integrated software components and computing resource allocation, where computing resources such as memory, nodes, and processing on a processor are used to execute the integrated software components. This combination of software and computing resources is dedicated to performing specific functions on behalf of one or more clients.

[0172] Resources from multiple nodes in a multi-node database system can be allocated to software running a specific database server. Each combination of software and resource allocation from the nodes is a server referred to herein as a "server instance" or "instance". A database server may include multiple database instances, some or all of which run on a single computer comprising individual server blades.

[0173] Hardware Overview

[0174] According to one embodiment, the technology described herein is implemented by one or more dedicated computing devices. The dedicated computing device may be hardwired to execute the technology, or may include one or more application-specific integrated circuits (ASICs) or field-programmable gate arrays (FPGAs) permanently programmed to execute the technology, or may include one or more general-purpose hardware processors programmed to execute the technology according to program instructions in firmware, memory, other storage devices, or a combination thereof. Such a dedicated computing device may also combine custom hardwired logic, ASICs, or FPGAs with custom programming to implement the technology. The dedicated computing device may be a desktop computer system, a portable computer system, a handheld device, a networking device, or any other device that combines hardwired and / or program logic to implement the technology.

[0175] For example, Figure 5 This is a block diagram illustrating a computer system 500 on which embodiments of the present invention can be implemented. The computer system 500 includes a bus 502 or other communication mechanism for transmitting information, and a hardware processor 504 coupled to the bus 502 for processing information. The hardware processor 504 may be, for example, a general-purpose microprocessor.

[0176] Computer system 500 also includes main memory (also referred to herein as "volatile memory") 506, such as random access memory (RAM) or other dynamic storage devices, coupled to bus 502 for storing information and instructions to be executed by processor 504. Main memory 506 can also be used to store temporary variables or other intermediate information during the execution of instructions to be executed by processor 504. When such instructions are stored in a non-transitory storage medium accessible to processor 504, the instructions turn computer system 500 into a dedicated machine customized to perform the operations specified in the instructions.

[0177] Computer system 500 also includes a read-only memory (ROM) 508 or other static storage device coupled to bus 502 for storing static information and instructions for processor 504. Storage device 510, such as a disk, optical disk, or solid-state drive, is provided and coupled to bus 502 for storing information and instructions.

[0178] Computer system 500 can be coupled via bus 502 to a display 512, such as a cathode ray tube (CRT), for displaying information to the computer user. Input devices 514, including alphanumeric keys and other keys, are coupled to bus 502 for transmitting information and command selections to processor 504. Another type of user input device is a cursor control 516, such as a mouse, trackball, or arrow keys, for transmitting directional information and command selections to processor 504 and for controlling cursor movement on display 512. Such input devices typically have two degrees of freedom in two axes (e.g., the first axis (x) and the second axis (y)), allowing the device to specify a position in a plane.

[0179] Computer system 500 may implement the techniques described herein using custom hard-wired logic, one or more ASICs or FPGAs, firmware, and / or program logic. The custom hard-wired logic, one or more ASICs or FPGAs, firmware, and / or program logic are combined with the computer system to make computer system 500 a, or to program computer system 500 as a, special-purpose machine. According to one embodiment, the techniques described herein are executed by processor 504 of computer system 500 in response to executing one or more sequences of instructions contained in main memory 506. Such instructions may be read into main memory 506 from another storage medium, such as storage device 510. Executing the sequence of instructions contained in main memory 506 causes processor 504 to perform the processing steps described herein. In alternative embodiments, hard-wired circuitry may be used in place of or in combination with software instructions.

[0180] As used herein, the term "storage medium" means any non-transitory medium that stores data and / or instructions that enable a machine to operate in a particular manner. Such storage media can include non-volatile media and / or volatile media. Non-volatile media include, for example, optical discs, magnetic disks, or solid-state drives, such as storage device 510. Volatile media include dynamic memory, such as main memory 506. Common forms of storage media include, for example, floppy disks, flexible disks, hard disks, solid-state drives, magnetic tape, or any other magnetic data storage media, CD-ROMs, any other optical data storage media, any physical media with a perforated pattern, RAM, PROMs and EPROMs, FLASH-EPROMs, NVRAMs, any other memory chips, or magnetic tape cassettes.

[0181] Storage media are distinct from transmission media, but can be used in conjunction with them. Transmission media participate in transferring information between storage media. For example, transmission media include coaxial cables, copper wires, and optical fibers, including leads containing bus 502. Transmission media can also take the form of sound waves or light waves, such as those generated in radio waves and infrared data communications.

[0182] Various forms of media can be used to carry one or more sequences of instructions to processor 504 for execution. For example, instructions may initially be carried on a disk or solid-state drive of a remote computer. The remote computer can load the instructions into its dynamic memory and transmit them via a telephone line using a modem. A modem located locally in computer system 500 can receive data over the telephone line and convert the data into an infrared signal using an infrared transmitter. An infrared detector can receive the data carried in the infrared signal, and appropriate circuitry can place the data on bus 502. Bus 502 carries the data to main memory 506, from which processor 504 retrieves and executes the instructions. Instructions received by main memory 506 may optionally be stored on storage device 510 before or after execution by processor 504.

[0183] Computer system 500 also includes a communication interface 518 coupled to bus 502. Communication interface 518 provides bidirectional data communication coupled to network link 520, which is connected to local network 522. For example, communication interface 518 may be an Integrated Services Digital Network (ISDN) card, a cable modem, a satellite modem, or a modem providing data communication connectivity to a corresponding type of telephone line. As another example, communication interface 518 may be a LAN card providing data communication connectivity to a compatible local area network (LAN). A wireless link may also be implemented. In any such implementation, communication interface 518 transmits and receives electrical, electromagnetic, or optical signals carrying digital data streams representing various types of information.

[0184] Network link 520 typically provides data communication to other data devices via one or more networks. For example, network link 520 may provide a connection to host computer 524 or to a data device operated by Internet Service Provider (ISP) 526 via local network 522. ISP 526 then provides data communication services via a global packet data communication network now commonly referred to as the "Internet" 528. Both local network 522 and Internet 528 use electrical, electromagnetic, or optical signals that carry digital data streams. Signals through various networks and signals on network link 520 via communication interface 518 are example forms of transmission media in which digital data is carried to or from computer system 500.

[0185] Computer system 500 can send messages and receive data, including program code, through one or more networks, network links 520, and communication interfaces 518. In the Internet example, server 530 can transmit requested code for an application through the Internet 528, ISP 526, local network 522, and communication interface 518.

[0186] The received code may be executed by processor 504 upon receipt and / or stored in storage device 510 or other non-volatile storage device for later execution.

[0187] In the foregoing description, embodiments of the invention have been described with reference to numerous specific details, which may vary depending on the implementation. Therefore, the description and drawings are to be considered illustrative rather than restrictive. The sole and exclusive indication of the scope of the invention, and what the applicant contemplates as the scope of the invention, is the written scope and equivalent scope of the specific form produced by the set of claims arising from this application, including any subsequent corrections.

Claims

1. A computer-implemented method, comprising: The database is maintained on persistent storage by a database server; The database mentioned above includes data portions that have been designated as eligible for mirroring within volatile memory; The database server performs an evaluation of one or more factors related to the data portion; The one or more of the factors include access statistics associated with the data portion, and the access statistics associated with the data portion include heatmap statistics of the data portion; Based on the assessment, qualification data corresponding to the data portion is generated; The evaluation includes evaluating the heatmap statistics of the data portion; The qualification data (a) reflects the estimated benefit of mirroring the data portion in the volatile memory, and (b) is determined by the database server based on the one or more factors. If the heatmap statistics indicate frequent write access to the data portion, the eligibility data is modified to reduce the likelihood that the data portion will be loaded into the volatile memory. Based on the qualification data, the mirror state of the data portion is automatically changed; The automatic change of the mirror state of the data portion includes one of the following: When the data portion is not currently mirrored in the volatile memory, the data portion is mirrored in the volatile memory, or When the data portion is currently mirrored in the volatile memory, the data portion is removed from the volatile memory.

2. The method of claim 1, further comprising: The database server periodically performs one or more evaluations of the one or more factors related to the data portion, or based on one or more of the following: a) Reaching the user-specified time. b) Reaching the time determined by the database server based on system statistics, or c) One or more user-specified triggers, wherein the one or more user-specified triggers include one or more of the following: availability of space in volatile memory or statistics of the database server crossing one or more thresholds; Based on the one or more assessments, new eligibility data corresponding to the data portion is generated; Based on the new qualification data, the mirror state of the data portion is automatically changed; The automatic change of the mirror state of the data portion includes one of the following: When the data portion is not currently mirrored in the volatile memory, the data portion is mirrored in the volatile memory, or When the data portion is currently mirrored in the volatile memory, the data portion is removed from the volatile memory.

3. The method of claim 1, wherein the database includes a second data portion that has been designated as eligible for mirroring in volatile memory, wherein the data portion is a first data portion, the mirroring state is a first mirroring state, and the eligibility data is first eligibility data, and the method further comprises: Store second qualification data corresponding to the second data portion, wherein the second qualification data reflects an estimated benefit of mirroring the second data portion in the volatile memory; The second qualification data is determined by the database server based on one or more of the factors; Based on the second qualification data, the second mirror state of the second data portion is automatically changed; The automatic change of the second mirror state of the second data portion includes one of the following: When the second data portion is not currently mirrored in the volatile memory, the data portion is mirrored in the volatile memory, or When the second data portion is currently mirrored in the volatile memory, the data portion is removed from the volatile memory.

4. The method of claim 3, further comprising: The database server periodically performs one or more evaluations of the one or more factors related to the first data portion and the second data portion, or based on one or more of the following: a) Reaching the user-specified time. b) Reaching the time determined by the database server based on system statistics, or c) One or more user-specified triggers, wherein the one or more user-specified triggers include one or more of the following: availability of space in volatile memory or statistics of the database server crossing one or more thresholds; Based on the one or more assessments, new first qualification data and new second qualification data are generated, respectively corresponding to the first data portion and the second data portion; Based on the new first qualification data and the new second qualification data, the first mirror state of the first data portion or the second mirror state of the second data portion is automatically changed. The automatic change of the first mirror state of the first data portion or the second mirror state of the second data portion includes one of the following: When the first data portion or the second data portion is not currently mirrored in the volatile memory, the first data portion or the second data portion is mirrored in the volatile memory, or When the first data portion or the second data portion is currently mirrored in the volatile memory, the data portion is removed from the volatile memory.

5. The method of claim 1, wherein the one or more factors include at least one of the following: The access statistics associated with the data portion, The data type of the data portion, Operational statistics associated with the data portion, or Data statistics associated with the data portion.

6. The method as described in claim 1, The method further includes: If the heatmap statistics indicate frequent read access to the data portion, then the eligibility data is modified to increase the likelihood that the data portion will be loaded into the volatile memory; If the heatmap statistics indicate infrequent read access to the data portion, the eligibility data is modified to reduce the likelihood that the data portion will be loaded into the volatile memory.

7. The method of claim 1, wherein: The one or more factors include the data type of the data portion, and The method further includes: if the data type is a large object type, modifying the eligibility data to reduce the likelihood that the data portion will be loaded into the volatile memory.

8. The method of claim 1, wherein: Automatically changing the mirror state of the data portion includes: when the data portion is currently mirrored in the volatile memory, causing the data portion to be removed from the volatile memory; and The removal of the data portion from the volatile memory is performed in response to a cleanup event that causes one or more copies of the mirrored data portion to be removed from the volatile memory.

9. The method of claim 8, further comprising: Perform a comparison between the eligibility data and the threshold, and Based on the comparison, it is automatically determined that data from the data portion will be removed from the volatile memory.

10. The method of claim 1, wherein: The data portion is the first data portion; The qualification data mentioned is the first qualification data; The database includes a second data portion separate from the first data portion, which has been designated as eligible for mirroring within the volatile memory; as well as The method further includes: Store second qualification data corresponding to the second data portion, wherein the second qualification data reflects the estimated benefit of mirroring the second data portion in the volatile memory; Based on the second qualification data, it is automatically determined that data from the second data portion should be loaded into the volatile memory; When data from the first data portion is mirrored in the volatile memory and data from the second data portion is not mirrored in the volatile memory, the database server performs the following steps: Perform a comparison between the second qualification data and the first qualification data; and Based on the comparison, the database server determines that data from the first data portion will be removed from the volatile memory; In response to a clear event that causes a copy of the mirrored data portion to be removed from the volatile memory, data from the first data portion is removed from the volatile memory.

11. The method of claim 1, wherein: Automatically changing the mirror state of the data portion includes: when the data portion is not currently mirrored in the volatile memory, causing the data portion to be mirrored in the volatile memory; and The mirroring of the data portion within the volatile memory is performed in response to a load event that causes the data portion to be mirrored within the volatile memory.

12. The method of claim 11, wherein loading data from the data portion into the volatile memory includes using the data from the data portion to construct a compression unit in the memory.

13. The method of claim 12, wherein in the persistent storage device, data from the data portion is organized in a row-major format, and in the compression unit of the memory, data from the data portion is organized in a column-major format.

14. The method of claim 1, wherein: The evaluation includes evaluating at least one of the following for a certain portion of the data: The access statistics value of the certain data portion, The data type of the aforementioned data portion, or Performance statistics for one or more compressed data portions that are similar to the aforementioned data portion; as well as The method further includes: Based on the assessment, the compression level of the certain data portion is modified; Compress the data in the specified data portion using the modified compression level; and The compressed data is loaded into the volatile memory.

15. The method of claim 14, wherein the performance statistics for the one or more compressed data portions similar to the given data portion are evaluated to indicate that different compression levels consume fewer resources of the database server; and the compression level of the given data portion is modified to approximate the different compression levels.

16. One or more non-transitory storage media storing instructions, which, when executed by one or more hardware processors, cause the method of any one of claims 1-15 to be performed.

17. An apparatus comprising components for performing the method as described in any one of claims 1-15.

18. An apparatus comprising: processor; as well as A memory coupled to the processor and the memory including instructions stored thereon, which, when executed by the processor, cause the processor to perform the method as described in any one of claims 1-15.

Images