informix slow

[someone_4]

hello,
We are having problems with our Informix database is having problems. The DBA's are complaining that dbaccess is slow. And that it is connecting slow to the database. I found a doc on google that says that it could be a dns issue. But everything looks fine on the dns settings. But the problem is intermittent. What are some trouble shooting procedures so I can try to isolate the problem?

Thanks

~ Richard

[Mark Greene_1]

Richard,

There's the obvious stuff: netstat -s and look for high error counts, check your syslog for network or process related errors, run vmstat to look for system performance issues.

If you are running dns on one of your unix box, you can do a kill -SIGUSR1 to activate debugging. Doing that three times takes it to debug level 3 and will give you all sorts of info in the debug file (/var/tmp/named.run) which should help determine if this is really a dns error, or not.

Which Informix product are you running?

mark

[harry d brown jr]

Richard,

If it takes 20 to 30 seconds to connect, then it is a name resolution problem. When it's happening, try using the IP address of the server, as a test.

How are you resolving host names?

live free or die
harry

[Daimian Woznick]

To test if it is a dns issue, change the name resolving on the system to look at the hosts file first and place the server and IP in the hosts file.

We also have problems on a server running Informix. This problem has more to do with the application running and not the database. Records within the database will get locked and then will start eating away at the CPU until it is killed.

Hope this helps.

[someone_4]

Hi,
We are running tcp Informix Dynamic Server Version 7.31.FC4.

here is the output from a script they told me to run to check how fast it connects.Ther script is:

while [ 1 ]
do
echo "select current from systables where tabid = 1"|dbaccess st_switch
done

And here is the output:

Database selected.
(expression)
2002-07-29 11:59:16.000
1 row(s) retrieved.
Database closed.
Database selected.
(expression)
2002-07-29 11:59:52.000
1 row(s) retrieved
Database closed.
Database selected.
(expression)
2002-07-29 12:00:27.000
1 row(s) retrieved
Database closed.
Database selected.
(expression)
2002-07-29 12:01:02.000
1 row(s) retrieved
Database closed
Database selected.
(expression)
2002-07-29 12:01:02.000
1 row(s) retrieved.
Database closed.
Database selected.
(expression)
2002-07-29 12:01:02.000
1 row(s) retrieved.
Database closed.
Database selected.
(expression)
2002-07-29 12:01:02.000
1 row(s) retrieved.
Database closed.
Database selected.
(expression)
2002-07-29 12:01:03.000
1 row(s) retrieved.
Database closed.
Database selected.
(expression)
2002-07-29 12:01:03.000
1 row(s) retrieved.
Database closed.
Database selected.
(expression)
2002-07-29 12:01:03.000


##########

as far as resolving host names it is we are looking at files and the dns.
hosts: files [NOTFOUND=continue TRYAGAIN=continue UNAVAIL=continue] dns
and all of the hostnames that it is talking to are in the /etc/host file.
It never looks at dns anyways. If it doesnt ever look at dns would dns still be an issue if it resovling though hostnames? I am thinking of just using "files" to test it.

Thanks
~ Richard

[Daimian Woznick]

Are you sure that it is resolving from the hosts file? Run the nslookup command for the host and it will confirm this.

[Bill Hassell]

Use nslookup to test name resolution. On your Informix server, type:

nslookup some-host-in-hosts-file

Does it report something like:

------------------------------------
nslookup freedom
Using /etc/hosts on: freedom

looking up FILES
Name: freedom
Address: 115.44.339.103
Aliases: freedom.atl0.hp.com

-------------------------------------

In the above example, everything came from /etc/hosts ("looking up FILES"). Now tell nslookup to bypass the hosts file:

nslookup freedom 115.351.240.3
Name Server: rc.atl0.hp.com
Address: 115.511.240.3

Trying DNS
Non-authoritative answer:
Name: freedom.atl0.hp.com
Address: 15.44.339.103

-------------------------------------

Now we've forced nslookup to use a DNS server. Was the response quick? On the Name Server: line, was the IP address shown? If not, the DNS server doesn't know it's own name and that's bad.

Like most databases that use sockets, it's not just the major servers that should be in /etc/hosts, it should the names of everyone that will connect. For DHCP clients, the DNS server should be ultra-production quality.

Check for any other activity (ie, disk or CPU) when testing the connect time. If the disk is busy, then that is Informix asking for lots of records. If the CPU is busy, then most likely Informaix processes are heavily computing (not much to fix in HP-UX). But if neither are busy during long, multi-second delays, it's likely to be network timeouts.

[Tim D Fulford]

Hi Richard

try checking (the obvious!) out if there are any errors in
# tail /var/adm/syslog/syslog.log
# tail /var/adm/daemons (see /etc/syslog.conf)
# dmesg

also look at if there were any problems with the network
# netfmt -t 10 -f /var/adm/nettl.LOG00

If you have MeasureWare try looking at the netwotk & see if the nmumber of errors or collisions increases during the problem periods.

I'm doint this from memory but I remember that a ndd parameter (tcp_max_conn I THINK???) was set too low on one of our systems. its default swas 20 & I think 256 may be better. Just a stab in the dark.

On the informix side check the vps are doing what they should.
Generally
cpu-vp - 80-90% work
soc-vp - 1-5% work
shm-vp - 1-5% work
aio-vp - 10-15% work
adm, msc, adm, lio, pio - 1% work

$ onstat -g glo
and check which vp's hold your poll threads
$ onstat -g ath | grep pol

Check general performance (from memory so feel free to correct if I got anything wrong)
$ onstat -p
read cache% 90+
write cache 80+
ovlocks == 0
bufferwaits = bufwaits/(pagreads + bufwrits) < 0.07
lock wait ratio = lckwts/(lockreqs) < 0.01

Check checkpoint timings (online.log) as this will freeze server during these periods. < 3seconds

There is loads more (actually a whole book) but the above should be a good "start point"

Regards

Tim

[someone_4]

Tim
what is the title of the book you are talking about?? Attached is the output of the command you sugested. Some of thoose numbers seem really high. We are looking on the network side also.

~ Richard

[Tim D Fulford · Accepted solution]

Richard

it look like you are running out of buffers occasionally!!

bufferwait ratio 22%
ovbuf > 0

Also you have 7 cpu-vps (relatively well balanced) BUT only 1 shm-poll thread and 1 network socket poll thread. This is probably why the 3-cpu has more CPU time than 1-cpu.

My guess is that you predominantly use Informix over the network?

Some things you might try.
1 - Add more socket poll thtreads on the cpu-vps

NETTYPE ipcshm,1,100,CPU
NETTYPE soctcp,6,100,CPU
NUMCPUVPS 7

This moves the socket poll threads to cpu-vp's which is more efficient & will ultimately allow about 600 users to be concurrent (you have about 100 now)

2 - You probably are using Kernel Asynchronous IO (KAIOON=1). The aio-vp is doing fine, I would prefer to see more though, (say 5), but do not bother about that. IF you are running HP-UX 11 there is a "bug" using KAIO in HP-UX (not HP-UX 11i). You can spot it because during busy times the system CPU usage shoots up. You may not be experiencing it. if %sys > %usr you are having problems.

3 - buffers How many do you have?
onstat -P | tail
what are the values for Data, Btree & Other.

----

Were you able to find the ndd parameter I suggested? Sorry I'm still at home so acannot help out more with te name.

The book is called "Experience, experience, experience" & I have not written it yet!!! - Seriously though, Informix have 100's of tuning scripts (www.iiug.org) & a few tuning courses. I'll try to post my version in this thread soon. (I'm rather busy at work currently!!)

Regards

Tim