slow system response

[Elena Leontieva]

Hello,
This is the problem that I have:
- DB users complain about slow response;
- df -t is scrolling slow;
- gpm shows 28% blocked on system, which is a catch-all WAIT, so would not tell much;

Network traffic is very low.
Other outputs do not show any clues:
cocgk02(root):/> vmstat 5 5
procs memory page faults cpu
r b w avm free re at pi po fr de sr in sy cs us sy id
0 0 0 10953 74706 37 4 0 0 26 0 0 1105 532 97 3 1 97
0 0 0 10965 74301 145 6 0 0 48 0 0 1065 849 184 2 1 97
0 0 0 11257 74293 119 7 0 0 57 0 0 1071 806 166 2 1 97
0 0 0 11188 74652 90 7 0 0 86 0 0 1061 705 100 1 1 98
0 0 0 11605 74652 111 8 0 0 80 0 0 1065 1285 86 2 1 97

sar -d 5 5
Average c0t6d0 2.84 0.50 4 34 3.00 12.01
Average c0t5d0 2.60 0.50 4 33 3.07 11.14
Average c0t3d0 2.56 0.50 3 22 4.00 25.52
Average c0t4d0 0.56 0.50 0 6 6.34 14.94
Average c4t3d0 1.08 0.50 1 19 4.98 10.40
Average c4t4d0 0.04 0.50 0 1 8.52 11.03

The last change on the server - installation of security patches, but they were installed on other machines where no problems occured so far.
I know that DBA did something with indexes over the weekend that could affect Oracle users, but what this has to do with the "df -t"?
HP-UX 11.00, Oracle

Any ideas are appreciated.
Elena.

[James R. Ferguson]

Hi Elena:

Consider a stale NFS mount or a 'pfs_mount' gone "bad".

Regards!

...JRF...

[doug mielke]

MY GOD...DBA + INDEXES = bad day for admins.

condition: System looks well behaved, but very busy.

check out a sar -M, look for high user usage, low system and i/o, and near zero idle.

Let us know if you have that...

[James Randall]

Something generating a core file to an NFS mount might also give the same slow symptoms.

[Elena Leontieva]

The server is not busy:
sar -M 5 5 :
Average 0 0 0 2 97
Average 1 3 4 0 93
Average 2 1 1 1 96
Average 3 1 1 1 97
Average system 1 2 1 96

The server has one NFS mount point where shared software reside (like perl, crisp, etc.), - no DB or application files.

Elena

[doug mielke]

more details:
df, and every other i/o function can slow if the DBA removes / alters indexes. Even though indexes are present, there are conditiona where they aren't used. If your system had grown, then there will be many extents in each table, and if index is unusable ( i.e. cost based vs rule based creation) then the resulting full table scans take all system resources. The hint is that much of the load shows up under the oracle application user, and not system i/o.
worst case looks like
sar -M
100 0 0 0
98 2 0 0
100 0 0 0 etc.

[doug mielke]

Sorry Elena,
I rattled on before refreshing my screen.

the NFS or CIFS mounts are always suspect, but a quick copy test can verify their speed.

DNS server response can also be a killer.
nslookup nfsname can tell you how slow response is, or a tracert.

If the NFS mount in your /etc/hosts, you can move the /etc/resolv.conf file ( although this can have negative effects on other connections) and see if perf. improves, or moduify the nsswitch file to have it search files first.

[Steven E. Protter]

If any of this database traffic is travelling through a NIC card, try these tests.

lanscan

test every card with the following command:

lanadmin -x 0

I've run into a number of circumstances where cards I thought were 100 BaseT Full Duplex were not.

If networking is a possible cause, make sure the switch settings are explicit, not auto negotiate. I'm getting away with auto negotiate on a Dell switch at home, but there are known issues with Cisco switches.

You may need to hard code your duplex settings in a file called /etc/rc.config.d/hpbtlanconf

I'm attaching a perf data collection script, recently debugged and tested for collecting data in the background.

Also, here is a link for Oracle performance tuning written by one of HP's Oracle PErformance guys.

