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
