|
|
Posted by dunleav1 on 09/15/06 13:05
Dan Guzman wrote:
> > The same job ran in Oracle had 187 disk writes per second as
> > compared to 3600 writes per second in Sql Server. What's up with that?
>
> It's hard to explain the I/O disparity without knowing the details of your
> job. My first guess is that you might be committing each SQL Server insert
> (default is autocommit) but not each Oracle insert (default is implicit
> transactions on). This would require SQL Sever to perform many more
> transaction log writes.
Each job Oracle Mssql are through jdbc - autocommit is on, sent as a
stored procedure and executed as a batch of 10000.
>
> > If the disks are showing no latency in the system monitor how can disk
> > be a bottleneck?
>
> An I/O intensive process will generally run at the max speed of your disk
> subsystem. Not all I/O is equal; sequential I/O is much more efficient than
> random I/O. You can typically do about twice as many sequential I/Os per
> second than random I/Os. This is one reason why it's usually best to
> perform large data loads using a bulk insert method.
I'm using a batch as I don't won't to use a log bypassed bulk insert.
This test is ran in simple recovery mode but in production in will be
full recovery model.
>
> Note that your performance monitor is reporting 3670 writes/sec but,
> according to the perf tuning technical reference, your 3 usable disks should
> only sustain about 375/sec (3 x 125). It looks to me line the write caching
> controller is skewing the numbers. Data must eventually get written to
> disk, but since the controller writes data asynchronously, that won't become
> a bottleneck until the cache is saturated.
I am writing the same amount of data in Oracle vs Mssql. But Mssql is
writing a lot more data.
>
> You mention only a single RAID 01 array. Is the transaction log on the same
> array as the data files?
Yes - I know it's not optimal but Oracle has the same not optimal
configuration and performs a lot faster.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "dunleav1" <jmd@dunleavyenterprises.com> wrote in message
> news:1158266291.118027.118880@p79g2000cwp.googlegroups.com...
> >I built a test job that loads data into the database. I get great
> > performance with Oracle and I'm trying to tune Sql Server to get the
> > same type of performance.
> >
> > Based on system monitoring it looks like an I/O issue. So I started
> > investigating based on sql server perfromance tuning technical
> > reference.
> >
> > Here are my system monitor findings:
> > The system monitor shows during my job:
> > disk writes per second 3670
> > disk reads per second 0
> > avg disk queue is .45
> > avg disk second read 0
> > avg disk second write 0
> >
> > My total i/os per second is 7200. (disk writes per second * 2) + disk
> > reads. It's times two because I am mirroring.
> >
> > I have a 6 disk raid array configured Raid 1/0 with 200M of cache
> > configured read/write. The average disk second read and write of 0
> > means no i/o latency but my i/os per disk is high compared to
> > Microsoft's recommendation of 125 i/o per disk. I don't see an i/o
> > issue based on the numbers, except for the amount of disk writes per
> > second. The same job ran in Oracle had 187 disk writes per second as
> > compared to 3600 writes per second in Sql Server. What's up with that?
> >
> > Here's my calculation:
> > i/o per disk = [reads + (2 * writes)/number of disks
> > i/o per disk = [0+(2*3600)/6] = 1200
> >
> > Microsoft is saying I need way more disks based on their calculation
> > recommendation.
> >
> > Total i/o / 125 i/os per disk = disk needed
> > 7200/125= 57.6 - 58 disks
> >
> >
> > If the disks are showing no latency in the system monitor how can disk
> > be a bottleneck?
> > Based on Microsoft's recommendation - I need 58 disks to relieve the
> > disk bottle.
> > But I have no i/o latency and the disk queue length is .45.
> >
> > Ahy recommendations ?
> >
Navigation:
[Reply to this message]
|