http://www2.itrc.hp.com/service/cki/search.do?category=c0&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchString=UPERFKBAN00000726&search.y=8&search.x=28&mode=id&admit=-1335382922+1058558917419+28353475&searchCrit=allwords

SEP

[Con O'Kelly]

Hi Elena

To me, your sar & vmstat figuures do not indicate any obvious performance issues.
Disks are not especially busy, you have nearly 300MB of free memory & no paging and CPU is doing nothing.

If DB users report slow response then it could very easily be a result of the index changes.
In my experience the df command is very slow to respond either when there is intense disk activity or its trying to read an NFS mount and there is an issue with NFS.
It may just be coincedental that you have issues with DB and df at same time. Could well be 2 different reasons.

Obviously the figures you have given are a point in time data.
What I'd do is gather historical perf data (if yo're not already doing so), either through MWA if its running or with sar.
If the DB users complain about poor performance, then determine the exact times they had "poor" performance and examine your historical data.
If you can't see any issues with CPU, Memory, disk, network etc then you have to point any issues back to them. Its always possible they may need to adjust some parameters in init.ora file or look at sql code etc.

Regarding the df issue, I'd look closely at what JRF said about NFS. Use nfsstat to determine if there's any issues with nfs. Presumably you have the same problem using bdf etc.
What about if you use df -tl or bdf -l (ie only look at local file systems). Is there still the same issues with df being slow.?
Cheers
Con

[Bill Hassell]

Have your DBAs look at the indexes for balance. Lots of row insertions can sometimes create unbalanced indexes which cause a LOT of extra time in some SQL scripts. Rather than re-index everything, I believe the DBA can rebuild indexes is a relatively short time.

The df (or much more useful bdf) command will take a long time when large numbers of changes are occuring within mounted directories. Reading has no effect, but writing, especially creating and deleting files (for example, an SGA that is too small to hold sorts in RAM and must resort to disk-based sort files) will really slow down these directory summary reports.

[malay boy]

Hi
Performance problem have to look at all angle.O/S,Database and application.

We also using HP , Oracle database and customize application sitting in the same box.From my previous experience most of the performance "hick-up" is bad application.

I assumed you are using oracle.Please asked the DBA to run the utlbstat and utlestat.This will give a good picture on database performance.

And yes,rebuilding index on a highly populated
table is a good idea.

regards
mB

[Tim D Fulford]

Elena

Your disks may not be busy, but by golly are they slow, 10-25 ms service time..... I would expect 5-8ms as JBODS & 1-3ms for an intellegent disk-subsystem.

Do you have MeasureWare statistics BEFORE things went pear shaped? I assume you do as you have gpm, if not then ignore rest of post. If so.... check out the service times of your disks. If you have an old version of MeausreWare do disk% * 10/ Phys IO, this is roughly the service time in ms.

get your extract
/opt/perf/bin/extract -xp -gd -r -b -e
this wil produce two files xfrdGLOBAL.asc * xfrdDISK.asc. The attached file contains a report-file & script to process the *.asc files. Get out MS Excel & start plottiong!!!

The other advantage of this is that you can do a before & after image of the problem!!!

Regards

Tim

[Elena Leontieva]

Some more details:

Do not have CIFS mounts;

No DNS;

I am alomost sure that NFS is not a problem (ran nfsstat, checked the NFS traffic on the switch, DB files are not on NFS mount);

Server has only one NIC ( 100BT, FULL, hardcoded);

The DB traffic is not going through NIC, because the DB files reside on four local Jamaica drives and XP256 (FC connection);

Do not have MWA;

bdf response is instant, as opposed to df -tl, which is scrolling line by line;

I still see about 30% blocked on system, even after hours; on my other, very busy server, this number is less than percent. (?)

The DBA is investigating to see if it is database-related.

Many thanks for all your responses.
Elena.

[Elena Leontieva]

I guess Con is right, i.e. DB slow response and df -l are different issues. I found another server where the DB is down and nothing else is running, but df -l is scrolling slow and gpm shows 50% blocked on system.

Elena.