Re: SQL Server for Oracle DBAs
Date: Fri, 30 May 2008 23:19:42 -0700
Message-ID: <4840EDFE.5040203@sbcglobal.net>
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 :-) Received on Sat May 31 2008 - 01:19:42 CDT