Severe Performance Degradation

[Stojcevski Dejan]

After an upgrade of the Informix DB from 7.3 to 9.4 we have a serious performance degradation on one of our's box.
Disk utilization is about 30-40%. CPU portion of wio% is 60%. It used to be a cpu:15% and disk:8%.
It looks like Informix is performing well. Where should I start looking the bottleneck?
Box is rp2450/2CPU's 500MHz/1GB Ram.
Attached is a small portion of sar's output.

[Stojcevski Dejan]

Here is a sysdef on this box as well.

[Victor BERRIDGE]

Hi Dejan,

Difficult to say like that... We dont know your config...
But a fast look seems to show you have maybe more than 2 controllers but only one is doing the work... IF this is the case try to balance the load to get a better bandwidth....

Be sure to have 2GB swap (2X1 is fine...)
change kernel param swapon to 0
And see if it helps...



All the best
Victor

[Stojcevski Dejan]

The story with the controlers is ok. c4 is main and c6 is mirror. I have a lot of reads and that's why utilization of those disks (c4txdx) is much more than on c6. Load is balanced between disks ok - it has been so before the upgrade as well.
Dejan.

[Alex Lavrov.]

What procs are consuming CPU? What do you see in "top"?

If it's Informix procs, do you have any monitoring utilities for Invormix so you can see what it's doing?

Alex.

[Stojcevski Dejan]

OK. Informix is main reason for this. It's buffer cache ration for reads is very small: around 65% so mostly Infx is reading from the disks which is causing the high read ratio and disk utilization. What I am concerned is why most of the cpu portion is in wio% part?Is it as simple as that disks are slower than RAM mem?Or is there anything else?
Dejan.

[Steve Lewis]

As part of the upgrade you must
UPDATE STATISTICS DROP DISTRIBUTIONS;

This includes stored procedures.

Then do the informix recommended update statistics i.e. high on index leading columns and medium/low on subsequent columns.
There are some well-known informix routines out there for doing this.

Its just like upgrading Oracle, when you go to a new version you get the new optimiser and query plans change, sometimes unexpectedly. Thats why you thoroughly load/stress test all new software before upgrading.

It doesn't matter that c6 is mirror - you should still use it as primary for half of the reads, with c4 as its mirror. Its about the order you add the disks to your volume group.

Your root disk has some high i/o - check that informix is configured to use DBSPACETEMP and not /tmp for disk sorts.

[Alessandro Pilati]

Dejan,
could you run:

sar -o sarfile 3 30
sar -Af sarfile > sar.report

and post the sar.report file?

Then it could be useful the output of:
vmstat 5 20

and
swapinfo -tam

Thank you

[Stojcevski Dejan]

Hi Lewis,
We did UPDATE STATISTICS DROP DISTRIBUTIONS
after upgrading the IDS.
The next day we did also upd stats medium for all tables in the db.
It seems like a lack of memory?
Attached is a onstat -p from IDS instance that is running on that box.

[Alex Lavrov.]

Can out post here the output of:
swapinfo -tam
kmtune | grep buf
kmtune | grep pct


Alex

[Stojcevski Dejan]

Here are the stats from my system.
Dejan.

[Raj D.]

Hi Stojcevski,

You can check with this , for the performance and resource being utilised:

# top
# glance -t
# sar -u -M 5 5
# vmstat 5 5 [ Check for the pi/po values for heavy swap usage]
#ps -el | sort -r -k10 | more

and can come to some point.

Cheers,
Raj.

[Alessandro Pilati]

Dejan, I need the sar.report after you launched these 2 commands:

sar -o sarfile 3 30
sar -Af sarfile > sar.report

[Alex Lavrov.]

These threads might be helpfull, read through it, there are some suggestions about Informix:

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=106504

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=106504

I just don't want to copy&paste them all.

Alex.

[Alex Lavrov.]

P.S. Ther are about settings for you buffer cache, because as you found out and as it said in one of these threads, low buffer cache hits cause high WIO.

[Raj D.]

Hi Stojcevski ,

It seems from the sar output that you are running , very less cpu idle time. less than 50%.

Also check vmstat output and process table utilisation.

You may need to tune some kernel parameter. Check,
# glance -t
# sar -v 5 5

Hope you can find out peroformance bottleneck soon.

Cheers ,
Raj.

[Stojcevski Dejan]

Hi Alexandro,
Here is a sar report you requested.
Dejan.

[Steve Lewis]

I saw that your total shared memory usage is low. Whats the output of onstat -g seg and your onconfig parameters, BUFFERS and SHMVIRTSIZE?

Your read cache % is way too low at 65 - it should be over 99.

Are you using cooked files because your sar -b stats show write waits through buffers.

[Stojcevski Dejan]

Steve,
We do not use any cooked files - just raw partitions.
I attached onstat -c and onstat -g seg for you to see what you requested.
Dejan.

[Steven E. Protter]

Collect some data.

Here is the hpux script set:

http://www.hpux.ws/system.perf.sh

Thats my latest production code btw, based on HP's code.

SEP

[Alessandro Pilati · Accepted solution]

Just a little adjustment:
%wcache in sar.report seems to be average < 65, so you should think to increase BUFPAGES parameter ( I think after change you shoul regenerate kernel... )
But I think this is not the real cause of your performances degradation.
I can't see memory or cpu or disk bottlenecks.
I think the problem is on the DB informix...

I try to find some hints about this.

[Steve Lewis]

OK firstly you need to increase SHMVIRTSIZE to 40000 to stop it adding an extra segment.
Because you use raw files (good!) you can reduce your dbc_max_pct. For 1Gb of memory on a DB server on raw you can probably get away with 20% or hard-set BUFPAGES in the kernel.

Then I recommend you increase BUFFERS as much as you can to allow your maximum number of users, programs and still not start paging out.

Informix 9.4 uses a lot more memory for itself - the oninit processes have got a lot bigger since 7.3x. You may be right with your guess - another 1Gb would certainly help.

Next idea: if it is the oninits that take up most cpu (top/glance) you need to look at the user's SQLs directly with onstat -u ; check those which have high read/write pages and look at the SQLs they are running with onstat -g ses xxx. You can re-enter some of those directly in dbaccess with SET EXPLAIN ON to see if the query plans have changed since the upgrade. In 9.40 you can also dynamically set explain on if you know the program's current directory. Unexpected full table scans may now require update statistics high, not just medium - especially on tables with more than a million rows.

[Stojcevski Dejan]

As Alesandro said,
There are no bottlenecks on the OS level.
IDS is making all the "problems".
I guess since oninit takes more memory for itself in 9.4 it will help a lot to increase BUFFERS as much as possible. But for that I need additional memory installed.
Since read cache ratio of IDS is about 65% (way too low!) and checkpoints are still ok (1 sec max) I think that first step is to add more memory , add it to IDS buffer pool and see what will hapen.
Any other opinions?
Dejan.

[Alessandro Pilati]

Take these links:
http://sofbot.com/article/Tunage.html

And consider also this optimal document in attachment

[Borislav Perkov]

Hi,
As far as I can see from attached documents you have problems with lack of system memory. Informix 9.4 needs much more memory for its operation and the best way is to upgrade the system phisical memory.
Regards,
Borislav