topic Re: Are those disk ratios good? in Operating System - HP-UX

Are those disk ratios good?

Eric Antunes — Fri, 19 Nov 2004 06:24:19 GMT

Hi,

I would like to know if the next read and write ratios are good (I get them during a large delete from an Oracle table)?

11:23:06 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s
11:23:12 10 47 78 177 178 1 0 0
11:23:18 56 64 13 180 181 0 0 0
11:23:24 43 189 77 169 168 0 0 0
11:23:30 14 121 88 178 180 1 0 0
11:23:36 14 68 79 177 177 0 0 0
11:23:42 14 153 91 167 185 10 0 0
11:23:48 17 48 64 164 164 0 0 0
11:23:54 7 62 89 150 149 0 0 0
11:24:00 7 85 92 152 153 1 0 0
11:24:06 18 77 76 147 146 0 0 0
11:24:12 15 51 70 148 148 0 0 0
11:24:18 36 58 37 138 140 2 0 0
11:24:24 49 50 2 97 101 4 0 0
11:24:30 10 26 64 115 113 0 0 0
11:24:36 46 117 61 172 171 0 0 0
11:24:42 76 147 48 160 176 9 0 0
11:24:48 26 65 60 114 118 4 0 0
11:24:54 22 136 83 134 133 0 0 0
11:25:00 34 55 39 188 189 0 0 0
11:25:06 69 139 50 186 183 0 0 0
11:25:12 99 274 64 155 165 6 0 0
11:25:18 10 61 83 143 144 0 0 0
11:25:24 22 70 68 151 150 0 0 0
11:25:30 12 48 74 170 170 1 0 0
11:25:36 12 55 79 153 154 0 0 0
11:25:42 13 159 92 155 172 10 0 0
11:25:48 18 166 89 154 156 1 0 0
11:25:54 46 130 65 162 161 0 0 0
11:26:00 18 89 80 162 162 0 0 0
11:26:06 29 94 69 154 156 1 0 0
11:26:12 6 58 90 161 161 0 0 0
11:26:18 8 32 73 158 158 0 0 0
11:26:24 10 36 72 171 172 1 0 0
11:26:30 8 34 77 162 162 0 0 0
11:26:36 11 74 86 151 150 0 0 0
11:26:42 47 142 67 144 165 13 0 0
11:26:48 13 30 56 166 165 0 0 0
11:26:54 11 52 79 159 158 0 0 0
11:27:00 15 53 72 155 158 1 0 0
11:27:06 7 64 89 160 158 0 0 0
11:27:12 46 385 88 150 180 17 0 0
11:27:18 10 117 91 157 218 28 0 0
11:27:24 8 34 75 156 155 0 0 0
11:27:30 10 47 78 170 170 0 0 0
11:27:36 287 240 0 174 174 0 0 0
11:27:42 249 355 30 145 158 9 0 0
11:27:54 0 12 99 104 105 1 0 0
11:28:00 11 34 67 122 119 0 0 0
11:28:06 237 386 39 161 163 1 0 0
11:28:12 6 93 94 120 119 0 0 0
11:28:18 0 15 98 152 151 0 0 0
11:28:24 119 203 41 156 154 0 0 0
11:28:30 189 350 46 168 168 0 0 0
11:28:36 148 245 40 169 172 2 0 0
11:28:42 2 64 98 144 145 1 0 0
11:28:48 13 50 75 121 121 0 0 0
11:28:54 113 401 72 138 156 12 0 0
11:29:00 206 390 47 123 124 1 0 0
11:29:06 59 94 37 172 171 0 0 0
11:29:12 2 22 90 151 150 0 0 0
11:29:18 12 68 82 107 108 2 0 0
11:29:24 62 190 68 133 134 1 0 0
11:29:30 64 272 77 148 147 0 0 0
11:29:36 37 280 87 148 147 0 0 0
11:29:42 0 32 99 104 104 0 0 0
11:29:48 59 50 0 105 105 0 0 0
11:29:54 8 140 94 59 80 26 0 0
11:30:00 1 7 83 5 4 0 0 0
11:30:06 0 7 100 4 6 27 0 0
11:30:12 1 6 89 2 2 0 0 0
11:30:18 22 22 0 3 3 0 0 0
11:30:24 98 248 61 12 18 35 0 0
11:30:30 0 6 100 4 3 0 0 0
11:30:36 20 42 53 2 3 35 0 0
11:30:42 235 208 0 12 8 0 0 0
11:30:48 164 239 31 160 158 0 0 0

