disk bottleneck issue.

[Kishore Gowda]

We have a RP8400 system running 11i OS & Oracle 8i database and custom oracle application. EMC symmetrix is the storage medium with dual fiber channel connection from the server.

From day one, the disk IO performance has been an issue with this application. Essentially, SAR indicates well over 80% utilization during business hours and frequently spikes over 90% during peak hours. Out of which, waitIO is constantly as high as 50 - 60% during busines hours. On Glance plus, we frequently receive "Disk Bottleneck Probability = 100%" warnings.

To this day, We have made following changes on system to reduce waitIO, but none have been effective thus far.

1. EMC did a performance analysis on Symmetrix using workload analyzer; report
indicated that Cache and channel ports were not saturated, read-and-write hit rates was not unusual, and there were no spindle (disk) contention.

2. Application vendor suggested that we should stripe all disks used for
Oracle datafiles (Disks weren't striped at this point, they were used in
concatenated mode). So, we recreated hardware based striped metas (256k * 4 = 1mb stripe depth) and moved the Oracle files over to striped volumes. Result, we didn't see any improvement in Wait IO.

3. Application vendor suggested that we should use software striped volumes to keep the OS IO size the same as Oracle IO size (128k). So, we added 16 more disks on symmetrix, created four way software striped volume set with 32k stripe size on each member. Spread the Oracle datafiles and indexfiles between hardwared striped metas and software striped volumes. Result, no improvement in WaitIO metric in SAR, and glance plus continues to warn on disk bottlenecks.

4. Upgraded the server from Nclass (8way 550 Mhz) to RP8400 (10 way 750Mhz), No change in system performance and disk bottleneck continues to be an issue.

I have attached SAR report, from which you can see that average wait and service times are OK. Can this problem due to a poorly designed database application making too many IO calls? Your thoughts are greatly appreciated.

Regards,
Kishore

[John Bolene]

I bet that is a screaming machine now.


Question..how is the user response? Is it OK or is it poor?

The HPUX disk utilization metrics are made to work with slower disks and do not report valid statistics with fast disk like VA, XP, and EMC.

We have both VA and XP with reported disk utilization problems, but with no queue and no user problems and no disk channel problems.

You may not have a problem unless the users are reporting slow response.

[John Poff]

Hi,

What are your mount options on your filesystems? Are you mounting them with 'mincache=direct' to bypass the system buffer cache? I ran into a similar problem with a filesystem on an 11i box connected to an EMC array, and when I remounted the filesystem with the default options, the I/O waits went away. I have seen some other people posting here on the forum saying that under 11i the default options are the best, as opposed to using 'mincache=direct' like we did on 11.0.

It's easy enough to test, and you can do a remount option and change it on the fly without having to unmount the filesystem. Just an idea.

JP

[Michael Tully]

Also have a look at some later patches than what you currently have. If your using fibre, you might also want to look at a later driver version. What do you have set up as your timeout value for your PV's. The default is normally far too low, and EMC will recommend something like 180.

[harry d brown jr]

Amount of memory is what?

SwapSpace??

SGA size?

bad block allocation off??

Do you see IO on the SYMM side on both FA ports? If your fibre cables are from corning, you might want to check the pairs to make sure they aren't "swapped".

live free or die (in maine today for the weekend working on aix boxes)
harry

[Yogeeraj_1]

hi,

High time you run a STATSPACK report and start analyzing.

See attached document for some tips!

Regards
Yogeeraj

[Kishore Gowda]

John B,

High waitIO or Glance disk warnings doesn't seem to have any effect on end user; our users are happy with performance of the system, and the times that we have had issues with response times, had nothing to do with disk bottleneck.

[Kishore Gowda]

How can you "remount" the filesystems? Could you please post the syntax for remount? We are using mincache=direct option on all Oracle related filesytstems; I would prefer to remount these filesystem without the mincache option.

Thanks!

[John Poff]

Hi,

Assuming you have Online JFS, here is how to re-mount a filesystem:

mount -F vxfs -o delaylog,remount /somefilesys

You can specify whatever options you want after the -o option, separated by commas. Since the default for mincache is to use the buffer cache, I think you don't need to specify any options for it on the remount.

