methods, apparatus, systems, and computer program products for utilizing a split dictionary are described. A mean and a standard deviation of a length of a plurality of records in a dictionary may be computed. A split condition may be determined based on the computed mean and the computed standard deviation. The dictionary may be split into a plurality of split dictionaries based on the split condition.
|
1. A computerized method for utilizing a split dictionary, the method comprising:
computing a mean and a standard deviation of a length of a plurality of records in a dictionary;
determining a split condition based on the computed mean and the computed standard deviation; and
splitting the dictionary into a plurality of split dictionaries based on the split condition, wherein a first dictionary of the plurality of split dictionaries is a major dictionary comprising records having a length shorter than a threshold and a second dictionary of the plurality of split dictionaries is a minor dictionary comprising records having a length greater than or equal to the threshold.
16. A non-transitory machine-readable storage medium comprising instructions that, when executed by one or more processors of a machine, cause the machine to perform operations comprising:
computing a mean and a standard deviation of a length of a plurality of records in a dictionary;
determining a split condition based on the computed mean and the computed standard deviation; and
splitting the dictionary into a plurality of split dictionaries based on the split condition, wherein determining the split condition comprises:
computing a performance for each combination of a selected split length and a selected block size, wherein the selected split length is one of a plurality of candidate split lengths and the selected block size is one of a plurality of block candidate sizes;
ranking the computed performances; and
selecting a combination of split length and block size corresponding to a highest ranking performance.
10. An apparatus for utilizing a split dictionary, the apparatus comprising:
a processor;
memory to store instructions that, when executed by the processor cause the processor to:
compute a mean and a standard deviation of a length of a plurality of records in a dictionary;
determine a split condition based on the computed mean and the computed standard deviation; and
split the dictionary into a plurality of split dictionaries based on the split condition, wherein determining the split condition comprises:
determining, for each of a plurality of different values of k, a percentage of records of the dictionary corresponding to each of a plurality of categories based on m+k*s, wherein m is a mean of a length of unique string of records, s is a standard deviation in lengths unique strings of records, and k is a positive integer;
computing a performance for each of the plurality of different values of k;
ranking the computed performances; and
selecting a value of k corresponding to a highest ranking performance.
2. The computerized method of
3. The computerized method of
obtaining a record length of a query term;
determining an identity of a split dictionary corresponding to the obtained record length; and
searching only clusters corresponding to the identified split dictionary based on the block encoding vector.
4. The computerized method of
determining, for each of a plurality of different values of k, a percentage of records of the dictionary corresponding to each of a plurality of categories based on m+k*s, wherein m is a mean of a length of unique strings of records, s is a standard deviation in lengths of the unique strings of records, and k is a positive integer;
computing a performance for each of the plurality of different values of k;
ranking the computed performances; and
selecting a value of k corresponding to a highest ranking performance.
5. The computerized method of
computing a performance for each combination of a selected split length and a selected block size, wherein the selected split length is one of a plurality of candidate split lengths and the selected block size is one of a plurality of block candidate sizes;
ranking the computed performances; and
selecting a combination of split length and block size corresponding to a highest ranking performance.
6. The computerized method of
8. The computerized method of
9. The computerized method of
11. The apparatus of
12. The apparatus of
13. The apparatus of
obtain a record length of a query term;
determine an identity of a split dictionary corresponding to the obtained record length; and
search only clusters corresponding to the identified split dictionary based on the block encoding vector.
14. The apparatus of
computing a performance for each combination of a selected split length and a selected block size, wherein the selected split length is one of a plurality of candidate split lengths and the selected block size is one of a plurality of block candidate sizes;
ranking the computed performances; and
selecting a combination of split length and block size corresponding to a highest ranking performance.
15. The apparatus of
17. The non-transitory machine-readable storage medium of
generate a block encoding vector, the block encoding vector comprising an encoding element for each of a plurality of clusters of integer identifiers within an attribute vector, the encoding element indicating an identification of one or more of the plurality of split dictionaries corresponding to the integer identifiers of the corresponding cluster.
18. The non-transitory machine-readable storage medium of
obtain a record length of a query term;
determine an identity of a split dictionary corresponding to the obtained record length; and
search only clusters corresponding to the identified split dictionary based on the block encoding vector.
|
|||||||||||||||||||||||||
The present disclosure relates, generally, to data compression and search. In an example embodiment, the disclosure relates to data dictionary compression and search for data in data dictionaries and transaction tables.
Enterprise data is growing rapidly and enterprises are looking for ways to utilize big data to gain a competitive advantage. Databases have traditionally used data compression models to make data easily consumable. In general, the major barrier in data compression is that, as the compression ratio increases, so does the complexity; a higher compression ratio often means that it is more difficult to decompress the data. An optimal compression technique would be one that can provide a high compression ratio, but still not consume excessive computer resources to query the data for analytics.
Many conventional compression algorithms, such as null suppression, Huffman, and the like may provide 50 to 85% compression rates, but may be complex and the decompression of data may be a time intensive process. Nonetheless, these techniques may be efficient in reducing the I/O (input/output) overhead and hence may be suitable for I/O bound applications. In contrast, in the case of more light-weight compression techniques such as data dictionary compression, run-length, delta encoding, and the like, the I/O benefits of compression may substantially outweigh the associated processing costs.
Compression techniques have evolved from row-based compression approaches to column-based compression schemes, as column stores have natural redundancy in data values due to a recurrence of data values or patterns. Such techniques make decompression or consumption of data relatively easier. In column stores, a high compression ratio can be achieved compared to traditional row-oriented database systems. This may result in reduced storage needs, improved performance (for I/O intensive applications), and an increase in buffer pool/cache hit rate.
In the particular case of dictionary encoding, values of a column are encoded as integers. Thus, a check for equality, during scans or join operations, can be executed on integer parameters, which may be much faster than comparing string values. Furthermore, the dictionary encoded attribute vectors may be compressed using various techniques, like prefix encoding, run-length encoding, cluster encoding, and the like. In addition, the dictionaries themselves may be compressed through methods like delta encoding compression.
The present disclosure is illustrated by way of example and not limitation in the figures of the accompanying drawings, in which like references indicate similar elements and in which:
The description that follows includes illustrative systems, methods, techniques, instruction sequences, and computing program products that embody example embodiments of the present invention. In the following description, for purposes of explanation, numerous specific details are set forth in order to provide an understanding of various embodiments of the inventive subject matter. It will be evident, however, to those skilled in the art, that embodiments of the inventive subject matter may be practiced without these specific details. In general, well-known instruction instances, protocols, structures and techniques have not been shown in detail.
Generally, methods, systems, apparatus, and computer program products for generating and processing dictionary tables are disclosed. In one example embodiment, dictionary tables are compressed through the use of statistical estimation techniques in order to reduce an amount of unused storage space allocated to the dictionary table. In one example embodiment, techniques to improve attribute vector (AV) query performance without losing the record identifier (e.g., row/record-id) are disclosed. The disclosed techniques may preserve the ability to directly access elements of the dictionary tables without increasing the utilization of computing resources.
Multi-Tiered Enterprise Computing Systems
The “business logic” component of the application 108 may represent the core program code of the application 108, i.e., the rules governing the underlying business process (or other functionality) provided by the application 108. The “presentation logic” may describe the specific manner in which the results of the business logic are formatted for display on the user interface. The “database” 116 may include data access logic used by the business logic to store and retrieve data.
In response to limitations associated with the two-tiered client-server architecture, a multi-tiered architecture has been developed, as illustrated in
This separation of logical components and the user interface 154 may provide a more flexible and scalable architecture compared to that provided by the two-tiered model of the system 100 in
For example, consider the following query:
SELECT COUNT (*) FROM Suppliers WHERE City=‘Delhi’.
To execute the above query, a system may have a master metadata table working in conjunction with a database manager to store information like table name, column name, start address of the dictionary, width of the string column in the dictionary, total number of records in the dictionary, encoding integer width, and the like.
In one example embodiment, the width of the string column may be useful in calculating the integer ID (used in the attribute vector), as the integer ID for each entry in the dictionary is equivalent to its memory offset from the start address of the dictionary. In this case, to perform the query, values in the dictionary are traversed using integral multiples of the string column width in search of the query term. For example, the term “Delhi” may be searched for. If a match is found, the integer ID is further used to scan the attribute vector to find the total count of entries that match the name “Delhi.”
Dictionary Table Compression
The number of unique strings in a dictionary table may be on the order of millions. In such instances, searching for a particular string in the dictionary may lead to performance degradation. To overcome this shortcoming, the dictionary tables may be compressed. For example, the dictionary tables may be compressed using delta encoding.
Challenges in Delta Encoding
Delta encoding algorithms may be used for compressing dictionary tables in terms of space requirements. Delta encoding may lead to degraded performance since compression creates varying column length vectors that complicate the calculation of the integer ID. To improve the performance, a master metadata record may be created to store the variable lengths of each unique string. The stored variable lengths may be used to traverse the dictionary while reading table entries while, for example, searching for a particular string.
The use of delta encoding for dictionary tables may, however, present various issues. For example, columns of a fixed width may provide a natural delimiter of the records. A characteristic of using delta encoding may be that each record is variable in length and thus there may be no natural record delimiting the records. This may make it complex to reconstruct the original record using the dictionary, as the address of the desired string cannot be easily computed.
For particular queries, like substring matching, where the entire dictionary needs to be sorted, delta compression may lead to severe performance degradation as decompression of data may be a complex procedure.
For queries involving an exact string search in the dictionary, generally, binary search algorithms may be implemented. If the dictionary is compressed using delta encoding, then traversing the compressed dictionary from one (middle) element to the next (middle) element in the search space becomes a challenge (as the computation of the same may be expensive). For example, consider that the search pointer is currently on the 11th dictionary element (sorted and compressed) and it needs to reach the 6th element to compare the query parameter with the string (since a binary search requirement is to split the search space in half). Based on this comparison, the search pointer either moves to the 3rd position or the 8th position for the next match. To calculate the memory address of the 6th record, it needs to get the length of the first 5 records and sum them to reach the beginning of the 6th element. This may be computationally expensive.
In one example embodiment, a modified version of delta compression known as block delta compression may be implemented, in which the dictionary is stored as sorted blocks of 16 string values that are compressed using the delta compression technique. To search a string in the block delta dictionary, a binary search may be performed at the block level and then a linear search may be performed once a block is selected. A direct access to the blocks may be maintained using an index/metadata table, leading to a slight overhead in terms of space requirements. This may improve the performance of searching for a string in the dictionary as compared to pure delta encoding, but it may compromise the compression ratio.
As the size of the dictionary increases, the size of the prefixed encoding parameters, namely, the overlap length and new string length, may increase in proportion. This may result in a larger memory being needed to store the delta encoded dictionary. Consequently, a larger number of bits may be needed to address it in the metadata table.
Delta encoding may further complicate the update of new and unique string entries in the dictionary. In a simple dictionary, a new string may be simply inserted in between existing entries and the memory may be re-adjusted for the trailing strings. In the case of delta encoding, in addition to the memory re-adjustment, the delta/overlap length needs to be recomputed for the new entry and the immediate next string. If an index/metadata table is maintained, even that may have to be updated for all of the trailing strings (following the new entry).
In addition, since the records are now variable in length, the task of finding the next record entry may become more complex.
Furthermore, null suppression, like delta encoding or any other approach that results in variable length columns/fields, may eliminate direct access to columns of records.
Split Dictionary Approach
The default width of a column in a table is set such that it can accommodate the maximum length of a string. The memory allocations in a conventional dictionary are done as per the fixed column width (maximum length) defined in the database table. This may lead, however, to a waste of storage space, as strings which are shorter in length than the maximum length do not utilize the entire width of the column.
Most of the current columnar database compression schemes focus only on representing data more concisely, without losing information by exploiting the statistical redundancy in data, like a recurrence of values. In one example embodiment, the unutilized storage space for shorter length strings may be reduced through a reorganization of the dictionary tables. The reorganization may comprise splitting the dictionary tables into two tables based on the length of unique strings, where each split dictionary table has a fixed column width that is based on the spread of the record lengths it is going to contain.
Considering the central limit theorem, which states that the distribution of the sum (or average) of a large number of independent, identically distributed variables will be approximately normal (regardless of the underlying distribution), the lengths of millions of strings in a dictionary table can be safely assumed to be normally distributed. A result of a normal distribution is that most of the values may lie close to the mean due to the bell shape of the distribution curve. In statistics, the 68-95-99.7 rule, or the three-sigma rule, states that, for a normal distribution, nearly all the values lie within 3 standard deviations of the mean. Therefore, ideally, 99.7% of the total records of a dictionary should have lengths less than:
Mean of lengths+(Constant*Standard Deviation of lengths)=m+K*s (1)
In equation (1), constant (K)=3.
In one example embodiment, the mean and standard deviation in length of all the strings in the dictionary are computed. Based on these calculations, the master dictionary table may be split into two parts, one having a reduced column width to accommodate the majority (possibly 99.7%) of all of the strings with length less than the mean length plus three times the standard deviation length (henceforth referred to as the “major” dictionary table) and the other having a non-reduced column width to accommodate the minority (possibly 0.3%) of all of the strings with length greater than the mean length plus three times the standard deviation length (henceforth referred to as the “minor” dictionary table). The strings in the minor table have lengths that significantly deviate from the mean of lengths. This leads to a reduction in memory space needed to store the dictionary 300 while maintaining direct access. The major and minor dictionaries 310, 314 are individually maintained as sorted dictionary tables for better performance (as done in the conventional single dictionary 300 approach).
In one example embodiment, information for two dictionaries 310, 314 for a single table column may be maintained. For example, information for two dictionaries 310, 314 for a single table column may be maintained in a database manager.
Creation of Block Encoding Vector for the Attribute Vector
In one example embodiment, a block encoding vector is created for an attribute vector. For example, consider a case in which 8 billion records may be stored, one for each individual of the world's population. The attribute vector for the name column in this case may need 20 bits to store an integer identifier. This may require a total of approximately 18.63 GB of memory space. If a query for a particular name is executed, such as:
SELECT COUNT (*) FROM Suppliers WHERE Name=‘Robert’,
the search space for the equivalent integer identifier of the “Name” parameter in the attribute vector is on the order of eight billion. Since the database system already knows the search parameter's length (20 bits), the search space can be reduced, as described more fully below.
In one example embodiment, the system creates three types of blocks, such as a major dictionary block, a minor dictionary block, and a mixed dictionary block (i.e., a block which contains integer identifiers from both the major and minor dictionaries 310, 314). A 2-bit vector may be used for storing clustering information of the attribute vector in the block encoding vector 322. In this vector, a value of ‘01’ may represent a block containing only integer identifiers from the major dictionary 314, a value of ‘10’ may represent a block containing only integer identifiers from the minor dictionary 310, a value of ‘11’ may represent a block containing integer identifiers from both the major and minor dictionaries 310, 314, and the value of ‘00’ may represent blocks of null values (in case a particular column allows null values).
In
Continuing with the world's population example, suppose a query of the name ‘Robert’ is submitted. The name ‘Robert’ has a length of six characters corresponding, in the present example, to six bytes. The length of six bytes is less than the mean length plus three times the standard deviation length and the name ‘Robert’ is therefore located in the major dictionary 314. Therefore, only clusters corresponding to the major dictionary 314 (two-bit vector equals ‘01’) or corresponding to both dictionaries 310, 314 (two-bit vector equals ‘11’) need to be searched; clusters corresponding to the minor dictionary 310 (two-bit vector equals ‘10’) and corresponding to blocks which represent null values (two-bit vector equals ‘00’) may be skipped. The block encoding vector information may be used to identify the cited clusters.
Performance Examples
Based on the combination of the types of blocks and the type of query, different scenarios may be contemplated to understand the performance improvements.
For example, in scenario #1, assume there are an equal number (approximately 47.5%) of major and minor dictionary blocks, and approximately 5% of the blocks are mixed dictionary blocks. The search space of the attribute vector will be equal to 47.5% major blocks, 47.5% minor blocks, and 5% mixed blocks, irrespective of whether the search parameter belongs to the major or the minor dictionary 310, 314. Thus, instead of scanning the entire attribute vector of 18.63 GB, the search space is limited to 9.78 GB. Assuming a processing speed of 2 MB/ms/core, a performance gain of almost 47.5% is achieved.
For example, in scenario #2, assume there are an equal number (approximately 33.3%) of major, minor, and mixed dictionary blocks. The search space of the attribute vector will be equal to 33.3% of major, minor, and mixed blocks, irrespective of the type of query parameter. Thus, instead of scanning the entire attribute vector of 18.63 GB, the search space is limited to 12.29 GB. Assuming a processing speed of 2 MB/ms/core, a performance gain of almost 34% is achieved.
For example, in scenario #3, assume there are an equal number (approximately 10-20%) of major and minor dictionary blocks, and approximately 70% of the blocks are mixed dictionary blocks. The search parameter belongs to the major (or minor) dictionary 310, 314. The search space of the attribute vector is limited to 70% of mixed dictionary blocks and 10-20% major (or minor) dictionary blocks. The limiting factor for performance gain in this case is the size of the mixed dictionary. Therefore, any variations in the performance are only due to the size of the remaining major and minor dictionary blocks. The performance gain in this case is between 10 and 20% depending on the size of the major and minor dictionary blocks and whether the search is in the major or the minor dictionary 314, 310.
For example, in scenario #4, assume there are a higher number (e.g., approximately 70%) of either major or minor dictionary blocks and the remaining blocks (minor/major and mixed dictionary blocks) are each approximately 10% to 20% of the total. The search parameter belongs to the major (or minor) dictionary 314, 310 and there are a higher number of major (or minor) dictionary blocks only. The search space of the attribute vector is limited to the 70% major (or minor) blocks and 10% mixed blocks. The performance gain in this case is about 20% as compared to the full attribute vector scan, as the search space is reduced to 14.90 GB.
Alternatively, the search parameter belongs to the minor (or major) dictionary 310, 314, but there are a higher number of major (or minor) dictionary blocks. The search space is effectively limited to 20% minor (or major) blocks and 10% mixed blocks. This may be the best scenario in terms of performance, as only 5.59 GB of memory space needs to be scanned, instead of 18.63 GB. This may lead to a performance gain of almost 70%, and the gain can further increase as the size of corresponding blocks decrease.
The following inferences may be drawn from the above examples:
Overall, a small memory allocation of 1.86 MB (0.00976% of the overall attribute vector) for the block encoding vector 322 may lead to a performance gain of 10% to 70% (based on the combination of different types of queries).
The apparatus 600 is shown to include a processing system 602 that may be implemented on a server 112, client 104, or other processing device that includes an operating system 604 for executing software instructions. In accordance with an example embodiment, the apparatus 600 may include a user interface module 606, a split condition determination module 610, a block encoding vector generator module 614, a split dictionary generator module 618, and a query execution module 622.
The user interface module 606 may enable a user to enter a query and view query results.
The split condition determination module 610 may determine a split condition, such as a split length, for a split dictionary, as described more fully below in conjunction with
The block encoding vector generator module 614 may generate a block encoding vector 322, as described more fully below in conjunction with
The split dictionary generator module 618 may generate a split dictionary, as described more fully below in conjunction with
The query execution module 622 may execute a query, as described more fully below in conjunction with
Deciding the Split Condition
To determine the split condition, the mean length of the strings in the dictionary 300 and the standard deviation in these lengths may need to be computed. These statistics may be computed for the entire population directly in in-memory databases. Alternatively, in certain situations (like a disk-based database system or batch loading—growing dictionary scenarios), the system utilizes the statistics with sampling data to estimate the mean and standard deviation of substantially the entire population. In one example embodiment, a sample set from the entire population may be taken through random sampling and used to estimate the population mean using the Z-test and the standard deviation of the population using the Chi-square test.
1) Deciding the Split Dictionary Cut-Length
In one example embodiment, using the population mean and standard deviation, the percentage of population which falls in each category may be found:
m+K*s
where K=1, 2, 3 . . . . Based on the above findings, the following steps may provide a generic approach to determine the cut length:
In one example embodiment, the mean and standard deviation of the lengths of the strings of the dictionary 300 may be computed (operation 704) and the percentage of the dictionary population that falls in each category of μ+K*σ (for K=1, 2, 3 . . . ) may be computed (operation 708). The memory and performance advantages for different values of K may be determined (operation 712) and the optimal value of K in regard to memory and performance may be determined (operation 716).
In one example embodiment, the dictionary split based on the optimal value of K may be simulated and the block encoding vector 322 for the attribute vector may be generated based on the simulated dictionary split (operation 720). A test may be performed to determine if a minimum number of mixed dictionary clusters and an equal number of major and minor dictionary blocks have been defined (operation 724). If a minimum number of mixed dictionary clusters and an equal number of major and minor dictionary blocks have been defined, the method may end; otherwise, the value of K may be shifted (operation 728) and the method may proceed with operation 720.
In one example embodiment, the optimal split length and block size for a given AV may be determined by the following steps.
In one example embodiment, the Split_Length, Max_Split_Length, Max_Block_Size, and Minimum_Cost variables may be initialized (operation 804). For example, the Split_Length may be initialized to ten, the Max_Split_Length may be initialized to 100, the Max_Block_Size may be initialized to 1024, and the Minimum_Cost variable may be initialized to the value of the Max_Long_Number. A test may be performed to determine if the Split_Length equals the Max_Split_Length (operation 808). If the Split_Length equals the Max_Split_Length, the method may end; otherwise, the variable Block_Size may be set equal to Start_Block_Size. For example, the variable Block_Size may be set equal to 1024 (operation 812).
A test may be performed to determine if the variable Block_Size equals the Max_Block_Size (operation 816). If the variable Block_Size equals the Max_Block_Size, the variable Split_Length may be incremented by the value Delta (e.g., the value ten) (operation 818) and the method may proceed with operation 808; otherwise, the performance cost (New_Cost) of the block encoding vector 322 may be computed (operation 820).
A test may be performed to determine if the variable New_Cost is less than the variable Minimum_Cost (operation 824). If the variable New_Cost is less than the variable Minimum_Cost, then the variable Minimum_Cost is set equal to the variable New_Cost (operation 828) and the method may proceed with operation 832; otherwise, the method may proceed with operation 832.
During operation 832, the variable Block_Size may be incremented by the value Delta (e.g., the value 1024) and the method may proceed with operation 816.
Dictionary Encoding Integer Identifiers for the Attribute Vector
In a conventional dictionary encoding implementation, the attribute vector may simply store the implicit memory offset of the strings in the dictionary 300 as integer identifiers. In one example embodiment of the split dictionary approach, the integer identifiers for both of the dictionaries may be continuous, i.e. the attribute vector may perceive the dictionary as one contiguous table. For example, as shown in the table of
Integer IDs for minor table=Number of total strings in major dictionary+Memory offset in minor dictionary
The maintenance overhead of using continuous integer identifiers is consistent with existing systems like SAP HANA of SAP AG of Walldorf, Germany. When a new unique string is inserted in either the major or minor dictionary 314, 310, the memory offsets of the existing strings change, as new strings may be added in between existing strings in order to keep the dictionary sorted. This may lead to a large number of updates in the attribute vector, as the entire set of integers below this new entry may need to be updated by one.
Allocating an Extra Bit to the Integer Identifier to Identify Referenced Dictionary
As illustrated in the table of
Thus, the first approach does not need any extra memory, but may have a higher cost for dictionary updates. The second approach of creating independent dictionaries may lead to efficient updates, but with an additional cost in terms of memory.
In one example embodiment, the Max_AV_Size, BEV_Block_Size, Max_BEV_size, BEV_Iterator, Is_Major_Block, Is_Minor_Block, and Is_Mixed_Block variables may be initialized (operation 1004). For example, the Max_AV_Size may be initialized to End_AV_Length (e.g., one billion), BEV_Block_Size may be initialized to Calculated_Block_Size (e.g., 1024), Max_BEV_size may be initialized to Max_AV_size/BEV_Block_Size, BEV_Iterator may be initialized to zero, BEV may be initialized to BEV=New_Vector[Max_BEV_Size], Is_Major_Block may be initialized to 01, Is_Minor_Block may be initialized to 10, and Is_Mixed_Block may be initialized to 11.
A test may be performed to determine if BEV_Iterator equals the Max_BEV_Size (operation 1008). If BEV_Iterator equals the Max_BEV_Size, the method may end; otherwise, a test may be performed to determine if all the elements of the BEV_Block in AV belong to the major dictionary 314 (operation 1012).
If all the elements of the BEV_Block in AV belong to the major dictionary, then BEV[BEV_Iterator] is set to Is_Major_Block (operation 1016) and the BEV_Iterator variable may be incremented by one (operation 1020). The method may then proceed with operation 1008.
If all the elements of the BEV_Block in AV do not belong to the major dictionary 314, a test may be performed to determine if all the elements of the BEV_Block in AV belong to the minor dictionary 310 (operation 1024). If all the elements of the BEV_Block in AV belong to the minor dictionary 310, then BEV[BEV_Iterator] is set to Is_Minor_Block (operation 1028) and the BEV_Iterator variable may be incremented by one (operation 1020). The method may then proceed with operation 1008.
If all the elements of the BEV_Block in AV do not belong to the minor dictionary 310, then BEV[BEV_Iterator] is set to Is_Mixed_Block (operation 1032) and the BEV_Iterator variable may be incremented by one (operation 1020). The method may then proceed with operation 1008.
According to the empirical rule, almost 99% of records in the dictionary 300 should have a length less than L=μ+K*σ, where K=3 and L is the Cut_Length. In one example embodiment, the compression ratio for the split dictionary approach at L=μ+K*a may be computed as:
(Original Dictionary Count*Original Dictionary Length)/(Major Dictionary Count*Split Length+Minor Dictionary Count*Original Dictionary Length)
The value of L may then be reduced by delta and the compression ratio may be re-computed. If the compression ratio increases, L may be decreased until it either becomes zero or the compression ratio value decreases instead of increasing. The L for the resulting compression ratio may be used to determine the optimal split length.
However, if after reducing L by delta, the compression ratio value decreases, then L may be increased by delta and the compression ratio may be re-computed. The value of L may be increased until the best compression ratio is found and the corresponding split length may be selected.
If, in both the cases of decreasing and increasing the L value by delta, the compression ratio decreases, then K=3 is the best case. The split length may then be set to L=m+3*s.
For the city dataset, the generated statistics (mean and standard deviation) are as shown in the table of
In one example embodiment, the Optimal_Split_Length, Has_Improved, and Delta variables may be initialized (operation 1104). For example, the Optimal_Split_Length may be initialized to zero, Has_Improved may be initialized to “false”, and Delta may be initialized to one. The compression ratio for L (i.e., Compression_Ratio_L) may then be calculated (operation 1108), where
Compression_Ratio_L=μ+K*σ.
L may be decreased by delta and the compression ratio for L may be re-computed (operation 1112).
A test may be performed to determine if the compression ratio has increased (operation 1116). If the compression ratio has increased, Optimal_Split_Length is set to L and Has_Improved is set to “true” (operation 1120). The method may proceed with operation 1112.
If the compression ratio has not increased, a test may be performed to determine if Has_Improved equals “true” (operation 1124). If Has_Improved equals “true”, the major and minor dictionaries may be created by splitting the original dictionary at Optimal_Split_Length (operation 1128). The method may then end.
If Has_Improved equals “false”, the compression ratio for L (i.e., Compression_Ratio_L) may then be reinitialized (operation 1132), where
Compression_Ratio_L=μ+K*σ.
L may be increased by delta and the compression ratio for L may be re-computed (operation 1136).
A test may be performed to determine if the compression ratio has increased (operation 1140). If the compression ratio has increased, Optimal_Split_Length is set to L and Has_Improved is set to “true” (operation 1144). The method may proceed with operation 1136.
If the compression ratio has not increased, a test may be performed to determine if Has_Improved equals “true” (operation 1148). If Has_Improved equals “true”, the major and minor dictionaries may be created by splitting the original dictionary at Optimal_Split_Length (operation 1128). The method may then end.
If Has_Improved equals “false”, Optimal_Split_Length is set to L=μ+K*σ, where K=3 (operation 1152). The method may proceed with operation 1128.
In one example embodiment, the system continuously iterates over the AV with different split lengths (for example, from 10 to 100, at a step size of 10). For each split length, the system loops over a number of block sizes (for example, from 1024 to 10240, at a step size of 1024) and re-generates the BEV for each block size. The aim is to reach scenario #1 described above. Therefore, for each BEV, a performance cost, which is to be minimized, is calculated according to the following formula:
BEV Performance Cost=(Total Major Dictionary Blocks+Total Mixed Dictionary Blocks)*Major Dictionary Count+(Total Minor Dictionary Blocks+Total Mixed Dictionary Blocks)*Minor Dictionary Count
In one example embodiment, the Optimal_Split_Length, Split_Length, Max_Split_Length, and Max_Block_Size variables may be initialized (operation 1204). For example, Optimal_Split_Length may be initialized to zero, Split_Length may be initialized to Start_Split_Length (e.g., 10), Max_Split_Length may be initialized to End_Split_Length (e.g., 100), and Max_Block_Size may be initialized to End_Block_Size (e.g., 1024).
A test may be performed to determine if Split_Length equals Max_Split_Length (operation 1208). If Split_Length equals Max_Split_Length, the major and minor dictionaries may be created by splitting the original dictionary at Optimal_Split_Length (operation 1212). The method may then end.
If Split_Length does not equal Max_Split_Length, Block_size may be initialized to Start_Block_Size (e.g., 1024) (operation 1216).
A test may be performed to determine if Block_Size equals Max_Block_Size (operation 1220). If Block_Size equals Max_Block_Size, Split_Length may be incremented by Delta (operation 1240) and the method may proceed to operation 1208. If Block_Size does not equal Max_Block_Size, the BEV performance cost is computed and New_Cost is set to the computed BEV performance cost (operation 1224).
A test may be performed to determine if New_Cost is less than Minimum_Cost (operation 1228). If New_Cost is not less than Minimum_Cost, Block_Size may be incremented by Delta (operation 1236) and the method may proceed with operation 1220. If New_Cost is less than Minimum_Cost, Minimum_Cost is set to New_Cost and Optimal_Split_Length is set to Split_Length (operation 1232) and the method may proceed with operation 1236.
Either the compression-oriented approach or the performance-oriented approach work well in case the user has only a single goal to achieve, i.e., better compression or better performance. In most cases, however, the system administrator would want to achieve the best trade-off between compression ratio and performance of the system. In such a case, a threshold level can be defined for both the factors of compression and performance and the above steps can be adequately modified to decide the split length in light of the threshold levels.
Updating Multiple Dictionaries
In some example embodiments, enterprise database systems may not be frequently updated; only 5% of the dictionary data may change over time. This may make it easier to maintain a dictionary in sorted form.
A delta store may be maintained for fast write operations. Instead of directly updating the compressed data, which may be slow, faster uncompressed delta caches (known as L1-delta & L2-delta) may be maintained and may be write optimized. Write operations may only be performed on the delta storage. The changes may then be transferred to the main storage through a process called delta merge. In the case of dictionary compression, the dictionaries of two delta structures are computed (only for L1-delta) and sorted (for both L1-delta and L2-delta), and merged with the main dictionary on the fly.
For a split dictionary implementation, the insert and delete queries may still be made in the delta store only. The delta merge process, however, may need to be modified such that, instead of merging the delta dictionaries with the main dictionary, the merge is done to the appropriate dictionary (major or minor dictionary 314, 310) on the same premise of length of string being lesser than or greater than m+K*s.
With changes in a dictionary, the database manager may need to be updated. For example, as shown in the table of
In the case of a split dictionary approach with continuous integer identifier mapping, this may affect all of the integer identifiers in the attribute vector which are greater than 150000, i.e., from 150000 to 1000000. In the split dictionary approach with an extra bit for integer identifier mapping, this may only affect the major table entries in the attribute vector, i.e., from 150000 to 997001 (the minor table entries remain unchanged).
Frequency of Split Condition Update
With updates in the dictionaries, the mean and standard deviation of lengths will change. Therefore the system should check and update the split condition during the lean/idle time periods. Due to this update, a few of the strings may need to be shifted from either the major dictionary 314 to the minor dictionary 310 or vice versa, resulting in re-sorting of the reorganized dictionary. Also, the attribute vector needs to be updated for both the dictionaries 310, 314 since the integer IDs change in both the dictionaries 310, 314.
For the enterprise dataset, however, the dictionary data may not change for some time. Also, since the total number of records may be in the millions, the addition of a relatively few new strings may not highly influence the computed mean and/or standard deviation.
In one example embodiment, a Count (*) may be selected from the table “Table_Name” where Column_Name is set to ‘Parameter’ (operation 1304). The length of the query ‘Parameter’ may be computed (operation 1308). A test may be performed to determine if the ‘Parameter’ length is less than the Cut_Length (operation 1312). If the ‘Parameter’ length is less than the Cut_Length, the major dictionary 314 may be selected (operation 1316) and the method may proceed with operation 1324; otherwise, the minor dictionary 310 may be selected (operation 1320) and the method may proceed with operation 1324.
In one example embodiment, the start address of the corresponding dictionary may be obtained (operation 1324). For example, a database manager may access a master metadata structure for “Table_Name” to retrieve the start address. The ‘Parameter’ may be searched for in the appropriate dictionary table and the corresponding memory offset from the start address of the dictionary may be computed (operation 1328). The memory offset is equivalent to the integer identifier used in the attribute vector.
An element may be selected from the block encoding vector 322 (operation 1332). The selected element may be tested to identify whether the selected element corresponds to the selected dictionary indicating that the corresponding attribute vector cluster from the attribute vector should be searched (operation 1336). If the selected element does not correspond to the selected dictionary, the attribute vector cluster may be skipped (operation 1348) and the method may proceed with operation 1344; otherwise, the corresponding attribute vector cluster is searched (operation 1340) and the method may proceed with operation 1344.
A test may be performed to determine if all elements of the block encoding vector 322 have been selected (operation 1344). If all of the elements of the block encoding vector 322 have been selected, the method may end; otherwise, the method may proceed with operation 1336.
In one example embodiment, a Count (*) may be selected from the table “Table_Name” where Column_Name equals ‘Parameter’ (operation 1404). The major dictionary and minor dictionary tables may be searched for ‘% Parameter %’ and an array of memory offsets (integer identifiers) to store each matched string may be created (operation 1408). A test may be performed to determine if the matched strings belong to only one of the major dictionary and minor dictionary tables (operation 1412). If the matched strings belong to only one of the major dictionary and minor dictionary tables, the method may proceed with operation 1416; otherwise, the method may proceed with operation 1428.
During operation 1416, the dictionary to which the matched string(s) belong may be selected. In one example embodiment, the start address of the corresponding dictionary may be obtained (operation 1420). For example, a database manager may access a master metadata structure for “Table_Name” to retrieve the start address.
An element in the block encoding vector 322 may be selected (operation 1424) and the selected element may be tested to identify whether the selected element corresponds to the selected dictionary indicating that the corresponding attribute vector cluster from the attribute vector should be searched (operation 1440). If the selected element does not correspond to the selected dictionary, the attribute vector cluster may be skipped (operation 1452) and the method may proceed with operation 1448; otherwise, the corresponding attribute vector cluster may be searched (operation 1444) and the method may proceed with operation 1448.
A test may be performed to determine if all elements of the block encoding vector 322 have been selected (operation 1448). If all of the elements of the block encoding vector 322 have been selected, the method may end; otherwise, the method may proceed with operation 1440.
During operation 1428, both the major dictionary 314 and the minor dictionary 310 may be selected as the search space. In one example embodiment, the start address(es) of both the major dictionary 314 and the minor dictionary 310 may be obtained (operation 1432). For example, a database manager may access a master metadata structure for “Table_Name” to retrieve the start address(es).
An element in the block encoding vector 322 may be selected (operation 1436) and the selected element may be tested to identify whether the selected element corresponds to the selected dictionary indicating that the corresponding attribute vector cluster from the attribute vector should be searched (operation 1456). If the selected element does not correspond to the selected dictionary, the attribute vector cluster may be skipped (operation 1468) and the method may proceed with operation 1464; otherwise, the corresponding attribute vector cluster may be searched (operation 1460) and the method may proceed with operation 1464.
A test may be performed to determine if all elements of the block encoding vector 322 have been selected (operation 1464). If all of the elements of the block encoding vector 322 have been selected, the method may end; otherwise, the method may proceed with operation 1456.
Space Optimization
The split dictionary approach may provide a high compression ratio due to reduced column width in the major dictionary 314.
Time/Performance Optimization: Reverse Lookup Efficiency
Reverse lookup efficiency is the speed with which the database can find the corresponding integer identifier of a unique string in the dictionary. In the split dictionary approach, the database manager has pre-defined information about the column width of each of the dictionaries. As a result, searching a particular string may be easier, as only the appropriate dictionary (on the basis of query parameter length) needs to be searched. In the particular case of searching only the minor dictionary table, the response rate increases substantially (nearly 100%) as the search space is limited to only a few records.
As a greater number of strings are shifted from the minor dictionary 310 to the major dictionary 314, the performance of the major dictionary 314 starts deteriorating, whereas the performance of the minor dictionary 310 nearly reaches 100%. This happens as the number of strings in the minor dictionary 310 falls below 0.1% of the total dictionary strings, resulting in a fast lookup.
Direct Access Advantage
An important factor to achieve high performance levels may be the ability to have direct dictionary access. Both the dictionaries 310, 314 in the split dictionary approach are of fixed length and thereby maintain direct access.
Attribute Vector Scanning Efficiency
In dictionary encoding approaches, database queries may be performed on the integer identifiers rather than the string values. Therefore, another aspect to consider while analyzing performance gain in the split dictionary approach is that of scanning the attribute vector values. In the block encoding vector 322 technique, there may be a definite improvement in the scan speeds. The percentage improvement, however, may vary substantially as the number of different types of blocks (minor, major, or mixed) varies.
An improvement of 70% for the scenario #4 may be attained, but only improvements in the range of 10-20% for the rest of the scenarios discussed above.
Improved Cache Utilization
The DRAM and cache capacity of conventional systems are limited. The reduction in storage requirements means the number of new cache loads may decrease, and the cache hit rate may improve. This may result in faster searching of strings in the column stores.
Faster Backup & Recovery
In-Memory database systems may have a backup mechanism to maintain high availability and fault tolerance. In SAP HANA, the snapshot approach may be used in which the data is backed up to an SSD (Solid State Drive) or an HDD (Hard Disk Drive) at regular intervals and only the log entries are replayed during recovery. With the reduced memory space of a dictionary, it is obvious that the time taken to perform the I/O (Input/Output) from in-memory systems may be faster for saving the information and the recovery process. While storing the dictionary and attribute values, the database may also backup the dictionary's metadata which may help with memory allocation needed while in recovery mode. Reduced memory usage may therefore lead to faster backups and/or recovery of data.
Easier Reconstruction of a Tuple
Unlike compression techniques like run-length encoding, cluster encoding, and the like, a split dictionary approach may not impact the attribute vector storage. Fixed column widths and row-ids of attribute vectors may be maintained (same as single dictionary approach). In the split dictionary approach, the integer identifiers may be mapped directly to the major or minor dictionary 314, 310. This may ensure a faster response during join operations over multiple columns for accessing the particular row (with row-id) and for fetching a unique string from the dictionary.
Improved Updates of Dictionary and Attribute Vector
The split dictionary approach may improve dictionary updates since the updates only impact one of the dictionaries 310, 314 and the relocation of strings (below the new entry) only needs to be done for one of the dictionaries 310, 314. Particularly in the case of updating the minor dictionary 310, there are significant improvements as the number of records in the minor dictionary 310 may be less than 1% of the total.
The updating of the integer identifiers in the attribute vector may also be improved, depending upon the implementation of the attribute vector mapping to the split dictionaries (as described above).
Advantages in Multi-Core Parallel Processing
For parallel processing, the attribute vector may be partitioned based on a fixed number of records and all the nodes may be run in parallel, for every query. With the usage of block encoding vector 322, there may be a possibility of wisely partitioning the attribute vector, such that a certain percentage of partitions are mapped to a particular dictionary only. The query may not be executed on all of the nodes, as the search parameter (integer identifiers) may map to only one of the dictionaries. This may help in running the query on dictionary specific nodes only, resulting in data locality as well as query load optimization.
Multi-Column Optimization
The BEV concept is independent for each column which can help multiple columns to leverage this advantage in parallel, without having to change row identifiers. The split approach can be used on multiple columns, resulting in optimized AV scanning, an advantage that may not be possible with known existing attribute vector compression techniques like prefix encoding, run length coding, cluster coding and sparse coding. Such compression techniques have a good compression ratio, but the overall performance may be impacted severely due to loss of row identifiers. Also, the key to achieving a high compression ratio may be dependent on the distribution of data and the best compression may be leveraged by sorting the complete AV and then applying one of the conventional compression techniques. Indirect coding algorithms try to address the row identifiers while encoding the values of each block with local dictionary. All these approaches work on a single column of the table and generally after applying the heuristic of compression across columns, one can choose the best fit column for applying these approaches individually or together. In this case, after sorting a single column of the table, the same cannot be applied to other columns, since there is no way of preserving the row identifiers across multiple compressed columns. Therefore, queries across multiple columns may not perform well. However, with the BEV approach, multiple (or all) columns of the table can be optimized simultaneously, thus resulting in an overall improved performance.
In addition to being sold or licensed via traditional channels, embodiments may also, for example, be deployed by software-as-a-service (SaaS), application service provider (ASP), or by utility computing providers. The computer may be a server computer, a personal computer (PC), a tablet PC, a set-top box (STB), a personal digital assistant (PDA), cellular telephone, or any processing device capable of executing a set of instructions (sequential or otherwise) that specify actions to be taken by that device. Further, while only a single computer is illustrated, the term “computer” shall also be taken to include any collection of computers that, individually or jointly, execute a set (or multiple sets) of instructions to perform any one or more of the methodologies discussed herein.
The example computer processing system 1600 includes a processor 1602 (e.g., a central processing unit (CPU), a graphics processing unit (GPU) or both), a main memory 1604 and static memory 1606, which communicate with each other via a bus 1608. The computer processing system 1600 may further include a video display unit 1610 (e.g., a plasma display, a liquid crystal display (LCD) or a cathode ray tube (CRT)). The computer processing system 1600 also includes an alphanumeric input device 1612 (e.g., a keyboard), a user interface (UI) navigation device 1614 (e.g., a mouse, touch screen, or the like), a drive unit 1616, a signal generation device 1618 (e.g., a speaker), and a network interface device 1620.
The drive unit 1616 includes machine-readable medium 1622 on which is stored one or more sets of instructions 1624 and data structures embodying or utilized by any one or more of the methodologies or functions described herein. The instructions 1624 may also reside, completely or at least partially, within the main memory 1604, static memory 1606, and/or within the processor 1602 during execution thereof by the computer processing system 1600, the main memory 1604, static memory 1606, and the processor 1602 also constituting machine-readable, tangible media.
The instructions 1624 may further be transmitted or received over network 1626 via a network interface device 1620 utilizing any one of a number of well-known transfer protocols (e.g., Hypertext Transfer Protocol).
While the machine-readable medium 922 is shown in an example embodiment to be a single medium, the term “machine-readable medium” should be taken to include a single medium or multiple media (e.g., a centralized or distributed database, and/or associated caches and servers) that store the one or more sets of instructions 1624. The term “machine-readable medium” shall also be taken to include any medium that is capable of storing, encoding or carrying a set of instructions 1624 for execution by the computer and that cause the computer to perform any one or more of the methodologies of the present application, or that is capable of storing, encoding or carrying data structures utilized by or associated with such a set of instructions 1624. The term “machine-readable medium” shall accordingly be taken to include, but not be limited to, solid-state memories, and optical and magnetic media.
While the invention(s) is (are) described with reference to various implementations and exploitations, it will be understood that these embodiments are illustrative and that the scope of the invention(s) is not limited to them. In general, techniques for maintaining consistency between data structures may be implemented with facilities consistent with any hardware system or hardware systems defined herein. Many variations, modifications, additions, and improvements are possible.
Plural instances may be provided for components, operations or structures described herein as a single instance. Finally, boundaries between various components, operations, and data stores are somewhat arbitrary, and particular operations are illustrated in the context of specific illustrative configurations. Other allocations of functionality are envisioned and may fall within the scope of the invention(s). In general, structures and functionality presented as separate components in the exemplary configurations may be implemented as a combined structure or component. Similarly, structures and functionality presented as a single component may be implemented as separate components. These and other variations, modifications, additions, and improvements fall within the scope of the invention(s).
Bhatnagar, Vidur Shailendra, Verma, Sudhir
| Patent | Priority | Assignee | Title |
| Patent | Priority | Assignee | Title |
| 4979109, | Jan 27 1986 | Hitachi Ltd. | Method of controlling a data dictionary directory system in a data base system |
| 5210853, | Nov 18 1988 | SHARP KABUSHIKI KAISHA, A CORP OF JAPAN | Electronic device/dictionary with improved information storage: memory capacity ratio |
| 5333313, | Oct 22 1990 | Franklin Electronic Publishers, Incorporated | Method and apparatus for compressing a dictionary database by partitioning a master dictionary database into a plurality of functional parts and applying an optimum compression technique to each part |
| 5956724, | Oct 25 1995 | Microsoft Technology Licensing, LLC | Method for compressing a data file using a separate dictionary file |
| 6804680, | Feb 09 2001 | Hewlett Packard Enterprise Development LP | Extensible database |
| 7496589, | Jul 09 2005 | GOOGLE LLC | Highly compressed randomly accessed storage of large tables with arbitrary columns |
| 7577679, | Dec 29 2006 | TERADATA US, INC | Techniques for extending database date statistics |
| 7868789, | Jun 28 2009 | SAP SE | Dictionary-based order-preserving string compression for main memory column stores |
| 8032499, | May 21 2007 | SAP SE | Compression of tables based on occurrence of values |
| 8051045, | Aug 31 2005 | SAP SE | Archive indexing engine |
| 8145662, | Dec 31 2008 | PayPal, Inc | Methods and apparatus for generating a data dictionary |
| 8326810, | May 21 2007 | SAP SE | Block compression of tables with repeated values |
| 8352510, | Apr 29 2008 | SAP SE | Dynamic database schemas for highly irregularly structured or heterogeneous data |
| 8627006, | Aug 19 2009 | Oracle International Corporation | Storing row-major data with an affinity for columns |
| 20100030796, | |||
| 20100281004, | |||
| 20100318903, | |||
| 20120221528, | |||
| 20120323927, | |||
| 20130151567, | |||
| 20140188818, | |||
| EP1859370, | |||
| WO2005072047, |
| Executed on | Assignor | Assignee | Conveyance | Frame | Reel | Doc |
| Dec 03 2013 | BHATNAGAR, VIDUR SHAILENDRA | SAP AG | ASSIGNMENT OF ASSIGNORS INTEREST SEE DOCUMENT FOR DETAILS | 031729 | /0091 | |
| Dec 05 2013 | VERMA, SUDHIR | SAP AG | ASSIGNMENT OF ASSIGNORS INTEREST SEE DOCUMENT FOR DETAILS | 031729 | /0091 | |
| Dec 06 2013 | SAP SE | (assignment on the face of the patent) | / | |||
| Jul 07 2014 | SAP AG | SAP SE | CHANGE OF NAME SEE DOCUMENT FOR DETAILS | 033625 | /0223 |
| Date | Maintenance Fee Events |
| Jun 01 2016 | ASPN: Payor Number Assigned. |
| Oct 17 2016 | ASPN: Payor Number Assigned. |
| Oct 17 2016 | RMPN: Payer Number De-assigned. |
| Oct 30 2019 | M1551: Payment of Maintenance Fee, 4th Year, Large Entity. |
| Nov 01 2023 | M1552: Payment of Maintenance Fee, 8th Year, Large Entity. |
| Date | Maintenance Schedule |
| May 10 2019 | 4 years fee payment window open |
| Nov 10 2019 | 6 months grace period start (w surcharge) |
| May 10 2020 | patent expiry (for year 4) |
| May 10 2022 | 2 years to revive unintentionally abandoned end. (for year 4) |
| May 10 2023 | 8 years fee payment window open |
| Nov 10 2023 | 6 months grace period start (w surcharge) |
| May 10 2024 | patent expiry (for year 8) |
| May 10 2026 | 2 years to revive unintentionally abandoned end. (for year 8) |
| May 10 2027 | 12 years fee payment window open |
| Nov 10 2027 | 6 months grace period start (w surcharge) |
| May 10 2028 | patent expiry (for year 12) |
| May 10 2030 | 2 years to revive unintentionally abandoned end. (for year 12) |