Re: [sqlite] Store error messages in thread local memory

2012-10-01 Thread Sebastian Krysmanski
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

Re: [sqlite] Store error messages in thread local memory

2012-09-24 Thread Olaf Schmidt
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):

Re: [sqlite] Store error messages in thread local memory

2012-09-24 Thread Sebastian Krysmanski
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

Re: [sqlite] Store error messages in thread local memory

2012-09-24 Thread Sebastian Krysmanski
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

Re: [sqlite] Store error messages in thread local memory

2012-09-21 Thread Olaf Schmidt
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

Re: [sqlite] Store error messages in thread local memory

2012-09-21 Thread Keith Medcalf
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

Re: [sqlite] Store error messages in thread local memory

2012-09-21 Thread Keith Medcalf
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

Re: [sqlite] Store error messages in thread local memory

2012-09-21 Thread 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 dbHdl per thread) => multi-thread >

Re: [sqlite] Store error messages in thread local memory

2012-09-21 Thread Sebastian Krysmanski
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

Re: [sqlite] Store error messages in thread local memory

2012-09-21 Thread Olaf Schmidt
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

Re: [sqlite] Store error messages in thread local memory

2012-09-21 Thread Olaf Schmidt
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

Re: [sqlite] Store error messages in thread local memory

2012-09-21 Thread Keith Medcalf
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

Re: [sqlite] Store error messages in thread local memory

2012-09-21 Thread Sebastian Krysmanski
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

Re: [sqlite] Store error messages in thread local memory

2012-09-21 Thread Sebastian Krysmanski
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

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Keith Medcalf
> 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

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Sebastian Krysmanski
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

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Sebastian Krysmanski
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

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Black, Michael (IS)
-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

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Sebastian Krysmanski
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

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Sebastian Krysmanski
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

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Sebastian Krysmanski
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 &

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Teg
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

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Keith Medcalf
> 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

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Black, Michael (IS)
] 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

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Sebastian Krysmanski
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

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Black, Michael (IS)
-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

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Black, Michael (IS)
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.

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Sebastian Krysmanski
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

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Richard Hipp
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. >

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Sebastian Krysmanski
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

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Richard Hipp
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. > >