Running a 'mount -v' afterwards should verify your new mount options. Also, you'll need to have the filesystem listed in /etc/fstab for the remount to work. You'll have to wait a few seconds for the new mount options to take effect, so don't worry if the 'mount -v' doesn't show the new options right away.


JP

[John Poff]

Just to make sure I had the syntax right, I did a couple of remounts on a filesystem on one of our test boxes [also an rp8400 on 11i connected to an EMC array]. It didn't have the mincache=direct option on the filesystem, so I turned it on and then turned it off. Here is what it looks like:

# bdf /u12
Filesystem kbytes used avail %used Mounted on
/dev/vg_orahome/lvol3
2097152 1047108 984480 52% /u12

# mount -v | grep u12
/dev/vg_orahome/lvol3 on /u12 type vxfs delaylog on Sat Nov 23 12:56:39 2002

# mount -F vxfs -o delaylog,mincache=direct,remount /u12
# mount -v | grep u12
/dev/vg_orahome/lvol3 on /u12 type vxfs delaylog on Sat Nov 23 12:56:39 2002

# mount -v | grep u12
/dev/vg_orahome/lvol3 on /u12 type vxfs delaylog,mincache=direct on Sat Nov 23 1
2:56:39 2002


# mount -F vxfs -o log,remount /u12
# mount -v | grep u12
/dev/vg_orahome/lvol3 on /u12 type vxfs log on Sat Nov 23 12:56:39 2002

# mount -F vxfs -o delaylog,remount /u12
# mount -v | grep u12
/dev/vg_orahome/lvol3 on /u12 type vxfs delaylog on Sat Nov 23 12:56:39 2002
#


JP

[Kishore Gowda]

Thanks John, I tested remount on our test server and it worked without any errors. I will try this out in production later this evening.

[Kishore Gowda]

Yogeeraj,

Thanks for the suggestion. Could you please attach the "tips" for running statspack. Thanks!

[Yogeeraj_1]

hi,

To start, note that:

Statspack collects stats at a point in time and then can "diff" the stats
between two points in time. Looking at b) again -- the report you run that
compares the second with the first snapshot basically just "subtracts". If at
the second snapshot you have done 1,000,000 IO's and at the first, you had done
900,000 -- the report will be able to tell you you did 100,000 IO's in the
observation period.

Installation:
Statspack is designed to be installed when connected as INTERNAL or more appropriately, as SYSDBA (CONNECT SYS/CHANGE_ON_INSTALL AS SYSDBA), although it will execute a CONNECT INTERNAL. In order to install, you must be able to perform that operation. In may installations, this will be a task that you must ask the DBA or administrators to perform.

In fact, Statspack is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. The installation script (statscre.sql -> in 8.1.6 or spcreate.sql -> in 8.1.7) calls several other scripts in order to create the entire Statspack environment.

All the scripts you need for installing and running Statspack are in the $ORACLE_HOME/rdbms/admin directory

To install Statspack, connect to the Oracle database as internal and run the statscre.sql or spcreate.sql script. This step creates the user perfstat, who owns all the PL/SQL code and database objects created as well as the Statspack tables, constraints, and the statspack package. This user has limited, query-only privileges on the V$ views required for performance tuning. The script prompts you to specify the user perfstat's default tablespace and temporary tablespace. It then asks you to specify the tablespace for the Statspack data-collection tables and indexes???Statspack does not assume you want to use the default tablespace.

The simplest interactive way to take a snapshot is to log in to SQL*Plus as the owner perfstat and execute the statspack.snap procedure:

SQL> connect perfstat/perfstat
SQL> execute statspack.snap;

This stores the current performance statistics in the Statspack tables. You can compare the data with other snapshots and begin to look for differences that account for performance problems.

In order to compare performance from one day, week, or year to the next, you need multiple snapshots taken over a period of time. The simplest way to collect a series of snapshots is to automate data collection at regular times. You can do this using the Oracle dbms_job procedure within the database to schedule the snapshots. (Statspack includes the statsauto.sql (8.1.6) or spauto.sql (8.1.7) script to assist you with this configuration.)

hope this helps you in getting started!
Regards
Yogeeraj