Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: The old raw devices chestnut.

Re: The old raw devices chestnut.

From: Data Goob <>
Date: Tue, 13 Apr 2004 23:40:16 -0400
Message-ID: <Xw2fc.15520$>

"Andrew Hamm" <> wrote in message news:c5i8fl$22chf$
> OK - so you are talking about SQL-Server and various issues related to a
> backup tool you use?

 Yes, standard backup tools.

> If so, we have no argument; I know nothing of your
> tools, and am speaking from an Informix + UNIX point of view. Our beloved
> symlinks on UNIX are not available to NT servers, so suggestions on this
> cannot help. I'm also of the opinion (only from reading) that unbuffered
> NTFS files are equivalent in performance and reliability to raw spaces on
> NT:

SQL-Server is a dish best served, well, cooked. In most SQL-Server situations, probably 95% or more, SQL-Server is stored in plain ole files in a directory. You can create FILEGROUPS akin to containers and dbspaces, but in the real world most SQL-Server people haven't a clue about how to use FILEGROUPS so they simply stuff everything in one big default filegroup called PRIMARY. It's the equivalent in Informix of leaving everything in the rootdbs and never bothering with it, letting it get larger and larger. SQL-Server databases can be detached, that is, taken off-line, moved, copied, etc. But most people don't ever bother with disk layout except in the larger shops. As you suggest there are no linked files, this concept is completely opaque to Windows people they just don't connect with it. Incidentally if you use more than 16 files to build filegroups you cannot use the GUI to reattach a database--very cool thing to learn in a down-server situation. You instead have to use a script like the one below with the syntax FOR ATTACH at the end. Really spiffy.

Example 1. Mydatabase with a lot of FILEGROUPS :


 ( NAME = 'MYDB_01' , FILENAME = 'R:\DATA\MYDB_Data\MYDB_01.NDF' ,  SIZE = 4172 MB ,   FILEGROWTH = 20% ) ,
 ( NAME = 'MYDB_02' , FILENAME = 'R:\DATA\MYDB_Data\MYDB_02.NDF' ,  SIZE = 4483 MB ,   FILEGROWTH = 20% ) ,
 ( NAME = 'MYDB_03' , FILENAME = 'R:\DATA\MYDB_Data\MYDB_03.NDF' ,  SIZE = 3887 MB ,   FILEGROWTH = 20% ) ,
 ( NAME = 'MYDB_04' , FILENAME = 'R:\DATA\MYDB_Data\MYDB_04.NDF' ,  SIZE = 3991 MB ,   FILEGROWTH = 20% ) ,
 ( NAME = 'MYDB_05' , FILENAME = 'R:\DATA\MYDB_Data\MYDB_05.NDF' ,  SIZE = 7964 MB ,   FILEGROWTH = 20% )
 ( NAME = 'MYDB_IDX_01' , FILENAME = 'R:\DATA\MYDB_Data\MYDB_IDX_01.NDF' ,  SIZE = 2048 MB ,   FILEGROWTH = 20% ) ,
 ( NAME = 'MYDB_IDX_02' , FILENAME = 'R:\DATA\MYDB_Data\MYDB_IDX_02.NDF' ,  SIZE = 2048 MB ,   FILEGROWTH = 20% ) ,
 ( NAME = 'MYDB_IDX_03' , FILENAME = 'R:\DATA\MYDB_Data\MYDB_IDX_03.NDF' ,  SIZE = 2048 MB ,   FILEGROWTH = 20% ) ,
 ( NAME = 'MYDB_IDX_04' , FILENAME = 'R:\DATA\MYDB_Data\MYDB_IDX_04.NDF' ,  SIZE = 2048 MB ,   FILEGROWTH = 20% ) ,
 ( NAME = 'MYDB_IDX_05' , FILENAME = 'R:\DATA\MYDB_Data\MYDB_IDX_05.NDF' ,  SIZE = 2048 MB ,   FILEGROWTH = 20% )

