Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Jens Alfke]

> On May 15, 2018, at 1:04 PM, Howard Chu  wrote:
> 
> https://github.com/LMDB/sqlightning/tree/mdb 
> 
> 
> You didn't look carefully enough, so you're in no position to offer advice.

It's not supposed to be hide-and-seek! There's a plain vanilla SQLite on the 
master branch, with your modified code over on a separate branch, and no 
indication of this in the readme.

It looks like you've worked by updating SQLite on the master branch and then 
merging master into 'mob'. That's somewhat more tractable than what I thought, 
but still daunting since I'm not familiar with either codebase. I've done my 
share of large merges in my career, but generally with code I know pretty well. 
How much work do you think it would be for someone in my position to update 
this to SQLite 3.24?

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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Howard Chu]

Jens Alfke wrote:




On May 14, 2018, at 11:25 PM, Howard Chu  wrote:


Do you have a link to the currently updated version of this? Google gives me 
projects that haven't been updated since 2015.


That's the most recent version. So far I haven't seen any compelling new 
features in subsequent SQLite versions to warrant a resync of the code.


It looks to be based on SQLite 3.7.16. New features since then include partial 
indexes, common table expressions, next-gen query planner, deferring foreign 
keys, WITHOUT ROWID tables, row values, secure pointer passing for native 
functions, sqlite3_serialize(), and the upcoming UPSERT … and that's just from 
skimming through the release history. Plus of course all sorts of query planner 
improvements, and misc. bug fixes.


Many of those new features resulted in performance regressions, which is the 
main reason I stayed away from them.


Partial indexes are a must-have for my use case, so when I ran across 
SQLightning in 2016 I quickly gave up on it. There have also been three or four 
bug fixes in recent SQLite releases that fixed serious problems we were having.

If you build a modified version of SQLite in such a way that it can't feasibly* 
be updated by anyone but you, and then have no plans to keep it up to date, it 
isn't a product anyone else can seriously use. It's more of a personal project, 
or a gauntlet thrown down to the SQLite team. Which is fine as it goes, but I 
don't think it's a good idea to suggest other people use it.

—Jens

* I've looked at the source. There's no version history in Git, no copy of the 
original SQLite source files, and no markers I could find in the source that 
show where your changes are. I quickly decided that trying to merge in a newer 
version of SQLite would be a waste of time.


Nonsense.

https://github.com/LMDB/sqlightning/tree/mdb

You didn't look carefully enough, so you're in no position to offer advice.

--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Jens Alfke]

> On May 14, 2018, at 11:25 PM, Howard Chu  wrote:
> 
>> Do you have a link to the currently updated version of this? Google gives me 
>> projects that haven't been updated since 2015.
> 
> That's the most recent version. So far I haven't seen any compelling new 
> features in subsequent SQLite versions to warrant a resync of the code.

It looks to be based on SQLite 3.7.16. New features since then include partial 
indexes, common table expressions, next-gen query planner, deferring foreign 
keys, WITHOUT ROWID tables, row values, secure pointer passing for native 
functions, sqlite3_serialize(), and the upcoming UPSERT … and that's just from 
skimming through the release history. Plus of course all sorts of query planner 
improvements, and misc. bug fixes.

Partial indexes are a must-have for my use case, so when I ran across 
SQLightning in 2016 I quickly gave up on it. There have also been three or four 
bug fixes in recent SQLite releases that fixed serious problems we were having.

If you build a modified version of SQLite in such a way that it can't feasibly* 
be updated by anyone but you, and then have no plans to keep it up to date, it 
isn't a product anyone else can seriously use. It's more of a personal project, 
or a gauntlet thrown down to the SQLite team. Which is fine as it goes, but I 
don't think it's a good idea to suggest other people use it.

—Jens

* I've looked at the source. There's no version history in Git, no copy of the 
original SQLite source files, and no markers I could find in the source that 
show where your changes are. I quickly decided that trying to merge in a newer 
version of SQLite would be a waste of time.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Peter Da Silva]

On 5/15/18, 1:25 AM, "Howard Chu"  wrote:

Peter Da Silva wrote:
> On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" 
 
wrote:
> 
>  SQLightning
> 
> Do you have a link to the currently updated version of this? Google gives 
me projects that haven't been updated since 2015.

