Re: Long IO Latency

Some random comments :)

The source code of iometer is available, you can try to adapt it maybe ?

Regions
If you look at your disk drive as a flat structure (no platters heads or anything) then doing random IO between GB 0 and GB 10 will be much faster then doing random IO between GB 0 and GB 200. Same for large arrays.

Indexes & random IO
Range scans on freshly re-created indexes is almost sequencial IO. Thus there's this hidden benefit of rebuilding your indexes (please dont get on me about recommending to rebuild indexes ... ) in addition to removing holes and etc.

If the index has grown in time, then the range scan becomes random IO.

Thus a on a freshly rebuild index, INDEX FAST FULL SCAN and INDEX RANGE SCAN should not be that different in speed ;) Too bad oracle doesn't have a stat for that.

"I did not notice any cache misses at the OS level." That's exactly my point. Your results will be affected by OS cache. The working set of random IO that Oracle does is probably much larger then your test, thus your timings will be much better during your test.

On 8/16/05, Shivanischal A <shivan_at_subexsystems.com> wrote:
> Hi,
>
> Replies inline....
>
> On Tue, 2005-08-16 at 22:06, Christo Kutrovsky wrote:
> > Welcome to the world of disk IO.
> >
> > You approach is generally correct. There are a few things you need to consider:
> >
> > - Random IO is much slower due to seek times
> > - Sequencial IO benefits from large reads
> > - OS cache
> >
>
> I did not notice any cache misses at the OS level. Seems to be sized
> properly. btw, the OS is an AIX 5.2 (pSeries). Asynchronous I/O has been
> enabled; is AIO stable on this platform? IBM claims so ;-)
>
> > You approach of finding out whether the latency you get is acceptable
> > is on the right way.
> >
> > Keep in mind disk manufacturer always give you the maximum sequencial
> > IO speed. Sequencial IO speed is faster in the beggining of the disk
> > drive, and almost 2x slower towards the end.
> > Usually sequencial IO (such as full table scans, or copying a file, or
> > taking a backup) is measured in Mb/sec. Larger reads yeld faster
> > speeds.
> > Random IO however (table access after index range scan, nested loop
> > joins) are measured in IO / sec.
> >
>
> Yes, I was aware of Random IO being slower. Hmm... using indexes has
> deeper impacts(!). I knew that indexes are not always good, but never
> thought from the Random IO perspective.
>
> > Doing Random IO in specific disk region is much faster then doing
> > random IO over the entire disk.
> >
>
> What do you mean when say "disk region"? Are you talking about the
> various mount points? Or about the inner, central & outer regions of
> individual platters?
>
> > Some approximate number are:
> >
> > Sequencial IO: 55 Mb/sec
> > Random IO: 120 IO/sec (1 sec = 1000 ms , 1000 ms / 120 IO = 8.33 ms latency)
> >
> > Depending on how fast your disks are (7200 rpm, 10000 rpm, 15 000 rpm)
> > you will get different latencies.
> >
> > Be carefull with OS cache or SAN (controller) cache.
> >
>
> I remember that of the 2 SAN controllers only was active and being used.
> I assume the bandwidth 'promised' by the controller will be specific to
> each vendor.
>
> > You dont need to write programs, www.iometer.org.
> >
>
> Thanks! this is the reason we need specialists. iometer is good. Will
> use it henceforth on my Linux box. However, its not available on AIX
> platform. Is anyone aware of similar tools on AIX? Till then, I'll have
> to improve my program. The program opens all large Oracle datafiles
> spread fairly across mount points and then then repeats the "random-seek
> and read" sequence. The program is multithreaded to simulate real-life
> activity as far as possible. It requires more improvements. Does anyone
> have suggestions to improve it?
>
> Thanks for everyone who have taken the time & effort to reply me.
> Have a good day.
>
> Best Regards,
> Shiva
>
>
> >
> > --
> > Christo Kutrovsky
> > Database/System Administrator
> > The Pythian Group
> >
> > On 8/16/05, Shivanischal A <shivan_at_subexsystems.com> wrote:
> > > Hi All,
> > >
> > > I'm an application developer on a huge OLTP/DSS product. During my
> > > performance tuning efforts using Statspack, I happened to notice huge
> > > latencies under the "Tablespace I/O" and "Disk I/O" sections. The delays
> > > sometimes were as huge as 700ms even for Undo Tablespace. I immediately
> > > took up this with the client's DBA team.
> > >
> > > And I found that they use RAID-5 for all their ".dbf" files. I know that
> > > that is a strict no-no. The client's DBA team seems to agree. Just
> > > wanted to know from you all, how difficult will it be to migrate all
> > > disks down to RAID 0+1? Are there any best-of-breed methods to do it?
> > >
> > > Just one more question, while reading the I/O latency in the Statspack
> > > report, how slow is slow? I mean lets say the latency is 50ms, how do I
> > > determine that it is slow?
> > >
> > > I multiplied the "Avg Blocks/Read" field with DB Block Size to arrive at
> > > "Avg Bytes/Read" and then used the below equation:
> > > (("Avg Bytes/Read" * 1000)/("Av Rd")) = X Bytes/Second.
> > >
> > > I then compared this X with the disk manufacturer's specification to
> > > determine if its slow or not.
> > >
> > > Could anyone advise me on how to write test scripts/programs to test
> > > disk latency? I've written a multi-threaded C++ program that does a lot
> > > of I/O on available disks. But, when I ran it on the same system, the
> > > delays my program observed were much smaller than what Statspack showed.
> > > Is my approach correct? Are there any other parameters that I should
> > > also consider, like our partitioning policy, separate RAID levels for
> > > log and undo tablespaces?
> > >
> > > Thanks,
> > > Shiva
> > >
> > >
> > > --
> > > http://www.freelists.org/webpage/oracle-l
> > >
> >
>
>

-- 
Christo Kutrovsky
Database/System Administrator
The Pythian Group
--
http://www.freelists.org/webpage/oracle-l