The Informix Warehouse Accelerator (IWA) is a software appliance for the Informix database server, using in-memory, columnar data storage for business intelligence applications doing OLAP. For such workloads IWA typically can achieve a query acceleration of 10 - 100 times faster than running the workload within the Informix database server. With this post we start a casual series of articles on IWA in general as well as specifics for its deployment in cloud environments like Bluemix.
Part 1: Installation, Configuration and Setup
The Informix on Cloud hosted offering in Bluemix comprises the Enterprise Advanced Edition which includes IWA. While IWA is always tightly coupled with the Informix database server, it can also be run in a separate cloud environment to allow maximum use of the provisioned resources. By default IWA is not readily installed in the Informix on Cloud hosted offering environment, but the installation file is present. In this post we will give a brief walk-through of the installation, configuration and setup of IWA. The given examples are for the case when IWA is installed and run on the same machine as the Informix database server, but they can easily be adopted for the case where IWA is to be run on a separate machine.
The installation medium in the cloud environment is
IWA can be installed in the same directory of an existing Informix database server. This later avoids switching environments for IWA administration commands. Before unpacking the installation medium, we prepare a minimum of environment variables:
With the environment set, as user "informix" we create the INFORMIXDIR if necessary, then change to it and unpack the medium:
Next, we run the installation program as user "root" and follow the interactive installation procedure. We just do the installation without configuring IWA:
The installation program tells us, that it has not configured IWA and recommends that we review and customize the default configuration. Once the installation program has finished we are ready to do this as described in the next section.
Configuring IWA is quite simple. The most important configuration parameters are how much memory IWA can use for data mart data and how much CPU resources it is expected to utilize. Disk space for the copy of the compressed data marts kept in memory usually is less problematic because disk space normally is more abundant than main memory.
In the cloud environment we find disk space mounted to /data . We can check the space with "df". Then we create - as user informix - a new subdirectory where IWA can put its data. In our example we name it simply "iwa":
To configure also memory and CPU resources for IWA, we quickly check, how much of these resources there are and that system parameters are set correctly:
Check CPU resources:
Check memory availability:
Check the size of "/dev/shm":
On Linux systems the default size of "tmpfs" usually is half of physical memory. Normally this should be OK as data marts should not occupy more memory in order to leave enough additional memory for query execution.
Check kernel parameters SHMMAX and SHMALL:
With the above information in mind we can now configure IWA. The default configuration is in file $INFORMIXDIR/dwa/etc/dwainst.conf. We edit this file to set the following parameters:
As in our example we have the Informix database server running on the same system, we specify only 80 percent of CPU resources for IWA. If IWA is running on a separate machine, then these last two parameters can be set to 100.
All other parameters in the "dwainst.conf" file normally can remain as they are. With that we have completed the configuration.
Setting up and starting IWA
To manage IWA itself we use the utility "ondwa". While this utility can be run as user "informix", we generally recommend to run "ondwa" always as user "root". This avoids problems with limits and file access rights.
As a first step we have to make the configuration effective and create the necessary directory structure in /data/iwa that we specified for the DWADIR parameter. The "ondwa" utility does all this for us when run with the setup command:
It reminds us of the fact that in the Informix database server we need an existing SmartBLOBSpace and must have the "onconfig"-parameter SBSPACENAME set accordingly.
If we chose to run the "ondwa" command as user "root", certain subdirectories in "DWADIR" will be accessible by user "root" only, so we can rest assured that no unauthorized access to certain data, e.g. dumps and traces, is possible.
After a successful setup command we now need to start IWA. We do this with the start command of "ondwa":
After a few moments we can use the status command to check whether IWA started successfully:
The shown output tells us that IWA was started successfully and is running correctly: "Fully Operational". We have two processes, a coordinator and a worker, that are both "active" and "healthy". With this we now can connect the Informix database server to IWA in the following section.
Connecting Informix database server to IWA
In order to utilize IWA for query acceleration, we need to connect the Informix database server to IWA. To do this, we use one of the IWA and data mart administration functions. These functions are available in any user database, but not in the system databases ("sysmaster", "sysadmin", etc.). If no such database exists yet, we can simply create an empty database - just for the initial connect - with a command like the following:
As we are creating a database in Informix, we need to run the above command in the correct environment for the Informix database server, especially when this is different from the environment for IWA (e.g. when the two are running on separate machines).
While the connection must be initiated from the Informix database server, IWA first needs to provide the connection information. This is done by running the getpin command of the "ondwa" utility (in the IWA environment):
The output contains the three necessary pieces: the IP-address, the port number and a four-digit PIN. We need to give this information to the Informix database server as parameters for the "ifx_setupDWA()" function. In addition, we need to choose a name for the accelerator that will be created in the process. The Informix database server will then always need to provide this name when contacting IWA. In our example we simply choose "iwa1" as the name for the new accelerator. We execute the function using "dbaccess" and connecting to the previously created database "dummy_db":
A quick test for successful connection setup is to run the data mart administration function "ifx_listMarts()". As parameter we now have to provide the chosen accelerator name, "iwa1":
While the output does not tell us much interesting about data marts, it tells us, that the function was executed successfully. The connection therefore was set up and is working. No rows were found because we did not yet create any data mart. We will learn how to do this in one of the next posts.
Advisory Development Engineer
Connect with me on LinkedIn