Database Server is crawling !!!

[Sritharan]

Hi,
OS - HPUX 11.11
RAM - 2GB

We are running Informix Dynamic Server 9.3 on our N class server.

But when we try to access the data it very slow, but when I check using command the server resources is not really utilize(check out the attachment top)

And when you see the daemon for informix is in "sleeping" mode.

What does this mean ??

We have PRM but we are not using it.Do you think by allcation the memory and processor for Informix daemon using PRM will solve this problem.

Need help URGENT
TIA - Sri

[T G Manikandan]

Can you just post the output of

1.#swapinfo -ta
2.The output of kernel parameters and their value.
3.How many processes are in the system

Revert on the same

[Sritharan]

Hi ,

this is the output

prshq01:>swapinfo -ta
Kb Kb Kb PCT START/ Kb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 4194304 0 4194304 0% 0 - 1 /dev/vg00/lvol2
reserve - 402168 -402168
memory 1574380 414072 1160308 26%
total 5768684 816240 4952444 14% - 0 -


Thanks & Regards
SRi

[Sritharan]

Hi,


2.The output of kernel parameters and their value.

STRMSGSZ 65535
dnlc_hash_locks 512
maxdsiz_64bit 0X40000000
maxssiz 0X800000
maxssiz_64bit 0X800000
maxswapchunks 2048
maxtsiz 0X4000000
maxtsiz_64bit 0X40000000
nfile 5000
nstrpty 60
semmni 4096
semmns 4096
semmnu 1461
shmmax 1073741824
shmmni 512
shmseg 500
~




3.How many processes are in the system
2 CPU

[Steve Lewis]

Check the following:

1. If you are connecting from a client using the hostname, check how long it takes to resolve the host name using DNS.

2. You have plenty of memory available so you could bump up the ONCONFIG values of BUFFERS and SHMVIRTSIZE. (onstat -g seg)

3. There are plenty of other informix tuning things to check. For instance, check the timings of your checkpoints using onstat -m. If they are too long you need to tune it a bit more. This requires an experienced DBA and there are lots of things you could change.

4. Check the disks. If they are busy so you could be doing sequential scans. Are you using an index? Does it need another index? Have you run UPDATE STATISTICS to the necessary level on these tables? Very large tables require UPDATE STATISTICS HIGH to be run on indexed columns.

5. Check the general network speed.

Thats just a starter.

[T G Manikandan]

Sri,

THe memory usage on the machine looks good.However you need to check the buffer cache value on the system.If that is too high reduce it.

The I/O,cpu and memory are interdependent and probably you need to also look at the I/O of the disks.

#sar -u 5 5

should give some amount of hint on the percentage CPU usage.check what is the % for usr time % and system time %.
if you can notice more idle % of CPU,then
concentrate on tuning the database application.

[T G Manikandan]

Sri,

There are lot of factors in determining the bottleneck for the performance and it cannot be found right away in these cases even for what I have mentioned above.
Just browse some of the docs for Tuning and narrow down the bottleneck

Just check some of the hp docs

http://www1.itrc.hp.com/service/cki/docDisplay.do?docLocale=en_US&docId=200000067036406

http://www1.itrc.hp.com/service/cki/docDisplay.do?docLocale=en_US&docId=200000063210677

http://www1.itrc.hp.com/service/cki/docDisplay.do?docLocale=en_US&docId=200000063203050

[Sritharan]

Hi,

Steve,

1. The clien connecting to our server thru ip address
2 & 3 - check out the attachment
4. Yes, we are using index and we have run UPDATE STATISTIC LOW & MEDIUM to the tables with index.
5. On the network we are still collecting some data.

Mani,

the output for sar -u 5 5 , have been attached.

As you say the CPU idle is around 80% is this mean that ,informix not utilizing the cpu ??

And i'm reading thru the docs very resources full but it concentrating on the system resources. I think on our sidee we have the resource more than enough.



Any other things that I have to look , maybe on the informix side or OS.??

TIA - Sri

[Sritharan]

Hi,

Sorry this are the attachments

TIA
SRi

[Steven E. Protter]

Performance data collection script: attached. Its production.

SEP

[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...

[Tim D Fulford]

Sri

Steves answer is great. The only thing I would add is from an understanding point of view...

o KAIO - This uses kernel threads to do AIO. It should be quicker as Informix passes its IO to the HP-UX kernel and does not require as many aio-vps (but will require some). There have been problems in HP-UX 11.00 that are now "fixed" in 11i. These problems only realy became apparent in larger CPU machines.

o aio-vps - These are simply extra Informix oninits that are dedicated to IO. The idea is to add more aio-vps to "scale" the ammount of IO possible. The main problem with aio-vps is that it is a law of deminishing returns. so doubling the number of aio-vps will, say, improve IO by 30%, the next doubling will add say 10%, an so on. Fairly soon you get to the point that there are so many aio-vps that the system is juggling too many processes!!

Steve has reccomended increasing the number of BUFFERS. The reasons behind this are
o % cached writes is low at 78% read (90%+ is the aim)
o bufferwait ratio is 8.7% [ bfwts/(pagrds+bufwts)]
I also suspect that the ratio of data:indexes is not too good (onstat -P | tail) and increasing the number of buffers will help.

Regards

Tim

[Tim D Fulford]

oops I've just read my formula and my abbrivations are confusing...

buffer waits = bufwaits/(pagreads+bufwrits)

Tim

[Sritharan]

Hi,

Is there any way where can see the logs for the UPDATE STATISTICS that we have done and is there any way we can automatically run the UPDATE STATISTICS(without using the crontab)like in Orace there can automatically do it.

On the setting up the KAIO/AIO we are talking to informix about it.

regarding the kernel parameters we have change the shmmax value to 4294967296 (we also have added 2GB of RAM - informix personnel recomendation).

Ok now let me look on the aio-vps (NUMAIOVPS)first,
you are saying that right now we are running 7 and asking to add more...let say 12...what happen for the process that are on the sleeping mode.How do I let all the oninit daemon run/active.


TIA
Sri

[Tim D Fulford]

Sri

as far as the aio-vps, online
onmode -a +12 aio
I would also update your ONCONFIG accordinly so that next time you start it will come up with 19 aio-vps (7+12)

As far as why are the oninits in the sleep state, this is purely HP-UX thing. Generally it is either
- blocked waitiong for IO (disk, semaphore, lan...)
- blocked on priority
- Other blocks (system etc)

I SUSPECT that you are suffereing from priority ageing/blocking as all the oninits were 178, usually I would expect to see 156 (ish).... & yes there is a flag NOAGE in ONCONFIG, speak to IBM-Informix about this. If you have glance you can see % wait stats "glance -W" & choose PID

Other that
o "set explain on" in a query
o seq_scan_cost.sql (attached)
o What steve said
I cant think of much else.

DO remember that
o if you are extracting 20-30% of a table a sequential scan is a good thing
o indexing things like true/false is BAD

Tim

[Tim D Fulford]

oops