That's the most recent version. So far I haven't seen any compelling new 
features in subsequent SQLite versions to warrant a resync of the code.

There's been a couple of changes in syntax recently (UPSERT and IS TRUE/IS 
FALSE) that you might want to pick up for compatibility. They make it easier to 
have the same program switch between an SQLITE and PGSQL back end, which we're 
wont to do.

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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Howard Chu]

Peter Da Silva wrote:

Now I'm even more confused, there's "sqlightning" and "sqlitening". Are these 
different names for the same project? It doesn't seem so. Which was intended?

https://github.com/LMDB/sqlightning


I was referring to this. ^^

Since it uses LMDB, and LMDB readers are wait-free, you can run as many reads 
as you have CPU threads, without blocking.




On 5/14/18, 8:47 AM, "sqlite-users on behalf of Bernard Ertl" 
 
wrote:

 This is the latest:
 
 http://www.sqlitening.planetsquires.com/index.php?topic=9427.0
 
 I contributed to the last SQLitening update.  No one has reported any issues that need fixing or updating since that update.  It seems to be working quite well/stable.
 
 
 
 > On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu"  wrote:
 
 > SQLightning
 
 > Do you have a link to the currently updated version of this? Google gives me projects that haven't been updated since 2015.
 
 > ___

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


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




--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Howard Chu]

Peter Da Silva wrote:

On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" 
 wrote:

 SQLightning

Do you have a link to the currently updated version of this? Google gives me 
projects that haven't been updated since 2015.


That's the most recent version. So far I haven't seen any compelling new 
features in subsequent SQLite versions to warrant a resync of the code.


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Keith Medcalf]

Yes, and why do you need to do that?  (Use multiple connections to the same 
read only data)?

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Abrozy Nieprzelozy
>Sent: Monday, 14 May, 2018 16:46
>To: SQLite mailing list
>Subject: Re: [sqlite] Multi threaded readers on memory sqlite cannot
>scale
>
>AFAIK the only way to access single memory database from multiple
>connections
>is through shared cache.
>
>2018-05-15 0:27 GMT+02:00, Keith Medcalf :
>>
>>>2018-05-13 12:50 GMT+02:00, Techno Magos :
>>
>>>> Hello
>>
>>>> I do not have clear examples to post  on this but would like to
>>>> report findings around multi threaded read access (single
>process) in a
>>>> large system that uses sqlite.
>>
>>>> This may be a known issue/restriction of memory sqlite behaviour,
>>>> but wanted to check with the list first:
>>
>>>> 1. Running 2, 3, ... 6 multi threaded readers of a single *memory
>>>> *sqlite database (via shared cache mode) on an 8 core cpu shows
>no
>>>> throughput gain at all compared to single threaded throughput.
>>>> In fact, it shows a throughput drop: i.e. if a single thread can
>>>> do N simple queries/sec, 2 threads .. up to 6 threads do a little
>>>> less (10% drop) in total.  This suggests that access to memory
>>>> sqlite can only be serialized?
>>
>> No one has asked the $10 million dollar question yet ... why are
>you doing
>> this?  It certainly does not sound like you have a highly
>constrained
>> itty-bitty wee boxen for which the "shared cache" was designed (ie,
>you seem
>> to have more that 4 cycles per second of CPU and 10 bytes of
>memory).
>>
>> So what was the configuration which you WERE PREVIOUSLY USING which
>was
>> deficient and for which you are trying this optimization?
>>
>> Everyone knows that when you use "shared cache mode" you are
>trading off
>> memory and CPU cycles against performance.  Shared Cache Mode is
>designed so
>> that if you are *really* so inclined you can run SQLite on your
>Tandy Pocket
>> Computer too (https://en.wikipedia.org/wiki/Tandy_Pocket_Computer)
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says a
>> lot about anticipated traffic volume.
>>
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Abroży Nieprzełoży]

AFAIK the only way to access single memory database from multiple connections
is through shared cache.

