Database Server is crawling !!!

[Michael Steele_2]

I'm anxious to see your attachment of Steve's performance script, especially the results of the sar -v command.

Have you rebooted recently or not at all for awhile? If your system memory is fragmented this will defragment it and provide a stable state.

How long has it been since a reboot?

uptime

-or-

who -b

[Steve Lewis]

Ok, phase 2.

It could be locks related and semaphores.
What is the value of kernel parameter semume?
If semume is less than 512 then set it to 512.

Next check the lock waiters and holders in your application, to make sure that processes are not simply waiting for others to release locks. Lester Knutsen has some super SQLs for displaying these on the informix IIUG website.

Your system is not waiting for i/o.

Definitely increase the values of BUFFERS, to say, 200000 for a start.

Please post the values of the following informix stats:
onstat -p
onstat -g glo
onstat -F
onstat -g iov

[Steve Lewis]

By BUFFERS I mean the informix onconfig parameter, NOT the kernel parameter.

I dont think PRM will help you.
Glance might help you, if you look at the number and type of system calls being made.

[Sritharan]

Hi,

Steven,
This is the ouput.

S. Lewis,

I have attach the onstat output. Our semume is 10.
Our BUFFERS setting on informix is 40000.


Thanks & Regards
SRi

[Tim D Fulford]

You have a 2 CPU machine. Two of your oninits are very busy but the rest are pritty quiet. There are 9 oninit in total. to me this seems like a very small?

o How many cpu-vps are there?
o What poll threads have you configured (NETTYPE in ONCONFIG)
o How many aio-vps have you configured? (NUMAIOVPS) or are you using KAIO?
o are you doing lots of sequential scans?

I **SUSPECT** that you have configured one cpu-vp of the wrong type ++ and are running 1 aio-vp (NUMAIOVPS=1). This will make your system crawl, especially doing sequential scans.

++ by wrong type I mean
you are doing lots of network connects & the cpu-vp is a shm poll thread. eg
NETTYPE ipcshm,1,100,CPU
OR
you are doing lots of shared memory connects & using a soc poll thread. eg
NETTYPE soctcp,1,100,CPU

The only exception to the above is that I believe multiple threadded app NEED soc poll threads. I would do the following
NETTYPE soctcp,1,100,CPU
NETTYPE ipcshm,1,100,NET
Configure you threadded app to use the "soctcp" connection. The ipcshm poll thread is there to address any shm connections, though it is not necessary.

I've written some perl to help with tuning decisions. it is at the bottom of the following thread

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

[Tim D Fulford]

I did not read all the attachments!!!

You are doing some 22,000 sequential scans in 71 minutes. I suggest some indexes or update statistics!!

Tim

[Sritharan]

Tim,

This are our configuration

NETTYPE ipcshm,1,400,CPU
NETTYPE soctcp,1,400,CPU

NUMCPUVPS 2
NUMAIOVPS 7
SINGLE_CPU_VP 0

We also have do UPDATE STATISTICS once a week


TIA
SRi

[Tim D Fulford]

Sri

On the sequential scan side..
In the IfxPerf.tar.gz.uue (that is a uuencode, gziped & tared file!!) is a directory called sql. This should contain a file called something like seq_scan_cost.sql. This should give you an idea which table(s) is(are) being scanned. The update stats onec a week [to me] sounds to infrequent. If tables are not being updated too much, then once a week is OK, but usually nightly is better. The main exception is if you could be doing "true" sequential scans (i.e. you want to extarct say 25-30%+ of a table). The best way to optimise this is to fragment the table and spread it over multiple disks too. As you only have a few disks this may not be possible! some food for thought. on the seq scan side.

On the NETTYPE side...
You split your usage 50:50 between shared memory & socket (network). From the onstat -g glo this split is more like 80:20 in favour of network. If may be bettwe if you used
NETTYPE soctcp,2,400,CPU
NETTYPE ipcshm,1,100,NET
NUMCPUVPS 2

As you are using HP-UX 11.11 I would use KAIO rather than Informix aio-vps. If so I would reduce set
NUMAIOVPS=2
and in the Informix .profile
export KAIOON=1
IF you do not want to use KAIO (Kernel Async IO) I would increase the number of aio-vps to 14 or even 24. The CPU usage of aio-vp1 to aio-vp7 is 80%. I prefere to see between 5% & 10%.

Regards

Tim

[Sritharan]

Hi,

Tim,

Can you explain me more regarding the KAIO vs AIO ?

Our tables are indexed but when we try to set explain on the table it shows
sequential scans.

TIA
Sri

[Steve Lewis]

KAIO requires setting up device /dev/async and including the 'asyncdsk' driver in the kernel.
# mknod /dev/async c 101 0

Then export environment variable KAIOON=1 before you start up the informix instace.

KAIO works by taken i/o calls away from the database engine processes and running them as threads in the HP-UX kernel.

If you dont want to try KAIOON then increase informix onconfig parameter NUMAIOVPS to 20.
I also recommend the following informix onconfig change:
BUFFERS 200000

I also recommend you make the following changes to kernel parameters:
dbc_max_pct 10
semume 512
shmmax 4294967296

Lastly you must know that 9 out of 10 informix performance problems are due to poor code, not the database.

Run this query to find out which queries are the bad ones:

database sysmaster;

select * from syssqexplain where
sqx_estcost>1000
order by sqx_estcost desc;

You said you had set explain on but it still did sequential scans.
If you have any tables with over 1000000 rows then you must run update statistics HIGH on those indexed columns. Update statistics medium will not be enough for very large tables.
The set explain output shows you which columns are being used to scan on. You may have to use optimizer directives to force the use of indexes. For example:
select {INDEX(emp idx1), AVOID_FULL(emp)}
col1,col2 from emp where...