Are those disk ratios good?

[Eric Antunes]

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

[Eric Antunes]

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

[Saurav_1]

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

[Eric Antunes]

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??

[Fred Ruffet]

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

[Eric Antunes]

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???

[Fred Ruffet]

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

[Eric Antunes]

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?

[Eric Antunes]

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

[Eric Antunes]

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?

[Ivajlo Yanakiev]

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

[Ivajlo Yanakiev]

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.

[Fred Ruffet · Accepted solution]

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

[Jean-Luc Oudart]

Hi Eric,

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

Regards
Jean-Luc

[Ivajlo Yanakiev]

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.

[Fred Ruffet]

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

[Ivajlo Yanakiev]

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.

[Ivajlo Yanakiev]

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

[Fred Ruffet]

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

Regards,

Fred

[Eric Antunes]

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

[Fred Ruffet]

To be comprehensive, I start by fixing one thing. What I'll call "Block Buffers" is Oracle size and defined by db_cache_size parameter. Other side (OS) is IO Cache (defined by parameters dbc_max_pct and dbc_min_pct for the dynamic settings).

When you request blocks from your Oracle session, Oracle requests a little bit more. So do OS. So OS put in cache more than more you request...

On a system I manage, it came to take system to swap, vhand (paging daemon) using 100% of CPU and so on. It's an extreme case (not so rare, some others on this forum had it too). Maybe on your system, it will be less, but it will make IO overhead, caching overhead and will drive you to more or less poor perfs.

Regards,

Fred

[Eric Antunes]

Fred,

That's right, db_cache_size or db_block_buffers in my Oracle version (8.0.5). That's the amount of memory you will allocate at instance startup for Buffer Cache for the Oracle side. I've only 16M (2000 buffers) in my instance and it seems to be enought.

I have oldy servers (4 years) and aprox. 60 concurrent users every day and I've never see this vhand process on the top. My 3 top processes are almost always RDBMS or APPS processes. Sometime I get vxfsd process on the 3 top list but never doing heavy stuffs...

My swap is always normal: I have never saw more than 30% of the avail. space used. Here is the Production server swap info.:

# swapinfo -tam
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 1024 89 935 9% 0 - 1 /dev/vg00/lvol2
dev 1024 88 936 9% 0 - 1 /dev/vg00/lvol9
reserve - 1104 -1104
memory 1129 221 908 20%
total 3177 1502 1675 47% - 0 -

I have some chaining problems that I'm solving day after day. Chaining is one of the more dificult issue in tuning Oracle databases! You may also have chaining problems?

Regards,

Eric

[Eric Antunes]

Fred,

select rfcr.value*100/(tsrg.value+tfbr.value) "Chaining Ratio"
from v$sysstat rfcr,
v$sysstat tsrg,
v$sysstat tfbr
where rfcr.name='table fetch continued row'
and tsrg.name='table scan rows gotten'
and tfbr.name='table fetch by rowid'

What is your chaining ratio on this system?

Eric

[Fred Ruffet]

You may never seen vhnd in top processes. As I said it was extrem conditions. I think that problem for you may be that you don't get bad IOs, but coordinating Buffers, you have more IOs that you need.

8.0.5... It's a bit old. If only we were given the right to upgrade to version we want :)

Chaining rows ? I give you for 3 representatives DB here (approx. 100GB, 50GB and 20GB) with same application.

Chaining Ratio
--------------
,032180179
,001366934
,010002747

It doesn't really worrying me. Note that I run 9iR2 DBs. I find 8i had brought many enhancements for Developpers, whereas 9i brought some for DBAs. I use locally managed tablespaces, segment space auto management, and so on. It's also probably provides better performances for an equivalent configuration.
Last year, we upgrade some DBs from 7.3.4 to 9i. Export/Import. Some modifications (new features) but not much. Users came to tell perfs where really better.

Regards,

Fred

[Eric Antunes]

Fred,

Indeed I've Oracle 8i already "apps patched" and ready to upgrade... Does it bring advantages in performance?

Well, I've this ratio on Production but it doesn't please me:

0,00442519328443966