What about using 2 or more databases? Wayne Bradney <wayne_brad...@yahoo.com> 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 performan 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 >> 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. Have you tried doing this >> using an SSD instead of a spinning disk ? A great deal of the time taken >> for on-disk SQLite is waiting for the disk to spin to the right place. With >> an SSD all this time vanishes and access is nearly as fast as for in-memory >> databases. The advantage is that you don't spend your time on clever >> optimal programming or have to do any of the things required for >> 'mode=memory'. In fact it works very quickly without any special modes or >> PRAGMAs at all. Though I don't know your setup in detail and it may not be >> of such a great advantage to you. >> >> Simon. >> _______________________________________________ >> 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