2018-05-15 0:27 GMT+02:00, Keith Medcalf :
>
>>2018-05-13 12:50 GMT+02:00, Techno Magos :
>
>>> Hello
>
>>> I do not have clear examples to post  on this but would like to
>>> report findings around multi threaded read access (single process) in a
>>> large system that uses sqlite.
>
>>> This may be a known issue/restriction of memory sqlite behaviour,
>>> but wanted to check with the list first:
>
>>> 1. Running 2, 3, ... 6 multi threaded readers of a single *memory
>>> *sqlite database (via shared cache mode) on an 8 core cpu shows no
>>> throughput gain at all compared to single threaded throughput.
>>> In fact, it shows a throughput drop: i.e. if a single thread can
>>> do N simple queries/sec, 2 threads .. up to 6 threads do a little
>>> less (10% drop) in total.  This suggests that access to memory
>>> sqlite can only be serialized?
>
> No one has asked the $10 million dollar question yet ... why are you doing
> this?  It certainly does not sound like you have a highly constrained
> itty-bitty wee boxen for which the "shared cache" was designed (ie, you seem
> to have more that 4 cycles per second of CPU and 10 bytes of memory).
>
> So what was the configuration which you WERE PREVIOUSLY USING which was
> deficient and for which you are trying this optimization?
>
> Everyone knows that when you use "shared cache mode" you are trading off
> memory and CPU cycles against performance.  Shared Cache Mode is designed so
> that if you are *really* so inclined you can run SQLite on your Tandy Pocket
> Computer too (https://en.wikipedia.org/wiki/Tandy_Pocket_Computer)
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a
> lot about anticipated traffic volume.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Keith Medcalf]

>2018-05-13 12:50 GMT+02:00, Techno Magos :

>> Hello

>> I do not have clear examples to post  on this but would like to
>> report findings around multi threaded read access (single process) in a
>> large system that uses sqlite.

>> This may be a known issue/restriction of memory sqlite behaviour,
>> but wanted to check with the list first:

>> 1. Running 2, 3, ... 6 multi threaded readers of a single *memory
>> *sqlite database (via shared cache mode) on an 8 core cpu shows no
>> throughput gain at all compared to single threaded throughput. 
>> In fact, it shows a throughput drop: i.e. if a single thread can 
>> do N simple queries/sec, 2 threads .. up to 6 threads do a little 
>> less (10% drop) in total.  This suggests that access to memory 
>> sqlite can only be serialized?

No one has asked the $10 million dollar question yet ... why are you doing 
this?  It certainly does not sound like you have a highly constrained 
itty-bitty wee boxen for which the "shared cache" was designed (ie, you seem to 
have more that 4 cycles per second of CPU and 10 bytes of memory).

So what was the configuration which you WERE PREVIOUSLY USING which was 
deficient and for which you are trying this optimization?

Everyone knows that when you use "shared cache mode" you are trading off memory 
and CPU cycles against performance.  Shared Cache Mode is designed so that if 
you are *really* so inclined you can run SQLite on your Tandy Pocket Computer 
too (https://en.wikipedia.org/wiki/Tandy_Pocket_Computer)

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Richard Hipp]

On 5/13/18, Abroży Nieprzełoży  wrote:
> Try to open N separate database connections (without shared cache) and
> load content using sqlite3_deserialize with
> SQLITE_DESERIALIZE_READONLY flag.
> http://www.sqlite.org/c3ref/deserialize.html
> SQLite won't copy data but use provided buffer so you won't have N
> copies of databse.

+1

This seems like the best solution proposed so far.  I wish I had been
the one to think of it :-)

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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Peter Da Silva]

On 5/14/18, 9:17 AM, "sqlite-users on behalf of Bernard Ertl" 
 wrote:

Apologies if I muddled the waters here.  I read the "SQLightning" response 
below as SQLitening.  I didn't know there was a similarly named project out 
there.  I also can't see the beginning of this discussion to have context on 
what was originally asked, so I don't know which project was actually intended.

Ah, OK. Here's more context, don't know if it'll help: 
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2018-May/079224.html

Clemens Ladisch wrote:
> Techno Magos wrote:
>> So, memory sqlite is not really usable with multiple threads (readers).
>> While one might expect  that multiple readers of *memory *content could
>> scale even better than with file content.
> 
> Concurrent accesses to the same in-memory data structures must be
> serialized.  In shared-cache mode, the connections share the cache, while
> on-disk connections each have their own cache.
> 
>> Is there some special mode possible to achieve scaling up throughput with
>> multiple threads for memory sqlite content?
> 
> Put a DB file on a RAM disk.  Or on a normal disk (with looser synchronous
> and journal_mode settings), and rely on the OS file cache.

