Re: [sqlite] Stored Procedures

[Rowan Worth]

On 14 May 2018 at 01:08, Richard Damon  wrote:

> On 5/13/18 12:55 PM, Rowan Worth wrote:
> > On 9 May 2018 at 08:56, Richard Hipp  wrote:
> >
> >>   But with
> >> SQLite, there is no round-trip latency.  A "round-trip" to and
> >> database is just a function call, and is very very cheap.
> >>
> > I want to emphasise that Dr. Hipp's usage of "round-trip" only includes
> the
> > latency of _communication_ between the app and database in this
> statement,
> > and excludes any processing time required by the database.
> >
> > If you were to interpret "round-trip" from an app-centric perspective (as
> > in "the time taken to retrieve/commit data") then the statement becomes
> > misleading because handling the data involves i/o, possibly even
> > synchronous i/o, which is not "very very cheap" by any standard I'm aware
> > of :)
> >
> > -Rowan
>
Yes, if the request requires I/O, then that costs time, but then the
> operation would likely use similar I/O in whatever way the application
> needed to get that information, so that I/O shouldn't really be charged
> to the use of a database, but to the information requested. One thing to
> remember is SQLite is often compared as a better way to get information
> then using simple disk i/o, so the 'cost' of using SQLite (compared to
> the alternative) shouldn't include the base time to read the file, but
> only any extra i/o above that.
>

That's fair, but I wouldn't call the extra i/o imposed by sqlite "very very
cheap" either - it doubles writes (once to the rollback journal, once to
the DB), forces syncs, and likely results in a more seek heavy i/o pattern
(this depends a bit on schema design and whether the app requires/fully
takes advantage of relational operations).

To be clear, this is not a criticism of sqlite. These costs are paid for a
reason (eg. durability) and I think sqlite does its job very efficiently.
You're also right that an app implementing similar features without sqlite
will have to pay similar costs.

My point is simply that it's unwise to think of any DB query as having "no
latency" even when dealing with an sqlite DB.
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] shell edit quoting

[David Burgess]

> And it works for me:

I'm pleased for you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem b building sqlite on macOS android.

[Mark Sibly]

Hi,

I am having problems building sqlite with the android NDK for macos.
Weirdly, it *does* build fine on windows ndk.

The errors I am getting are shown below.

Bye,
Mark

'-- errors building sqlite on macOS android ndk


jni/../../../sqlite-amalgamation/sqlite3.c:31006:43: error: address of
overloaded function 'ioctl' is ambiguous
  { "ioctl", (sqlite3_syscall_ptr)ioctl,  0 },
  ^
/Users/marksibly/Library/Android/sdk/ndk-bundle/sysroot/usr/include/bits/ioctl.h:57:5:
note: candidate function has different number of parameters (expected 0 but
has 2)
int ioctl(int __fd, unsigned __request, ...) __overloadable __enable_if(1,
"") __RENAME(ioctl);
^
/Users/marksibly/Library/Android/sdk/ndk-bundle/sysroot/usr/include/bits/ioctl.h:36:5:
note: candidate function has different number of parameters (expected 0 but
has 2)
int ioctl(int __fd, int __request, ...);
^
jni/../../../sqlite-amalgamation/sqlite3.c:31006:22: error: initializer
element is not a compile-time constant
  { "ioctl", (sqlite3_syscall_ptr)ioctl,  0 },
 ^~
2 errors generated.
make: ***
[obj/local/armeabi-v7a/objs/mx2_sqlite/__/__/__/sqlite-amalgamation/sqlite3.o]
Error 1
___
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] Stored Procedures

[Richard Damon]

On 5/13/18 12:55 PM, Rowan Worth wrote:
> On 9 May 2018 at 08:56, Richard Hipp  wrote:
>
>>   But with
>> SQLite, there is no round-trip latency.  A "round-trip" to and
>> database is just a function call, and is very very cheap.
>>
> I want to emphasise that Dr. Hipp's usage of "round-trip" only includes the
> latency of _communication_ between the app and database in this statement,
> and excludes any processing time required by the database.
>
> If you were to interpret "round-trip" from an app-centric perspective (as
> in "the time taken to retrieve/commit data") then the statement becomes
> misleading because handling the data involves i/o, possibly even
> synchronous i/o, which is not "very very cheap" by any standard I'm aware
> of :)
>
> -Rowan
Yes, if the request requires I/O, then that costs time, but then the
operation would likely use similar I/O in whatever way the application
needed to get that information, so that I/O shouldn't really be charged
to the use of a database, but to the information requested. One thing to
remember is SQLite is often compared as a better way to get information
then using simple disk i/o, so the 'cost' of using SQLite (compared to
the alternative) shouldn't include the base time to read the file, but
only any extra i/o above that.

-- 
Richard Damon

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

Re: [sqlite] Stored Procedures

[Rowan Worth]

On 9 May 2018 at 08:56, Richard Hipp  wrote:

>   But with
> SQLite, there is no round-trip latency.  A "round-trip" to and
> database is just a function call, and is very very cheap.
>

I want to emphasise that Dr. Hipp's usage of "round-trip" only includes the
latency of _communication_ between the app and database in this statement,
and excludes any processing time required by the database.

If you were to interpret "round-trip" from an app-centric perspective (as
in "the time taken to retrieve/commit data") then the statement becomes
misleading because handling the data involves i/o, possibly even
synchronous i/o, which is not "very very cheap" by any standard I'm aware
of :)

-Rowan
___
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

Re: [sqlite] shell edit quoting

[Clemens Ladisch]

David Burgess wrote:
>> Where do the quotes around the value come from?
>
> I typed them. Simgle set of double quotes

I meant the quotes around the entire value returned by the SELECT.

>> Are you using the standard command-line shell, and which output mode?
>
> yes and the default mode

The default mode would not output the column name.

And it works for me:

  sqlite> create table sql_procs(name,sql);
  sqlite> insert into sql_procs (name, sql) values ('a', edit('sql','vim'));
  sqlite> select sql from sql_procs where name = 'a';
  select * from "mytable" ;

  sqlite>


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