Available with the IBM DB2®10.5 release, BLU Acceleration delivers unparalleled performance improvements for analytic applications and reporting using dynamic in-memory optimized columnar technologies. BLU Acceleration is one of the most significant pieces of technology that has ever been delivered in DB2 and arguably in the database market in general. This IBM® Redbooks® Solution Guide provides an overview of the DB2 with BLU Acceleration feature.

IBM DB2® with BLU Acceleration is a revolutionary technology, delivered in DB2 for Linux, UNIX, and Windows Release 10.5. Using dynamic in-memory columnar technologies, BLU Acceleration enables organizations to perform analytic queries at breakthrough speeds.

With query response times up to 100 times faster than earlier systems, BLU Acceleration provides a simple, fast, and easy-to-use solution for organizations that seek rapid access to business answers. Armed with this new information, organizations can lower costs and quickly gain an edge on competitors that are slower to draw insight from their data.

Yes, the industry is abuzz with discussion of in-memory columnar data processing. Yet BLU Acceleration offers much more. For example, it delivers significant improvements in database compression but does not require you to have all your data in memory.

Furthermore, BLU Acceleration is built directly into the DB2 kernel (see Figure 1). It is not just an additional feature; it is an integral part of DB2 and every part of DB2 is aware of it. BLU Acceleration still uses the same storage unit of pages, the same buffer pools, and the same backup and recovery mechanisms, so BLU Acceleration tables can coexist with traditional row tables in the same schema, storage, and memory, and you can query a mix of traditional row and BLU Acceleration tables at the same time.


Figure 1. DB2 10.5 engine with BLU Acceleration


Did you know?

BLU Acceleration from IBM differs from solutions that other vendors offer in that it allows the unified computing of online transaction processing (OLTP) and analytics data inside a single database. This helps remove barriers and speed results for users.


Business value

Although the cost of memory continues to fall, data growth is exponential, so analytics systems that require all data to be kept in memory are no longer practical. This is where BLU Acceleration technology enters the picture, delivering in-memory performance even when the need for active data is bigger than the available memory space.

Extends existing investments

DB2 with BLU Acceleration runs on existing infrastructure, helping to extend the return on investments that the organization has already made. With relatively loose hardware requirements, system configurations can be optimized to meet specific cost targets and service levels. As data volumes increase, the cost of manipulating each piece of the data decreases dramatically.

Customers can choose between IBM POWER or x86 processor architectures. In addition, Advanced Workload Management capabilities enhance transactional and analytics operations while supporting more concurrent users at higher service levels than alternative solutions.

Proven business support

BLU Acceleration runs on DB2, so it builds upon the business-proven performance, reliability, and security of the platform while adding its own unique memory management and query optimization features.

DB2 is used around the world for online transactional processing (OLTP) and online analytic processing (OLAP) workloads, with decades of high-availability performance and a reputation for flexible scalability, both up and out. DB2 has consistently recorded top results in industry-standard benchmark tests, with multiple 100 TB-and-larger systems in production use every day.

Easy to adopt and use

The real breakthrough in BLU Acceleration is in how easily organizations can adopt the technology. Competing solutions often require migration to new platforms, database software, and business applications, making the transition both costly and resource intensive. But, because BLU Acceleration is integrated into DB2, you can move into it at your own pace.

The move to column-based tables can be accomplished one at a time or all at once. For guidance, DB2 includes tools that help IT personnel assess the impact of using BLU tables in different workloads and analytical environments.


Solution overview

In introducing BLU Acceleration, IBM did not just bolt a new component onto DB2. Support for the new solution already existed within the DB2 engine, so there is no new skill gap for DBAs and developers to overcome.

Simply stated, you need only to install DB2 10.5 on a supported platform and enable a single configuration parameter to tell DB2 that you want to optimize the default settings for analytical workload characteristics. After you create your database, create your tables, and load the needed data using common DB2 tools; your workloads immediately run faster and without the need for SQL syntax or schema changes.

At the center of BLU Acceleration is a column-organized table store combined with actionable compression that operates on a column and page level to save storage space. The column organization eliminates the need for creating and maintaining secondary indexes and aggregates. In DB2 10.5, both column-organized and traditional row-organized tables can coexist in the same database.

DB2 with BLU Acceleration includes several features that work together to make it a significant advancement in technology:

• Simplicity and ease-of-use

Purchasing new hardware for a BLU Acceleration deployment might not be necessary. Autonomics and intelligence is built into the DB2 engine to optimize your hardware for analytic workloads. The goal is to minimize deployment complexity and database maintenance tasks for DBAs.

Manual tuning efforts are minimized, replaced with a single registry variable (DB2_WORKLOAD=ANALYTICS) that automates initial tuning of an analytics database environment. With this registry variable set, the database manager and database parameters are automatically tuned by the DB2 engine to optimize analytic-type workloads.

