Re: SQL Server for Oracle DBAs
From: Mark Townsend <markbtownsend_at_sbcglobal.net>
Date: Fri, 30 May 2008 23:33:41 -0700
Message-ID: <4840F145.8030006@sbcglobal.net>
>
> 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 :-)
Date: Fri, 30 May 2008 23:33:41 -0700
Message-ID: <4840F145.8030006@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. Received on Sat May 31 2008 - 01:33:41 CDT