Or just use SQLightning, which has no scalability limits for readers.
 

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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Bernard Ertl]

Apologies if I muddled the waters here.  I read the "SQLightning" response 
below as SQLitening.  I didn't know there was a similarly named project out 
there.  I also can't see the beginning of this discussion to have context on 
what was originally asked, so I don't know which project was actually intended.

SQLitening is a (multi-threaded) client-server wrapper for SQLite.



> Now I'm even more confused, there's "sqlightning" and "sqlitening". Are these 
> different names for the same project? It doesn't seem so. Which was intended?

> https://github.com/LMDB/sqlightning

> ?On 5/14/18, 8:47 AM, "sqlite-users on behalf of Bernard Ertl" 
>  bern...@interplansystems.com> wrote:

> This is the latest:

> http://www.sqlitening.planetsquires.com/index.php?topic=9427.0

> I contributed to the last SQLitening update.  No one has reported any 
> issues that need fixing or updating since that update.  It seems to be 
> working quite well/stable.



> > On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" 
>  
> wrote:

> > SQLightning

> > Do you have a link to the currently updated version of this? Google 
> gives me projects that haven't been updated since 2015.

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


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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Peter Da Silva]

Now I'm even more confused, there's "sqlightning" and "sqlitening". Are these 
different names for the same project? It doesn't seem so. Which was intended?

https://github.com/LMDB/sqlightning

On 5/14/18, 8:47 AM, "sqlite-users on behalf of Bernard Ertl" 
 wrote:

This is the latest:

http://www.sqlitening.planetsquires.com/index.php?topic=9427.0

I contributed to the last SQLitening update.  No one has reported any 
issues that need fixing or updating since that update.  It seems to be working 
quite well/stable. 



> On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" 
 
wrote:

> SQLightning

> Do you have a link to the currently updated version of this? Google gives 
me projects that haven't been updated since 2015.

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


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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Bernard Ertl]

This is the latest:

http://www.sqlitening.planetsquires.com/index.php?topic=9427.0

I contributed to the last SQLitening update.  No one has reported any issues 
that need fixing or updating since that update.  It seems to be working quite 
well/stable. 



> On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" 
>  
> wrote:

> SQLightning

> Do you have a link to the currently updated version of this? Google gives me 
> projects that haven't been updated since 2015.

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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Peter Da Silva]

On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" 
 
wrote:

SQLightning

Do you have a link to the currently updated version of this? Google gives me 
projects that haven't been updated since 2015.

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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Abroży Nieprzełoży]

Try to open N separate database connections (without shared cache) and
load content using sqlite3_deserialize with
SQLITE_DESERIALIZE_READONLY flag.
http://www.sqlite.org/c3ref/deserialize.html
SQLite won't copy data but use provided buffer so you won't have N
copies of databse.

2018-05-13 12:50 GMT+02:00, Techno Magos :
> Hello
>
> I do not have clear examples to post  on this but would like to report
> findings around multi threaded read access (single process) in a large
> system that uses sqlite.
>
> This may be a known issue/restriction of memory sqlite behaviour, but
> wanted to check with the list first:
>
> 1. Running 2, 3, ... 6 multi threaded readers of a single *memory *sqlite
> database (via shared cache mode) on an 8 core cpu shows no throughput gain
> at all compared to single threaded throughput. In fact, it shows a
> throughput drop: i.e. if a single thread can do N simple queries/sec, 2
> threads .. up to 6 threads do a little less (10% drop) in total. This
> suggests that access to memory sqlite can only be serialized?
>
> 2. Running the same example on sqlite *file *(multi threaded mode; WAL
> journal) scales almost linearly;  so 6 threads provide nearly 6xN
> throughput. Single threaded throughput is a bit slower (around 15-20%)
> than single threaded in-memory access (expected).
>
> So, memory sqlite is not really usable with multiple threads (readers).
> While one might expect  that multiple readers of *memory *content could
> scale even better than with file content.
>
> Can this restriction be lifted?
> Is there some special mode possible to achieve scaling up throughput with
> multiple threads for memory sqlite content?
>
>
> Thanks
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Kevin O'Gorman]