I'm asking this question because I don't know how to interpret ratios greater than 1...

Thanks,

Eric

Re: Are those disk ratios good?

Eric Antunes — Fri, 19 Nov 2004 06:26:54 GMT

The first post was the output of sar -d 6 100.

Here is the final output of sar -b 5 100:

Average c1t2d0 0.51 0.50 1 7 3.34 9.26
Average c2t2d0 0.32 0.50 1 5 3.53 7.75
Average c4t8d0 25.00 0.50 44 1023 4.71 8.17
Average c4t9d0 50.24 0.50 89 1429 4.86 6.55
Average c4t10d0 8.92 0.50 17 351 4.94 7.10
Average c5t8d0 17.63 0.50 35 904 4.82 7.05
Average c5t9d0 43.73 0.50 84 1369 4.84 5.91
Average c5t10d0 6.52 0.50 14 318 4.92 6.21

Re: Are those disk ratios good?

Saurav_1 — Fri, 19 Nov 2004 06:31:54 GMT

USE glance , GPM and pv to analyse the performance of the disks. PV will give you output in graphs. Glance you can check the highest used disk and process.

Usage of swap and its equal distribution will also help performance tuning.

Cheers

Re: Are those disk ratios good?

Eric Antunes — Fri, 19 Nov 2004 06:39:49 GMT

Here are the PRODUCTION ENVIRONMENT statistics:

sar -d 4 25

11:43:38 device %busy avque r+w/s blks/s avwait avserv

...

Average c1t2d0 5.98 1.81 10 144 8.09 10.31
Average c2t2d0 3.07 2.45 6 109 9.74 8.52
Average c4t8d0 22.39 0.50 47 1079 4.87 6.09
Average c4t9d0 18.57 0.88 33 989 6.25 8.01
Average c5t8d0 10.48 0.50 17 490 4.73 8.25
Average c5t9d0 6.10 1.44 13 540 7.26 7.49

sar -b 4 25

11:44:40 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s
11:44:44 105 1466 93 44 182 76 0 0
11:44:48 253 432 41 24 27 10 0 0
11:44:52 224 411 45 14 15 8 0 0
11:44:56 627 1104 43 17 22 20 0 0
11:45:00 862 1466 41 34 39 12 0 0
11:45:04 261 2960 91 20 61 67 0 0
11:45:08 38 2126 98 18 80 77 0 0
11:45:12 2 226 99 17 46 63 0 0
11:45:16 2 677 100 10 26 61 0 0
11:45:20 1 171 99 5 6 9 0 0
11:45:24 7 114 94 2 6 62 0 0
11:45:28 34 392 91 3 10 74 0 0
11:45:32 2 66 97 4 4 0 0 0
11:45:36 0 86 100 6 6 0 0 0
11:45:40 24 172 86 8 17 51 0 0
11:45:44 13 128 90 9 9 0 0 0
11:45:48 84 226 63 5 5 5 0 0
11:45:52 10 143 93 4 4 0 0 0
11:45:56 26 157 84 5 5 0 0 0
11:46:00 8 938 99 3 8 66 0 0
11:46:04 74 1876 96 20 34 42 0 0
11:46:08 22 552 96 5 8 39 0 0
11:46:12 466 833 44 31 60 49 0 0
11:46:16 479 1126 57 7 8 15 0 0
11:46:20 525 1215 57 4 4 0 0 0

