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 12:55:47PM -0600, Peter K. Stys wrote: > 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. > > > > > > > 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. > > > > 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. Christian ___ 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
[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