On Sun, May 13, 2018 at 9:01 AM, Dennis Clarke 
wrote:

> On 05/13/2018 11:57 AM, Kevin O'Gorman wrote:
>
>> The arguments here are simplified
>>
>
>
> Will you stop top posting please?
>
> I am trying to follow along here about some x86 boxen stuff but
> you are top posting madly. Also is that a single socket machine
> with a single big memory bank or is it NUMA and multiple sockets
> or is it just a single motherboard unit?
>
>
> Dennis
>
>
It is a single motherboard with two Xeon sockets, and 16 memory sockets.
I think those sockets are in two banks, which may be relevant.

Anyhow, those details weren't my point.  The point was that computing
hardware
is a galaxy of possibilities, some with more inherent parallelism than
others.  I'm
not personally having throughput troubles.

-- 
Dictionary.com's word of the year: *complicit*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Richard Damon]

On 5/13/18 11:57 AM, Kevin O'Gorman wrote:
>  More importantly, it uses DDR4 memory which I think means there
> are 4 channels to memory which can be used in parallel

DDR4 does NOT have 4 independent memory channels. DDR4 is the forth
generation of the Double Data Rate interface standard. DDR memory chips
only have a single channel of access to them, so can only access a
single chunk of memory at a time. Some machines may have multiple DDR
interfaces allowing them to access multiple memories in parallel, but
the accesses would need to be to different memory modules and the
different buses.

-- 
Richard Damon

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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Tim Streater]

On 13 May 2018, at 17:01, Dennis Clarke  wrote:

> On 05/13/2018 11:57 AM, Kevin O'Gorman wrote:

> Also is that a single socket machine with a single big memory bank or
> is it NUMA and multiple sockets or is it just a single motherboard unit?

And I'd be curious to know whether memory is interleaved or not.



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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Clemens Ladisch]

Kevin O'Gorman wrote:
> why is a read-only database being serialized at all?

The database file is read only, the in-memory data structures are not.
For example, when the cache size is smaller than the DB size, pages
must be removed from and added to the internal list of cached pages.

When using multiple connections (without shared cache), each connection
has its own cache, and knows that nobody else can access it.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Simon Slavin]

On 13 May 2018, at 4:57pm, Kevin O'Gorman  wrote:

> More importantly, it uses DDR4 memory which I think means there
> are 4 channels to memory which can be used in parallel -- perhaps not on
> exactly the same address but the memory is spread among 16 DIMMs.

Suppose your different threads are reading different rows of the database, a 
long way away from one-another.  But to find out where those rows are in memory 
they need to consult an index.  And the index fits in a single page of memory, 
so they all have to read data from the same page of memory.

There isn't any locking in SQLite that was put in just to annoy users.  If 
you've followed section 2.2.1, 5 and 6 of



you're seeing the best SQLite can do.  But since you're obviously unhappy with 
what you're seeing try setting all your PRAGMAs and other settings the other 
way, one by one, and see if you see any difference.

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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Dennis Clarke]

On 05/13/2018 11:57 AM, Kevin O'Gorman wrote:

The arguments here are simplified



Will you stop top posting please?

I am trying to follow along here about some x86 boxen stuff but
you are top posting madly. Also is that a single socket machine
with a single big memory bank or is it NUMA and multiple sockets
or is it just a single motherboard unit?


Dennis

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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Kevin O'Gorman]

The arguments here are simplified, and assume some things that may or may
not be true.  The server I keep in my garage has  16 real cores, 32
threads.  More importantly, it uses DDR4 memory which I think means there
are 4 channels to memory which can be used in parallel -- perhaps not on
exactly the same address but the memory is spread among 16 DIMMs. (It's a
System76 "Silverback" with 256 GB RAM).  Lots of opportunities for
parallelism.  Moreover, depending on the actual work to be done, there may
be a considerable amount of "inherently parallelizable" work.  You don't
know until you try it -- or better yet, measure it.

Sure, there will be limits to how far this can go, but modern machines are
designed to take advantage of opportunities for parallelism.  You just have
to get rid of unnecessary locking.  So I repeat, why is a read-only
database being serialized at all?

