On Wed, May 14, 2008 at 02:40:28PM -0600, Peter K. Stys scratched on the wall:
> On Wed, May 14, 2008 at 2:09 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> > Performance on a remote volume is about 20x slower because the raw I/O
> > capability of a network disk drive is about about 1/20th of a local
> > disk drive. There isn't anything much SQLite can do about that.
> I would disagree with this, unless I misunderstand. File copies (from the
> Finder under OS X) to/from our Xserve run at about 50 MBytes/s or about 50%
> of theoretical max on our Gbit LAN, whereas reading the records from the
> same file via SQLite is 20-25x slower (?2MB/sec at best, terrible
> performance). So there is plenty of raw I/O bandwidth across the LAN and
> network drive, but for some reason SQLite access to its remote files is
> extremely slow (to be clear: these are single users accessing single files).
Bandwidth isn't the issue... it is mostly latency. Copy operations
can buffer large amounts of data and spit it across in one big linear
stream. The file system driver can easily pipeline file requests,
keeping the pipe good and full (or nearly so).
SQLite, on the other hand, is going to pick and choose different
blocks for reading and writing from all over the data file. That's
why you see the 40% drop (vs copies) even on local disks. The killer
is that the overhead for a file request is MUCH larger for network
based systems since it requires a network round-trip and that's aways
going to be a dozen milliseconds or so, no matter what the bandwidth of
the network. Copies can hide a lot of this because they're moving
larger chunks of data, so the overhead percentage is reduced. If you
know what you need next (such as a copy), you can also stagger requests.
SQLite (or any application that accesses a file in a non-linear way)
can't do that. SQLite tends to digest fairly small chunks of data
(related to the page size), keeping the overhead high for network
systems. Additionally, SQLite is normally extremely paranoid about
I/O operations and blocks fully on all file operations, including
writes. Operating systems also tend to be a lot more cautious about
caching file pages in RAM from network volumes vs. local disks, which
will further cut into your SQLite performance (but make little
difference to a copy).
I can go on and on, but it basically boils down to A) Copies are
about the worst comparison you can make because they're a near-ideal
situation. B) Network transaction overhead is significant and
SQLite's I/O behavior tends to make the worst of that.
This is pretty inherent in what SQLite does. It isn't poor coding,
it's just the way things work for any system that needs quasi-random
access to a file in small chunks.
> So I don't understand why the huge performance hit (compared to other
> network file access like Finder copies, not comparing remote vs. local) when
> accessing remote SQLite files? I could understand some slowdown because of
> extra overhead with network operations, but 20-fold?
Yes. Easily.
I'm sure if you do a bit of testing you'll also find the bandwidth of the
network doesn't matter much. If you see 20-fold for GigE, I'd expect
25-fold (or less) for 100Mb. The issue is the huge number of
round-trips, not the speed in which the smallish pages are passed
back.
WORKAROUNDS:
Set SQLites page size to be much larger (PRAGMA page_size).
Makes SQLite deal with bigger chunks of data, reducing the overhead
percentage. You'll very likely need to turn this up pretty high
to see significant changes.
Set SQLites page cache to be much larger (PRAGMA cache_size)
Reduces the number of I/O operations. Great for lookups and sorts.
Not that useful for writes. Depends a lot on how you use the DB.
Live dangerously and turn down/off disk syncing (PRAGMA synchronous).
Reduces the delay for writes. Dangerous.
Or, brute force: Copy the file locally, do your stuff, copy it back.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"'People who live in bamboo houses should not throw pandas.' Jesus said that."
- "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
