Note the cross-posting - but no flame wars please.

This question was prompted by a thread on the a postgres mailing list
during which someone (Gregory Williamson) claimed

<quote>
raw devices, at least on Solaris, are about 10 times as fast as cooked
file systems for Informix.
<quote>

This made me think about the old arguments, and I wondered about the
current state of thinking. Some of my knowledge will be a bit out of
date.

Oracle: (my main experience)
At various times Oracle have claimed (talking to consultants, not
marketers) that raw devices are 5-20% faster than filesystems. This may
vary on the current state of the oracle code and/or the filesystem being
compared against. Veritas seem to agree by producing QuickIO for Oracle,
claiming "performance of raw with the management of filesystem".

I have never been sufficiently convinced to implement a major system
with raw.

Sybase: (some experience)
Sybase claim filesystems are faster, because of OS buffering, but unsafe
for the same reason. They only ever suggest filesystem for tempdb. They
don't seem to have heard of fsync()[1]

DB2:
No idea

Informix:
No idea beyond the claim which started this off.

What is the latest thinking, both in terms of vendor claims and
practical experience?

[1] or whatever system call forces write-through caching
--
Jim Smith
Because of their persistent net abuse, I ignore mail from
these domains (among others) .yahoo.com .hotmail.com .kr .cn .tw
For an explanation see <http://www.jimsmith.demon.co.uk/spam>

"Jim Smith" <jim@jimsmith.demon.co.uk> wrote in message
news:dS4PuczgskeAFwJq@jimsmith.demon.co.uk...
> This question was prompted by a thread on the a postgres mailing list
> during which someone (Gregory Williamson) claimed
>
> <quote>
> raw devices, at least on Solaris, are about 10 times as fast as cooked
> file systems for Informix.
> <quote>
>


Let's see first if we can all understand what the heck
is meant by "faster"?

Is it faster I/O requests?
Or faster I/O overall?
Or less CPU used?

Here is IME:

1- Raw does not make for "faster" I/O. I/O speed is defined by
your hardware (disk + controller) and raw or cooked means nothing
in that context.

2- Raw produces overall faster I/O response, all else being equal.

3- However, this can be MUCH faster, or slightly faster.

Explain:

If db is reading off the file system buffer cache, then it is
eminently STUPID to claim "file system I/O" is faster: there is
no I/O in that case, just in-memory access!

If db is reading from disk to fs cache and then copying from there
to db cache then raw will be faster: it doesn't need to copy into the fs
cache, I/O goes directly to the db cache. In such cases one notices
a marked drop in CPU use(!) rather than I/O use by switching to raw:
the block/page copy doesn't come cheap or free.

If the fs cache allows for referencing via pointers from the db processes,
then the fs I/O will be almost same speed as raw but CPU use will be a
little higher: use of indirect addressing (via pointers) is slightly
heavier on resources than direct addressing (via segment addressing).

However a fs cache will be servicing requests from the ENTIRE system, not just
the database hardware. So the potential for heavy interference with the
db I/O activity is there. As such if the system is not a dedicated db server,
one can see some wild variations in I/O response times with varying system
loads.


It all depends on what is being measured, and when and how.

--
Cheers
Nuno Souto
wizofoz2k@yahoo.com.au.nospam

"Jim Smith" <jim@jimsmith.demon.co.uk> wrote in message
news:dS4PuczgskeAFwJq@jimsmith.demon.co.uk...
> Note the cross-posting - but no flame wars please.
>
> This question was prompted by a thread on the a postgres mailing list
> during which someone (Gregory Williamson) claimed
>
> <quote>
> raw devices, at least on Solaris, are about 10 times as fast as cooked
> file systems for Informix.
> <quote>

I have 3 thoughts on this.

1. A FS is an extra layer of code between the db and the hardware, to that
extent if you are comparing a direct read from the actual disk - rather than
a read from a cache somewhere - then RAW will be faster - ten times seems a
hefty claim though.

2. Who cares - what matters isn't the speed of the disk read, but the
response time for the end users screens/jobs - if and only iff you can show
that this is slow and the speed is caused by a file system would you
consider RAW.