On Sun, May 13, 2018 at 8:08 AM, Keith Medcalf  wrote:

>
> >2. Running the same example on sqlite *file *(multi threaded mode;
> >WAL journal) scales almost linearly;  so 6 threads provide nearly 6xN
> >throughput. Single threaded throughput is a bit slower (around 15-
> >20%) than single threaded in-memory access (expected).
>
> So, there is some "part" of the process that is "inherently
> parallelizable" and you have managed to have a one "thread" to some work
> during the time some "other thread" is consumed doing something you cannot
> see.  Congratulations.  This will increase by diminishing returns.
> Eventually adding more parallelism will make things slower.
>
> >So, memory sqlite is not really usable with multiple threads
> >(readers).  While one might expect that multiple readers of
> >*memory *content could scale even better than with file content.
>
> I would expect that a single connection to a single in memory database is
> 100% efficient and cannot be further optimized, and therefore I would not
> try.
>
> Why would I (or anyone of sound mind) want to insert "inefficiencies" so
> that one can then spend inordinate amounts of time to never quite eliminate
> them, and only go forever slower in the process?
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Dictionary.com's word of the year: *complicit*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Keith Medcalf]

>2. Running the same example on sqlite *file *(multi threaded mode;
>WAL journal) scales almost linearly;  so 6 threads provide nearly 6xN
>throughput. Single threaded throughput is a bit slower (around 15-
>20%) than single threaded in-memory access (expected).

So, there is some "part" of the process that is "inherently parallelizable" and 
you have managed to have a one "thread" to some work during the time some 
"other thread" is consumed doing something you cannot see.  Congratulations.  
This will increase by diminishing returns.  Eventually adding more parallelism 
will make things slower.

>So, memory sqlite is not really usable with multiple threads
>(readers).  While one might expect that multiple readers of 
>*memory *content could scale even better than with file content.

I would expect that a single connection to a single in memory database is 100% 
efficient and cannot be further optimized, and therefore I would not try.  

Why would I (or anyone of sound mind) want to insert "inefficiencies" so that 
one can then spend inordinate amounts of time to never quite eliminate them, 
and only go forever slower in the process?





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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Simon Slavin]

On 13 May 2018, at 11:50am, Techno Magos  wrote:

> So, memory sqlite is not really usable with multiple threads (readers).
> While one might expect  that multiple readers of *memory *content could
> scale even better than with file content.
> 
> Can this restriction be lifted?

It's not a pointless restriction.  It's how computers work.

A memory bus cannot respond to two requests for memory at once.  It has a set 
of address lines.  You put the address you require on those lines.  You then 
send the "read that address" signal.  The memory bus goes to fetch the data 
from that address, puts whatever it finds on the data lines, then signals 
"completed".

Having multiple processors speeds things up only if all you're doing is 
processing.  When two threads need to read data from the same piece of memory 
they have to queue up to make requests for data.  You may be better off if each 
process has its own memory.

[above is simplified for clarity]

See what happens if you stop using shared cache mode.  Also, if all your 
threads are merely reading, not writing, make sure they're using a connection 
(or one connection per thread) which was opened that way.  That allows SQLite 
to use lots of internal tricks.

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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Kevin O'Gorman]

It's not clear to me why reads must be serialized at all.  Maybe this could
be re-thought?  Maybe there should be a way to tell SQLite that a certain
DB or table is to be read-only and unserialized?

On Sun, May 13, 2018 at 7:15 AM, Keith Medcalf  wrote:

