Re: Raw partitions / cooked files

Shane Booth writes: |> We have OnLine databases (version 5.0) on a Sun SPARCstation 10, one on a |> raw partition and one on a cooked file. |> |> For a query involving a join on two tables, where one of the tables is very |> small (~10 rows) and we do a sequential scan through the second table |> (~40Mb and ~50000 rows), the raw partition takes 2.6 times as long (on |> average) as the cooked file to execute the query. I just can't work out why? |> It's supposed to be the other way 'round isn't it? |> |> Details: SPARCstation10 Model 30, OnLine version 5.00.UC3. That depends on a lot of different factors. In some cases, the filesystem does a better job of readahead than does 5.0 (where we only do 8K readaheads). This can improve the performance of such things as sequential scans. Bear in mind, however, that performance is not the only reason for using raw devices. There are issues of physical space allocation (there is no guarantee that an extent is comprised of truly contiguous physical space when a cooked file is used), not to mention those of integrity (a successful return from a write() does not guarantee that the data is on disk when a cooked file is used; the data could be sitting in a Unix buffer, which can result in data loss if a crash occurs before that buffer is flushed to disk). When you do more random access than sequential, the readahead advantage is lost. Another thing to be careful of is to be sure that in one case, more of the data wasn't already cached in the shared memory buffers than in the other. You will often find that running a query twice against the same system will take different times, the second being faster, if the first time caused the needed data to be cached when it previously wasn't. In our 6.0 release, this issue has been addressed by the new i/o subsystem which includes tuneable readahead parameters. Dave Disclaimer: These opinions are not those of Informix Software, Inc. ************************************************************************** "I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney