Re: Compression and on-disk sorting

On Mon, May 15, 2006 at 03:44:50PM -0400, Andrew Dunstan wrote:
> Jim C. Nasby wrote:
> >On Mon, May 15, 2006 at 02:18:03PM -0400, Tom Lane wrote:
> >  
> >>"Jim C. Nasby" <jnasby@pervasive.com> writes:
> >>    
> >>>A recent post Tom made in -bugs about how bad performance would be if we
> >>>spilled after-commit triggers to disk got me thinking... There are
> >>>several operations the database performs that potentially spill to disk.
> >>>Given that any time that happens we end up caring much less about CPU
> >>>usage and much more about disk IO, for any of these cases that use
> >>>non-random access, compressing the data before sending it to disk would
> >>>potentially be a sizeable win.
> >>>      
> >>Note however that what the code thinks is a spill to disk and what
> >>actually involves disk I/O are two different things.  If you think
> >>of it as a spill to kernel disk cache then the attraction is a lot
> >>weaker...
> >>    
> >
> >I'm really starting to see why other databases want the OS out of their
> >way...
> >  
> 
> Some of it is pure NIH syndrome. I recently heard of some tests done by 
> a major DB team that showed their finely crafted raw file system stuff 
> performing at best a few percent better than a standard file system, and 
> sometimes worse. I have often heard of the supposed benefits of our 
> being able to go behind the OS, but I am very dubious about it. What 
> makes people think that we could do any better than the OS guys?

The problem is that it seems like there's never enough ability to clue
the OS in on what the application is trying to accomplish. For a long
time we didn't have a background writer, because the OS should be able
to flush things out on it's own before checkpoint. Now there's talk of a
background reader, because backends keep stalling on waiting on disk IO.

In this case the problem is that we want to tell the OS "Hey, if this
stuff is actually going to go out to the spindles then compress it. And
by the way, we won't be doing any random access on it, either." But
AFAIK there's no option like that in fopen... :)

I agree, when it comes to base-level stuff like how to actually put the
data on the physical media, there's not much to be gained in this day
and age by using RAW storage, and in fact Oracle hasn't favored RAW for
quite some time. Every DBA I've ever talked to says that the only reason
to use RAW is if you're trying to eek every last ounce of performance
out of the hardware that you can, which for 99.99% of installs makes
absolutely no sense.

But, there's a big range between writing your own filesystem and
assuming that the OS should just handle everything for you. I think a
lot of commercial databases lean too far towards not trusting the OS
(which is understandable to a degree, givin how much OSes have
improved), while in some areas I think we still rely too much on the OS
(like read-ahead).
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461