Disk utilization on 9i oracle.

[Charles Temple]

When doing alot of reads on the database. We get the disk utilization 100%. All the .dbf files are in this one filesystem(441 files). I know we need to break them out. I just got into this environment recently. But is there another reason why when we do reads disk util is 100%? Some ideas would be great. Kernel parms?
Thanks in advance,
Charlie

[Charles Temple]

Just a followup. We are running this on an EVA disk solution and the disks for the database are raid 0+1.

[RAC_1]

As you know, ne thing is you need to split the FS over multiple disks.

How is cache set up?? And ststic or dynamic??

[TwoProc]

Charles, you'll need to run a statspack to see what's up with all the reads. My guess is you've got a really good share of full table scans - due to untuned code, missing indexes, etc. Besides fixing the problem that all .dbf files are in one file system - you'll need to tune it up too. Try to get your database on as many drive spindles, controllers, etc. that you can spare. Separate data from indexes, redo-logs from archive-logs, temp files from undo (redo) files, etc.

[Charles Temple]

HI,

The redos and archives are separte filesystems. Just all the .dbf files are in one big filesystem.
There is only 512 cache on those EVA's so it has to go to disk all the time. we have 16gig of memory on the server side.

Also, 90% of connection to the EVA goes over one controller on the EVA side. We have to split that up.

Any other idea's? Is there lock limit it could be hitting with all .dbf files in one directory?

Thanks,
Charlie

[Eric Antunes]

Hi Charles,

What values in $ORACLE_HOME/dbs/init.ora for the following (equivalent in 9i)?

db_block_buffers
shared_pool_size
log_checkpoint_interval
log_buffer
db_file_multiblock_read_count
sort_area_size

What values for dbc_max_pct and dbc_min_pct?? What output for:

#sar -b 6 30


Best Regards,

Eric

[TwoProc]

Try setting the scsi queue depth ( I know it's not real scsi - it uses the same/similar code base for parts of the driver)

scsictl -m queue_depth /dev/rdsk/..
for each LUN for the drives involved in the disk set.

The default is 8 - try 32.

This can be changed interactively - if you like it change it "permanently" via an rc script on startup.

[Hein van den Heuvel]

The disk utilization 100% may not be significant. It's just a visual misread. Try this for a thought: If every time you issue an IO there is an other IO active, then the disk is 100% busy right? Well, if it was a traditional disk that would be true. But if it is an intellegent controller, like the EVA, then it can handle hundred of IOs to the same 'disk' concurrently! IMHO the OS just can not tell you how busy an EVA lun really it. There might be 8 physical drives behind it, there might be 80. That 10x the IO potential with no way to tell from the OS.

RAC>>> As you know, ne thing is you need to split the FS over multiple disks.

I beg to differ. As per above. That mountpoint might have all, or dozens of disks, supporting it. To carve a large lun up into multiple smaller ones is not likely to help all that much (if any)
Of course, as suggested, you need to allow for more than 8 concurrent IO on an EVA lun.

Anyway,

Instead of doing the math in %busy, please try to get a handle on IO/sec and KB/sec and read-write ratios. Then see if those are in a supportable range.

The suggestion for statspack is good. Check out the READ (and write) TIMES in the per file / per tablespace areas. Is it in the very low (1ms) range for writes? (WB cache).
Is is in the lowish ( 3ms - 10ms) for reads?
Those statspack tables will also tell you which things to move, if you still think you need to move stuff around.

hth,
Hein.

[Yoann Mainguy]

I agree with Hein,

The % uilisation on this type of bay is not a good (only) indicator, you should look at.

We have Oracle 9i running on HP-UX 11.11 and an EVA. In Glance our disk utilization is constantly 100%.

In glance try looking at the disk queue length (type u after you start glance). Although our utilizaton is always 100% the queue is always 0. If the EVA was getting behind this would increase.

Also try "sar -d 5 5" and look at avwait and avserv.

Cheers Simon.

[Charles Temple]

Thanks for all the reply's. I did the sar -d 5 5 and got about 6-7 for avwait. Also in glance the NO WAIT is only 85-90% meaning there is a wait of 1-2 about 15% of the time. That means the eva is getting behind right?

scsictl -m queue_depth on the disk(LUN) that is being 100% is at 8. What impact could I cause if I increase that to 32? Also, how would that resolve the disk utilization being at 100%.
Thanks again.

[Charles Temple]

Sorry, the dbc_max_pct is 10 and min is 5

[Yoann Mainguy]

You have about the same stats as ue. I'm not a unix or Oracle expert but I don't believe they indicate that the EVA is performing poorly.

I think we tried changing the scsictl queue_depth thing and it didn't change anything.

Simon.

[Alzhy]

Unless I miss it somewhere, you did not mention IF you have performance issues. If you do - it will manifest itself with your queues starting to depart from 0 and yur wait times on your EVA LUN(s) will start to increase as reckone from "sar -d".

The default Glance / (parm?) config file does not do justice to accurately getting real I/O subsystem performance. You will need to tweak it so it matches your storage architcture. Please rely on "sar -d" and "glance -u" to give you a better picture of your I/O load.

[Charles Temple]

What are acceptable values for sar -d? When would it appear to look like there are issues? We are going live with the boxes and want to try to be ahead of the users calling in if there are performance issues.

Thanks again for all responses.

Charlie

[Alzhy]

avque -- should be no more than 1.0
avwait -- should be 0 or near 0.
avserv -- should be less than 20.

If your sar -d stats meet the above guidelines, then overall - your I/O subsystem should be responsive enough.

[Eric Antunes]

Nelson (escuse me Charles for using you post),

I've a 5 average value for avwait: can you give me some clue about the reason or anything I should check?

Best Regards,

Eric Antunes

[Yoann Mainguy]

Our stats:

device %busy avque r+w/s blks/s avwait avserv
Av c5t6d0 1.08 0.50 2 12 0.00 5.40
Av c8t6d0 0.96 0.50 2 11 0.00 5.12
Av c19t0d2 80.18 0.50 270 4472 0.00 5.14
Av c19t0d3 82.29 0.50 268 4281 0.00 5.40
Av c19t0d0 0.32 0.50 1 6 0.00 3.79
Av c19t0d1 0.08 0.50 0 1 0.00 2.88

You can see avwait is 0 and the avserv time is the same for all the volumes. All our data/indexs/redo/undo/archive is on c19t0d2 and c19t0d3

[Alzhy]

Eric,

On my EVA connected systems.... avwait is consistently 0. Only if the EVA is swamped to I see it (along with avque) go beyond 0.

What storage subsystem are you using? What other sar -d stats do you observe when you've youe avwait at 5?

[Charles Temple]

Here is my sar -d 5 5 stats.

2:40:37 device %busy avque r+w/s blks/s avwait avserv
12:40:42 c0t6d0 3.37 0.50 6 46 6.17 14.64
c7t6d0 2.18 0.50 5 42 5.89 12.92
c58t0d6 1.19 0.50 20 354 4.69 0.57
c22t1d2 0.20 0.50 2 25 6.10 1.08
c22t0d4 0.40 0.50 2 29 5.67 0.30
c22t0d7 0.99 0.50 21 370 4.75 0.47
c58t1d1 0.20 0.50 1 19 5.71 0.26
12:40:47 c0t6d0 5.85 0.50 26 219 5.14 3.83
c7t6d0 2.02 0.50 4 36 5.04 9.98
c58t0d6 0.20 0.50 2 35 4.70 0.30
c22t1d2 1.61 0.50 23 371 5.17 0.54
c22t0d4 0.81 0.50 16 276 4.66 0.45
c22t0d7 0.20 0.50 2 29 5.38 0.28
c22t0d6 0.20 0.50 1 11 9.32 0.28
c22t0d5 5.85 0.50 14 287 5.13 4.37
c58t1d1 0.40 0.50 2 32 4.74 1.91
c58t0d0 0.81 0.50 8 187 5.36 1.11

[RAC_1]

For Hein,

I agree to you to some extend, but not completely. while it is true that 100 % i/o is not a accurate measure of EVA(for that matter for SAN), but it sure that those disks
for that LUN are hitting high. what I mean is if SAN lun consists 4 pvs and i/o from Os looks 100 %, it certainly sure that few of them are getting hammered. to further analyze it you can use iostat on individual disks, you can use array commands to check thode disks. The point is, it would be possible to pinpoint the disk that is hitting high and then see what files are being present on it and carry it forward.

But I do agree that statspack and i/o stats will go a long way to help on this issue.

Anil

[Jean-Luc Oudart]

It seems there is a lot of datafiles and each oracle checkpoint will update the header :
"Depending on the number of datafiles in a database, a checkpoint can be a
highly resource intensive operation, since all datafile headers are frozen
during the checkpoint. There is a performance trade-off regarding frequency
of checkpoints. "

cf. Metalink doc id 147468.1

what value do you have for (oracle parameters) :
- CHECKPOINT_PROCESS
- LOG_CHECKPOINT_INTERVAL
- LOG_CHECKPOINT_TIMEOUT
- LOG_CHECKPOINTS_TO_ALERT

Regards
Jean-Luc

[Jean-Luc Oudart]

Charlie

as you are running Oracle with EVA backend storage you may want to read this doc
http://h71028.www7.hp.com/ERC/downloads/5983-0322EN.pdf

Regards
Jean-Luc

[Alzhy]

You do have queuing per your sar -d post.

If you're scsi_max_qdepth is 8 or 16, increase it to 32 or even 64 since you are using few LUNs.

scsi_max_qdepth is a dynamic kernel tunable so you need not reboot your box.

[Charles Temple]

what kind of damage or issues can occur by changing that. Right now it is set to 8.

Thanks again for all responses!!!

Charlie

[Alzhy]

There should be none.