Informix 7.24 on HP 10.20. Performance degradation.

[Juan González]

Hi,
we have a system with Informix 7.24 on HP10.20.
Three months ago we migrated our applications from other database system to Infomix. Since this change our users noticed that the applications run very slow. The users works directly in the server, there above 200 users. We detected using glance that there was CPU and disk bottleneck. We added a CPU and four disks and the bottlenecks dissapeared, but some problems persists. Users still notice that application run slow and we verify this. We detected that the application slows after situations of high load, even when systems resources are free and we must restart Informix to get the application run normal.
The connection between the application and Informix is by sockets (trough the loopback interface). We are thinking in change this connection to shared memory to reduce the system overhead.

What do you think about this change?
Do you have any idea about the cause of this problem?
Any help will be apreciated.
Thanks in advantage.

Juan Gonzalez

[MARTINACHE]

Hi,

Do you verify database indexes ?

During the migration, maybe someone forgot to create some indexes.

This arrived very often ...

Regards,

Patrice.

[Juan González]

Yes, database indexes are OK. At first we touhgth in this too...but are OK

thanks for answer

[melvyn burnard]

I recall an issue on HP-UX 10.20 and Informix 7.24 specifically, where a certain parameter is incorrectly set in the profile for the Informix product.
My problem is I cannot remember which one :-(
It had to do with locking the main processes into memory, because I think your problem is the system is swaping or de-activating the Informix processes.
I think the file was called oninit, but may be wrong.
If you post your Informix profile I may rememeber which one, otherwise Informix SHOULD know of the issue and it's fix.

[Volker Borowski]

Hello,

what about database statistics ?
Informix 7.24 uses a cost-based optimizer.
This one needs calculated statistics (as oracle CBO as well) to provide correct access
path to the data.
I do not recall how the program is called.

On SAP you have a program infupdstat, but I do not recall if it is SAP or informix tool.

Hope this helps
Volker

[Steve Lewis]

It sounds like you may have NOAGE set to 1 in your onconfig, I had a similar problem to yours where the database ended up at a higher priority than the o/s until I set it to zero. Noone could ping the server or do anything.
It could be lots of things actually - long checkpoints, sequential scans, patching. If you have new tables being populated, run update statistics often on them. If you have a mixture of batch and on-line processes, keep the batch ones thru the socket and set the on-line user processes to go thru ipcshm. This will bypass a saturated socket and make it quicker for the users.
By the way, 7.24 has a few tcp socket related bugs which makes it crash a lot - I suggest upgrading to 7.31 asap.

[Juan González]

Thanks for your answers,

Melvyn
I attach the configuration file of Informix, could you take a look?

Volker
We run the update stadistics process weekly

Steve
The parameter NOAGE=0, and we have no sequential scans. We will follow your advice of divide the users in two groups if we migrate to shared memory

Best Regards
Juan Gonzalez

[melvyn burnard]

Thanks for the attachment, I have had a look an dnow remember the NOAGE, RESIDENT and I think it was a KAIO parameter.

RESIDENT I believe needs to be a 1, this ensures the main Informx processes stay locked in memory.
Again, it was about 2 years ago I found out about this, and recommend you discuss with Informix to be sure, as I do not have the information to hand.
Also and additional thought, have you installed the recommended patches for Informix?

[Bill Burton]

Hi Juan,

I just found this month old thread. There are a number of significant problems with your configuration file. If you are still watching this thread let me know and I'll post my thoughts.

You might also wish to study my notes on the subject... http://www.bamph.com/informix.htm

Enjoy,

Bill

[Juan González]

Hi Bill,
yes I am still attending this thread. Could you post your conclusions about the configuration?
Thanks in advance

[Bill Burton · Accepted solution]

Hi Juan,

First know that I really need more info to do a top job but this should help a great deal. I have marked follow-up questions with an asterick(*).

Try to test your changes on a development system. Also if possibile, do one change at a time, measure results, back out the change, then try the next one.

TBLSPACE_STATS - unless you are using the statistics gathered by this set it to "0". This sounds like it could cause horribile degradation but I've never played with it.

*How many CPUs on the system and how many do you want informix to use? You should know that whatever your answer your system is set up incorrectly. If you want to use more than one NUMCPUVPS then MULTIPROCESSOR must be "1" and SINGLE_CPU_VP must be "0". IMHO this should be one parameter but I digress.

You have four NETTYPE entries and I've never seen more than two. If you really want three NET threads you might be better served with:

NETTYPE soctcp,3,250,NET

*How many connections through shared memory vs. network do you really have? Since you say that all users work directly on the server I would wager that all these NETTTYPE entries could be reduced to one CPU type thread which may really increase performance.

Run "onstat -p" and look at your "%cached". If this is less than about 90% increase BUFFERS. If there is no change increase RA_* which may be too low. *How much RAM is available? If this is an oltp system with little RAM you may not be able to get more than 70 or 80%.

CLEANERS, NUMAIOVPS and LRUS don't seem to mesh well. *How many disks and dbspaces are you using?

LRU_M??_DIRTY are very low. I really do not like to use zero (0) here as your machine might thrash itself trying to fullfill an unobtainable goal. *Were your checkpoints too long? *What are they now?

Another HUGE potential problem could be shared memory segmentation. Run "onstat -g seg" and look at the number of shared memory segments marked "V". There should be only one. If there are more increase SHMVIRTSIZE and SHMADD accordingly. Because you mention that restarting informix speeds it up this is likely a big problem for you.

Are you running update statistics often enough? If your tables are very dynamic you may benefit from running this nightly.

If your system has enough CPUs you probably want to set NOAGE to "1" and RESIDENT to "1".

Note that most DB performance problems are due to poor application design and index usage and we have not addressed that. Nor have we looked at table fragmentation (oncheck -pe).

I would bet a weeks pay that you can get 50% boost from tuning your onconfig file. On most systems I can get several orders of magnitude performance inprovement with just with the type of adjustments that I've outlined here.

Good luck Juan and let us know how this works for you!

Enjoy,

Bill