For the IBM Redbook Using Informix Dynamic Server with WebSphere, SG24-6948, there was no performance criteria to meet. However, there were some general guidelines we followed when using only Informix Dynamic Server (IDS) and WebSphere for our testing purposes that may be of value to you.

From an IDS perspective, the performance tuning process is very dependent on the systems environment, and usually requires either an experienced server administrator or an IBM consultant to configure and test the installation. This is to be expected, particularly for the first implementation, because new applications and users may be added to the system. The performance tuning process for IDS should be reanalyzed and reconfigured on a regular basis to maintain the best performance.

It is not our intention to describe how to tune an IDS server, but simply to present some general guidelines that could be used as a starting point. Normally these guidelines should work fine for new systems, but after some time more advanced techniques may be required. The guidelines are presented for two types of environments: Online Transaction Processing (OLTP) and Decision Support Systems (DSS).

OLTP tuning

When tuning the online system for an OLTP environment, it is important to focus on specific areas that will have the greatest impact on performance. What you want to achieve is:

1. High read and write buffer cache rates

2. Fast checkpoints
3. Maximum I/O throughput by:
a. Eliminating I/O bottlenecks
b. Optimizing fragmentation strategy (including fragmenting the indexes as well as data)
c. Optimizing index utilization

Typical OLTP environments are characterized by a large number of users performing a high volume of short transactions that INSERT, UPDATE, and DELETE data. When tuning IDS in an OLTP environment, it is important to spend your time in areas that will have the greatest impact on performance. It is also important to realize that performance tuning is iterative in nature, and doing too many changes at one time can present a challenge. Finally, benchmarking is key to knowing where you stand and when you have succeeded. With this in mind, here are some initial ONCONFIG ($INFORMIXDIR/etc/$ONCONFIG) settings for OLTP.

1. BUFFERS - Maximize. Set to 50% - 75% of available free memory. Set to an even greater percent if not using the memory in the in virtual portion (after testing to see if the memory is needed). When tuning this parameter and SHMVIRTSIZE, understand that both may need to be changed if the combined total is 75% of the operating system physical memory.

2. LOCKS - Set to 1000 * number of users.

3. PHYSBUFF - Pages per I/O should be about 75% of the buffer size (to monitor use onstat -l).

4. LOGBUFF - Pages per I/O should be about 75% of the buffer size (to monitor use onstat -l).

5. LRUS - Configure four LRU pairs per CPU VP.

6. CLEANERS - Configure one page cleaner thread per LRU pair.

7. SHMVIRTSIZE - Set to 32000 + expected number of users * 800

8. CKPTINTVL - Set to 9999. Let the physical log initiate checkpoints. This may be contrary to popular belief but the reasoning is that you make the LRU cleaning keep dirty buffers to a minimum and even with a long interval, you can minimize the checkpoint waits.

9. LRU_MAX_DIRTY - Set to 10. With many systems, the final settings may be as low as 1. Also with 9.4 release, you can use fractional percentages so the number of dirty buffers at checkpoint time should only be the maximum duration that system should perform a checkpoint in.

10. LRU_MIN_DIRTY - Set to 5. With many systems, the final setting may be as low as 0. Also with 9.4 release, you can use fractional percentages so you should not need a 0.

11. RA_PAGES - Set to 32.

12. RA_THRESHOLD - Set to 30.

Decision Support Systems (DSS) Tuning

When tuning for a DSS environment, it is important to focus on specific areas that will have the greatest impact on performance. What you want to achieve are:

1. Optimum memory utilization

2. Parallel data queries (PDQ)
3. Light scans
4. Maximum I/O throughput

For optimum memory utilization, the area that will have the greatest impact is shared memory. Due to the nature of DSS queries, large amounts of shared memory located in the virtual segment are required to perform a variety of operations, such as light scans, hash joins, and sorts. It is critical to properly configure and tune the shared memory and PDQ parameters in the online onconfig file. To increase performance for DSS queries, increase the amount of available virtual shared memory. With this in mind here are initial ONCONFIG settings for tuning DSS queries:

1. BUFFERS - Minimize. Set to 2000

2. SHMVIRTSIZE - Maximize. Set to 75% of available memory (or an even higher % if memory is not needed elsewhere).

3. SHMADD - Set to 32000.

4. SHMTOTAL - Set to available memory for the online system not the entire memory of the UNIX box.

5. RA_PAGES - Set to 128.

6. RA_THRESHOLD - Set to 120.

7. DS_TOTAL_MEMORY - Set to 90% of SHMVIRTSIZE.