topic Re: Database Server is crawling !!! in Operating System - HP-UX

Database Server is crawling !!!

Sritharan — Fri, 21 Mar 2003 08:56:34 GMT

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

Re: Database Server is crawling !!!

T G Manikandan — Fri, 21 Mar 2003 09:11:29 GMT

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

Re: Database Server is crawling !!!

Sritharan — Fri, 21 Mar 2003 09:19:13 GMT

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

Re: Database Server is crawling !!!

Sritharan — Fri, 21 Mar 2003 09:41:16 GMT

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

Re: Database Server is crawling !!!

Steve Lewis — Fri, 21 Mar 2003 12:18:40 GMT

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.

Re: Database Server is crawling !!!

T G Manikandan — Fri, 21 Mar 2003 12:28:46 GMT

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.

Re: Database Server is crawling !!!

T G Manikandan — Fri, 21 Mar 2003 12:36:59 GMT

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

Re: Database Server is crawling !!!

Sritharan — Fri, 21 Mar 2003 13:24:32 GMT

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

Re: Database Server is crawling !!!

Sritharan — Fri, 21 Mar 2003 13:27:20 GMT

Hi,

Sorry this are the attachments

TIA
SRi

Re: Database Server is crawling !!!

Steven E. Protter — Fri, 21 Mar 2003 13:30:46 GMT

Performance data collection script: attached. Its production.

SEP

Re: Database Server is crawling !!!

Michael Steele_2 — Fri, 21 Mar 2003 13:40:49 GMT

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

Re: Database Server is crawling !!!

Steve Lewis — Fri, 21 Mar 2003 14:06:51 GMT

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

Re: Database Server is crawling !!!

Steve Lewis — Fri, 21 Mar 2003 14:45:35 GMT

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.

Re: Database Server is crawling !!!

Sritharan — Sat, 22 Mar 2003 09:12:37 GMT

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

Re: Database Server is crawling !!!

Tim D Fulford — Sat, 22 Mar 2003 11:47:47 GMT

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

Re: Database Server is crawling !!!

Tim D Fulford — Sat, 22 Mar 2003 11:58:06 GMT

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

Re: Database Server is crawling !!!

Sritharan — Sun, 23 Mar 2003 09:16:05 GMT

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

Re: Database Server is crawling !!!

Tim D Fulford — Sun, 23 Mar 2003 18:04:10 GMT

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

Re: Database Server is crawling !!!

Sritharan — Mon, 24 Mar 2003 09:42:53 GMT

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

Re: Database Server is crawling !!!

Steve Lewis — Mon, 24 Mar 2003 10:08:20 GMT

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