Keith,I started Experiment 5 soon after the original post, which was to do as
you suggest and run concurrent queries on a single connection shared across
multiple threads. For both shared cache databases (file- and memory-backed) it
didn't seem to make any difference in my test case -- queries still appear to
be serialized. Would this approach require SQLITE_CONFIG_MULTITHREAD or
SQLITE_OPEN_NOMUTEX, I wonder?
> Date: Sat, 12 Jan 2013 13:39:35 -0700
> From: kmedc...@dessus.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Concurrent read performance
>
>
> If your application can reasonably multi-leave "in-engine" operations -vs-
> out-of-engine operations, then you might want to try multiple threads against
> a single connection. In any case, make sure that you are not opening and
> closing connections. Open the connections when your application starts, and
> close them when it is done. The page cache will have almost no effect if you
> delete it between each use (think of the page cache as loading the database
> into RAM -- when you open a connection, the entire database must be copied
> into RAM -- when you close the connection, you are discarding that copy. If
> you then open another connection, you have to load all your data from disk
> into that connection too. As you can see, opening and closing connections
> will incur a significant overhead to repetitively read data which should
> already be in the cache, were it not for you discarding it from memory.) As
> long as you are properly resetting and disposing of your prepared statements
> and make
su
> re that you do not keep "unnecessary crap" (like forgetting to commit
> transactions or reset statements), keeping a connection open for
> minutes/hours/days/years/decades/centuries does not have any ill effect
> (though it will make your programming errors jump out at you, often with odd
> behaviour and crashes).
>
> ---
> () ascii ribbon campaign against html e-mail
> /\ www.asciiribbon.org
>
>
> > -----Original Message-----
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Wayne Bradney
> > Sent: Saturday, 12 January, 2013 12:36
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Concurrent read performance
> >
> > Increasing cache_size didn't seem to have an effect. I think I'm going to
> > lean towards taking the 15% or so hit and use a file-backed db without
> > shared
> > cache for now -- I'll have to see what the impact of that will be on write
> > performance. If that's OK, then maybe a ramdisk will get back some of that
> > performance. I guess I was hoping that a memory-backed db would simply
> > behave
> > exactly like a ramdisk in the first place, but the shared cache requirement
> > kills us.
> > > From: mdblac...@yahoo.com
> > > To: sqlite-users@sqlite.org
> > > Date: Sat, 12 Jan 2013 12:02:25 -0600
> > > Subject: Re: [sqlite] Concurrent read performance
> > >
> > > Also...does increasing cache_size help?
> > > Are you able to use a RAM disk?
> > >
> > >
> > > -----Original Message-----
> > > From: sqlite-users-boun...@sqlite.org
> > > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Wayne Bradney
> > > Sent: Saturday, January 12, 2013 11:39 AM
> > > To: sqlite-users@sqlite.org
> > > Subject: [sqlite] Concurrent read performance
> > >
> > > I have a requirement for which I'm proposing SQLite as a solution, and
> > would
> > > appreciate some feedback to clarify my thinking. The application is a
> > shared
> > > service (.NET/WCF) that caches relational data in-memory and serves it up
> > > when (read-only) requests come in (via operation contracts) from multiple
> > > clients. Such client requests are high-level, not relational, and could
> > > result in several (potentially many) individual queries to the cache
> > itself.
> > > These individual cache queries are dynamically generated and could be
> > > arbitrarily complex, but are all essentially relational in nature. The
> > > service itself will periodically load data from an external data source,
> > > transform it and update the cache. There's no requirement currently for
> > > the
> > > cache to ever be persisted - it can be reloaded from the external source
> > > if
> > > necessary, but performance (especially read performance) is critical. The
> > > amount of data/indexes to cache potentially could be quite large (of the
> > > order of several gigabytes, let's
> > > say). I've already worked an initial implementation that uses an
> > > in-memory
> > > SQLite database via System.Data.SQLite (1.0.82.0). The service maintains a
> > > "mode=memory&cache=shared" database, and each SQL query happens on its own
> > > connection, and in its own thread. Some observations of our read
> > performance
> > > (when the cache is fully populated and there are no writers): [Experiment
> > 1:
> > > Memory-backed, single query]
> > > For simple client requests that only result in a single (albeit complex)
> > SQL
> > > query to the database, performance is excellent, and I'm very happy to get
> > > the maintenance benefits of using a flexible query language against the
> > > cache. [Experiment 2: Memory-backed, concurrent queries]
> > > For any client request that results in multiple simultaneous SQL queries
> > > to
> > > the database, those queries seem to be serialized rather than concurrent,
> > > and the whole request actually performs much worse than the old
> > > home-grown-but-horribly-limited caching/querying mechanism that was in
> > place
> > > beforehand, and I'm sad. [Experiment 3: File-backed, concurrent queries,
> > > with shared cache]
> > > I switched to a file-backed database (but still "cache=shared") and it
> > > appears that the queries are still being serialized, and is overall about
> > > 15% slower than Experiment 2. [Experiment 4: File-backed, concurrent
> > > queries, without shared cache]
> > > I switched to a file-backed database without a shared cache, and
> > performance
> > > improved dramatically (about 70% faster than Experiment 3). It appears
> > > that
> > > the queries are now truly happening concurrently. So it appears that,
> > since:
> > > 1. when shared cache is enabled, all reads are serialized, and
> > > 2. there doesn't seem to be any way to have a memory-backed database that
> > > can be accessed by multiple connections without using a shared cache,
> > > then
> > > I guess I MUST use a file-backed database to get concurrent reads, even
> > > though I don't need the persistence and don't want to take the I/O hit. Am
> > I
> > > making any sense? Anything I'm missing?
> > >
> > > _______________________________________________
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > > _______________________________________________
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
