Re: [sqlite] SQLite bug on AFP?
On Thu, Jun 5, 2008 at 2:04 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > On Jun 5, 2008, at 12:09 PM, Shawn Wilsher wrote: > > > Hey all, > > > > We are getting reports that sqlite is not working for users of AFP > > (Apple Filing Protocol) in Bug 417037 [1]. I was wondering if this > > was a known issue, and what we could do to help get this resolved. > > > I think the solution might be as simple as compiling with - > DSQLITE_ENABLE_LOCKING_STYLE=1. This option only works on a Mac. It > enables some Apple-contributed code that does file locking that works > on AFP as well as on other network filesystems that the Mac supports. > would this switch in any way affect the dismal R/W speeds for network volumes that were discussed a few weeks ago? P. -- - Peter K. Stys, MD Dept. of Clinical Neurosciences Hotchkiss Brain Institute University of Calgary tel (403) 210-8646 - ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why is SQLite so slow across a network (afp Mac OS X)?
> > > > > > BTW, those PRAGMAs made little difference. I resorted to caching the > remote > > file to the local drive via a fast OS-level file copy then doing the SQL > > R/W, then copying back to the remote in a bkgnd thread. A programming > > headache to keep everything in sync, but very acceptable performance. > > > Actually, you might want to try using a larger page size. SQLite uses, by > default, 1KB pages. Increasing that to 16KB or perhaps larger will not > only reduce the overhead of BLOBs, but also increase performance > significantly, as each page will be going across the network one by one. > Tried that too (I believe I set to the max of 32KB) with little improvement. In fact I had: PRAGMA page_size = SQLITE_MAX_PAGE_SIZE PRAGMA cache_size = 100 PRAGMA synchronous = OFF With little improvement. P. -- - Peter K. Stys, MD Dept. of Clinical Neurosciences Hotchkiss Brain Institute University of Calgary tel (403) 210-8646 - ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why is SQLite so slow across a network (afp Mac OS X)?
On Fri, May 23, 2008 at 4:31 AM, Christian Smith <
[EMAIL PROTECTED]> wrote:
> On Tue, May 13, 2008 at 11:15:51PM -0600, Peter K. Stys wrote:
> > Hi Folks:
> > I'm new to this list and just came across a major issue so I thought I'd
> > post here: I'm using SQLite (from REALbasic which uses the SQLite DB
> engine
> > for SQL support) to store image files, and while R/W to local files is
> > excellent, the same operations on files residing on a remote volume
> mounted
> > via afp or smb (all Mac OS X) suffer a 20-fold performance hit.
> >
> > Why is this, and is there a workaround?
> >
> > To be clear, while the remote files are on a server, this is still single
> > user access to a single file at a time, just remotely.
> >
> > Any input greatly appreciated!
>
>
> A local disk is on a >1Gb/s connection, probably at the end of a wire <0.5m
> long. The remote volume will have that, but also a <1Gb/s connection, on
> top
> of a >10m length of cabling to implement the network.
>
> Laws of physics, such as the speed of light, limit the turn-around of
> synchronous writes across a network. Your hard disk has probably an order
> of magnitude quicker synchronous write than your network share.
>
> Try playing with synchronous writes turned off. The reduced synchronous
> requirements may allow you to make more optimum use of the network file
> protocols, which operate best asynchronously.
>
> Try, in order:
> PRAGMA synchronous = NORMAL;
> PRAGMA synchronous = OFF;
>
> And measure performance of each. But SQLite is simply not designed to work
> efficiently across a network based file system, so manage your
> expectations.
>
Thanks Christian, however, laws of physics says that a packet will travel
along a 100ft run of cable in about 100 ns, give or take. Also laws of
physics (actually laws of well coded copy routines I suspect) allows Finder
copies to run @ 50 Mbytes/sec over our Gbit LAN (this includes reading from
the drive at one end AND writing to the drive at the other) vs. SQLite that
can barely manage 2 MB/sec only reading BLOBs from one end into RAM at the
other.
So the other explanations people have advanced (namely huge protocol
overheads for SQL access/networked volume access, etc...) are more likely
the cause. With much effort I suspect this can be overcome ('cause raw
binary file copies can do it), but unlikely to happen.
BTW, those PRAGMAs made little difference. I resorted to caching the remote
file to the local drive via a fast OS-level file copy then doing the SQL
R/W, then copying back to the remote in a bkgnd thread. A programming
headache to keep everything in sync, but very acceptable performance.
thanks for responding anyway,
Peter.
--
-
Peter K. Stys, MD
Dept. of Clinical Neurosciences
Hotchkiss Brain Institute
University of Calgary
tel (403) 210-8646
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] baffling performance decrease across network (specific case)
On Tue, May 20, 2008 at 9:43 AM, Serena Lien <[EMAIL PROTECTED]> wrote: > Hello, > > I wonder if anyone could shed some light on this. I am using sqlite in my > client programs to access databases over the network. I know sqlite isn't > recommended for this usage model, but I am happy that I have implemented it > such that only 1 client ever has write access, and am also happy with the > performance (speed) over the network in normal situations. > I posted a query on this list very recently about a similar issue, except I noticed a 20-fold decrease in R/W performance across the network with a single connection (BLOB access), compared to what you get with a binary file R/W. Cogent explanations were given, and I conclude that SQLite (and perhaps all non-backend-server based solutions will suffer greatly across a network vs. local volume). I had to rejig my app so that remote SQLite files are first copied to a local temp file, operated on, then copied back to remote volume. I can't see any other way. Peter. -- ----- Peter K. Stys, MD Dept. of Clinical Neurosciences Hotchkiss Brain Institute University of Calgary tel (403) 210-8646 - ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
On Wed, May 14, 2008 at 3:40 PM, Jay A. Kreibich <[EMAIL PROTECTED]> wrote: > > > 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. > Thank you all for your suggestions and explanations. I now understand better the complexity underlying networked volumes. I tried: PRAGMA page_size = SQLITE_MAX_PAGE_SIZE PRAGMA cache_size = 100 PRAGMA synchronous = OFF (all executed before creating any tables) There was no improvement in first read/write performance at all. Looks like the brute force solution is the only answer here. Cheers, Peter. -- - Peter K. Stys, MD Dept. of Clinical Neurosciences Hotchkiss Brain Institute University of Calgary tel (403) 210-8646 - ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
On Wed, May 14, 2008 at 2:09 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > On May 14, 2008, at 3:58 PM, Peter K. Stys wrote: > > > I'm curious, when you say performance enhancements, does that include > > improved R/W performance to/from remote volumes on OS X, which > > presently > > suffer serious (20-fold) speed issues. I had a recent post on this. > > If not, will 3.6.0 address this? > > > > 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. > Dear Richard, 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). In contrast SQLite R/W to local files runs at about 60% of raw binary file access on a local volume, very acceptable (≈35MB/s vs. 60-70MB/sec). 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? Peter. -- - Peter K. Stys, MD Dept. of Clinical Neurosciences Hotchkiss Brain Institute University of Calgary tel (403) 210-8646 - ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why is SQLite so slow across a network (afp Mac OS X)?
Hi Folks: I'm new to this list and just came across a major issue so I thought I'd post here: I'm using SQLite (from REALbasic which uses the SQLite DB engine for SQL support) to store image files, and while R/W to local files is excellent, the same operations on files residing on a remote volume mounted via afp or smb (all Mac OS X) suffer a 20-fold performance hit. Why is this, and is there a workaround? To be clear, while the remote files are on a server, this is still single user access to a single file at a time, just remotely. Any input greatly appreciated! Peter. -- - Peter K. Stys, MD Dept. of Clinical Neurosciences Hotchkiss Brain Institute University of Calgary tel (403) 210-8646 - ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
