Informix DBA: Informix Warehouse Accelerator

Shockingly fast performance

The Informix Warehouse Accelerator combines new data warehouse technology with traditional relational database technology. Learn how the author's benchmark went from 9 hours and 40 minutes to just over 15 minutes using this new technology.

This article was originally published in IBM Data magazine.

Lester Knutsen, President, Advanced DataTools Corp.

Lester Knutsen is president of Advanced DataTools Corporation, an IBM Informix consulting and training partner specializing in data warehouse development, database design, performance tuning, and Informix training and support. He is president of the Washington, D.C. Area Informix User Group, a founding member of IIUG, an IBM Gold Consultant, and an IBM Data Champion.



01 August 2011

Also available in Chinese

At the end of March 2011, 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 Table 1 shows the results of my benchmark testing.

Table 1. Performance of Informix with and without Informix Warehouse Accelerator
RunQueryType of QueryIDS onlyIDS & IWAImprovement multipleRows returned
1Query12Scan - Select First 1000105m7.7309m48.07110.72611000
2Query13Index - by Dim0m0.1160m11.6050.01009
3Query14Index - by Dim0m0.0550m11.9710.00469
4Query15Index - by Dim0m8.3450m4.4691.86739
5Query0Scan - Select First 1000106m44.4740m20.946305.76121000
6Query10Index - by Dim0m4.8170m6.0620.79461
7Query11Index - by Dim0m3.4300m6.0680.56531
8Query1Scan - Select First 10063m33.0831m13.50151.8780100
9Query2Scan - Select First 10065m17.0751m29.51843.7574100
10Query3Scan - Sum by Dim87m47.8420m17.207306.14533107
11Query4Scan - Select First 10053m57.1690m14.162228.5813100
12Query5Index - by Dim0m0.0490m12.6920.00390
13Query6Scan - Select First 10001m48.8860m9.49511.46771000
14Query7 Scan - by Dim w Group by0m15.8550m9.1711.728826937
15Query8Scan - by Dim w Group by0m14.8460m2.3476.325528358
16Query9Scan - by Dim w Group by79m14.2530m42.011113.1669 523499
Total in hours9 hr 40 min15 min 32 sec

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 1). 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 Table 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 1. Informix managing the Informix Warehouse Accelerator
Query applications send requests to Informix data warehouse server, which communicates with IWA compressed in memory tables

When Informix takes over

For some queries, IWA does not improve performance. As the chart in Table 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.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=749785
ArticleTitle=Informix DBA: Informix Warehouse Accelerator
publish-date=08012011