>
> Say Hi to Gene!
>
> https://en.wikipedia.org/wiki/Amdahl%27s_law
>
> So I believe what you are saying is something like this:  If I take a
> child and have it count as fast as it can then it can count to X in an
> hour.  However, I take the same child but have it count as fast as it can
> at five minute stretches, the sum of the X's is less than it was at one
> go.  If I get the child to do this at random intervals consuming juice
> boxes in between, the sum of the X's is even lower, the higher the number
> of interruptions becomes.
>
> In the second case the task consists of counting to ten and then drinking
> a juice box.  If you get one child, then it takes time X.  Interestingly,
> if you get two children, the tasks (empty juice boxes) stack up twice as
> fast.  There is some overlap between the operations.  As you add more and
> more children it goes faster and faster, but not quite.  Eventally all the
> children are drinking the juice box as the same time and adding more
> children does not make things go faster.
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Techno Magos
> >Sent: Sunday, 13 May, 2018 04:51
> >To: sqlite-users@mailinglists.sqlite.org
> >Subject: [sqlite] Multi threaded readers on memory sqlite cannot
> >scale
> >
> >Hello
> >
> >I do not have clear examples to post  on this but would like to
> >report
> >findings around multi threaded read access (single process) in a
> >large
> >system that uses sqlite.
> >
> >This may be a known issue/restriction of memory sqlite behaviour, but
> >wanted to check with the list first:
> >
> >1. Running 2, 3, ... 6 multi threaded readers of a single *memory
> >*sqlite
> >database (via shared cache mode) on an 8 core cpu shows no throughput
> >gain
> >at all compared to single threaded throughput. In fact, it shows a
> >throughput drop: i.e. if a single thread can do N simple queries/sec,
> >2
> >threads .. up to 6 threads do a little less (10% drop) in total. This
> >suggests that access to memory sqlite can only be serialized?
> >
> >2. Running the same example on sqlite *file *(multi threaded mode;
> >WAL
> >journal) scales almost linearly;  so 6 threads provide nearly 6xN
> >throughput. Single threaded throughput is a bit slower (around 15-
> >20%)
> >than single threaded in-memory access (expected).
> >
> >So, memory sqlite is not really usable with multiple threads
> >(readers).
> >While one might expect  that multiple readers of *memory *content
> >could
> >scale even better than with file content.
> >
> >Can this restriction be lifted?
> >Is there some special mode possible to achieve scaling up throughput
> >with
> >multiple threads for memory sqlite content?
> >
> >
> >Thanks
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Dictionary.com's word of the year: *complicit*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Keith Medcalf]

Say Hi to Gene!

https://en.wikipedia.org/wiki/Amdahl%27s_law

So I believe what you are saying is something like this:  If I take a child and 
have it count as fast as it can then it can count to X in an hour.  However, I 
take the same child but have it count as fast as it can at five minute 
stretches, the sum of the X's is less than it was at one go.  If I get the 
child to do this at random intervals consuming juice boxes in between, the sum 
of the X's is even lower, the higher the number of interruptions becomes.

In the second case the task consists of counting to ten and then drinking a 
juice box.  If you get one child, then it takes time X.  Interestingly, if you 
get two children, the tasks (empty juice boxes) stack up twice as fast.  There 
is some overlap between the operations.  As you add more and more children it 
goes faster and faster, but not quite.  Eventally all the children are drinking 
the juice box as the same time and adding more children does not make things go 
faster.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Techno Magos
>Sent: Sunday, 13 May, 2018 04:51
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Multi threaded readers on memory sqlite cannot
>scale
>
>Hello
>
>I do not have clear examples to post  on this but would like to
>report
>findings around multi threaded read access (single process) in a
>large
>system that uses sqlite.
>
>This may be a known issue/restriction of memory sqlite behaviour, but
>wanted to check with the list first:
>
>1. Running 2, 3, ... 6 multi threaded readers of a single *memory
>*sqlite
>database (via shared cache mode) on an 8 core cpu shows no throughput
>gain
>at all compared to single threaded throughput. In fact, it shows a
>throughput drop: i.e. if a single thread can do N simple queries/sec,
>2
>threads .. up to 6 threads do a little less (10% drop) in total. This
>suggests that access to memory sqlite can only be serialized?
>
>2. Running the same example on sqlite *file *(multi threaded mode;
>WAL
>journal) scales almost linearly;  so 6 threads provide nearly 6xN
>throughput. Single threaded throughput is a bit slower (around 15-
>20%)
>than single threaded in-memory access (expected).
>
>So, memory sqlite is not really usable with multiple threads
>(readers).
>While one might expect  that multiple readers of *memory *content
>could
>scale even better than with file content.
>
>Can this restriction be lifted?
>Is there some special mode possible to achieve scaling up throughput
>with
>multiple threads for memory sqlite content?
>
>
>Thanks
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Abroży Nieprzełoży]

