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

> 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

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

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

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

> 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

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

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

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

>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 >connec

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

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

>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

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

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

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

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

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

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

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

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"

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

>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

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

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

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

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! >

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

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

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

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

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

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

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

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