3. show us the numbers and the experiment done to prove the statement.


--
Niall Litchfield
Oracle DBA
Audit Commission UK
http://www.niall.litchfield.dial.pipex.com
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************

Jim Smith <jim@jimsmith.demon.co.uk> wrote in
news:dS4PuczgskeAFwJq@jimsmith.demon.co.uk:

> Sybase: (some experience)
> Sybase claim filesystems are faster, because of OS buffering, but
> unsafe for the same reason. They only ever suggest filesystem for
> tempdb. They don't seem to have heard of fsync()[1]

Your data on Sybase ASE is dated. Sybase allows you to use buffered
devices and you can elect to have the writes posted (DSYNC=on) or
buffered (DSYNC=off). Sybase has always recommended one uses raw
devices.

For all DB's, I always use raw devices based on the benchmark
numbers I've run and intuitively, it makes sense. If you have a
read-mostly table, you may gain from utilizing OS buffering with a
32-bit DB and more than 4G of main memory on the box. (I'm over
simplifying however not too much).

A benchmarking trick I learned from one of the team members was to
use symlinks to my raw devices. If I needed to add more disks to a
particular volume, I could down the DBMS, dd the data out to another
disk, rebuild the volume, dd the data back, start the DBMS. Voila!

Regards,
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com

Jim Smith wrote:
> Oracle: (my main experience)
> At various times Oracle have claimed (talking to consultants, not
> marketers) that raw devices are 5-20% faster than filesystems. This may
> vary on the current state of the oracle code and/or the filesystem being
> compared against. Veritas seem to agree by producing QuickIO for Oracle,
> claiming "performance of raw with the management of filesystem".

Well, I have been using the Veritas VxFS Quick I/O feature on Informix
7.31 and 9.20 for a couple of years now. On my system running on heavy
OLTP load and large number of buffers, I get about 5x shorter checkpoint
times on VxFS with QIO, compared to cooked chunks on 'plain' Veritas
VxFS file system. And yes, LRU_MAX_DIRTY/LRU_MIN_DIRTY values are
already at 1/0.

QIO is essentially a normal file system which allows you to access the
space reserved by a file through a character special device. So on
Informix, you can use KAIO even on top of a file system.
--
Toni

Jim Smith wrote:

> Note the cross-posting - but no flame wars please.
>
> This question was prompted by a thread on the a postgres mailing list
> during which someone (Gregory Williamson) claimed
>
> <quote>
> raw devices, at least on Solaris, are about 10 times as fast as cooked
> file systems for Informix.
> <quote>
>
> This made me think about the old arguments, and I wondered about the
> current state of thinking. Some of my knowledge will be a bit out of date.
>
[...]]

I'm still trying to figure out why ext3 filesystem under Red Hat Linux
seems to be *so* much faster than ufs under Solaris... even for simple
OS utilities like "find" and "cp", let alone Oracle imports and bulk
inserts. Is it the journaling? Or is it the five year old hardware? ;-)

