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

[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 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):
>
>
>  --**--**--
>> SELECT_COUNT: 133,333
>> THREAD_COUNT: 15
>> Testing with one connection (ReadWrite) and filled table...
>> Elapsed: 12.3 s  (162,647.6 stmt/sec)
>> Testing with multiple connections (ReadWrite) and filled table...
>> Elapsed: 12.7 s  (157,324.1 stmt/sec)
>>
>
> Whilst the following result (from an earlier of your replies),
> was not using a shared cache (instead using separate caches
> per thread in case of the multiple connections):
>
>
> > --**--**--
> > SELECT_COUNT: 133,333
> > THREAD_COUNT: 15
> > Testing with one connections (ReadWrite) and filled table...
> > Elapsed: 9.5 s
>
> > Testing with multiple connections (ReadWrite) and filled table...
> > Elapsed: 51.2 s
>
> Possible things which could explain that (because it differs from
> my results considerably) are potentially:
> 1. the setting for: PRAGMA read_uncommitted
> 2. the implementation of the Busy-Handler
>
> Ok, just looking what my settings were for case 1...
> and 'PRAGMA read_uncommitted' was at Zero, so this case
> can be dumped I think.
>
> So I suspect the latter case ...
> Which Busy-Handler do you use, the built-in one?
> called up per: sqlite3_busy_timeout(sqlite3*, int ms)
>
> Or your own implementation?
>
> In my own BusyHandler (I'm dealing only with Windows here)
> my first (few) "wait-fast" calls in the Busy-WaitLoop are always:
> Sleep 0
>
> which according to the MSDN:
> > http://msdn.microsoft.com/en-**us/library/windows/desktop/**
> ms686298(v=vs.85).aspx
> "relinquishs the remainder of the threads timeslice", but
> will try to return as soon as possible in this case.
> So, if you implement your own Handler (for Windows),
> calling Sleep 0 first (a few times) may worth a try,
> so that you give the pausing thread the chance, to
> take up its work much earlier, in case the cause for
> the blocking was only a really short one (on some
> other thread).
>
> Also worth noting in this regard is, that the TimeSlice for
> low values as e.g.
> Sleep 1
> doesn't (always) send the thread sleeping for "exactly" 1 msec -
> the time until the thread resumes its work depends in this
> case (for such low values) on the settings which were
> (only potentially) performed earlier with timeSetEvent...
>
> By default Windows has a "Tick-Interval" of about 12-15msec
> (as the minimum-sleep-time for millisec-values > 0).
> But this "Tick-Interval-granularity" is explained also in
> the 'Remarks'-section of the MSDN-page for Sleep().
>
> Olaf
>
>
>
>
> __**_
> 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

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

[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):


--
SELECT_COUNT: 133,333
THREAD_COUNT: 15
Testing with one connection (ReadWrite) and filled table...
Elapsed: 12.3 s  (162,647.6 stmt/sec)
Testing with multiple connections (ReadWrite) and filled table...
Elapsed: 12.7 s  (157,324.1 stmt/sec)


Whilst the following result (from an earlier of your replies),
was not using a shared cache (instead using separate caches
per thread in case of the multiple connections):

> --
> SELECT_COUNT: 133,333
> THREAD_COUNT: 15
> Testing with one connections (ReadWrite) and filled table...
> Elapsed: 9.5 s
> Testing with multiple connections (ReadWrite) and filled table...
> Elapsed: 51.2 s

Possible things which could explain that (because it differs from
my results considerably) are potentially:
1. the setting for: PRAGMA read_uncommitted
2. the implementation of the Busy-Handler

Ok, just looking what my settings were for case 1...
and 'PRAGMA read_uncommitted' was at Zero, so this case
can be dumped I think.

So I suspect the latter case ...
Which Busy-Handler do you use, the built-in one?
called up per: sqlite3_busy_timeout(sqlite3*, int ms)

Or your own implementation?

In my own BusyHandler (I'm dealing only with Windows here)
my first (few) "wait-fast" calls in the Busy-WaitLoop are always:
Sleep 0

which according to the MSDN:
> 
http://msdn.microsoft.com/en-us/library/windows/desktop/ms686298(v=vs.85).aspx

"relinquishs the remainder of the threads timeslice", but
will try to return as soon as possible in this case.
So, if you implement your own Handler (for Windows),
calling Sleep 0 first (a few times) may worth a try,
so that you give the pausing thread the chance, to
take up its work much earlier, in case the cause for
the blocking was only a really short one (on some
other thread).

Also worth noting in this regard is, that the TimeSlice for
low values as e.g.
Sleep 1
doesn't (always) send the thread sleeping for "exactly" 1 msec -
the time until the thread resumes its work depends in this
case (for such low values) on the settings which were
(only potentially) performed earlier with timeSetEvent...

By default Windows has a "Tick-Interval" of about 12-15msec
(as the minimum-sleep-time for millisec-values > 0).
But this "Tick-Interval-granularity" is explained also in
the 'Remarks'-section of the MSDN-page for Sleep().

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 multiple connections (ReadWrite) and filled table...
Elapsed: 41.0 s  (48,792.7 stmt/sec)


--
SELECT_COUNT: 133,333
THREAD_COUNT: 15
Testing with one connection (ReadWrite) and filled table...
Elapsed: 11.3 s  (177,669.7 stmt/sec)
Testing with multiple connections (ReadWrite) and filled table...
Elapsed: 11.1 s  (180,816.2 stmt/sec)


--
SELECT_COUNT: 20,000
THREAD_COUNT: 100
Testing with one connection (ReadWrite) and filled table...
Elapsed: 11.3 s  (177,155.8 stmt/sec)
Testing with multiple connections (ReadWrite) and filled table...
Elapsed: 13.6 s  (146,847.6 stmt/sec)

- Sebastian

On Mon, Sep 24, 2012 at 11:26 AM, Sebastian Krysmanski  wrote:

> 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 with one connection (ReadWrite) and filled table...
> Elapsed: 58.9 s  (33,938.7 stmt/sec)
> Testing with multiple connections (ReadWrite) and filled table...
> Elapsed: 102.7 s  (19,482.7 stmt/sec)
>
>
> --
> SELECT_COUNT: 133,333
> THREAD_COUNT: 15
> Testing with one connection (ReadWrite) and filled table...
> Elapsed: 12.3 s  (162,647.6 stmt/sec)
> Testing with multiple connections (ReadWrite) and filled table...
> Elapsed: 12.7 s  (157,324.1 stmt/sec)
>
>
> --
> SELECT_COUNT: 20,000
> THREAD_COUNT: 100
> Testing with one connection (ReadWrite) and filled table...
> Elapsed: 11.8 s  (169,204.7 stmt/sec)
> Testing with multiple connections (ReadWrite) and filled table...
> Elapsed: 15.0 s  (133,612.1 stmt/sec)
>
> - Sebastian
>
> On Fri, Sep 21, 2012 at 7:45 PM, Keith Medcalf wrote:
>
>>
>> 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 because the main slow down are the file system locks.
>>
>> Can you try passing the SQLITE_OPEN_SHAREDCACHE and SQLITE_OPEN_NOMUTEX
>> to the connection per thread and see how that behaves.  You get the same
>> advantage of reducing I/O and memory management by using a single
>> page-cache, yet you now no longer have serialization (or mutexes to enforce
>> serialization) in the library call path.  If the serialization/mutex code
>> is causing any significant performance effect, this should demonstrate it
>> clearly.
>>
>> ---
>> ()  ascii ribbon campaign against html e-mail
>> /\  www.asciiribbon.org
>>
>>
>>
>>
>> ___
>> 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

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

[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 with one connection (ReadWrite) and filled table...
Elapsed: 58.9 s  (33,938.7 stmt/sec)
Testing with multiple connections (ReadWrite) and filled table...
Elapsed: 102.7 s  (19,482.7 stmt/sec)


--
SELECT_COUNT: 133,333
THREAD_COUNT: 15
Testing with one connection (ReadWrite) and filled table...
Elapsed: 12.3 s  (162,647.6 stmt/sec)
Testing with multiple connections (ReadWrite) and filled table...
Elapsed: 12.7 s  (157,324.1 stmt/sec)


--
SELECT_COUNT: 20,000
THREAD_COUNT: 100
Testing with one connection (ReadWrite) and filled table...
Elapsed: 11.8 s  (169,204.7 stmt/sec)
Testing with multiple connections (ReadWrite) and filled table...
Elapsed: 15.0 s  (133,612.1 stmt/sec)

- Sebastian

On Fri, Sep 21, 2012 at 7:45 PM, Keith Medcalf  wrote:

>
> 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 because the main slow down are the file system locks.
>
> Can you try passing the SQLITE_OPEN_SHAREDCACHE and SQLITE_OPEN_NOMUTEX to
> the connection per thread and see how that behaves.  You get the same
> advantage of reducing I/O and memory management by using a single
> page-cache, yet you now no longer have serialization (or mutexes to enforce
> serialization) in the library call path.  If the serialization/mutex code
> is causing any significant performance effect, this should demonstrate it
> clearly.
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
>
>
> ___
> 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

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

[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 dbHdl per thread) => multi-thread
(SQLITE_THREADSAFE=2)



You descriptions are incorrect.  No matter how you set
SQLITE_THREADSAFE,you have one cache per connection --
unless you open the connections in"shared cache" mode,
in which case the connections so opened share a single cache.


Not really "incorrect"... maybe "incomplete" matches better.
By default SQLite compiles (IMO) with disabled shared cache.
And that means, that a single DBHandle (shared among threads)
works against a single Cache of course - and multiple DBHandles
(one per Thread) work (by default) against separated Caches.

And for the latter case (one Connection-Hdl per Thread) the
enabling of the shared cache can make a difference of course.


SQLITE_THREADSAFE=2 means that the SQLite library DOES NOT
enforce single entrance per connection with a mutex for you.
It is how you tell SQLite that the application will enforce
those serialization requirements, and that the SQLite library
need not bother itself with enforcement, and should instead
just explode/dump core/fault/corrupt whatever it wants if you
are careless or fail to adhere to the serialization requirements.


My understanding so far was, that SQLITE_THREADSAFE=2 is only
"unsafe", when a single DB-Connection is shared among threads.
When each thread gets its own connection-handle, then this
mode should work a bit faster than with SQLITE_THREADSAFE=1
(and my tests, I've done just now, prove that).


For the multiple-connection-approach (each Cnn having its own cache)
you will probably see better results, when you work against an
sqlite-binary, which was compiled (or set to) multi-thread
(-DSQLITE_THREADSAFE=2).


If and only if you adhere to the serialization requirements.
If you fail to do so, then hell on earth is likely to ensue.


Don't know what you mean by that, because I rely on what's
stated in the SQLite-Docs - so, implementing my own serializing
seems not to be required, as long as SQLite is working with either
SQLITE_THREADSAFE=2 (and my app-code does not share Connection-
Handles among threads) or SQLITE_THREADSAFE=1 (sharing of Cnn-
Handles among threads is allowed).

The only thing I've implemented myself (with regards to potential
"locking issues") is my own SQLite_Busy-Retry-handling (I don't
use the built-in BusyHandler).



My DB-Sizes are not that huge, so I can effort the independent
DB-Connection-caches on each thread in my server-threadpool.


And that is probably why connection-per-thread works for you.


Sure - I've compiled my wrapper-lib with SQLITE_THREADSAFE=2 -
because a single Connection (shared among threads) can never
happen in my threaded scenarios (my COM-Wrapper is bound to be
used only in COM-STAs, which have isolated memory per "thread-
class-instance").


You have not yet hit the point where the supervisors'effort
of memory management for duplicated cache data exceeds
benefit from multiprogramming,...


In case the concurrently accessed DB-Files get that large, that
the benefit of "more or less redundant caches per thread" will
become questionable (degrades the over-all-performance), then
there's always the option to enable SQLites shared-cache-mode
on the Server-Instance (or the multi-threaded Process).

Further below comes a performance-comparison for you... just
wrote a test, to make sure my results are "still as before"
(against newest SQLite-version 3.7.14, compiled with MSVC-
 2008 - my last tests in this regard were done already some
 years ago, but the latest Dll-version still delivers the
 same results which led to my decision in the past, to compile
 my "wrapped SQLite-binary" with SQLITE_THREADSAFE=2).

Ok, this was done on Win7 (no VM) on a "real QuadCore"
(an Intel-i5, 2.8 GHz) having no "virtual cores".

ThreadPool-Count was set to 4, matching the CPU-cores -
and the "Count of total Selects to perform" was set to
8000 - so in multi-thread-mode, each of the 4 threads
was performing 2000 Selects in a loop (each Select in
addition also copying the incoming data over into dedicated
recordset-structures within the SQLite-step-loop).

All the Selects were working against a smaller DB, fitting
entirely into each threads page-cache - and the table
addressed by the Selects contains hierarchical Tree-Data
with about 12000 table-records...

1. A pure throughput-oriented Select (RecordCount as per Limit-Clause)
   SQL = "Select * From Tree Limit 3000"

   Single-threaded performance (8000 Rs-Selects in a Loop): 19.5s

   And using 4 threads (2000 Selects on each thread)
   SQLITE_THREADSAFE=2: 5.4s vs 34.2s <-with enabled shared cache
   SQLITE_THREADSAFE=1: 6.1s vs 38.6s <-with enabled shared cache

The above scenario (more throughput-related) is t

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

[Keith Medcalf]

Wooops.  You may need to call

int sqlite3_enable_shared_cache(int);

to enable shared-cache mode first ...

https://www.sqlite.org/sharedcache.html

---
()  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 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, 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 because the main slow down are the file system locks.
> 
> Can you try passing the SQLITE_OPEN_SHAREDCACHE and SQLITE_OPEN_NOMUTEX to
> the connection per thread and see how that behaves.  You get the same
> advantage of reducing I/O and memory management by using a single page-cache,
> yet you now no longer have serialization (or mutexes to enforce
> serialization) in the library call path.  If the serialization/mutex code is
> causing any significant performance effect, this should demonstrate it
> clearly.
> 
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
> 
> 
> 
> 
> ___
> 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

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

[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 because the main slow down are the file system locks.

Can you try passing the SQLITE_OPEN_SHAREDCACHE and SQLITE_OPEN_NOMUTEX to the 
connection per thread and see how that behaves.  You get the same advantage of 
reducing I/O and memory management by using a single page-cache, yet you now no 
longer have serialization (or mutexes to enforce serialization) in the library 
call path.  If the serialization/mutex code is causing any significant 
performance effect, this should demonstrate it clearly.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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
>(SQLITE_THREADSAFE=2)
> 
> The last one does well for me at least on the Win-OS'
> (not tested on Linux here).

You descriptions are incorrect.  No matter how you set SQLITE_THREADSAFE, you 
have one cache per connection -- unless you open the connections in "shared 
cache" mode, in which case the connections so opened share a single cache.  
Page operations within a cache instance are serialized (and you have no option 
to modify this behaviour).

SQLITE_THREADSAFE=1 means that the SQLite library ENFORCES single entrance per 
connection by associating a mutex with a connection, acquiring the mutex when 
you make an sqlite_* call, and releasing it on returning to your application 
code.

SQLITE_THREADSAFE=2 means that the SQLite library DOES NOT enforce single 
entrance per connection with a mutex for you.  It is how you tell SQLite that 
the application will enforce those serialization requirements, and that the 
SQLite library need not bother itself with enforcement, and should instead just 
explode/dump core/fault/corrupt whatever it wants if you are careless or fail 
to adhere to the serialization requirements.

> For the multiple-connection-approach (each Cnn having its own cache)
> you will probably see better results, when you work against an
> sqlite-binary, which was compiled (or set to) multi-thread
> (-DSQLITE_THREADSAFE=2).

If and only if you adhere to the serialization requirements.  If you fail to do 
so, then hell on earth is likely to ensue.

Whether mulitple threads per connection, multiple connections (with or without 
multiple threads per connection), or even some kind of shared-cache provides 
the best performance is highly dependant on your application design and the 
workload it is performing.  The only thing which can be said for certain is 
that if you application is very heavily I/O bound the single-process 
single-thread with SQLITE_THREADSAFE=0 and _CRT_DISABLE_PERFCRIT_LOCKS (for 
MSVC -- use the single-thread library for real compilers) will be the most 
efficient.

Of course, the most quickest way to accomplish any operation is to not do it.  
This especially applies to I/O, context switching, spin/polling locks, and 
memory cache (L1 & L2) loads and evictions.

> My DB-Sizes are not that huge, so I can effort the independent
> DB-Connection-caches on each thread in my server-threadpool.

And that is probably why connection-per-thread works for you.  You have not yet 
hit the point where the supervisors' effort of memory management for duplicated 
cache data exceeds benefit from multiprogramming, or the long length of the 
operations performed inside the library are not conducive to interleaving 
(serialization) on a single connection.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 which are almost identical to the previous results
(even though I passed "SQLITE_OPEN_NOMUTEX" to "sqlite3_open_v2()" in the
multiple connection tests):

--
SELECT_COUNT: 1,000,000
THREAD_COUNT: 2
Testing with one connections (ReadWrite) and filled table...
Elapsed: 53.3 s
Testing with multiple connections (ReadWrite) and filled table...
Elapsed: 66.6 s


--
SELECT_COUNT: 133,333
THREAD_COUNT: 15
Testing with one connections (ReadWrite) and filled table...
Elapsed: 9.5 s
Testing with multiple connections (ReadWrite) and filled table...
Elapsed: 51.2 s


--
SELECT_COUNT: 20,000
THREAD_COUNT: 100
Testing with one connections (ReadWrite) and filled table...
Elapsed: 10.0 s
Testing with multiple connections (ReadWrite) and filled table...
Elapsed: 54.2 s

- Sebastian

On Fri, Sep 21, 2012 at 6:03 PM, Olaf Schmidt  wrote:

> 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 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 thread: 59,3 seconds
>
> For the multiple-connection-approach (each Cnn having its own cache)
> you will probably see better results, when you work against an
> sqlite-binary, which was compiled (or set to) multi-thread
> (-DSQLITE_THREADSAFE=2).
>
> 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
>   (SQLITE_THREADSAFE=2)
>
> The last one does well for me at least on the Win-OS'
> (not tested on Linux here).
> My DB-Sizes are not that huge, so I can effort the independent
> DB-Connection-caches on each thread in my server-threadpool.
>
> Olaf
>
>
> __**_
> 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

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

[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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 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 thread: 59,3 seconds

For the multiple-connection-approach (each Cnn having its own cache)
you will probably see better results, when you work against an
sqlite-binary, which was compiled (or set to) multi-thread
(-DSQLITE_THREADSAFE=2).

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
  (SQLITE_THREADSAFE=2)

The last one does well for me at least on the Win-OS'
(not tested on Linux here).
My DB-Sizes are not that huge, so I can effort the independent
DB-Connection-caches on each thread in my server-threadpool.

Olaf

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Keith Medcalf]

It takes time (cycles) to do the mutex operations and time (cycles) to perform 
I/O.  These actions are serialized -- only one can happen at a time.  So, if 
you use a single thread, then your "application thread" only progresses when it 
is not blocked waiting for these operations to complete.  For example, when 
sqlite needs to read a page from disk, your application stops until the OS 
completes the read.  

If you have multiple threads, then when the OS has blocked progress on one 
thread because it is waiting for a disk read to complete (which takes an eon in 
comparison to straight CPU operations), other threads that are ready-to-run 
will run.  These types of blocking operations can occur at non-obvious places 
-- when allocating memory and diddling the MMU page map, any type of I/O 
operation, or anything else that is carried out by hardware rather than by 
continuous stream of instructions executed on the CPU.

The same applies to multiple processes (heavy threads), except that in that 
case you also introduce overhead of context switching since each process is 
executing within its own virtual machine (Windows, for example, runs each 
process in a separate virtual machine (they call it a memory space though it is 
more akin to a virual machine) and the Operating System itself is mapped into 
the virtual machine address space through a Discontiguous Saved Segment -- 
quite similar to CP/CMS under VM/370).  The concurrency limit is therefore 
lower with processes than threads, but only because PC's do not have the 
necessary VM Assist hardware to perform context switching independent of the 
main CPU.

The big difference between PC hardware and a Mainframe is in the opportunity 
for processor offloading.  In a PC, almost everything executes on the "main" 
(user) CPU, and very little is carried out in independent hardware.  For 
example, old ATA and earlier disks I/O was carried out entirely by the main 
CPU, so I/O provided no opportunity for concurrency.  SCSI (and modern 
equivalents such as SATA) provide hardware offloading of operations so that 
they occur without CPU supervision.  Since the CPU is no longer tied up waiting 
for the eon of time it takes to read a disk sector, it can process other useful 
work during this time.  This is where you find that "multiple threads" progress 
faster than a "single thread".  CPU cycles that would otherwise be unused (or 
consumed spinning waiting for a completion notification) are used to progress 
useful work.  This is especially true if you have "user I/O" (click a mouse, 
type, etc).

If you take a PC, install co-processed I/O, and add CPU's to handle 
"supervisor" and "I/O" co-ordination leaving the "main" CPU to run only 
ready-to-run straight "user" processing, you will have a mainframe.

In the ancient days you used to be able to buy a 370-on-a-card.  You plugged it 
into the PC and it turned the PC into a mainframe.  The card contained a 
mainframe (370) "main" (user) processor, and the rest of the PC was turned into 
the "supervisor", "I/O" and "VM Assist" hardware.

Modern HPC (high-performance computing) and mainframe systems work the same 
way.  The CPU that does the meaningful application processing is dedicated to 
only that task, and everything else is handed off to separate processors and 
coprocessed hardware.  Most HPC platorms on commodity PC-type hardware add 
extra CPU's to dedicate to "main" processing, and turn the PC hardware into the 
support system to allow this additional CPU to actually run unrestrained.  In 
typical PC systems the CPU never runs more than about 50% duty even when 100% 
utilized simply because there is too much administrative crap going on to allow 
it to run at full speed except in very short bursts.

If PC systems were designed with enough cache (L1 & L2) and hardware 
coprocessing to allow the CPU to run with a 100% duty cycle, they would burst 
into flames.  Modern CPU and PC design requires massively inefficient design in 
order to work.  It is interesting that the faster 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
> Sent: Friday, 21 September, 2012 01:32
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Store error messages in thread local memory
> 
> 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, 2

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

[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 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 thread would be 'stepping' at a time, in much the same way that you can 
> have fifty programs writing to disk 'concurrently' (yet I/O is serialized so 
> only one write operation proceeds at a time).
> 
> I think it all depends on how you define "concurrency" ... the database 
> connection is serialized so no operations occur concurrently, however, you 
> can execute separate operations against the same connection in separate 
> threads and all will "make progress" in an interleaved (serialized) fashion 
> within the constraints of the OS scheduler.
> 
> ---
> () ascii ribbon campaign against html e-mail
> /\ www.asciiribbon.org (http://www.asciiribbon.org)
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org (mailto: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

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

[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 Medcalf wrote:

> > 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 thread reduces the
> > elapsed time only from 66 to 55 seconds.
> > 
> 
> 
> This is what one would expect.
> 
> > * The virtual machine has 2 CPU cores assigned. When using only one
> > connection, only one core seems to be used. When using one connection per
> > thread, both cores are used.
> > 
> 
> 
> This is also to be expected. 
> 
> Because thread access to the sqlite engine is serialized by a mutex, the OS 
> ought to set thread affinity for all operations running through the same 
> mutex to the same CPU. If you repeated the test using two connections only, 
> and spreading your threads amongst connections, you ought to be able to get 
> about 5 or 6 seconds with 10 to 20 threads per connection.
> 
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org (mailto: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

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

[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 thread reduces the
> elapsed time only from 66 to 55 seconds.

This is what one would expect.

> * The virtual machine has 2 CPU cores assigned. When using only one
> connection, only one core seems to be used. When using one connection per
> thread, both cores are used.

This is also to be expected.  

Because thread access to the sqlite engine is serialized by a mutex, the OS 
ought to set thread affinity for all operations running through the same mutex 
to the same CPU.  If you repeated the test using two connections only, and 
spreading your threads amongst connections, you ought to be able to get about 5 
or 6 seconds with 10 to 20 threads per connection.





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Sebastian Krysmanski]

Well, I did some profiling and here are the results, if you're interested.

Again, 2,000,000 SELECT queries. This time I used only shared connections (i.e. 
all threads share the same connection).

With 2 threads:
Overall hot path: http://imgur.com/DHXQj
In thread: http://imgur.com/KX8KY

With 20 threads:
Overall hot path: http://imgur.com/s28Hs
In thread: http://imgur.com/8NBB6


On Thursday, 20. September 2012 at 17:39, Black, Michael (IS) wrote:

> What's your threading mode?
> http://www.sqlite.org/threadsafe.html
> 
> 
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
> 
> 
> From: sqlite-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 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 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 time:
> 
> --
> SELECT_COUNT: 1,000,000
> THREAD_COUNT: 2
> Testing with one connections (ReadWrite) and filled table...
> Elapsed: 91.0 s
> Testing with one connections (ReadWrite) and filled table...
> Elapsed: 66.3 s
> 
> 
> --
> SELECT_COUNT: 133,333
> THREAD_COUNT: 15
> Testing with one connections (ReadWrite) and filled table...
> Elapsed: 11.6 s
> Testing with one connections (ReadWrite) and filled table...
> Elapsed: 51.6 s
> 
> 
> --
> SELECT_COUNT: 20,000
> THREAD_COUNT: 100
> Testing with one connections (ReadWrite) and filled table...
> Elapsed: 11.5 s
> Testing with one connections (ReadWrite) and filled table...
> Elapsed: 55.9 s
> 
> 
> On Thursday, 20. September 2012 at 16:22, Teg wrote:
> 
> > 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 timing it till after all 100 threads have opened the connections
> > to the file.
> > 
> > Wonder if running this same test 100 times in a row for each mode,
> > leaving the connections open in between, would show the timing's
> > converging? Basically reducing the affect of the startup overhead.
> > 
> > 
> > C
> > 
> > Thursday, September 20, 2012, 9:46:07 AM, you wrote:
> > 
> > SK> I tested with a database containing one table with 50,000 entries.
> > 
> > SK> I then ran "SELECT *" on this table from 100 concurrent threads
> > SK> where each thread randomly selected 20,000 table entries.
> > 
> > SK> The results are:
> > 
> > SK> * using a single connection for all threads: 11 seconds
> > SK> * using one connection per thread: 59,3 seconds
> > 
> > 
> > SK> On Thursday, 20. September 2012 at 15:37, Black, Michael (IS) wrote:
> > 
> > > > 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.
> > > > 
> > > > http://www.tomshardware.com/forum/266376-28-intel-cache
> > > > 
> > > > 
> > > > Michael D. Black
> > > > Senior Scientist
> > > > Advanced Analytics Directorate
> > > > Advanced GEOINT Solutions Operating Unit
> > > > Northrop Grumman Information Systems
> > > > 
> > > > 
> > > > > 
> > > > > > 
> > > > > > 
> > > > > > 
> > > > > 

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

[Sebastian Krysmanski]

Serialized 


On Thursday, 20. September 2012 at 17:39, Black, Michael (IS) wrote:

> What's your threading mode?
> http://www.sqlite.org/threadsafe.html
> 
> 
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
> 
> 
> From: sqlite-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 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 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 time:
> 
> --
> SELECT_COUNT: 1,000,000
> THREAD_COUNT: 2
> Testing with one connections (ReadWrite) and filled table...
> Elapsed: 91.0 s
> Testing with one connections (ReadWrite) and filled table...
> Elapsed: 66.3 s
> 
> 
> --
> SELECT_COUNT: 133,333
> THREAD_COUNT: 15
> Testing with one connections (ReadWrite) and filled table...
> Elapsed: 11.6 s
> Testing with one connections (ReadWrite) and filled table...
> Elapsed: 51.6 s
> 
> 
> --
> SELECT_COUNT: 20,000
> THREAD_COUNT: 100
> Testing with one connections (ReadWrite) and filled table...
> Elapsed: 11.5 s
> Testing with one connections (ReadWrite) and filled table...
> Elapsed: 55.9 s
> 
> 
> On Thursday, 20. September 2012 at 16:22, Teg wrote:
> 
> > 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 timing it till after all 100 threads have opened the connections
> > to the file.
> > 
> > Wonder if running this same test 100 times in a row for each mode,
> > leaving the connections open in between, would show the timing's
> > converging? Basically reducing the affect of the startup overhead.
> > 
> > 
> > C
> > 
> > Thursday, September 20, 2012, 9:46:07 AM, you wrote:
> > 
> > SK> I tested with a database containing one table with 50,000 entries.
> > 
> > SK> I then ran "SELECT *" on this table from 100 concurrent threads
> > SK> where each thread randomly selected 20,000 table entries.
> > 
> > SK> The results are:
> > 
> > SK> * using a single connection for all threads: 11 seconds
> > SK> * using one connection per thread: 59,3 seconds
> > 
> > 
> > SK> On Thursday, 20. September 2012 at 15:37, Black, Michael (IS) wrote:
> > 
> > > > 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.
> > > > 
> > > > http://www.tomshardware.com/forum/266376-28-intel-cache
> > > > 
> > > > 
> > > > Michael D. Black
> > > > Senior Scientist
> > > > Advanced Analytics Directorate
> > > > Advanced GEOINT Solutions Operating Unit
> > > > Northrop Grumman Information Systems
> > > > 
> > > > 
> > > > > 
> > > > > > 
> > > > > > 
> > > > > > 
> > > > > > 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,

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

[Black, Michael (IS)]

What's your threading mode?
http://www.sqlite.org/threadsafe.html


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-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 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 time:

--
SELECT_COUNT: 1,000,000
THREAD_COUNT: 2
Testing with one connections (ReadWrite) and filled table...
Elapsed: 91.0 s
Testing with one connections (ReadWrite) and filled table...
Elapsed: 66.3 s


--
SELECT_COUNT: 133,333
THREAD_COUNT: 15
Testing with one connections (ReadWrite) and filled table...
Elapsed: 11.6 s
Testing with one connections (ReadWrite) and filled table...
Elapsed: 51.6 s


--
SELECT_COUNT: 20,000
THREAD_COUNT: 100
Testing with one connections (ReadWrite) and filled table...
Elapsed: 11.5 s
Testing with one connections (ReadWrite) and filled table...
Elapsed: 55.9 s


On Thursday, 20. September 2012 at 16:22, Teg wrote:

> 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 timing it till after all 100 threads have opened the connections
> to the file.
>
> Wonder if running this same test 100 times in a row for each mode,
> leaving the connections open in between, would show the timing's
> converging? Basically reducing the affect of the startup overhead.
>
>
> C
>
> Thursday, September 20, 2012, 9:46:07 AM, you wrote:
>
> SK> I tested with a database containing one table with 50,000 entries.
>
> SK> I then ran "SELECT *" on this table from 100 concurrent threads
> SK> where each thread randomly selected 20,000 table entries.
>
> SK> The results are:
>
> SK> * using a single connection for all threads: 11 seconds
> SK> * using one connection per thread: 59,3 seconds
>
>
> SK> On Thursday, 20. September 2012 at 15:37, Black, Michael (IS) wrote:
>
> > > 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.
> > >
> > > http://www.tomshardware.com/forum/266376-28-intel-cache
> > >
> > >
> > > Michael D. Black
> > > Senior Scientist
> > > Advanced Analytics Directorate
> > > Advanced GEOINT Solutions Operating Unit
> > > Northrop Grumman Information Systems
> > >
> > >
> > > >
> > > > >
> > > > >
> > > > >
> > > > > 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 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.
> > > > > > >
> > > > > > > However, http://www.sqlite.org/c3ref/errcode.html states:
> > > > > > >
> > > > > > > "When the serialized threading mode (
> > > > > htt

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

[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 time:

--
SELECT_COUNT: 1,000,000
THREAD_COUNT: 2
Testing with one connections (ReadWrite) and filled table...
Elapsed: 91.0 s
Testing with one connections (ReadWrite) and filled table...
Elapsed: 66.3 s


--
SELECT_COUNT: 133,333
THREAD_COUNT: 15
Testing with one connections (ReadWrite) and filled table...
Elapsed: 11.6 s
Testing with one connections (ReadWrite) and filled table...
Elapsed: 51.6 s


--
SELECT_COUNT: 20,000
THREAD_COUNT: 100
Testing with one connections (ReadWrite) and filled table...
Elapsed: 11.5 s
Testing with one connections (ReadWrite) and filled table...
Elapsed: 55.9 s


On Thursday, 20. September 2012 at 16:22, Teg wrote:

> 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 timing it till after all 100 threads have opened the connections
> to the file.
> 
> Wonder if running this same test 100 times in a row for each mode,
> leaving the connections open in between, would show the timing's
> converging? Basically reducing the affect of the startup overhead.
> 
> 
> C
> 
> Thursday, September 20, 2012, 9:46:07 AM, you wrote:
> 
> SK> I tested with a database containing one table with 50,000 entries.
> 
> SK> I then ran "SELECT *" on this table from 100 concurrent threads
> SK> where each thread randomly selected 20,000 table entries.
> 
> SK> The results are:
> 
> SK> * using a single connection for all threads: 11 seconds
> SK> * using one connection per thread: 59,3 seconds
> 
> 
> SK> On Thursday, 20. September 2012 at 15:37, Black, Michael (IS) wrote:
> 
> > > 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.
> > > 
> > > http://www.tomshardware.com/forum/266376-28-intel-cache
> > > 
> > > 
> > > Michael D. Black
> > > Senior Scientist
> > > Advanced Analytics Directorate
> > > Advanced GEOINT Solutions Operating Unit
> > > Northrop Grumman Information Systems
> > > 
> > > 
> > > > 
> > > > > 
> > > > > 
> > > > > 
> > > > > 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 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.
> > > > > > > 
> > > > > > > However, http://www.sqlite.org/c3ref/errcode.html states:
> > > > > > > 
> > > > > > > "When the serialized threading mode (
> > > > > http://www.sqlite.org/threadsafe.html)
> > > > > > > is in use, it might be the case that a second error occurs on a
> > > > > > 
> > > > > 
> > > > > 
> > > > > 
> > > > > separate
> > > > > > > thread in between the time of the first error and the call to 
> > > > > > > these
> > > > > > > interfaces. When that happens, the second error will be reported 
> > > > > > > since
> > > > > > > these interfaces always report the most recent result."
> > > > > > > 
> > > > > > > So, this is a problem in my application (and I definitely need 
> > > > > > > multi
> > > > > > > threading).
> > > > > > > 
> > > > > > > Obtaining an exclusive lock for the database connection, as 
> > > > > > > suggested
> > > > > in
> > > > > > > the documentation, is not an option for me because even read only
> > > > > > > statements (SELECT) can potentially return an error. And 
> > > > > > > obtaining an
> > > > > > > exclusive lock for a read statement eliminates all concurrency 
> > > > > > > there
> > > > > > > 
> > > > > > 
> > > > > 
> > > > > 
> > > > > 
> > > > > is in
> > > > > > > SQLite.
> > > > > > 
> > > > > > 
> > > > > > 
> > > > > > 
> > > > > > 
> > > > > > Every operation on an SQLite database connection operates under an
> > > > > > exclusive mutex on that database connection, so you don't have any
> > > > > > concurrency

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

[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 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 thread would be 'stepping' at a time, in much the same way that you can 
> have fifty programs writing to disk 'concurrently' (yet I/O is serialized so 
> only one write operation proceeds at a time).
> 
> I think it all depends on how you define "concurrency" ... the database 
> connection is serialized so no operations occur concurrently, however, you 
> can execute separate operations against the same connection in separate 
> threads and all will "make progress" in an interleaved (serialized) fashion 
> within the constraints of the OS scheduler.
> 
> ---
> () ascii ribbon campaign against html e-mail
> /\ www.asciiribbon.org (http://www.asciiribbon.org)
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org (mailto: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

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

[Sebastian Krysmanski]

I've compiled some test results that are quite interesting. You can find the 
details here: 

http://pastebin.com/yGhDepr6 
(I forgot to change the locale for the "Elapsed" lines, so "," means ".")

In all test cases 2,000,000 select operations are executed (evenly distributed 
among the threads).

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 thread reduces the 
elapsed time only from 66 to 55 seconds.

I probably should note a couple of things about these tests:

* I'm using a .NET wrapper (P/Invoke) that I've written myself 
(https://bitbucket.org/mayastudios/sqlite.net ). So, it may not be bug free 
(and thus may influence the results).
* I'm running Windows 7 x64 inside a virtual machine (VMWare) so elapsed times 
may be higher than usual.
* The virtual machine has 2 CPU cores assigned. When using only one connection, 
only one core seems to be used. When using one connection per thread, both 
cores are used.
* Each thread gets its own compiled/prepared statement.

I've posted the test code here:

http://pastebin.com/RHXWfrdx

Again, it's C# but should be understandable anyway.

- Sebastian 


On Thursday, 20. September 2012 at 15:52, Black, Michael (IS) wrote:

> Wow...almost 6X differencewould you care to share your test code?
> 
> I would imagine 50 threads would be MORE than 2X faster if caching is the 
> cause.
> 
> So if you run a test and time 10,20,30..100 threads what kind of curve is 
> seen?
> 
> Then do the same for single connection. 
> 
> The ratio of those entries would be quite enlightening.
> 
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
> 
> 
> From: sqlite-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
> 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 "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 thread: 59,3 seconds
> 
> 
> On Thursday, 20. September 2012 at 15:37, Black, Michael (IS) wrote:
> 
> > 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.
> > 
> > http://www.tomshardware.com/forum/266376-28-intel-cache
> > 
> > 
> > Michael D. Black
> > Senior Scientist
> > Advanced Analytics Directorate
> > Advanced GEOINT Solutions Operating Unit
> > Northrop Grumman Information Systems
> > 
> > 
> > > 
> > > > 
> > > > 
> > > > 
> > > > 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 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.
> > > > > > 
> > > > > > However, http://www.sqlite.org/c3ref/errcode.html states:
> > > > > > 
> > > > > > "When the serialized threading mode (
> > > > http://www.sqlite.org/threadsafe.html)
> > > > > > is in use, it might be the case that a second error occurs on a
> > > > > 
> &g

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

[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 timing it till after all 100 threads have opened the connections
to  the  file.

Wonder if running this same test 100 times in a row for each mode,
leaving the connections open in between, would show the timing's
converging? Basically reducing the affect of the startup overhead.


C

Thursday, September 20, 2012, 9:46:07 AM, you wrote:

SK> I tested with a database containing one table with 50,000 entries.

SK> I then ran "SELECT *" on this table from 100 concurrent threads
SK> where each thread randomly selected 20,000 table entries.

SK> The results are:

SK> * using a single connection for all threads: 11 seconds
SK> * using one connection per thread: 59,3 seconds


SK> On Thursday, 20. September 2012 at 15:37, Black, Michael (IS) wrote:

>> 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.
>> 
>> http://www.tomshardware.com/forum/266376-28-intel-cache
>> 
>> 
>> Michael D. Black
>> Senior Scientist
>> Advanced Analytics Directorate
>> Advanced GEOINT Solutions Operating Unit
>> Northrop Grumman Information Systems
>> 
>> 
>> > 
>> > > 
>> > > 
>> > > 
>> > > 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 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.
>> > > > > 
>> > > > > However, http://www.sqlite.org/c3ref/errcode.html states:
>> > > > > 
>> > > > > "When the serialized threading mode (
>> > > http://www.sqlite.org/threadsafe.html)
>> > > > > is in use, it might be the case that a second error occurs on a
>> > > > 
>> > > 
>> > > 
>> > > separate
>> > > > > thread in between the time of the first error and the call to these
>> > > > > interfaces. When that happens, the second error will be reported 
>> > > > > since
>> > > > > these interfaces always report the most recent result."
>> > > > > 
>> > > > > So, this is a problem in my application (and I definitely need multi
>> > > > > threading).
>> > > > > 
>> > > > > Obtaining an exclusive lock for the database connection, as suggested
>> > > in
>> > > > > the documentation, is not an option for me because even read only
>> > > > > statements (SELECT) can potentially return an error. And obtaining an
>> > > > > exclusive lock for a read statement eliminates all concurrency there
>> > > > > 
>> > > > 
>> > > 
>> > > 
>> > > is in
>> > > > > SQLite.
>> > > > 
>> > > > 
>> > > > 
>> > > > 
>> > > > Every operation on an SQLite database connection operates under an
>> > > > exclusive mutex on that database connection, so you don't have any
>> > > > concurrency anyhow.
>> > > > 
>> > > > 
>> > > > > 
>> > > > > So the only solution I can come up with is to make "sqlite3_errmsg()"
>> > > (and
>> > > > > related functions) use thread local memory.
>> > > > > 
>> > > > > Is there (or has there ever been made) any attempt on storing the 
>> > > > > error
>> > > > > message in thread local memory? (I'm a C# and Java developer, so I'm
>> > > > > 
>> > > > 
>> > > 
>> > > 
>> > > not
>> > > > > sure whether thread local memory even exists in C. It does in C# and
>> > > > 
>> > > 
>> > > 
>> > > Java
>> > > > > though.)
>> > > > 
>> > > > 
>> > > > 
>> > > > 
>> > > > Thread local storage has been available to C code since long before 
>> > > > Java
>> > > > and C# were even invented. But it is accessed through library routines
>> > > > that are not cross-platform, so we are not interested in using it in
>> > > > SQLite. Furthermore, making such a change would break backwards
>> > > > compatibility, which is a huge no-no with SQLite.
>> > > > 
>> > > > Best regards
>> > > > > Sebastian
>> > > > > 
>> > > > > ___
>> > > > > sqlite-users mailing list
>> > > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
>> > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> > > > > 
>> > > > 
>> > > > 
>> > > > 
>> > > > 
>> > > > 
>> > > > 
>> > > > --
>> > > > D. Richard Hipp
>> > > > d...@sqlite.org (mailto:d...@sqlite.org)
>> > > > ___
>> > > > sqlite-users mailing list
>> > > > sqlite-users@sqlite.org (mailto

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

[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 
thread would be 'stepping' at a time, in much the same way that you can have 
fifty programs writing to disk 'concurrently' (yet I/O is serialized so only 
one write operation proceeds at a time).

I think it all depends on how you define "concurrency" ... the database 
connection is serialized so no operations occur concurrently, however, you can 
execute separate operations against the same connection in separate threads and 
all will "make progress" in an interleaved (serialized) fashion within the 
constraints of the OS scheduler.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Black, Michael (IS)]

Wow...almost 6X differencewould you care to share your test code?

I would imagine 50 threads would be MORE than 2X faster if caching is the cause.

So if you run a test and time 10,20,30..100 threads what kind of curve is seen?

Then do the same for single connection.  

The ratio of those entries would be quite enlightening.

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] 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 "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 thread: 59,3 seconds


On Thursday, 20. September 2012 at 15:37, Black, Michael (IS) wrote:

> 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.
>
> http://www.tomshardware.com/forum/266376-28-intel-cache
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
>
> >
> > >
> > >
> > >
> > > 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 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.
> > > > >
> > > > > However, http://www.sqlite.org/c3ref/errcode.html states:
> > > > >
> > > > > "When the serialized threading mode (
> > > http://www.sqlite.org/threadsafe.html)
> > > > > is in use, it might be the case that a second error occurs on a
> > > >
> > >
> > >
> > > separate
> > > > > thread in between the time of the first error and the call to these
> > > > > interfaces. When that happens, the second error will be reported since
> > > > > these interfaces always report the most recent result."
> > > > >
> > > > > So, this is a problem in my application (and I definitely need multi
> > > > > threading).
> > > > >
> > > > > Obtaining an exclusive lock for the database connection, as suggested
> > > in
> > > > > the documentation, is not an option for me because even read only
> > > > > statements (SELECT) can potentially return an error. And obtaining an
> > > > > exclusive lock for a read statement eliminates all concurrency there
> > > > >
> > > >
> > >
> > >
> > > is in
> > > > > SQLite.
> > > >
> > > >
> > > >
> > > >
> > > > Every operation on an SQLite database connection operates under an
> > > > exclusive mutex on that database connection, so you don't have any
> > > > concurrency anyhow.
> > > >
> > > >
> > > > >
> > > > > So the only solution I can come up with is to make "sqlite3_errmsg()"
> > > (and
> > > > > related functions) use thread local memory.
> > > > >
> > > > > Is there (or has there ever been made) any attempt on storing the 
> > > > > error
> > > > > message in thread local memory? (I'm a C# and Java developer, so I'm
> > > > >
> > > >
> > >
> > >
> > > not
> > > > > sure whether thread local memory even exists in C. It does in C# and
> > > >
> > >
> > >
> > > Java
> 

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

[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 thread: 59,3 seconds


On Thursday, 20. September 2012 at 15:37, Black, Michael (IS) wrote:

> 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.
> 
> http://www.tomshardware.com/forum/266376-28-intel-cache
> 
> 
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
> 
> 
> > 
> > > 
> > > 
> > > 
> > > 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 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.
> > > > > 
> > > > > However, http://www.sqlite.org/c3ref/errcode.html states:
> > > > > 
> > > > > "When the serialized threading mode (
> > > http://www.sqlite.org/threadsafe.html)
> > > > > is in use, it might be the case that a second error occurs on a
> > > > 
> > > 
> > > 
> > > separate
> > > > > thread in between the time of the first error and the call to these
> > > > > interfaces. When that happens, the second error will be reported since
> > > > > these interfaces always report the most recent result."
> > > > > 
> > > > > So, this is a problem in my application (and I definitely need multi
> > > > > threading).
> > > > > 
> > > > > Obtaining an exclusive lock for the database connection, as suggested
> > > in
> > > > > the documentation, is not an option for me because even read only
> > > > > statements (SELECT) can potentially return an error. And obtaining an
> > > > > exclusive lock for a read statement eliminates all concurrency there
> > > > > 
> > > > 
> > > 
> > > 
> > > is in
> > > > > SQLite.
> > > > 
> > > > 
> > > > 
> > > > 
> > > > Every operation on an SQLite database connection operates under an
> > > > exclusive mutex on that database connection, so you don't have any
> > > > concurrency anyhow.
> > > > 
> > > > 
> > > > > 
> > > > > So the only solution I can come up with is to make "sqlite3_errmsg()"
> > > (and
> > > > > related functions) use thread local memory.
> > > > > 
> > > > > Is there (or has there ever been made) any attempt on storing the 
> > > > > error
> > > > > message in thread local memory? (I'm a C# and Java developer, so I'm
> > > > > 
> > > > 
> > > 
> > > 
> > > not
> > > > > sure whether thread local memory even exists in C. It does in C# and
> > > > 
> > > 
> > > 
> > > Java
> > > > > though.)
> > > > 
> > > > 
> > > > 
> > > > 
> > > > Thread local storage has been available to C code since long before Java
> > > > and C# were even invented. But it is accessed through library routines
> > > > that are not cross-platform, so we are not interested in using it in
> > > > SQLite. Furthermore, making such a change would break backwards
> > > > compatibility, which is a huge no-no with SQLite.
> > > > 
> > > > Best regards
> > > > > Sebastian
> > > > > 
> > > > > ___
> > > > > sqlite-users mailing list
> > > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
> > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > > > 
> > > > 
> > > > 
> > > > 
> > > > 
> > > > 
> > > > 
> > > > --
> > > > D. Richard Hipp
> > > > d...@sqlite.org (mailto:d...@sqlite.org)
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > > 
> > > 
> > > 
> > > 
> > > 
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > 
> > 
> > 
> > 
> > 
> > 
> > --
> > D. Richard Hipp
> > d...@sqlite.org (mailto:d...@sqlite.org)
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.or

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

[Black, Michael (IS)]

I should add that one reason I mention that is that as your database grows the 
speed difference to to caching disappears.  All has to do with the probability 
of hitting the caches decreases as the database grows.

You may find making smaller page sizes might help too as that will reduce the # 
of cache ejects.  Depends on how scattered your select calls are.

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-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

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.

http://www.tomshardware.com/forum/266376-28-intel-cache


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


>
> >
> >
> >
> > 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 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.
> > > >
> > > > However, http://www.sqlite.org/c3ref/errcode.html states:
> > > >
> > > > "When the serialized threading mode (
> > http://www.sqlite.org/threadsafe.html)
> > > > is in use, it might be the case that a second error occurs on a
> > >
> >
> > separate
> > > > thread in between the time of the first error and the call to these
> > > > interfaces. When that happens, the second error will be reported since
> > > > these interfaces always report the most recent result."
> > > >
> > > > So, this is a problem in my application (and I definitely need multi
> > > > threading).
> > > >
> > > > Obtaining an exclusive lock for the database connection, as suggested
> > in
> > > > the documentation, is not an option for me because even read only
> > > > statements (SELECT) can potentially return an error. And obtaining an
> > > > exclusive lock for a read statement eliminates all concurrency there
> > > >
> > >
> >
> > is in
> > > > SQLite.
> > >
> > >
> > >
> > > Every operation on an SQLite database connection operates under an
> > > exclusive mutex on that database connection, so you don't have any
> > > concurrency anyhow.
> > >
> > >
> > > >
> > > > So the only solution I can come up with is to make "sqlite3_errmsg()"
> > (and
> > > > related functions) use thread local memory.
> > > >
> > > > Is there (or has there ever been made) any attempt on storing the error
> > > > message in thread local memory? (I'm a C# and Java developer, so I'm
> > > >
> > >
> >
> > not
> > > > sure whether thread local memory even exists in C. It does in C# and
> > >
> >
> > Java
> > > > though.)
> > >
> > >
> > >
> > > Thread local storage has been available to C code since long before Java
> > > and C# were even invented. But it is accessed through library routines
> > > that are not cross-platform, so we are not interested in using it in
> > > SQLite. Furthermore, making such a change would break backwards
> > > compatibility, which is a huge no-no with SQLite.
> > >
> > > Best regards
> > > > Sebastian
> > > >
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > >
> > >
> > >
> > >

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

[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.

http://www.tomshardware.com/forum/266376-28-intel-cache


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


>
> >
> >
> >
> > 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 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.
> > > >
> > > > However, http://www.sqlite.org/c3ref/errcode.html states:
> > > >
> > > > "When the serialized threading mode (
> > http://www.sqlite.org/threadsafe.html)
> > > > is in use, it might be the case that a second error occurs on a
> > >
> >
> > separate
> > > > thread in between the time of the first error and the call to these
> > > > interfaces. When that happens, the second error will be reported since
> > > > these interfaces always report the most recent result."
> > > >
> > > > So, this is a problem in my application (and I definitely need multi
> > > > threading).
> > > >
> > > > Obtaining an exclusive lock for the database connection, as suggested
> > in
> > > > the documentation, is not an option for me because even read only
> > > > statements (SELECT) can potentially return an error. And obtaining an
> > > > exclusive lock for a read statement eliminates all concurrency there
> > > >
> > >
> >
> > is in
> > > > SQLite.
> > >
> > >
> > >
> > > Every operation on an SQLite database connection operates under an
> > > exclusive mutex on that database connection, so you don't have any
> > > concurrency anyhow.
> > >
> > >
> > > >
> > > > So the only solution I can come up with is to make "sqlite3_errmsg()"
> > (and
> > > > related functions) use thread local memory.
> > > >
> > > > Is there (or has there ever been made) any attempt on storing the error
> > > > message in thread local memory? (I'm a C# and Java developer, so I'm
> > > >
> > >
> >
> > not
> > > > sure whether thread local memory even exists in C. It does in C# and
> > >
> >
> > Java
> > > > though.)
> > >
> > >
> > >
> > > Thread local storage has been available to C code since long before Java
> > > and C# were even invented. But it is accessed through library routines
> > > that are not cross-platform, so we are not interested in using it in
> > > SQLite. Furthermore, making such a change would break backwards
> > > compatibility, which is a huge no-no with SQLite.
> > >
> > > Best regards
> > > > Sebastian
> > > >
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > >
> > >
> > >
> > >
> > >
> > >
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org (mailto:d...@sqlite.org)
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org (mailto:d...@sqlite.org)
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org (mailto: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

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

[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 (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.
> 
> 
> > 
> > 
> > 
> > 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 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.
> > > > 
> > > > However, http://www.sqlite.org/c3ref/errcode.html states:
> > > > 
> > > > "When the serialized threading mode (
> > http://www.sqlite.org/threadsafe.html)
> > > > is in use, it might be the case that a second error occurs on a
> > > 
> > 
> > separate
> > > > thread in between the time of the first error and the call to these
> > > > interfaces. When that happens, the second error will be reported since
> > > > these interfaces always report the most recent result."
> > > > 
> > > > So, this is a problem in my application (and I definitely need multi
> > > > threading).
> > > > 
> > > > Obtaining an exclusive lock for the database connection, as suggested
> > in
> > > > the documentation, is not an option for me because even read only
> > > > statements (SELECT) can potentially return an error. And obtaining an
> > > > exclusive lock for a read statement eliminates all concurrency there
> > > > 
> > > 
> > 
> > is in
> > > > SQLite.
> > > 
> > > 
> > > 
> > > Every operation on an SQLite database connection operates under an
> > > exclusive mutex on that database connection, so you don't have any
> > > concurrency anyhow.
> > > 
> > > 
> > > > 
> > > > So the only solution I can come up with is to make "sqlite3_errmsg()"
> > (and
> > > > related functions) use thread local memory.
> > > > 
> > > > Is there (or has there ever been made) any attempt on storing the error
> > > > message in thread local memory? (I'm a C# and Java developer, so I'm
> > > > 
> > > 
> > 
> > not
> > > > sure whether thread local memory even exists in C. It does in C# and
> > > 
> > 
> > Java
> > > > though.)
> > > 
> > > 
> > > 
> > > Thread local storage has been available to C code since long before Java
> > > and C# were even invented. But it is accessed through library routines
> > > that are not cross-platform, so we are not interested in using it in
> > > SQLite. Furthermore, making such a change would break backwards
> > > compatibility, which is a huge no-no with SQLite.
> > > 
> > > Best regards
> > > > Sebastian
> > > > 
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > > 
> > > 
> > > 
> > > 
> > > 
> > > 
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org (mailto:d...@sqlite.org)
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > 
> > 
> > 
> > 
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > 
> 
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org (mailto:d...@sqlite.org)
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org (mailto: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

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

[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.


>
>
>
> 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 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.
> > >
> > > However, http://www.sqlite.org/c3ref/errcode.html states:
> > >
> > > "When the serialized threading mode (
> http://www.sqlite.org/threadsafe.html)
> > > is in use, it might be the case that a second error occurs on a
> separate
> > > thread in between the time of the first error and the call to these
> > > interfaces. When that happens, the second error will be reported since
> > > these interfaces always report the most recent result."
> > >
> > > So, this is a problem in my application (and I definitely need multi
> > > threading).
> > >
> > > Obtaining an exclusive lock for the database connection, as suggested
> in
> > > the documentation, is not an option for me because even read only
> > > statements (SELECT) can potentially return an error. And obtaining an
> > > exclusive lock for a read statement eliminates all concurrency there
> is in
> > > SQLite.
> > >
> >
> >
> > Every operation on an SQLite database connection operates under an
> > exclusive mutex on that database connection, so you don't have any
> > concurrency anyhow.
> >
> >
> > >
> > > So the only solution I can come up with is to make "sqlite3_errmsg()"
> (and
> > > related functions) use thread local memory.
> > >
> > > Is there (or has there ever been made) any attempt on storing the error
> > > message in thread local memory? (I'm a C# and Java developer, so I'm
> not
> > > sure whether thread local memory even exists in C. It does in C# and
> Java
> > > though.)
> > >
> >
> >
> > Thread local storage has been available to C code since long before Java
> > and C# were even invented. But it is accessed through library routines
> > that are not cross-platform, so we are not interested in using it in
> > SQLite. Furthermore, making such a change would break backwards
> > compatibility, which is a huge no-no with SQLite.
> >
> > Best regards
> > > Sebastian
> > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org (mailto:d...@sqlite.org)
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org (mailto: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
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 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.
> > 
> > However, http://www.sqlite.org/c3ref/errcode.html states:
> > 
> > "When the serialized threading mode (http://www.sqlite.org/threadsafe.html)
> > is in use, it might be the case that a second error occurs on a separate
> > thread in between the time of the first error and the call to these
> > interfaces. When that happens, the second error will be reported since
> > these interfaces always report the most recent result."
> > 
> > So, this is a problem in my application (and I definitely need multi
> > threading).
> > 
> > Obtaining an exclusive lock for the database connection, as suggested in
> > the documentation, is not an option for me because even read only
> > statements (SELECT) can potentially return an error. And obtaining an
> > exclusive lock for a read statement eliminates all concurrency there is in
> > SQLite.
> > 
> 
> 
> Every operation on an SQLite database connection operates under an
> exclusive mutex on that database connection, so you don't have any
> concurrency anyhow.
> 
> 
> > 
> > So the only solution I can come up with is to make "sqlite3_errmsg()" (and
> > related functions) use thread local memory.
> > 
> > Is there (or has there ever been made) any attempt on storing the error
> > message in thread local memory? (I'm a C# and Java developer, so I'm not
> > sure whether thread local memory even exists in C. It does in C# and Java
> > though.)
> > 
> 
> 
> Thread local storage has been available to C code since long before Java
> and C# were even invented. But it is accessed through library routines
> that are not cross-platform, so we are not interested in using it in
> SQLite. Furthermore, making such a change would break backwards
> compatibility, which is a huge no-no with SQLite.
> 
> Best regards
> > Sebastian
> > 
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > 
> 
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org (mailto:d...@sqlite.org)
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org (mailto: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

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

[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.
>
> However, http://www.sqlite.org/c3ref/errcode.html states:
>
> "When the serialized threading mode (http://www.sqlite.org/threadsafe.html)
> is in use, it might be the case that a second error occurs on a separate
> thread in between the time of the first error and the call to these
> interfaces. When that happens, the second error will be reported since
> these interfaces always report the most recent result."
>
> So, this is a problem in my application (and I definitely need multi
> threading).
>
> Obtaining an exclusive lock for the database connection, as suggested in
> the documentation, is not an option for me because even read only
> statements (SELECT) can potentially return an error. And obtaining an
> exclusive lock for a read statement eliminates all concurrency there is in
> SQLite.
>

Every operation on an SQLite database connection operates under an
exclusive mutex on that database connection, so you don't have any
concurrency anyhow.


>
> So the only solution I can come up with is to make "sqlite3_errmsg()" (and
> related functions) use thread local memory.
>
> Is there (or has there ever been made) any attempt on storing the error
> message in thread local memory? (I'm a C# and Java developer, so I'm not
> sure whether thread local memory even exists in C. It does in C# and Java
> though.)
>

Thread local storage has been available to C code since long before Java
and C# were even invented.  But it is accessed through library routines
that are not cross-platform, so we are not interested in using it in
SQLite.  Furthermore, making such a change would break backwards
compatibility, which is a huge no-no with SQLite.

Best regards
> Sebastian
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Store error messages in thread local memory

[Sebastian Krysmanski]

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.

However, http://www.sqlite.org/c3ref/errcode.html states:

"When the serialized threading mode (http://www.sqlite.org/threadsafe.html) is 
in use, it might be the case that a second error occurs on a separate thread in 
between the time of the first error and the call to these interfaces. When that 
happens, the second error will be reported since these interfaces always report 
the most recent result."

So, this is a problem in my application (and I definitely need multi threading).

Obtaining an exclusive lock for the database connection, as suggested in the 
documentation, is not an option for me because even read only statements 
(SELECT) can potentially return an error. And obtaining an exclusive lock for a 
read statement eliminates all concurrency there is in SQLite.

So the only solution I can come up with is to make "sqlite3_errmsg()" (and 
related functions) use thread local memory.

Is there (or has there ever been made) any attempt on storing the error message 
in thread local memory? (I'm a C# and Java developer, so I'm not sure whether 
thread local memory even exists in C. It does in C# and Java though.)

Best regards
Sebastian

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users