Tuning for informix database

[Equipe systeme GHM]

Hi,

We have a systeme L3000 with 3 processor with informix 9.21 on HPUX 11i.

What do you think about tuning of this five informix parameters:

NUMCPUVPS
SINGLE_CPU_VP
MULTIPROCESSOR
NUMCPUVPS
AFF_SPROC
AFF_NPROCS

for the moment we have the following parameters:

MULTIPROCESSOR 1
NUMCPUVPS 3
SINGLE_CPU_VP 0
NOAGE 1
AFF_SPROC 0
AFF_NPROCS 3

thanks in advantage.

Denis Aubert

[Rainer von Bongartz]

You should set:

NUMCPUVPS 2
SINGLE_CPU_VP 0
MULTIPROCESSOR 1
AFF_SPROC 0
AFF_NPROCS 0


Regards
Rainer

[Steve Lewis]

Rainer's answer is the informix standard response. However I would say that the correct answer really depends on your application architecture.

If you have programs running on the server, or users using telnet to log in locally to access the database, then chop CUMCPUVPS to 2.

If you have a 3-tier architecture or a thick-client 2-tier architecture and the database is the only thing running on this system, then you are quite safe setting NUMCPUVPS to 3.

All the other listed parameters are fine.

The only thing remaining to tune is your memory allocation, in particular buffers and cleaning paramters etc.

If you have many clients then ensure that your shmvirtsize is big enough to put everything in a single segment, maybe increase the number of soctcp VPs if you have over 200 users.

If you have any more questions, then post your onconfig.
Regards,
Steve

[Rainer von Bongartz]

I aggre with Steve in the point regarding NUMCPUVPS

Denis:

If you have perfomance problems with the engine the above parameters will not!!! solve this.

In this case you should look at

BUFFERS
NUMAIOVPS
LRU
CLEANERS
LRU_MAX_DIRTY
LRU_MIN_DIRTY



Regards
Rainer

[Equipe systeme GHM]

I have no performance problem for the moment.

100 users in a call center are working on this system, but this number will increase to 300.
And it is better to tune informix before having performance problem.

Thanks for answer,

Regards,

Denis.

[Equipe systeme GHM]

the other parameters are:

BUFFERS 50000
NUMAIOVPS 3
LRUS 4
LRU_MAX_DIRTY 15
LRU_MIN_DIRTY 10
CLEANERS 4

What do you think of them?

In fact, it is a cluster with 2 nodes L3000 (3 proc, 4Go RAM), 6 informix packages. Perhaps, it is necessary to increment the parameter AFF_SPROC for each informix engine?

for exemple:

pack1 AFF_SPROC=0
pack2 AFF_SPROC=1
pack3 AFF_SPROC=2
pack4 AFF_SPROC=0
pack5 AFF_SPROC=1
pack6 AFF_SPROC=2

Regards,

Denis.

[Rainer von Bongartz]

Denis,

I never did gain any performance
fumbling around with AFF_SPROC.

What you should do is first of all check the performance of the engines and then decide if you have to do something.

If possible change the WSTATS parameter in onconfig to 1 and restart the engine(s).

WSTAT=1 will enable you to monitor the wait statistics of your engine. It will tell you if INFORMIX is waiting for BUFFERS LRU or anything else.
In such a case you can adjust the parameter and restart.
If you are satisfied with the results set WSTATS to 0 again .
(otherwise you will loose some perfomance because the engine is gathering the metrics)

Regards
Rainer

[Tim D Fulford]

From some of the above I'm implying that you have a database backend and something else (computer wise) running the apps. If so, it will be network intensive, so

NETTYPE soctcp,X,100,CPU # X=NUMCPUVPS
NETTYPE ipcshm,1,100,NET # If at all, depends on sqlhosts file

Because your machine is 11i I would use KAIO (Kernel Asynchronous IO) so

NUMAIOVPS 3

I would NOT set NOAGE 1, ESPECIALLY if it is in a MC ServiceGuard cluster. You may well find just as the system gets really busy it will fail-over!!! NOAGE only really plays a positive role when the system is running hard & running out of CPU. If this is the case, buy more processes (easier said than done, I know). What actually happens is that Informix hogs ALL the CPU not allowing the machine to run other essential tasks.

I've also put together a perl "Informix Tuning Suite" I'll forward you the URL.

Regards

Tim

Regards

Tim

[Tim D Fulford]

ifxperf.tar.gz.uue -->

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x1c204b3ef09fd611abdb0090277a778c,00.html

This SHOULD help you decide if BUFFERS is too low etc...

I'd zero your statistics (onstat -z) & wait 24 hours or 48 hours before running the script to let the stats build up over a KNOWN period of time.

Regards

Tim