BLU Acceleration is simple to implement and easy to use. BLU Acceleration tables coexist with traditional row-organized tables in the same database, the same table spaces, and the same buffer pools. All you have to do is create your BLU Accelerated tables, load the data into tables, and run your queries.



• Column store

BLU Acceleration uses dynamic in-memory columnar technology. Each column is physically stored in a separate set of data pages.

This column-organized approach brings many benefits to analytic workloads, which compared to other workloads tend to involve more table joins, grouping, aggregates, and so on, and often only access a subset of columns in a query. By storing data in column-organized tables, DB2 only needs to pull the wanted column data into memory rather than the entire row. As a result, more column data values can be packed into processor cache and memory, significantly reducing I/O from disks

Column-organized tables typically compress much more effectively. BLU Acceleration compresses data by column; the probability of finding repeating patterns on a page increases significantly when the data on the page is from the same column. In addition, being able to store both row-organized and column-organized tables in the same database allows you to use BLU Acceleration even in database environments that require mixed OLTP and OLAP workloads.

• Adaptive compression

A key aspect of DB2 with BLU Acceleration is the manner in which data is encoded on disk, which enables significant compression and, because DB2 uses a different encoding scheme, allows data to be scanned or compared while it remains encoded. The ability to scan compressed data allows DB2 to delay materialization until absolutely necessary, and do as much work as possible without decoding the data. This, in turn, conserves processing power and I/O throughput.

BLU Acceleration stores data using column organization, so each column is compressed with its own compression dictionaries. When BLU Acceleration compresses data, it uses traditional adaptive mechanisms plus a form of Huffman encoding, in which data is compressed based on the frequency of values in a column (so values that appear many times are compressed more than other values that do not appear as often). For instance, if a column displays the states of the United States, the relative populations of the states provides the likelihood that California will be listed many more times than Vermont. So DB2 can encode California with a short, single bit (1), and encode Vermont with eight bits (11011001). For even greater compression, DB2 combines this feature with prefix encoding and offset coding for better compression in various scenarios.

In addition, because BLU Acceleration handles query predicates without decoding the values, more data can be packed in processor cache and buffer pools. The result is less disk I/O, better use of memory, and more effective processor utilization. Query performance is better and storage needs are significantly reduced.



• Parallel vector processing

Single-instruction, multiple data (SIMD) is a common processor technology. When SIMD-enabled hardware is used, DB2 with BLU Acceleration has special algorithms to take advantage of the built-in parallelism of the processors. In this way, you can use special hardware instructions that work on multiple data elements with a single instruction.

For instance, without SIMD, for a query that requires a predicate comparison of sales orders sent in December 2013 (see Figure 2), the data elements must be processed one at a time, with multiple iterations needed to process a single instruction. With BLU Acceleration using both SIMD processor parallelism and efficient columnar compression, multiple encoded data values can be packed into the processor register at the same time. All compressed September, October, November, and December data values for a single comparison can be taken concurrently (if they are in the same processor register), so predicate processing is much faster.




Figure 2. Efficient use of SIMD processor packing in BLU Acceleration

If SIMD-enabled processors are not available in your current hardware, BLU Acceleration still functions and speeds query processing using other features. In some cases, BLU Acceleration even uses its advanced algorithms to emulate SIMD parallelism to deliver similar results.

• Core-friendly parallelism

BLU Acceleration is a dynamic in-memory technology that makes efficient use of all processor cores in the system, so queries are processed using multi-core parallelism and scale across processor sockets. The idea is to maximize processing from processor caches while minimizing latencies.

In developing BLU Acceleration, much effort was invested in comprehensive algorithms for what is called core-friendly parallelism. The algorithms are designed so that data that is likely to be revisited is carefully placed and aligned in the processor cache lines. This maximizes the hit rate to the processor caches and increases the effectiveness of the cache lines.

Figure 3 shows an example. When a query is run, BLU Acceleration uses a separate agent for each available processor core to fetch individual column data values. Each agent can then work on different query functions. In the figure, each processor core (0, 1, 2, and 3) works on fetching data for different columns being queried. This is only one level of parallelism.



Figure 3. Multiple cores are used in parallel on multiple columns with BLU Acceleration

Figure 4 illustrates another level of parallelism. BLU Acceleration has intelligent algorithms that place and align data onto processor caches. This helps reduce the number of cache misses and maximizes processor cache hit rates. With data being available for reading from the processor cache without reading from the main memory or disks, query performance is improved significantly.


Figure 4. BLU Acceleration optimizes data alignment in cache lines

• Scan-friendly memory caching

BLU Acceleration is also designed for the demands of a big data world, where all of the target data for queries are less likely to fit into memory. Although DB2 can always benefit from having more hot (active) data in memory, it is not a requirement.

