As a side note: I've compiled some performance data and published them as
an article on my blog:
http://manski.net/2012/10/01/sqlite-performance/
Do these result seem to be ok?
- Sebastian
On Mon, Sep 24, 2012 at 5:02 PM, Olaf Schmidt wrote:
> Am 24.09.2012 11:26, schrieb
Am 24.09.2012 11:26, schrieb Sebastian Krysmanski:
Ok, I tried that. It definitely improves performance when using a lot
threads (15+)...
So I take it, that your last posted result here was using a
shared cache (in case of the multiple connections):
Ok - yet another test. This time, with WAL enabled. Results are much better:
--
SELECT_COUNT: 1,000,000
THREAD_COUNT: 2
Testing with one connection (ReadWrite) and filled table...
Elapsed: 15.8 s (126,316.8 stmt/sec)
Testing with
Ok, I tried that. It definitely improves performance when using a lot
threads (15+) but decreases the performance considerably when using only
two thread (from 60s down to 100s).
--
SELECT_COUNT: 1,000,000
THREAD_COUNT: 2
Testing
Am 21.09.2012 19:28, schrieb Keith Medcalf:
So one needs to take care that the engine operates in the properly
matching modes for the two approaches when used in threads:
- shared Cache (single dbHdl over all threads) => serialized
(SQLITE_THREADSAFE=1)
- separate Caches (a dedicated
te.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Keith Medcalf
> Sent: Friday, 21 September, 2012 11:46
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Store error messages in thread local memory
>
>
> On Friday, 21 September, 2012, @10:53
On Friday, 21 September, 2012, @10:53, Sebastian Krysmanski said:
> I wish it were like you said. However, in my understanding multiple
> connections to the same database are realized by file system locks. So
> switching from serialized to multi-threading mode doesn't make much
> difference
> So one needs to take care that the engine operates in the properly
> matching modes for the two approaches when used in threads:
>
> - shared Cache (single dbHdl over all threads) => serialized
>(SQLITE_THREADSAFE=1)
>
> - separate Caches (a dedicated dbHdl per thread) => multi-thread
>
I wish it were like you said. However, in my understanding multiple
connections to the same database are realized by file system locks. So
switching from serialized to multi-threading mode doesn't make much
difference because the main slow down are the file system locks.
Here are some results
Am 21.09.2012 18:03, schrieb Olaf Schmidt:
...so I can effort the independent...
...arrgh, 'afford' of course, sorry for the noise...
Olaf
___
sqlite-users mailing list
sqlite-users@sqlite.org
Am 20.09.2012 17:40, schrieb Sebastian Krysmanski:
>> What's your threading mode?
>> http://www.sqlite.org/threadsafe.html
Serialized
This could explain, why you get these performance-results, you
reported before (comparing multiple connections vs. a shared one):
> I tested with a
ster the CPU's peak processing
capability, the lower the duty cycle.
---
() 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 Sebastian Krysmanski
> Se
Well, I thought that WAL and the old locking mechanisms allowed for concurrent
read access. (That's why I thought this would be true for a shared connection
as well - which apparently isn't true.)
On Thursday, 20. September 2012 at 15:55, Keith Medcalf wrote:
> > Two separate database
Ok - could you elaborate on the first "this is what one would expect". What
difference does it make whether I use two threads or 20 threads with one
connection when all operations are serialized? Shouldn't both cases have the
same throughput?
On Friday, 21. September 2012 at 03:52, Keith
> With two threads, using only one connection (87.8 s) is actually slower than
> using two connections (66.7 s). The performance of using only one connection
> drastically increased until a thread count somewhere between 10 and 20 where
> it settles at about 11 seconds. Using one connection per
t; (mailto:sqlite-users-boun...@sqlite.org)] on behalf of Sebastian Krysmanski
> [sql...@lists.manski.net (mailto:sql...@lists.manski.net)]
> Sent: Thursday, September 20, 2012 10:25 AM
> To: sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
> Subject: EXT :Re: [sqlite] Store error messa
ki
> [sql...@lists.manski.net (mailto:sql...@lists.manski.net)]
> Sent: Thursday, September 20, 2012 10:25 AM
> To: sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
> Subject: EXT :Re: [sqlite] Store error messages in thread local memory
>
> It's the whole process including c
-users-boun...@sqlite.org] on
behalf of Sebastian Krysmanski [sql...@lists.manski.net]
Sent: Thursday, September 20, 2012 10:25 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Store error messages in thread local memory
It's the whole process including creating threads, opening database
It's the whole process including creating threads, opening database connections
and waiting for the threads to finish. However, startup time is negligible (as
expected). Here are some results where opening and closing of connections as
well as compiling statements is excluded from the elapsed
Well, I thought that WAL and the old locking mechanisms allowed for concurrent
read access. (That's why I thought this would be true for a shared connection
as well - which apparently isn't true.)
On Thursday, 20. September 2012 at 15:55, Keith Medcalf wrote:
> > Two separate database
te-users-boun...@sqlite.org
> (mailto:sqlite-users-boun...@sqlite.org) [sqlite-users-boun...@sqlite.org
> (mailto:sqlite-users-boun...@sqlite.org)] on behalf of Sebastian Krysmanski
> [sql...@lists.manski.net (mailto:sql...@lists.manski.net)]
> Sent: Thursday, September 20, 2012 8:46 AM
&
Hello Sebastian,
Is this total time or time just of the DB access? I'm wondering how
much of this is just "opening the connection" overhead time versus
query time. Assuming the overhead of creating 100 threads is the same.
I'm be interested in knowing how long it takes assuming you don't
start
> Two separate database connection can read concurrently. But you can only
> do one thing at a time with a single database connection.
You could prepare a select statement, one on each thread, and step them each in
their own thread, against the same database connection, even though only one
] on
behalf of Sebastian Krysmanski [sql...@lists.manski.net]
Sent: Thursday, September 20, 2012 8:46 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Store error messages in thread local memory
I tested with a database containing one table with 50,000 entries.
I then ran "S
I tested with a database containing one table with 50,000 entries.
I then ran "SELECT *" on this table from 100 concurrent threads where each
thread randomly selected 20,000 table entries.
The results are:
* using a single connection for all threads: 11 seconds
* using one connection per
-users-boun...@sqlite.org] on
behalf of Black, Michael (IS) [michael.bla...@ngc.com]
Sent: Thursday, September 20, 2012 8:37 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Store error messages in thread local memory
You don't say how much speed difference you see
You don't say how much speed difference you see
But a separate connection will have separate caches. So you could just be
seeing a difference in caching behavior.
One connection uses one cache so will be in L1/L2/L3 cache more often than
multiple threads thrashing the cache.
Ah - I didn't know that. Thanks for the clarification.
On Thursday, 20. September 2012 at 15:28, Richard Hipp wrote:
> On Thu, Sep 20, 2012 at 9:25 AM, Sebastian Krysmanski <
> sql...@lists.manski.net (mailto:sql...@lists.manski.net)> wrote:
>
> > So you mean, that even read operations
On Thu, Sep 20, 2012 at 9:25 AM, Sebastian Krysmanski <
sql...@lists.manski.net> wrote:
> So you mean, that even read operations (SELECT) are not concurrent?
>
Two separate database connection can read concurrently. But you can only
do one thing at a time with a single database connection.
>
So you mean, that even read operations (SELECT) are not concurrent?
On Thursday, 20. September 2012 at 15:23, Richard Hipp wrote:
> On Thu, Sep 20, 2012 at 9:12 AM, Sebastian Krysmanski <
> sql...@lists.manski.net (mailto:sql...@lists.manski.net)> wrote:
>
> > Hi,
> >
> > I'm trying to use
On Thu, Sep 20, 2012 at 9:12 AM, Sebastian Krysmanski <
sql...@lists.manski.net> wrote:
> Hi,
>
> I'm trying to use SQLite in a multi-threaded application. I've done some
> tests and it seems that using the same connection on multiple threads is
> faster than having one connection per thread.
>
>
31 matches
Mail list logo