Example 2. Mydatabase with no thought or plan, out of the box:

        ( NAME = 'a_Data' , FILENAME = 'R:\Data\A\DATA\A_Primary.MDF' , SIZE = 4 MB , FILEGROWTH = 10% ) , LOG ON
        ( NAME = 'a_Log' , FILENAME = 'R:\DATA\A\LOG\A_Log.ldf' , SIZE = 14 MB , FILEGROWTH = 10% ) GO

Big bummer on FILEGROUPS, if you set up a clustered index guess where your table goes? It gets moved into the same FILEGROUP as the index! Is this retarded or what! All that planning, all that design, down the toilet. Detached indexes are only valid on non-clustered indexes. ( And people say they will move to SQL if Informix dies. Hee hee! We haven't even talked about logging... )

> Even with Informix on NT (of which i have almost no experience) symlinks are
> not available, and further, the recommendations from Informix states that
> you can use normal files (O/S buffered and capable of going onto FAT), or
> normal files on NTFS (which will be used unbuffered) or a raw partition on
> NT. Further, the documentation says that on NT, the use of unbuffered NTFS
> files is of equal performance to NT raw spaces, therefore it's not worth
> using raw spaces on NT. But that's NT, something I don't play with.

Again with risk. SQL-Server docs specifically point to raw-disks as an unsupported feature, so this is not really an option in Windows. Who would be available to support it? Most Windows people wouldn't even want to attempt this one. What about planning, migrations, etc? There wouldn't be anyone around to admin a raw-disk SQL-Server. :-)


> 4) There is no point 4.
> All of these points are significant when major sequential writing is taking
> place.
> BTW I must also qualify that this experience applies to machines without
> fancy storage managers. If you are using a machine with SCSI disks directly
> connected to the SCSI bus or a straight-forward SCSI raid controller, then
> you'll notice performance benefits from using raw with Informix (any other
> engines?) If it's got a big fat storage manager, then it's implementation
> will hide the benefits, in which case, you need to ask "I'm using Engine E
> on platform P with storage manager SM, so what's the best storage model to
> use?"

I defer to K.I.S.S.

> > One other thing you might find attractive to plain ole files
> > instead of raw devices is the ability to clone databases. You
> > can get quite creative with plain files in ways that you cannot
> > with raw devices because of the lock-in of raw-devices. You
> > create more work for yourself in the long run with raw disks,
> > unless of course you're not as lazy as me and actually enjoy
> > the extra work. '-)
> This is where the YMMV slogan comes into play. I would never setup an
> Informix,UNIX,SCSI machine with anything else but symlinks and raw spaces.

If you have the time be my guest.

> If I ever setup a machine with a high performance storage box, I'll look
> into the most appropriate mechanism for that. I'd probably setup any brand
> of engine on UNIX with symlinks, unless experience or advice shows that it's
> pointless.

Welcome to my world.

> As for administration of raw spaces, on UNIX it's trivial. Meaningless. Not
> a problem. Do what your engine, backup tool and storage manager works best
> with. Unless someone else chips in with some detailed advice about other
> brands, the original poster will only be lurnin' about Informix engines
> today. If the OP wants more advice about Informix, please we should stop
> cross-posting and get into more detail only on comp.databases.informix, and
> stop boring the other newsgroups.
> Goob, I think you hang around c.d.i quite a bit, so if you wish, please
> further my education about the pain of raw spaces on c.d.i. Perhaps some
> specific stories are needed so I can undertand your experience.

I'm not arguing that maintaining symlinks or raw-disks are difficult. In an environment where things are somewhat stable and you have the expertise available I'm sure there are benefits. But in shops where the expertise is not available raw disks are a luxury. The additional effort in setting them up and getting people to take care of them adds risk. It's not a competition either, every shop is different and has different needs. Me, I'm lazy and concerned that the talent pool won't be able to manage them properly. As fast as our environment changes we don't have the time for luxuries. The expectations again are also something you manage. We are seeing speed on Linux that is easily 10x faster than on Windows, and with considerable hardware to compare with. This is with cooked files, so again, we're already faster than windows, so how are raw-disks going to really make a difference? 10%? Not enough to bother with, and with as many servers as I manage, definitely more trouble than benefit. Received on Tue Apr 13 2004 - 22:40:16 CDT

Original text of this message