Blogs

Informix Warehouse Accelerator

Shockingly fast performance

At the end of March, IBM announced the new Informix Warehouse Accelerator (IWA) for Informix 11.70.xC2. I have been beta testing this accelerator for a while and would like to share some of my findings with you. IWA is an exciting breakthrough—combining new data warehouse technology with traditional Informix relational database servers—that results in very fast performance. My benchmark run of 16 queries went from 9 hours and 40 minutes to just over 15 minutes using this new technology. The chart in Figure 1 shows the results of my benchmark testing.


Figure 1: These test results compare the performance of Informix with and without Informix Warehouse Accelerator.

IWA technology loads the records from a data warehouse star schema into memory in a compressed format, and organizes the data using new columnar storage technology. The records are not stored as rows but as columns of data that can be very quickly accessed using this new technology. When an application sends a query to Informix, the Informix optimizer decides whether the query should be sent to IWA. When the Informix server gets the data back from IWA, it returns the results to your application—only much faster than before (Figure 2). Your applications do not need to change or be recompiled to benefit from the speed of IWA. My favorite business intelligence query tools continue to work unchanged with Informix: Cognos, Hyperion, Pentaho—they all just work. All ODBC, JDBC, or ESQL/C applications connect to Informix and do not even need to know that they are connecting to IWA.

From one hour to nine minutes

Based on my testing, one-hour queries run in two to three minutes with IWA. Disk access is the slowest part of a data warehouse query, and when you need to read millions of records to get a result set for a query, the performance of your disks determines how fast you can get data back. The best performance gains with IWA are in table scans of large fact tables, where disk activity is normally the limiting factor.

Over the years, I have built up a midsize data warehouse star schema that I use for testing new releases of Informix, and I used it for this benchmark. It contains a fact table of 630 million records, a customer dimension of 2.5 million customers, and three small dimension tables for organization, programs, and commodities. It uses real data on tracking government payments to farmers, which is available to the public by request.

The 16 queries in the benchmark were based on real application query requests I have seen from business users. Some seem simple, like the first one in the chart in Figure 1. The objective is to find the top 1,000 farmers that received payments. This query requires reading all 630 million records, sorting and summing the payments by farmer, and returning 1,000 records. This query can take hours to run on big servers with expensive storage systems.

Running this query without IWA on Informix took more than an hour to read, gather, sort, and sum all the data. The data set was too large to fit into Informix’s memory buffers. Running this query with IWA took nine minutes, and we were able to reduce the size of the Informix memory buffers. Both of these tests were from a cold start with no data in the memory buffers. Running the same query a second time on Informix alone did not speed things up much because all the data would not fit in memory and the buffers were continually thrashing. In contrast, the same query run a second time with IWA finished in less than a minute.

Figure 2: Informix manages the Informix Warehouse Accelerator so that applications do not need to be aware of its presence.

When Informix takes over

For some queries, IWA does not improve performance. As the chart in Figure 1 shows, most queries that returned nine or fewer records and can use an index are faster without IWA. Most were very fast queries, and Informix returned the results in less than a second. IWA does not handle all types of queries yet, such as correlated sub-queries or queries using temp tables. One of my tests used a query to select records into a temp table and then a second query to further refine the results from the temp tables. IWA could send the results from the first query into a temp table, and that was very fast. But since the temp tables had not been defined and realized in IWA, Informix handled the second query as a normal query. Any query that cannot be optimized by IWA is optimized and handled by Informix without users even knowing it, except that they will notice the slower response time.

A database must be designed using a dimensional model star schema to work with IWA, and the tables must be defined and loaded into IWA to take advantage of its performance. Load times were very fast, comparable to creating some of the summary tables I traditionally build in a data warehouse. In fact, I see IWA replacing the need for many of the summary tables I design in a data warehouse.

I have been building data warehouses with Informix for more than 18 years, and it has been getting better and faster with each new release. The Informix Warehouse Accelerator really raises the performance level that we can get and expect from a data warehouse. It is also exciting to see IBM invest so heavily in Informix and its role as a data warehouse engine.

[followbutton username='lesterknutsen' count='false' lang='en' theme='light']
 
[followbutton username='IBMdatamag' count='false' lang='en' theme='light']