Average 166 763 78 13 28 54 0 0

I had Glance instaled but it was a demo/try and has expired. What is GPM and PV??

Re: Are those disk ratios good?

Fred Ruffet — Fri, 19 Nov 2004 06:49:57 GMT

Eric,

This is a bit hard to read, so I'll stop on the header :)

This is output of sar -b, (not d). This reflects buffer activity, whereas -d reflects disks activity. It makes me think of something (I keep in mind preceding thread we discussed in) :
Don't you bypass the cache for DB files ? If you have online JFS on your machine, you'd better mount FS for DB files with options convosync=direct and mincache=direct. It will bypass the cache (Oracle has its own) and you will not see such activity on your sar output. More important, your IOs performances on your DB will be better.

Problem is that, even if you have online JFS, you will need to unmount to apply those change, so you will need to stop DB...

Note that you must not bypass cache for FS other than those having DB files.

Regards,

Fred

Re: Are those disk ratios good?

Eric Antunes — Fri, 19 Nov 2004 06:50:02 GMT

Another statistic, more extended in time (50 iterations):

sar -b 4 50:

11:44:40 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s
...
Average 15 383 96 8 21 61 0 0

The Buffer cache hit ratio for write requests seems to be poor (61%), isn't it???

Re: Are those disk ratios good?

Fred Ruffet — Fri, 19 Nov 2004 07:54:10 GMT

Eric,

Making a big delete doens't necessarly mean you will have to look at wcache. Oracle will take blocks from your disks (rcache is 96%), but won't necessarly have to write to disks (blocks are in Oracle Cache).

Other point. If your blocks are for a long time in Oracle cache and got out of write cache. When you'll have to make your writes, you will have a bad (relativ.) cache ratio as long as they have been read a long time ago.

What I mean (and merge with my precedent reply) is : bypass cache and look at Oracle cache, not System one.

Regards,

Fred

Re: Are those disk ratios good?

Eric Antunes — Fri, 19 Nov 2004 07:58:51 GMT

Hi Fred,

Here we are we the same discussion... ;)

I've tried this solution (mount options) on the TEST environment but the results seems to me to be worst.

Here the output of 3 statistics while I was purging an Oracle table from the applications:

12:20:46 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s
...
Average 73 160 55 158 160 1 0 0
...
Average 14 115 87 83 88 6 0 0
...
Average 22 118 82 89 99 10 0 0

Here the output of 1 statistics while I was purging an Oracle table directly from SQL*Plus:

oracle@hp440> sar -b 4 9

HP-UX hp440 B.11.00 U 9000/800 11/19/04

13:08:41 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s
13:08:45 44 114 61 230 254 9 0 0
13:08:49 1 11 91 147 147 0 0 0
13:08:53 0 4 100 154 152 0 0 0
13:08:57 46 123 62 205 206 0 0 0
13:09:01 112 206 46 316 354 11 0 0
13:09:05 89 162 45 249 264 6 0 0
13:09:09 0 6 96 149 148 0 0 0
13:09:13 1 6 88 152 151 0 0 0
13:09:17 18 312 94 290 304 4 0 0

Average 35 105 67 210 220 4 0 0

What do you think?

Re: Are those disk ratios good?

Eric Antunes — Fri, 19 Nov 2004 08:00:40 GMT

So, I'm not getting more than 10% in Buffer cache hit ratio for write requests... :(

Re: Are those disk ratios good?

Eric Antunes — Fri, 19 Nov 2004 08:07:23 GMT

In this TEST environment, I've mounted the logical volumes with:

/dev/vg01/vol1 /disc1 vxfs rw,suid,delaylog,nodatainlog 0 2
/dev/vg01/vol2 /disc2 vxfs rw,suid,delaylog,nodatainlog 0 2

