Tuning Informix Engine Parameters v1.7

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.

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.

Sometimes the DBA 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 *.

MAIN

ROOTSIZE

SERVERNUM

CPU USAGE

RESIDENT §

NUMCPUVPS * §

MULTIPROCESSOR

SINGLECPU

NOAGE §

AFF_SPROC §

AFF_NPROCS §

USEOSTIME

NOTES:

-g glo There should be a 10:1 ratio between time spent in usr vs. sys for CPUVPS. (This has become perhaps 3:1 with KAIO). If sys is too high on a system with just a few HW CPUs try using just 1 CPUVPS.

-g rea If this shows seven or more threads waiting adding CPUVPS can bring it down.

UNIX COMMANDS:

mpstat

sar

uptime

DISK IO

BUFFERS* (pages)

NUMAIOVPS* – Suggestions for this seem to change with the weather.

RA_PAGES

RA_THRESHOLD

DBSPACETEMP*

FILLFACTOR (indices)

MIRROR

IOSTATS

TBLSPACE_STATS

NOTES:

Increasing the Unix priority of AIO processes can improve the performance of data returned from disk.

Monitor IO with -g ioq (iof and iov are also worthy). When AIOs used gfd len SB < 10, maxlen <25. Maxlen often breaks 25 during engine initialization when it is unimportant so make this distinction. –D will show hotspots at disk level –g ppf at partition level.

When building an important Data Warehouse for my current employer the Sun Hotshot suggested placing all data in only the middle 2GB sectors of each 4GB disk, leaving the remaining unused. The highly paid Informix representative felt strongly that using only the leading 2GB of sectors would perform better. I suggested that we test and if it was within 5% that this be decided by ease of maintenance. The leading sectors proved 2% faster than the middle. I do not recall which I ended up implementing.

If your system is IO bound verify if it be controller or disk bound. The solutions are different.

Throughput = (pg_size * num_pgs_requested/max_transfer_rate) + latency

The use of clustered indices can greatly increase sequential reads.

Informix recommends using fragmentation over HW striping unless the table is a poor canditate for fragmentation. I would like to test this statement someday.

I have not been able to test how Kernal IO effects NUMCPUVPS configuration. From DSA Performance Tuning Manual (2-97) "If your system supports kernal aio, onstat –g ath will show one kio thread per CPUVP." Therefore should NUMCPUVPS be associated with the number of disks, etc or remain a function of the number of hardware CPUs?

UNIX COMMANDS:

iostat

sar

vmstat

LOGGING

CLEANERS*

PHYSDBS

PHYSFILE

PHYSBUFF

LOGFILES

LOGSIZE

LOGBUFF

LOGSMAX

CKPTINTVL

LRUS*

To monitor –g spi shows contention for individual LRU queues

LRU_MAX[MIN]_DIRTY - %buffers assigned to modified queue

 

Monitor: –R queue length, -F writes forced by this parameter by CLEANER thread

LTAPEDEV

LTXHWM

LTXEHWM

LBU_PRESERVE - Preserve last log for log backup

NOTES:

BLOBS/OPTICAL

STAGEBLOB (Optical)

OPCACHEMAX

 

NETWORK

NETTYPE

UNIX COMMANDS:

netstat

SESSION

LOCKS

OPTCOMPIND

MEMORY

SHMBASE

SHMVIRTSIZE* (kilobytes)

SHMADD (kilobytes) -

SHMTOTAL §

Monitoring: -g seg – One of the first things I check as consolidating shared memory segments can be huge bang for the buck.

Note that onmode -F (used to free memory segments) can cause system failures on an active system and should be avoided.

UNIX COMMANDS:

Sar -g 3 3 – will show paging activity

Vmstat

Notes:

To calc shared memory segments corresponding to a DB instance shmid – 52564801*.0001 = SERVERNUM

DSS PARAMETERS

PDQPRIORITY

MAX_PDQPRIORITY

DS_MAX_QUERIES

DS_TOTAL_MEMORY

DS_MAX_SCANS

Notes:

Quantum = (PDQPRIORITY/100)*(DS_TOTAL_MEMORY/DS_MAX_QUERIES)

Each sort thread gets quantum/#sort threads memory

A users effective priority = (pdqpriority/100) * ( MAX_PDQPRIORITY/100) where pdqpriority is set by the environment variable or the SET PDQPRIORITY statement.

DEBUGGING/RECOVERY

OFF_RECVRY_THREADS

ON_RECVRY_THREADS

DUMPDIR

DUMPSHMEM

DUMPGCORE

DUMPCORE

DUMPCNT

DATASKIP

ONDBSPACEDOWN

DATA REPLICATION

DRAUTO

DRINTERVAL

DRTIMEOUT

DRLOSTFOUND

MISC NOTES

Oncheck –pr can be used to replace lost onconfig file

Resident – Buffer Pool (Pool is for residents use only - I needed a mnemonic for this.)

Virtual – Light Scan Area (Virtually no logging - this too.)

Storage overhead

Scans and Sorts for index builds are always parallel with 7.2. Idx builds on fragmented tables add parallel B-(sub)tree builders

UPDATE STATISTICS – single most important SQL statement for Q perf.

HIGH

MEDIUM

LOW

To speed up update statistics set PSORT_NPROCS to 2, use DBSPACETEMP (duh) do NOT set DBUPSPACE (limits RAM for US)

The following came from a class handout. I have no idea the original author.

Database Tuning the Informix Way.

  1. Establish performance objectives.
  2. Measure database activity and use of resources.
  3. Identify performance problems such as excessive use of CPU, memory, or disks.
  4. Tune the operating system.
  5. Tune the Online Dynamic Server
  6. Optimize the placement of logs, sort space, and temporary space.
  7. Optimize table placement, sizes of extents, and fragmentation
  8. Make sure the indices are appropriate.
  9. Optimize background activities such as logging, checkpoints, and page cleaning.
  10. Schedule backups and batch jobs for off-peak hours.
  11. Review application programs to make sure appropriate access methods are used to retrieve data and algorithms are efficient.
  12. Repeat steps 2 through 11.

My additions would be:

Calculating maximum number of extents for a particular table:

Max#extents <= (pagesize – ((4 * number of columns in a table) + (8 * number of BLOB and VARCHAR columns + 136) + (12 * number of indices) + (4 * number of columns in the indices) + 84))

Loading Hints:

Fragment large tables by round robin

Find hot tables:

SELECT t.tabname table, p.pf_dskreads + p.pf_dskwrites totalops,p.pf_dskreads diskreads , p.pf_dskwrites diskwrites

FROM sysptntab p, systabnames t

WHERE t.partnum = p.partnum

ORDER BY totalops DESC

Bibliography

INFORMIX-OnLine Dynamic Server Performance Tuning Training Manual - Informix part number 502-5-403-1-9999999-1 - Liz Suto, Course Designer

Tuning Informix Dynamic Server and Your System for Optimum Performance - Art S. Kagel

More to come...

 

 

Please forward any comments to informix@bamph.com