Is software RAID slower than hardware RAID, or not? (do a search on
this... you'll see....).

I haven't worked with an Oracle raw device since version 7.3 seven years
ago, and would never go back. The administrative overhead is just too
much of a headache. We have Veritas Quick I/O licensed in our shop, but
we never implemented it in production because it turns out the database
I/O has never even been close to being the bottleneck for our particular
application... and as stated, your applicatino may be different.

--Mark Bole

I've found jfs to be "fast enough", considering we are moving
from SQL-Server, no raw-disks on that one ( chuckles and laughs
allowed :-) . The risks that raw-disks present makes me wonder
if they are worth it. Restoring raw-disk databases presents its
own set of problems, whereas regular files can be backed up and
restored more simply and with more flexibility. After all is
said and done, we already see an increase in Linux over Windows,
so the expectations are different in our shop. Maybe in a situation
where you need to squeeze every bit of speed out of the system
are raw-disks worth it, only to have to live with the limitations
of having to restore the database in the event of a crash.
( Especially with Informix, the restore horror stories alone are
enought to scare me away from raw disks. And really, if you are
on a SAN, aren't you wasting your time with raw disks anyway? What
'raw' disk are you really accessing on a SAN? Having used raw
disks in the past vs using regular files at least from an
operational viewpoint I'd go with regular files any day of the
year. Perhaps good backups will offset the fear for now of anything
shitting our jfs drives until we hear of something better. JFS
is supposed to be improving too, to add data journaling along with
the metadata in the near future. It is also 'supposedly' the
lowest overhead file system, but every few months things change,
and of course the high priests of Reiser will soon come down from
heaven and release yet another version to mere mortals, so it looks
the game changes frequently. No matter, we'll back up the database,
then restore it to whatever file system is most appropriate, but
no raw disks for now, they're just not portable enough.

"Mark Bole" <makbo@pacbell.net> wrote in message news:3NHec.36245$az7.27185@newssvr29.news.prodigy. com...
> Jim Smith wrote:
>
> > Note the cross-posting - but no flame wars please.
> >
> > This question was prompted by a thread on the a postgres mailing list
> > during which someone (Gregory Williamson) claimed
> >
> > <quote>
> > raw devices, at least on Solaris, are about 10 times as fast as cooked
> > file systems for Informix.
> > <quote>
> >
> > This made me think about the old arguments, and I wondered about the
> > current state of thinking. Some of my knowledge will be a bit out of date.
> >
> [...]]
>
> I'm still trying to figure out why ext3 filesystem under Red Hat Linux
> seems to be *so* much faster than ufs under Solaris... even for simple
> OS utilities like "find" and "cp", let alone Oracle imports and bulk
> inserts. Is it the journaling? Or is it the five year old hardware? ;-)
>
> Is software RAID slower than hardware RAID, or not? (do a search on
> this... you'll see....).
>
> I haven't worked with an Oracle raw device since version 7.3 seven years
> ago, and would never go back. The administrative overhead is just too
> much of a headache. We have Veritas Quick I/O licensed in our shop, but
> we never implemented it in production because it turns out the database
> I/O has never even been close to being the bottleneck for our particular
> application... and as stated, your applicatino may be different.
>
> --Mark Bole
>

Raw disk has the potential for best performance with large tablespace and
index scans in a data warehouse environment. During sequential prefetch, DB2
can make sure that the extents are contiguous on the disk if the space is
DMS raw disk. It makes no difference for OLTP systems. Decision support
systems that do not frequently use large tablespace scans, also will show
very little benefit. To the extent that data is already in the buffer pool,
it makes no difference.

Software RAID does not perform well. If striping is desired, superior
performance can usually be achieved by placing DB2 containers on different
physical drives (or different hardware arrays) and letting DB2 stripe the
data across the containers.

If disks are striped by the OS or hardware using some form of RAID, you must
make sure the DB2 parameter for Container Tag Size is set properly. For
version 7 that means that DB2_STRIPED_CONTAINERS must be set ON or YES if
RAID is used. This sets the container tag equal to the extent size (instead
of using a single page). For version 8, make sure that
DB2_USE_PAGE_CONTAINER_TAG is set OFF or NULL for RAID disks. This will make
sure that extents after the container tag will align properly with the RAID
stripes.

Also be sure that the extent size is equal to or a multiple of the stripe
size, and the prefetch size should be a multiple of the extent size. If data
is striped by DB2 (by placing the containers on different physical drives)
then the prefetch size should be n times the extent size, where n is the
number of containers that are placed on different physical disks (or
different physical RAID arrays).

These comments relate to DB2 and are different with other DBMS'.

Jim Smith wrote:

> Note the cross-posting - but no flame wars please.
>
> This question was prompted by a thread on the a postgres mailing list
> during which someone (Gregory Williamson) claimed
>
> <quote>
> raw devices, at least on Solaris, are about 10 times as fast as cooked
> file systems for Informix.
> <quote>

That's a pretty steep claim -- I would say it was an exaggeration.
Raw is usually faster, all other things being equal, but I doubt if 10
times could be justified.

> This made me think about the old arguments, and I wondered about the
> current state of thinking. Some of my knowledge will be a bit out of date.
>
> Oracle: (my main experience)
> At various times Oracle have claimed (talking to consultants, not
> marketers) that raw devices are 5-20% faster than filesystems. This may
> vary on the current state of the oracle code and/or the filesystem being
> compared against. Veritas seem to agree by producing QuickIO for Oracle,
> claiming "performance of raw with the management of filesystem".
>
> I have never been sufficiently convinced to implement a major system
> with raw.
>
> Sybase: (some experience)
> Sybase claim filesystems are faster, because of OS buffering, but unsafe
> for the same reason. They only ever suggest filesystem for tempdb. They
> don't seem to have heard of fsync()[1]
>
> DB2:
> No idea
>
> Informix:
> No idea beyond the claim which started this off.

The advantage, and possibly disadvantage, of raw i/o over cooked i/o
is that the data is copied less. With cooked i/o, the data is copied
first from the user process (eg DBMS) to the kernel buffer space, and
then from kernel buffer pool to the disk, or vice versa. With raw
i/o, the transfer can occur direct from disk to user process without
travelling through the kernel buffer pool. That's one less copy
operation plus the overhead of coordinating the access. Against that,
the kernel buffer pool can sometime provide the same disk block to
multiple processes without rereading the disk. However, since
Informix's process structure is set up so that all the DBMS data pages
are kept in a shared memory segment, all the DBMS processes (distinct
from the user processes which aks the DBMS to do things) can share the
page without troubling the Unix kernel. So, if your system is busy
working on database stuff, the best use of memory is gained by having
the disk drivers copy data directly to/from the shared memory buffer
pool to disk - making the data available to any of the processes
comprising the DBMS without further copying.

YMMV, as they say. It depends on many factors. Generally, I'd quote
a 10-20% performance benefit from raw disk over cooked (not times,
just percent). That's not something I've measured recently, but it is
in the right ballpark for historical systems.

Things like humoungous main memories (TB of main memory) combined with
monstrous disks (TB of them, too) and logical volume managers,
SAN/NAS, RAID and the like all make the analysis more complex.


> What is the latest thinking, both in terms of vendor claims and
> practical experience?
>
> [1] or whatever system call forces write-through caching

O_FSYNC or O_DSYNC flag on open() system call? There are three or
four synchronization options in POSIX 2003, IIRC.


--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

On 2004-04-13, Mark A scribbled:

> Raw disk has the potential for best performance with large tablespace
> and index scans in a data warehouse environment. During sequential
> prefetch, DB2 can make sure that the extents are contiguous on the
> disk if the space is DMS raw disk. It makes no difference for OLTP
> systems. Decision support systems that do not frequently use large
> tablespace scans, also will show very little benefit. To the extent
> that data is already in the buffer pool, it makes no difference.

One other point to note regarding DB2's DMS (Database Managed)
table-spaces is that they don't /have/ to be on raw devices - you can
use file "containers" instead (just a great big file on the file-system
the entire contents of which is managed by DB2). Assuming the file
system has sufficient contiguous space (or you defrag after creating
one) you get the benefit of contiguous data without the complexity of
raw devices.

Though I haven't tried raw devices on DB2, I regularly use DMS
file-based containers and have found them to be noticeably faster than
the default SMS (System Managed) table-spaces (which are mostly
individual files for individual database objects) with queries against
massive tables.

I'd be interested to know if anyone's directly compared DB2's DMS
table-spaces on raw devices and file system containers. I suspect
there'd be a difference - but I doubt it'd be huge.

> Software RAID does not perform well. If striping is desired, superior
> performance can usually be achieved by placing DB2 containers on
> different physical drives (or different hardware arrays) and letting
> DB2 stripe the data across the containers.

Well, I do recall an interesting review of hardware and software RAID
devices by Anandtech a while ago (all on Wintel boxes), in which the
hardware RAID devices were outperformed by every single software RAID
device they were trying. Still, benchmarks have never been a great way
of measuring real-world performance, and this was only on Wintel, so
YMMV.

> These comments relate to DB2 and are different with other DBMS'.

Same here.

--
Dave
Remove "_nospam" for valid e-mail address

"Never underestimate the bandwidth of a station wagon full of CDs doing
a ton down the highway" -- Anon.