I have not Online JFS, so I can't use convosync and mincache.

Do you think I'll get better I/O speeds just changing from datainlog to nodatainlog?

Re: Are those disk ratios good?

Ivajlo Yanakiev — Fri, 19 Nov 2004 08:27:45 GMT

Do you think I'll get better I/O speeds just changing from datainlog to nodatainlog?

Re: Are those disk ratios good?

Ivajlo Yanakiev — Fri, 19 Nov 2004 08:31:11 GMT

nodatainlog will speed up disk i/o but be aware that you will lose recvoery of FS if your server crash.
nodatainlog will almost disable joranl FS ability.

Re: Are those disk ratios good?

Fred Ruffet — Fri, 19 Nov 2004 08:48:34 GMT

Ivajlo,

nodatainlog is also something I would recomend. It is not as you said : It will not mix on disks data and log. It gives better IO especially for sequential ones. What you describe is nolog option.

Eric,

If you set up options, do not look anymore at sar -b (buffer stats) as long as you bypass it. You can see sar -d but it will give you no more than pure disk IO perfs from your disks. What you will have to look at are stats for Oracle.

Regards,

Fred

Re: Are those disk ratios good?

Jean-Luc Oudart — Fri, 19 Nov 2004 08:54:54 GMT

Hi Eric,

I would also run a statspack report on Oracle to see where contentions are.

Regards
Jean-Luc

Re: Are those disk ratios good?

Ivajlo Yanakiev — Fri, 19 Nov 2004 08:57:11 GMT

Advantage using nodatalog is less than 1-2%.
For me it is pointless.
What you think ?
Radically if you want to optimize disk i/o or any server performance, first hint must be application. Query in Oracle word.
It can bring as more than 300 or more % optimizing.

Re: Are those disk ratios good?

Fred Ruffet — Fri, 19 Nov 2004 09:19:09 GMT

Ivajlo,

1-2% can be meaningful, depending on your IO rate...

Following Oracle Tuning Courses basically, when issuing some perfs problems, you must investigate in such order :
. Queries
. DB perf
. OS perf

Before using GPM and changing anything to your system conf, just start here changing those options on FS.

If you want to go further, just read this document :
http://h21007.www2.hp.com/dspp/files/unprotected/database/HP3KOracle.ppt
I think it will really help

Regards,

Fred

Re: Are those disk ratios good?

Ivajlo Yanakiev — Fri, 19 Nov 2004 09:34:16 GMT

Yes you are right 1% can be important in some case.
But I think this isn't case here.
anyway I downloading files and will read document refered by you.
Sorry that I am disargee you.
My be I'm wrong.

Re: Are those disk ratios good?

Ivajlo Yanakiev — Fri, 19 Nov 2004 09:41:46 GMT

OK I read document. It was realy nice.
Tanks.
Using raw device will end this dispute :)
It will save more I/O :)
Have a nice day

Re: Are those disk ratios good?

Fred Ruffet — Fri, 19 Nov 2004 09:41:58 GMT

Don't worry Ivajlo, and don't be sorry :)
Richness of these forums comes from our different minds and from our discussions.

Regards,

Fred

Re: Are those disk ratios good?

Eric Antunes — Fri, 19 Nov 2004 09:56:16 GMT

Ok,

In fact, the tuning priorities are:

- SQL
- RDBMS (init parameters, tablespaces, etc...)
- OS

I'm done with SQL.

I've also done some kernel ajustments (dbc_max_pct, maxdsiz, maxssiz, ninode, maxswapchunks, etc...)

Now, I'm tuning the database. I've done some work with init.ora parameters and I'm finishing it. After that, I will install and run the statspack scripts...

But, I've one pertinant question:

Block Buffers exists to avoid disk work (more time expensive that memory work) when inserting, updating or deleting a record. So, what's the point avoiding it specially in an ERP system, like the one I'm administrating, that have no heavy transactions??

Eric