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> wrote in message
>>> news:483E3BA3.4040400_at_sbcglobal.net...
>>> 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.
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