I have a few ideas you could try with a file db.

1. Use VFS with no locks - named "win32-none" or "unix-none" depending
on your system (4th argument to sqlite_open_v2).

2. Run "pragma locking_mode=exclusive;" on each connection or compile
SQLite with -DSQLITE_DEFAULT_LOCKING_MODE=1

3. Compile SQLite with -DSQLITE_MAX_MMAP_SIZE= and -DSQLITE_DEFAULT_MMAP_SIZE=


2018-05-13 12:50 GMT+02:00, Techno Magos :
> Hello
>
> I do not have clear examples to post  on this but would like to report
> findings around multi threaded read access (single process) in a large
> system that uses sqlite.
>
> This may be a known issue/restriction of memory sqlite behaviour, but
> wanted to check with the list first:
>
> 1. Running 2, 3, ... 6 multi threaded readers of a single *memory *sqlite
> database (via shared cache mode) on an 8 core cpu shows no throughput gain
> at all compared to single threaded throughput. In fact, it shows a
> throughput drop: i.e. if a single thread can do N simple queries/sec, 2
> threads .. up to 6 threads do a little less (10% drop) in total. This
> suggests that access to memory sqlite can only be serialized?
>
> 2. Running the same example on sqlite *file *(multi threaded mode; WAL
> journal) scales almost linearly;  so 6 threads provide nearly 6xN
> throughput. Single threaded throughput is a bit slower (around 15-20%)
> than single threaded in-memory access (expected).
>
> So, memory sqlite is not really usable with multiple threads (readers).
> While one might expect  that multiple readers of *memory *content could
> scale even better than with file content.
>
> Can this restriction be lifted?
> Is there some special mode possible to achieve scaling up throughput with
> multiple threads for memory sqlite content?
>
>
> Thanks
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Howard Chu]

Clemens Ladisch wrote:

Techno Magos wrote:

So, memory sqlite is not really usable with multiple threads (readers).
While one might expect  that multiple readers of *memory *content could
scale even better than with file content.


Concurrent accesses to the same in-memory data structures must be
serialized.  In shared-cache mode, the connections share the cache, while
on-disk connections each have their own cache.


Is there some special mode possible to achieve scaling up throughput with
multiple threads for memory sqlite content?


Put a DB file on a RAM disk.  Or on a normal disk (with looser synchronous
and journal_mode settings), and rely on the OS file cache.


Or just use SQLightning, which has no scalability limits for readers.

--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Clemens Ladisch]

Techno Magos wrote:
> So, memory sqlite is not really usable with multiple threads (readers).
> While one might expect  that multiple readers of *memory *content could
> scale even better than with file content.

Concurrent accesses to the same in-memory data structures must be
serialized.  In shared-cache mode, the connections share the cache, while
on-disk connections each have their own cache.

> Is there some special mode possible to achieve scaling up throughput with
> multiple threads for memory sqlite content?

Put a DB file on a RAM disk.  Or on a normal disk (with looser synchronous
and journal_mode settings), and rely on the OS file cache.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multi threaded readers on memory sqlite cannot scale

[Techno Magos]

Hello

I do not have clear examples to post  on this but would like to report
findings around multi threaded read access (single process) in a large
system that uses sqlite.

This may be a known issue/restriction of memory sqlite behaviour, but
wanted to check with the list first:

1. Running 2, 3, ... 6 multi threaded readers of a single *memory *sqlite
database (via shared cache mode) on an 8 core cpu shows no throughput gain
at all compared to single threaded throughput. In fact, it shows a
throughput drop: i.e. if a single thread can do N simple queries/sec, 2
threads .. up to 6 threads do a little less (10% drop) in total. This
suggests that access to memory sqlite can only be serialized?

2. Running the same example on sqlite *file *(multi threaded mode; WAL
journal) scales almost linearly;  so 6 threads provide nearly 6xN
throughput. Single threaded throughput is a bit slower (around 15-20%)
than single threaded in-memory access (expected).

So, memory sqlite is not really usable with multiple threads (readers).
While one might expect  that multiple readers of *memory *content could
scale even better than with file content.

Can this restriction be lifted?
Is there some special mode possible to achieve scaling up throughput with
multiple threads for memory sqlite content?


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