Re: Oracle Databases, NTFS file systems and file system fragmentation ?

From: Doug Coan <dcoan_at_aegonusa.com>
Date: Mon, 25 Sep 2000 18:25:35 GMT
Message-ID: <8qo5a3$3uc$1@nnrp1.deja.com>

In article <39CF88C4.17F681D8_at_cska.net>, Alexander Penev <webmaster_at_cska.net> wrote:
> Hi Sybrand,
> We've just have to make such a decision on raid 5 or not and it went
as follows:
>
> Sun E10k , 1 virtual server for our database with 4 cpu, 4G RAM. We
had to
> choose the proper disk array. First our hardware and oracle "experts"
have
> ordered a D1000 (no raid controller, 4x 36G disks). Of course this was
canceled.
> I've insisted on buying a A1000 with 12 disks with a scsi raid
controller (I
> would have made some raid1s and redo logs on 2 single disks) but the
sun people
> have strongly recommended a T3 disk array on fiber channel with 9x 18G
disks
> with one big raid5. After hours of conversations I've decided to call
the oracle
> support (in austria) and they told me that I wouldn't have any
performance
> problems at all, even the contrary. I would have problems only if I
run in
> archive mode and we run in noarchive. So i had to give up and we've
ordered the
> T3. I was the only one who didn't recommend this T3. The sun
representative has
> told us that the disks run with 100MB/s theoretical and about 90MB/s
practical
> throughput (every single disk). The fiber channel run also 100MB. Do
you know
> whether it's true? The redologs would be also on this raid5. Our db is
about 56
> GB of netto tablespaces , currently running on NT 8.1.6 PP200, 256MB
and about
> 14 disks (very old, 14ms access time, 5400rps) on some raid1 and some
single
> disks. There are jobs running every day processing 2.5-20 Mio recs per
job
> (depending on the job). With the new machine we want to achieve a
performance
> win of min 300% (instead of 17 hours duration, 5 and so on)
> Do you think this "wonder" disks and disk arrays from sun would be a
bottleneck.
> Can you give me a tip how to determine this bottleneck in a short time
after the
> machine is configured (in order to push a change of this array before
we go into
> production)
> Thank you in advance
> Alex
>
> Sybrand Bakker wrote:
>
> > If that raid disk system is raid-5, get rid of the raid-5.
> > Raid-5 has CRC for every write, which is a major bottleneck for all
writes
> > to redolog.
> > If that is 100G data on 1 single raid-array, on one disk-controller,
I'm not
> > going to tune anything: the raid-array is the source of the problem
as the
> > recommendation from Oracle and other sources, to distribute I/O
amongst
> > different individual disks are clear enough.
> > I'm now at a shop where I have one raid-array, consisting of 3
disks,
> > configured as a stripe set, configured as three *logical* disks,
and the
> > performance is a disaster (and of course they didn't consult us when
buying
> > that server). Also, when you loose one disk in that array, as you
can't
> > determine which file is on which disk, you basically will loose your
> > database.
> > I'm not sure how much time it would take to restore 100G, but I
wouldn't be
> > prepared to live with your situation.
> > In my experience Oracle doesn't suffer from fragmentation at all,
provided
> > of course, you don't are continually adding new files to your
database.
> >
> > However, talking about application problems: did you our your firm
do
> > everything to isolate inefficient sql?
> > Usually it's not the hardware, but the application, and most
developers just
> > don't want to admit that.
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
> >
> > "Al Dykes" <adykes_at_panix.com> wrote in message
> > news:8qgm19$png$1_at_panix2.panix.com...
> > > I'm not an oracle admin but I'm getting involved with an NT Oracle
> > > (8.1.5) server with application performance problems. I'd like to
> > > hear about possible affects of ntfs file system fragmentation and
what
> > > production shops do about it, if/when it happens. We have about
100GB
> > > of data on raid disk system.
> > >
> > >
> > > Thanks.
> > >
> > >
> > >
> > >
> > > --
> > > Al Dykes
> > > -----------
> > > adykes_at_panix.com
> > >
>
>

Do a search for my name and RAID. This is a fun topic for me. I'll summarize my opinions:

DO NOT USE RAID 5. bad bad bad bad bad bad bad idea!!
All of our best running DB that are in the 75-120 GB range are configured as a single raid 0+1 array and are spread out across 12+ or more drives. I know this goes aginst good DBA practices 101, but it really really works. I continously challenge different DB vendors and disk vendors to show me something faster and easier to administer and NONE have been able to yet.
Check the block size on the fiber channel. This can actually make your db slower than SCSI if not set right. Currently we have and AIX box on fiber and the thoughput is really bad due to a block size issue. We currently have the DASD, controller board and OS vendor looking into how we fix this.
Oh yeah - DON'T DO RAID 5 any where for any reason. I'm not talking nichel and dime performance gains either. I have NEVER gone from a raid 5 array to RAID 0+1 and not seen at least a 100% performance increase. I have gone back and forth several times to make sure it was just the RAID config and not any other type of fragmentation issue. I had one load job go from 39 hours to 3!!!!! If anyone tells you that it should not make a difference in a read environment.... well... Try it yourself if possible. I'll bet a bright quarter that RAID 0+1 blows it away.

To see if you have a problem. Here is a query i use: /* Begin: Disk I/O Speeds */

prompt

   prompt *** Disk I/O Speeds ***
   prompt * Purpose : Calcuates how fast disk I/O are
   prompt * Goal    : Reads should be less than 6 ms. per IO
   prompt *           Writes should be less than 40 ms. per IO
   prompt * NOTE: These numbers may be skewed (ie. lower than actual)
   prompt *       if Timed Statistics has not been turned on for the
   prompt *       entire time the instance has been up.
   prompt * Options : Layout = Look at moving files to eliminate
contention
   prompt *           Drives = Install faster drives
   prompt *           RAID = Look at using RAID 10 optimally, RAID 1
minimally
   prompt *           Controllers = Use differnet SCSI or Fiber. Ensure

controller configured correctly

prompt *

select a.name "File Name", 'R' xxx, b.phyrds IOs, b.readtim*10 readtim, b.readtim/b.phyrds*10 io

      from v$datafile a, v$filestat b
      where a.file# = b.file# and a.name is not null

union all
select a.name "File Name", 'W' xxx, b.phywrts IOs, b.writetim*10 writetim, b.writetim/b.phywrts*10 io2

      from v$datafile a, v$filestat b
      where a.file# = b.file# and a.name is not null

order by 1, 2;

    prompt
    clear columns
    clear breaks
/* End: Disk I/O Speeds */

Also - use IOSTAT and VMSTAT and look for throughput and disk wait time.

Oh yeah - and DON'T do raid 5 :-)

HTH and good luck!!!

--
Doug Coan
Oracle Certified Professional DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Mon Sep 25 2000 - 13:25:35 CDT