Patent Application
20140074819
Relational database query processing has been optimized for a traditional processing model that assumes a set of tightly-coupled, very fast central processors and a very large (on the order of 100 Terabytes), but relatively slow disk system, which has sufficient capacity to store all of the needed tables. A virtualized memory, including terabytes of main memory, between the processor and the disk system helps avoid costly disk I/O operations. However, the fast central processors and large disk systems are expensive and consume large amounts of power (on the order of 10 kW).
New lower cost and lower power processing models are available, partly because the cost per bit of main memory such as DRAM has dropped substantially. One such model is a cluster having a large number of processing units, each including a low-speed processor, modest amounts of main memory compared to the amount of storage in a disk system, and no persistent storage by which the main memory is virtually extended. In this model, a cluster may have as many as 1000 processing units.
The large number of processing units in a cluster has very high aggregate computing power and memory, if each of the processing units can be properly utilized. This potentially high performance makes a cluster attractive for query processing, but the disk-based model poses problems when the query processing is moved to in-memory database processing.
A more complete appreciation of the embodiments and many of the attendant advantages thereof will be readily obtained as the same becomes better understood by reference to the following detailed description when considered in connection with the accompanying drawings, wherein:
An embodiment adapts the disk-based query processing to in-memory database query processing, which requires that the amount of memory and the compute and memory bandwidth used be minimized, while maintaining performance. To minimize memory and bandwidths, data structures on which the queries operate are adapted to the size of the memory. To maintain performance, an optimal set of auxiliary data structures is kept in memory.
In an embodiment depicted in
The computer system determines the cost of each candidate encoding Ek in step 204, after determining candidate encodings for a given column Cj, in step 202. In an embodiment, the cost reflects a given representative query workload, where the workload is characterized by types of operations, such as projections, groupings, and sorting operations, described in more detail below. In particular, for each given column Cj, statistics are gathered for each type of operation Oi and for all queries (∀Qm) in the workload, where the statistics include the average number of rows processed R[Cj, Oi], the fraction of query processing time (or estimated cost) taken by the operation on the column F[Cj, Oi], and the average selectivity for filters on the column S[Cj, Oi]. The cost is computed from these statistics according to the following function:
ΣoCost[Cj,Oi]=Σo(Costbasic[Cj,Oi]·AveLengthE
The system selects the encoding having the minimum memory or minimum cost, after the cost is determined.
As mentioned above, the cost of each candidate encoding in one embodiment is based on operations found in a representative query workload. The types of operations considered in such a workload include at least filter, standard operators and functions, projection, grouping, and sorting operations.
Filter operations includes relational comparison operator such as “=”, “<”, “>” on the value of the column or the value of applying a function/operator on the column.
Standard operators and functions include arithmetic operators such as “+” and “−”, string operations such as concatenation, “upper”, “substr”, as well as aggregation functions like “sum.”
Projection operations involve decoding the data representation (decompressing) to get the value in the original representation for that data type, e.g., text for strings.
Grouping operations involve gathering all rows that have the same value for the group-by-column(s). Such operations typically involve the calculation of a hash function and probing of a hash table.
Sorting operations include sorting a relation or sub-relations by a set of columns.
Many query processes benefit from auxiliary structures, which speed up the processing of the query. Such structures include B-Trees, sorted representations, bit maps, bloom filters, and indexes.
B-Trees benefit columns that are frequently filtered with highly selective range predicates. The benefit of a B-Tree is even higher if the column is frequently the subject of a sort operation.
Sorted representations are arrays in which entries are (column-value, row-id) pairs sorted by column value. Columns that are frequently the subject of a sort operation benefit from sorted representations.
Bitmap structures are very useful for low-cardinality columns that are frequently filtered using equality conditions. The column has one bitmap for each distinct value and each bit map has as many bits are there are rows. For each row, the corresponding bit indicates whether the column's value in that row is the value of that the bitmap represents.
Bloom filter structures, in which hash functions map a set element to a bit array, are useful for filter operations having equality predicates. A bloom filter that represents a summary of the data values in a chunk can be used to skip a chunk if the given value is not present in the chunk (as indicated by a bit not present in the bloom filter).
Given a certain quantity of available memory, it is desirable to choose a set of auxiliary structures that provides the most benefit at the least cost to in-memory database query operations, specifically, operations on columns. Therefore, the system chooses in step 302 of
For a given column and for each candidate structure in a set of auxiliary structures, an embodiment of the system determines, in step 304, a measure of the benefit Bi that the candidate structure can provide to the given column and the cost, which is the amount of memory Mi that the candidate auxiliary structure uses. The embodiment then decides, in step 306, on the subset of auxiliary structures that provide the most benefit for the available memory.
To help determine the cost of an auxiliary structure, available memory is divided into a number L of equal-sized chunks. Thus, the amount Mi of memory needed for an auxiliary structure has a range of 1 to L chunks, meaning the structure can occupy one chunk or the entire amount of available memory. The actual number of chunks needed for an auxiliary structure depends on the length of the column as well as the type of auxiliary structure.
The benefit Bi of an auxiliary structure i is a sum, over all applicable operations, of a product of an estimate of the improvement per row that the auxiliary structure provides for each applicable operation, the number of rows for the operation on that column R[Cj, Oi], and the query cost fraction F[Cj, Oi]. Thus, the benefit of a particular structure is
Bi=Σo(improvement·R[Cj,Oi]·F[Cj,Oi]). (2)
Given a set of benefits B={B1 . . . BN} for each candidate auxiliary structure and a set of costs M={M1 . . . MN}, in terms of memory needed, for each candidate structure, where N is the total number of candidate structures, the system computes the optimal set. Specifically, the system computes a function ƒ (B, M) whose output is a pair of sets T and S, where T includes benefit data for every combination of N auxiliary structures and L memory sizes, and S includes entries that identify the particular auxiliary structures that provide the benefit for the corresponding entry in T. The final item in T is the optimal benefit and the final item in S indicates the structures that provide the optimal benefit.
In one embodiment, the function ƒ (M, B) is the one depicted in
M[j]>i (3)
T(i,j−1)>T(−M[j],j−1)+B[j] (4)
T(i,j)=T(i,j−1) (5)
S(i,j)=S(i,j−1) (6)
T(i,j)=T(−M[j],j−1)+B[j] (7)
S(i,j)={Set the jth bit in S(i−M[j],j−1)}, (8)
As an example, suppose that a particular auxiliary structure at j=1 has a benefit of 5 and uses 7 chunks of memory. When the cost is too large (7>[1 . . . 6]), the previous column to be copied to the current column. When the cost is not too large (7≯[ 8 . . . L]), then T is updated with the benefit 5, and S has its bitmap updated to indicate that structure j provided the benefit. Each succeeding row in T is updated with the benefit 5 and each bit map is updated in S, until the final row in the column is reached.
runtime Encoding
The system encodes, in real-time, each candidate column Cj with the encoding selected for the column. This means that while the system is processing in-coming queries it is also encoding columns in relations that may be the subject of an in-coming query.
To get the encoding process and the query processing to cooperate with each other, memory is partitioned into a set of chunks, query processing operates serially over the chunks, and each type of processing obtains a lock to operate on one of the chunks, as depicted in
According to one embodiment, the techniques described herein are implemented by one or more special-purpose computing devices. The special-purpose computing devices may be hard-wired to perform the techniques, or may include digital electronic devices such as one or more application-specific integrated circuits (ASICs) or field programmable gate arrays (FPGAs) that are persistently programmed to perform the techniques, or may include one or more general purpose hardware processors programmed to perform the techniques pursuant to program instructions in firmware, memory, other storage, or a combination. Such special-purpose computing devices may also combine custom hard-wired logic, ASICs, or FPGAs with custom programming to accomplish the techniques. The special-purpose computing devices may be desktop computer systems, portable computer systems, handheld devices, networking devices or any other device that incorporates hard-wired and/or program logic to implement the techniques.
For example,
Computer system 600 also includes a main memory 606, such as a random access memory (RAM) or other dynamic storage device, coupled to bus 602 for storing information and instructions to be executed by processor 604. Main memory 606 also may be used for storing temporary variables or other intermediate information during execution of instructions to be executed by processor 604. Such instructions, when stored in non-transitory storage media accessible to processor 604, convert computer system 600 into a special-purpose machine that is customized to perform the operations specified in the instructions.
Computer system 600 further includes a read only memory (ROM) 608 or other static storage device coupled to bus 602 for storing static information and instructions for processor 604. A storage device 610, such as a magnetic disk or optical disk, is provided and coupled to bus 2902 for storing information and instructions.
Computer system 600 may be coupled via bus 602 to a display 612, such as a cathode ray tube (CRT), for displaying information to a computer user. An input device 614, including alphanumeric and other keys, is coupled to bus 602 for communicating information and command selections to processor 604. Another type of user input device is cursor control 616, such as a mouse, a trackball, or cursor direction keys for communicating direction information and command selections to processor 604 and for controlling cursor movement on display 612. This input device typically has two degrees of freedom in two axes, a first axis (e.g., x) and a second axis (e.g., y), that allows the device to specify positions in a plane.
Computer system 600 may implement the techniques described herein using customized hard-wired logic, one or more ASICs or FPGAs, firmware and/or program logic which in combination with the computer system causes or programs computer system 600 to be a special-purpose machine. According to one embodiment, the techniques herein are performed by computer system 600 in response to processor 604 executing one or more sequences of one or more instructions contained in main memory 606. Such instructions may be read into main memory 606 from another storage medium, such as storage device 610. Execution of the sequences of instructions contained in main memory 606 causes processor 604 to perform the process steps described herein. In alternative embodiments, hard-wired circuitry may be used in place of or in combination with software instructions.
The term “storage media” as used herein refers to any non-transitory media that store data and/or instructions that cause a machine to operation in a specific fashion. Such storage media may comprise non-volatile media and/or volatile media. Non-volatile media includes, for example, optical or magnetic disks, such as storage device 610. Volatile media includes dynamic memory, such as main memory 606. Common forms of storage media include, for example, a floppy disk, a flexible disk, hard disk, solid state drive, magnetic tape, or any other magnetic data storage medium, a CD-ROM, any other optical data storage medium, any physical medium with patterns of holes, a RAM, a PROM, and EPROM, a FLASH-EPROM, NVRAM, any other memory chip or cartridge.
Storage media is distinct from but may be used in conjunction with transmission media. Transmission media participates in transferring information between storage media. For example, transmission media includes coaxial cables, copper wire and fiber optics, including the wires that comprise bus 602. Transmission media can also take the form of acoustic or light waves, such as those generated during radio-wave and infra-red data communications.
Various forms of media may be involved in carrying one or more sequences of one or more instructions to processor 604 for execution. For example, the instructions may initially be carried on a magnetic disk or solid-state drive of a remote computer. The remote computer can load the instructions into its dynamic memory and send the instructions over a telephone line using a modem. A modem local to computer system 600 can receive the data on the telephone line and use an infra-red transmitter to convert the data to an infra-red signal. An infra-red detector can receive the data carried in the infra-red signal and appropriate circuitry can place the data on bus 602. Bus 602 carries the data to main memory 606, from which processor 604 retrieves and executes the instructions. The instructions received by main memory 606 may optionally be stored on storage device 610 either before or after execution by processor 604.
Computer system 600 also includes a communication interface 618 coupled to bus 602. Communication interface 618 provides a two-way data communication coupling to a network link 620 that is connected to a local network 622. For example, communication interface 618 may be an integrated services digital network (ISDN) card, cable modem, satellite modem, or a modem to provide a data communication connection to a corresponding type of telephone line. As another example, communication interface 618 may be a local area network (LAN) card to provide a data communication connection to a compatible LAN. Wireless links may also be implemented. In any such implementation, communication interface 618 sends and receives electrical, electromagnetic or optical signals that carry digital data streams representing various types of information.
Network link 620 typically provides data communication through one or more networks to other data devices. For example, network link 620 may provide a connection through local network 622 to a host computer 624 or to data equipment operated by an Internet Service Provider (ISP) 626. ISP 626 in turn provides data communication services through the world wide packet data communication network now commonly referred to as the “Internet” 628. Local network 622 and Internet 628 both use electrical, electromagnetic or optical signals that carry digital data streams. The signals through the various networks and the signals on network link 620 and through communication interface 618, which carry the digital data to and from computer system 600, are example forms of transmission media.
Computer system 600 can send messages and receive data, including program code, through the network(s), network link 620 and communication interface 618. In the Internet example, a server 630 might transmit a requested code for an application program through Internet 628, ISP 626, local network 622 and communication interface 618.
The received code may be executed by processor 604 as it is received, and/or stored in storage device 610, or other non-volatile storage for later execution.
In the foregoing specification, embodiments have been described with reference to numerous specific details that may vary from implementation to implementation. The specification and drawings are, accordingly, to be regarded in an illustrative rather than a restrictive sense. The sole and exclusive indicator of the embodiments, and what is intended by the applicants to be the scope of embodiments, is the literal and equivalent scope of the set of claims that issue from this application, in the specific form in which such claims issue, including any subsequent correction.
