Tuning Informix Engine Parameters v1.7

 

Note: An update version of this article has been published in Informix Tech Notes Volume 9, Issue 3, 1999. The Adobe version to the right is an exact copy.

INTRODUCTION

Tuning engines is one of my all time favorite activities be they internal combustion or database.

Rare is the database engine that cannot be tuned for another 20%. Several orders of magnitude improvement is not uncommon for a well organized tuning session. This is not due to any mysterious talents of mine. Rather, that systems go out of tune rather quickly or were never tuned in the first place.

Below is a list of suggested settings for Informix Engine Configuration parameters I have gathered over the years. I keep a copy in my organizer for easy field reference.

I have collected many books that discuss tuning Informix databases. It has been my observation that they conflict more than they agree. In my mind there are several reasons for this:

  1. Changes to underlying engine architecture obsoletes recommendations with tremendous frequency.
  2. Unix implementations differ wildly at granularities important to the tuner.
  3. The rate of hardware advances quickly overtakes information learned.

The first item above was demonstrated in an early release of version 7. The Informix engine disk IO subsystem changed so markedly that all previous NUMAIOVPS recommendations were incorrect.

A wonderful example of item 2 above is the efficiency of the implementation of the Unix process manager. A series of tests I performed at Informix’s benchmark labs in Menlo Park demonstrated configuring twice as many CPUVPS as hardware CPUs was optimal for HP/UX. The version of Solaris that we were testing preferred the recommended 1:1 relationship for this.

My favorite conflicting pronouncements are on page 6-134 of INFORMIX-OnLine Dynamic Server Performance Tuning Training Manual (2/97). When discussing configuring NUMAIOVPS for systems that do not use kernel IO the page states a 1:1 relationship with controllers while the third sentence following states one per disk.

Note that these examples discuss wide variability in what are arguably the two most significant tuning parameters.

This should leave the reader with three important conclusions.

It is dangerous to speak of such things in absolute terms.
Each important end user environment must be measured and tuned individually i.e. cookie cutter approaches will cause trouble for the DBSA.
More importantly, due to the fluidity of this most fundamental knowledge set DBSA should continue to be well compensated.

What follows is my collection of notes for each onconfig parameter. Most are direct out of a book. Comments in blue/italics are from my own experience.

Where more than one suggestion is listed they are in chronological order, oldest first.

The assumption is made that the system under test (SUT) is a dedicated DB server. If it isn’t, it should be. Informix should be thought of as an Operating System unto itself. All it really wants from Unix is slices of CPU time. If it must share it's sandbox with applications, other database engines, or even other Informix instances it is operating out of the bounds of it's target design.

Acknowledgement that this level of isolation is not always possible in the real world has been addressed recently with new onconfig parameters. Important adjustments for tuning in a "mixed environment" are marked "".

Any mission critical DB should be on a dedicated machine.

"-" implies the onstat command.

Sometimes the DSBA does not have the luxury of an extended tuning session. He must make a quick change to a production box to get it back within acceptable parameters. I have noted parameters that allow for a "big bang for the buck" with a *.

These parameters are grouped logically and can be viewed via the hyperlinks to the right.

 

 

 

Introduction

Main & CPU

Disk IO

Logging

Memory & DSS

Miscellaneous

Notes

Print Version

Adobe Version
(most current)

bamph

                     

Web design and content bamph