BLU Acceleration includes a set of big data-aware algorithms for cleaning out memory pools that are more advanced than the typical Least Recently Used (LRU) algorithms that are associated with traditional, row-organized database technologies. The new algorithms detect data patterns that are likely to be revisited and then hold those pages in the buffer pool as long as possible. When this is combined with BLU Acceleration’s advanced columnar compression, even more hot data can fit into the buffer pool. Typical memory-to-disk ratios are 15 - 50 percent, but with BLU Acceleration, in many cases, up to 70 - 80 percent of active data can fit into memory.



• Data skipping

To speed query processing and reduce unnecessary I/O, BLU Acceleration can skip ranges of data that are not relevant to the current query. As an example, for a report about sales orders from December 2013, scanning them from October, November, and other months is unnecessary. BLU Acceleration goes straight to the sales orders for the target month and proceeds with the query.

This data skipping is accomplished by loading data into column-organized tables. BLU Acceleration tracks the minimum and maximum values in the various rows, using metadata objects called synopsis tables. When a query is run, BLU Acceleration looks at the synopsis tables for ranges of data that contain the value matching the query and skips directly to that point. Only necessary data is read or loaded into system memory, which speeds query execution by avoiding unnecessary scanning.

• Raw data: 12 TB
• Database server: SIMD-enabled processor, 30 cores, and 240 GB of memory
• Database: Two years of data (2012-2013)
• Sales order table: 100 columns

• Query

SELECT SUM(ORDER_TOTAL) FROM SALES_FACT WHERE MONTH='201312'



• Columnar Compression

From 12 TB of raw data, we conservatively assume a five times (5x) compression rate, reduces the size to 6 TB. This is a conservative assumption because BLU Acceleration is often observed with 10x compression rates.



• Column Store

From the total of 100 columns, the query accesses only those for ORDER_TOTAL and MONTH. This method of accessing column-organized tables reduces the target data to just two of the 100 columns, representing a mere 1/50th of the compressed data or 120 GB.

• Data Skipping

By using synopsis tables, BLU Acceleration skips to the data that matches the query predicate without decoding or evaluation processing. The query accesses just the ORDER_TOTAL for December 2013, or 1/24th of the records in the entire database, reducing target data to 5 GB (120 GB / 24 = 5 GB).



• Core-Friendly Parallelism

Data is processed in parallel across the 30 available processor cores, so each core needs to process only 167 MB of data (5 GB / 30 = 167 MB).



• Parallel Vector Processing

Data can be scanned using a SIMD-enabled processor to achieve faster performance. The amount of improvement depends on the processors being compared. Using a conservative assumption that our SIMD-enabled processor obtains each byte four times faster, DB2 will have to scan only about 42 MB of data (167 MB / 4 = approximately 42 MB).

• Reporting queries run faster when column-organized tables are used.
• Performance improvements can be achieved with little or no database tuning. Table statistics are collected automatically, and reorganizing tables or creating indexes for specific queries, statistical views, or database hints is usually unnecessary.
• Thanks to the faster query performance, the need for aggregates can be reduced or potentially eliminated. The SAP BW Accelerator might become obsolete, which greatly improves the extract, transform, and load (ETL) processing time.
• Except for primary key and unique constraints, column-organized tables have no indexes, so during ETL processing, hardly any time must be spent on index maintenance and index logging.

• IBM DB2 BLU Acceleration Kit for Trial: Install a fresh operating system and DB2 software stack together, with minimal system setup hassle. For more information and to download the kit, visit our Information Management Solution Portal:

http://ibm.co/15zDz3C

• IBM DB2 with BLU Acceleration for Cloud Beta: Access DB2 with BLU Acceleration on the cloud with your browser for up to 5 hours. To learn more, visit this website:

https://help.sap.com/docs/r/77e3a615f633437e9c4549891bb6bad7/CURRENT_VERSION/en-US/4a799891bf0d42c682ba890e1f80a2fe.html

• IBM DB2 BLU Boot Camp or IBM Education: Attend boot camps and education classes with presentations and hands-on lab exercises to learn about BLU Acceleration. Contact your IBM representative for details.

• Leveraging DB2 10 for High Performance of Your Data Warehouse, SG24-8157:
  https://www.redbooks.ibm.com/abstracts/sg248157.html?Open
  
• IBM Cognos Dynamic Cubes, SG24-8064:
  https://www.redbooks.ibm.com/abstracts/sg248064.html
  
• DB2 Workload Manager for Linux, UNIX, and Windows, SG24-7524:
  https://www.redbooks.ibm.com/abstracts/sg247524.html
  
• InfoSphere Warehouse: A Robust Infrastructure for Business Intelligence, SG24-7813:
  https://www.redbooks.ibm.com/abstracts/sg247813.html?Open