Howard,Thanks for the links - mdb/sqlightning looks interesting.Before I dive
in, however:1. As I understand it, sqlightning is a drop-in replacement for
SQLite.Interop.dll, and I can still use System.Data.SQLite as my high-level
interface?2. Is there a compiled release available?3. How often is sqlightning
updated against SQLite? We're at SQLite 3.7.15.2 / System.Data.SQLite 1.0.84.0
right now4. Does sqlightning allow lock-free reads, even against a SQLite
memory-backed, shared cache database?
> Date: Sun, 13 Jan 2013 10:25:06 -0800
> From: h...@symas.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Concurrent read performance
>
> Wayne Bradney wrote:
> >>> All access in SQLite is serialized. Apologies if I'm missing something
> >>> fundamental here, but that's not what I'm seeing with a file-backed
> >>> database when shared cache is OFF.My test has a single table with 1M
> >>> rows, and four queries that each yield 100K different rows. I run them
> >>> two ways: 1. All queries in a loop on the same thread in the same
> >>> connection.2. Each query in parallel on separate threads, each with its
> >>> own connection. If all access were serialized, I would expect these two
> >>> tests to take about the same amount of time overall, wouldn't I?In fact,
> >>> with a file-backed database and shared cache OFF, the second run takes
> >>> about 70% less time.With shared cache ON, they're the same. As to your
> >>> second point, I probably should have made it clear that this isn't an
> >>> internal project, it's a software product, and we don't control where it
> >>> runs. I understand what an SSD is and why it's better than a spindle
> >>> drive, but my question wasn't really meant to solicit suggestions for
> >>> perfor
ma
> n
> c
> > e improvements outside the proposal at hand, which was to retire our
> > existing home-grown in-memory cache implementation (which is very fast for
> > concurrent reads, but is extremely limited in how it can be queried), and
> > replace it with a SQL-capable, relational store and still get roughly the
> > same performance. Our expectation was that we could achieve this with
> > SQLite, but were surprised by the apparent lack of read-concurrency, and
> > wanted to get some input on what our options might be in terms of SQLite
> > configuration of memory-backed databases. > From: slav...@bigfraud.org
>
> You should look into MDB, which does no locking for read operations. Reads
> scale perfectly across arbitrarily many CPUs. More info here
>
> http://symas.com/mdb/
>
> and SQLite ported to use MDB as its backend is available here
>
> https://gitorious.org/mdb/sqlightning
>
> >> Date: Sat, 12 Jan 2013 17:48:56 +0000
> >> To: sqlite-users@sqlite.org
> >> Subject: Re: [sqlite] Concurrent read performance
> >>
> >>
> >> On 12 Jan 2013, at 5:38pm, Wayne Bradney <wayne_brad...@yahoo.com> wrote:
> >>
> >>> "mode=memory&cache=shared"
> >>
> >>
> >>> 1. when shared cache is enabled, all reads are serialized, and
> >>
> >> All access in SQLite is serialised. All transactions require locking the
> >> entire database. SQLite is very simple -- 'lite' -- so queries run
> >> extremely quickly, so you don't normally realise that any locking has
> >> taken place.
> >>
> >>> 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?
> >>
> >> You are putting programming effort into making your code fast, and this is
> >> costing you (or your employer) programmer time.
>
> For any reasonably useful piece of software, every moment of programmer time
> invested in proper coding saves eons of user time. Putting programmer effort
> into making correct code fast is always The Right Thing to Do. Software that
> delivers the correct answer, late, is still wrong.
>
> --
> -- Howard Chu
> CTO, Symas Corp. http://www.symas.com
> Director, Highland Sun http://highlandsun.com/hyc/
> Chief Architect, OpenLDAP http://www.openldap.org/project/
> _______________________________________________
> 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
