MIS and Informix performance

[lawrenzo]

Guys we have an MIS server running informinx 9.4FC2 for reporting and are considering a possible upgrade because the cpu stats show 90% utilization however this is a constant metric since last October when the system had extra cpu added and when we extend the trend line to 12 months ahead there is no real increase in the utilization.

We have apps running on the server and the system performs well of an evening when scheduled jobs are run ( around 40% cpu however when users login the cpu reaches around 90%

we use the system as mentioned below:

1. MISPORTAL users : Connect via ODBC from misportal01 server (upto 30 connections).
2. BRIO Users : Connect via ODBC from respective user PCs.
3. Informix CRON users: Connect via Shared Memory and run on misusec as cron jobs.

MISPORTAL is a web interface and as mentioned connect through ODBC connects.

I suppose my question is that if the cpu is upgraded is anyone aware that will the system still use 90% of the resource?

has anyone had any experiences of this?

any help will be greatly appreciated.

Thnaks

Chris.

[Steven E. Protter · Accepted solution]

Shalom Chris,

90% utilization still means there is spare cpu capacity.

The question I'd like to direct you to is this:

Are there known performance problems? User complaints? Errors in /var/adm/syslog/syslog.log ?

If the answer to any of the above is yes, keep working. If not, find something better to do.

90% CPU utilization is not a good reason to upgrade. Lack of hardware or software support is.

uname -a
model

It would be nice to know what kind of system you have.

If you think you have a performance problem, check it with this:
http://www.hpux.ws/system.perf.sh

Memory may solve any issues you have.

Lets make sure there is a problem before we try and solve it.

SEP

[lawrenzo]

This is the thing Steve,

We need the system to last us 2 years until we upgrade to a total new solution using informix so this exercise is to identify the need to upgrade and work out any cost which can then be planned into the projected budget.

Thanks for the comments and I totally agree that 90% utilization is an optimised system. There is some degregation to service and jobs running when users run thier reports in the morning however this could just be down to the way the users connect to the DB through ODBC connections.

I have to write a report and recommendations so not much work to do here.

Thanks

Chris

[Steve Lewis]

The problem with MIS users is that they write stupid queries and never think of anyone else.
Its a lot easier to blame the system.

I suggest that you use onstat -u to check for users who have READS or WRITES over 100000 and then drill down into the actual SQL they are running using onstat -g ses. If the flags in onstat -u shows that the thread is blocked on a buffer (B) and reading (R) then there is a chance that they are doing a big scan.
High writes in queries often points to disk sorts or group by operations which will increase the CPU.
If the flags show Y that means waiting on a condition - often this is simply waiting for the client end to come back with another SQL request.

So start by checking the flags and user queries.

Then look at disk utilisation to see if you can move any tables around, fragment(=Oracle partition) them.

Also you can check the index strategy and update statistics. I have known some serious tuning of the HIGH parameter to be required on large tables (10 million rows and upwards). What is your read cache% and write cache % in onstat -p ? It should be over 90 for a DW and over 99 for an OLTP system.

Then I also suggest that you check the network utilisation.

Lastly, it may be a simple case that your system has reached capacity and needs a faster processor, or several. I and several DBAs from other companies I know got a huge (and unexpected) increase in performance going from PA-RISC to IPF.