Re: SQL Server for Oracle DBAs

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Sat, 31 May 2008 10:49:56 +0200
Message-ID: <48411137$0$14357$e4fe514c@news.xs4all.nl>

"Mark Townsend" <markbtownsend_at_sbcglobal.net> schreef in bericht news:4840F145.8030006_at_sbcglobal.net...

> Mark Townsend wrote:
>> Tony Rogerson wrote:

>>> "Mark Townsend" <markbtownsend_at_sbcglobal.net> wrote in message
>>> news:483E3BA3.4040400_at_sbcglobal.net...
>>>> > in SQL Server we don't do it that way
>>>>> - we
>>>>> put the logs on their own mirrored pair; we put the data on it's own 
>>>>> RAID 10
>>>>> array etc...
>>>>>
>>>>
>>>> Out of curiosity - why ?
>>>

>>> The LDF files (Transaction log) is sequentially written to; it therefore
>>> is good to have the disk head at the place it last wrote; also - you
>>> don't want a mix of read/write traffic on the array where the LDF's are
>>> held - the Transaction Log is SQL Server's weakness in terms of high
>>> levels of scalability when dealing with large numbers of small
>>> transaction insert/update/deletes.
>>>

>>> Tables can be put on seperate file groups; it's a method of balancing
>>> read and write load within the database; those file groups can sit on
>>> their own dedicated raid arrays and the IO isolated. It also allows
>>> scenario's where one file group can read at max speed because it's not
>>> got disk heads bouncing reading and writing and whilst reading from that
>>> file group - writing out to the other - again, you can achieve the max
>>> sustained write speed because the disk heads aren't bouncing around
>>> doing read and writes.
>>>

>>> There is plenty of research out there; there is a really good article on
>>> MS recommending DASD over SAN's too - basically how they scale MSN and
>>> hotmail.
>>>

>>> I'm suprised oracle recommend's SAME because I'd have thought at the
>>> file IO level that if you are reading a 30GB table and writing out 20GB
>>> the ideology behind it is the same whatever product is doing it.
>>>
>>
>> The basic idea behind SAME is that (in Oracle's collective experience) 
>> I/O isolation doesn't actually work in practice. The minute you tune I/O 
>> for one workload another one comes along that screws everything up. For 
>> instance, log I/O is different when loading data, different again when 
>> doing DML, and then different again when issuing queries. We spent years 
>> tuning I/O only to have it all go south again 6 months later. Kept a lot 
>> of performance experts busy, but they were just really moving deckchairs 
>> around. So the simplest approach for all likely workloads is to simply 
>> have as many of the disks involved as possible in whatever the database 
>> is doing at the time. Hence the SAME methodology. Least amount of work, 
>> highest payback.
>>
>> And I guess that most Oracle environments produce a ton of log 
>> information, so isolation to one mirrored pair is probably not going to 
>> cut it - multiple disks will need to be used anyhow. And then last by not 
>> least, the whole concept of sequential writes and head movement is pretty 
>> much a moot point nowadays, as everything pretty much goes to cache in 
>> the first instance, anyhow.
>>
>> But if SQL Server guys want to knock themselves out isolating and tuning 
>> I/O hotspots, then I'm all for them doing it :-)
>
> BTW - if you want to see some of the original thought that went into SAME, 
> here's the original white paper - 
> http://www.oracle.com/technology/deploy/availability/pdf/oow2000_same.pdf
>
> Note that this predates the Automatic Storage Management capabilities 
> released in 10g, which removes the need for cluster volumne management 
> software and automates the whole setup, as well as the addition and 
> removal of disks.

The original link was already posted by mr. Rogerson. That's what started the SAME discussion. Search this discussion for 'shut up you donut' ... ;-)

Shakespeare Received on Sat May 31 2008 - 03:49:56 CDT

Original text of this message