Re: [sqlite] What happens if an in memory database runs out of memory

[Rowan Worth]

No clue sorry. Not familiar with c++ builder or clang.
-Rowan

On 29 December 2017 at 15:05, x  wrote:

> Well spotted Rowan. I still get the same error message though. I’m using
> c++ builder (clang compiler) and in Project | Options | C++ (Shared
> Options) | Conditional defines I've entered the following
>
> SQLITE_EXTRA_INIT=core_init;-DSQLITE_ENABLE_MEMSYS5
>
> I’m unsure how c++ builder presents that on the command line though.
>
> If I compile without the minus sign it builds OK but I presume the minus
> sign is required?
>
> Does that mean anything to you? If it doesn’t I’ll make a post on the c++
> builder forum.
>
>
> From: Rowan Worth<mailto:row...@dug.com>
> Sent: 29 December 2017 03:13
> To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] What happens if an in memory database runs out of
> memory
>
> On 23 December 2017 at 00:17, curmudgeon  wrote:
>
> > >You can run tests yourself by compiling with -DSQLITE_ENABLE_MEMSYS5
> >
> > Is that a minus sign before the DSQLITE_ENABLE_MEMSYSS? If I try
> compiling
> > with a minus sign before that directive I get a compile error "macro
> names
> > must be identifiers ". Compiles OK if I leave out the minus
> > sign.
> >
>
> Yes it's a minus sign, and also it ends in a 5 not an S. Both
> SQLITE_ENABLE_MEMSYS5 and SQLITE_ENABLE_MEMSYSS are valid indentifiers
> though so not sure about the error; best guess is that a funny character
> has somehow crept into your command line.
>
> -Rowan
> ___
> 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] What happens if an in memory database runs out of memory

[x]

Well spotted Rowan. I still get the same error message though. I’m using c++ 
builder (clang compiler) and in Project | Options | C++ (Shared Options) | 
Conditional defines I've entered the following

SQLITE_EXTRA_INIT=core_init;-DSQLITE_ENABLE_MEMSYS5

I’m unsure how c++ builder presents that on the command line though.

If I compile without the minus sign it builds OK but I presume the minus sign 
is required?

Does that mean anything to you? If it doesn’t I’ll make a post on the c++ 
builder forum.


From: Rowan Worth<mailto:row...@dug.com>
Sent: 29 December 2017 03:13
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] What happens if an in memory database runs out of memory

On 23 December 2017 at 00:17, curmudgeon  wrote:

> >You can run tests yourself by compiling with -DSQLITE_ENABLE_MEMSYS5
>
> Is that a minus sign before the DSQLITE_ENABLE_MEMSYSS? If I try compiling
> with a minus sign before that directive I get a compile error "macro names
> must be identifiers ". Compiles OK if I leave out the minus
> sign.
>

Yes it's a minus sign, and also it ends in a 5 not an S. Both
SQLITE_ENABLE_MEMSYS5 and SQLITE_ENABLE_MEMSYSS are valid indentifiers
though so not sure about the error; best guess is that a funny character
has somehow crept into your command line.

-Rowan
___
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] What happens if an in memory database runs out of memory

[Rowan Worth]

On 23 December 2017 at 00:17, curmudgeon  wrote:

> >You can run tests yourself by compiling with -DSQLITE_ENABLE_MEMSYS5
>
> Is that a minus sign before the DSQLITE_ENABLE_MEMSYSS? If I try compiling
> with a minus sign before that directive I get a compile error "macro names
> must be identifiers ". Compiles OK if I leave out the minus
> sign.
>

Yes it's a minus sign, and also it ends in a 5 not an S. Both
SQLITE_ENABLE_MEMSYS5 and SQLITE_ENABLE_MEMSYSS are valid indentifiers
though so not sure about the error; best guess is that a funny character
has somehow crept into your command line.

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

Re: [sqlite] What happens if an in memory database runs out of memory

[curmudgeon]

>You can run tests yourself by compiling with -DSQLITE_ENABLE_MEMSYS5 
and then starting the "sqlite3.exe" command-line shell with the 
"--heap" argument to tell it how much memory to use.  Give it a few 
megabytes.  Then start up your in-memory database and fill it up to 
see what happens. 

Richard suggested this earlier. Can this be done in C rather than the shell?

Is that a minus sign before the DSQLITE_ENABLE_MEMSYSS? If I try compiling
with a minus sign before that directive I get a compile error "macro names
must be identifiers ". Compiles OK if I leave out the minus
sign.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[curmudgeon]

>I would think that a temp file database (created with an empty string) is no
different from a regular disk file resident database EXCEPT that the file is
generated with an random tmpfile name and automatically unlinked when
closed, and that "memory pressure" equates to "page cache is full".  I don't
know if it would use the temp page cache size or the database page cache
size. 


I think it has something to do with persistent versus temp tables. You can't
qualify a temp table e.g. "create temp table mem.Tbl" isn't allowed so it's
either "create temp table Tbl" (standard temp table) or "create table
mem.Tbl" (persistent table within a temp db).



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[Keith Medcalf]

>I thought I had posted this earlier but I don't see it.

>Earlier I said the ideal solution would be something that uses memory
>and defaults to disc if it runs out of memory. In response Richard's
>suggested using a temp database with a blank name as that would use 
>memory but parts of it would be flushed to disc if sqlite came under 
>memory pressure (https://sqlite.org/inmemorydb.html). I tried the 
>large insert in such a db but the performance was only on a par 
>with a temp table with temp_store set as FILE.

I would think that a temp file database (created with an empty string) is no 
different from a regular disk file resident database EXCEPT that the file is 
generated with an random tmpfile name and automatically unlinked when closed, 
and that "memory pressure" equates to "page cache is full".  I don't know if it 
would use the temp page cache size or the database page cache size.

---
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] What happens if an in memory database runs out of memory

[curmudgeon]

I thought I had posted this earlier but I don't see it.

Earlier I said the ideal solution would be something that uses memory and
defaults to disc if it runs out of memory. In response Richard's suggested
using a temp database with a blank name as that would use memory but parts
of it would be flushed to disc if sqlite came under memory pressure
(https://sqlite.org/inmemorydb.html). I tried the large insert in such a db
but the performance was only on a par with a temp table with temp_store set
as FILE. 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[curmudgeon]

Keith / Simon, thanks to both of you for those detailed replies but I'll need
a bit of time to digest them.

It may seem I'm a bit OCD trying to save a few microseconds here and there
but this is to do with a thread I started a while back regarding getting a
list of RowIDs pointing to the query results where the query consists of a
BaseTbl linked to lookup tables. As I said on that thread I consider the
time taken to run a query to be equal to the time taken to compile that list
of RowIDs. Thereafter a page of results can be obtained pretty much
instantaneously by linking the appropriate RowIDs with a subsection of the
original query. In that thread I was storing the RowIDs in a vector but this
thread is about testing storing them in a temp table. It isn't as fast as
the vector but there isn't a great deal of difference until you get into the
millions of records and (I'm hoping) to remove any dependency on RAM.

I have a well known sqlite browser on my laptop. If I view my largest table
(2.4 million recs) in a grid in this browser the top page appears with the
counter showing 'record 1 of at least x' alongside a vertical scrollbar that
doesn't work properly (it can't because it doesn't have the record count).
If I click 'Last record' in the navigation bar it takes almost 3 secs for
the last page to appear. Even then the vertical scrollbar doesn't work
properly. It's all pretty ugly. In contrast I can get a list of all the
RowIDs for that table in approx. 0.6 secs, show an accurate record counter /
vertical scrollbar and navigate to any point in the table in microseconds.
That's in win64 with an SSD based DB but it's still well worth it in win32
with a hard drive.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[Simon Slavin]

On 21 Dec 2017, at 4:34pm, curmudgeon  wrote:

> Put it this way, if I create n temp tables will there be n_+ 1 page caches
> or just the main cache containing a further  n temporary caches?

Try it and see.  But unless you intentionally make us strange numbers the 
problem you’ll run into is not set by SQLite, it’s down to the amount of memory 
your OS is allowing your application to use.  The SQLite settings you use are 
unlikely to have any effect on what actually happens.  This is one of the 
reasons I think you’re wasting your time worrying about this stuff.

> I would've liked to have known if sqlite took care of it
> all for me or if it was down to the OS.

SQLite has no low-level access to your computer memory.  If the documentation 
says it is keeping some data in memory, it means that SQLite allocates that 
memory by asking your OS for it, just the same as if you’d done it yourself in 
C.  And your OS will manage that memory using virtual memory the same as it 
would for any other memory your application uses.  If the operating system 
doesn’t use virtual memory then you’ll get an "out of memory" error.

> All I was able to determine was that setting temp_store = 2 (MEMORY) did
> speed up the queries but I've no idea if using that setting is risky on a
> lower spec pc.

SQLite can’t do anything with its memory that the rest of your program can't 
do.  So if you allocate too much your OS will step in and handle things for 
you, outside of SQLite’s control.

Also you should be aware that virtual memory is a very efficient way of letting 
your computer handle RAM and disk.  By artificially allocating RAM to specific 
resources you are preventing the OS using that RAM in what /it/ thinks is the 
most efficient way.  And generally computers are better at this stuff than 
humans are.

Lastly, don’t confuse your development environment with your runtime 
environment.  The amounts of memory you have free, and the memory your 
application will be allowed to use when it’s in real life use may be totally 
unlike what’s available in your IDE on your dev computer.  So don’t bother 
picking numbers for making things run really fast on your development platform.

The recommendation in this case is to leave the configuration to its default 
settings, leave the journal_mode at its default setting, and just go ahead and 
write your software the way your programming language and SQL suggest to you.  
Rely on the OS and SQLite to manage memory properly, which is usually very 
well.  Once you have a ton of experience about how your program runs in real 
life, /then/ you can start thinking about optimization.

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

Re: [sqlite] What happens if an in memory database runs out of memory

[Keith Medcalf]

>All I was able to determine was that setting temp_store = 2 (MEMORY)
>did speed up the queries but I've no idea if using that setting is risky
>on a lower spec pc.

I "tested" this and setting temp_store to MEMORY is the same as placing the 
database itself in :memory:.  That is, the page_cache and temp_page_cache size 
(as approriate) has zero effect.

That is, the size and memory consumption of both temp objects and objects in 
the database are limited by the ability of system to allocate virtual storage 
and once that is used up, an "OutOfMemory" error occurs with whatever 
consequences may ensure from that thus ensue.

If however the database is a file, or the temp_store is a file, then the 
applicable page cache size specifications provide a best effort limitation 
(note that it is best effort, not a guarantee) on the amount of memory consumed 
in the page_cache for each object type (database or temp).

So yes, if you run with temp_store = 2 on a computer with 4K of RAM you will 
have a limitation of being able to use a maximum of 4K of RAM.  On the other 
hand, if you run on a computer that has RAM in excess of the supported process 
virtual size, then you will keep consuming RAM until the process has allocated 
all the arena that it is allowed by the OS to allocate before that process gets 
an out of memory error.  Whether or not the "process" memory allocator runs out 
of allocable arena before the OS runs out of storage is defined by the OS (and 
the amount of virtual storage actually installed in the computer).

So "risky" is a questionable concept.  Everything is "risky".  I believe what 
you are trying to express is the probability of an out of memory condition, and 
that is an entirely different thing.  If your application is controlling a 
life-and-death process then it is quite likely that your CONSEQUENCE of failure 
is HIGH (immediate death of one of more persons).  

IF you have the temp_store on disk, then the probability of running out of 
memory is equal to the probability of running out of disk space, which is a 
separate question.  On the other hand, if you have temp_store in memory and 
only have 4K of virtual storage, then the probability that you will experience 
out-of-memory is high.  Alternatively, if you are using a computer than has 4 
Petabytes of RAM and you have temp_store in memory AND your software is written 
and running such that it can use all that RAM, then the probability of running 
out of RAM is remote.

Of course, these probabilities do not take into account the probability of 
"slow lingering death of one or more persons" because, since you cannot afford 
actual RAM, you instead use spinning-rust backed "Virtual Storage" which 
introduces "random latency" into your systems.


---
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] What happens if an in memory database runs out of memory

[curmudgeon]

>According to the documentation you quoted, 

>"Each temporary table and index is given its own page cache" 

>every temporary table gets its own cache.  Each cache can grow to a maximum
size of SQLITE_DEFAULT_TEMP_CACHE_SIZE .

Yeah, but it also says

"SQLite uses a page cache of recently read and written database pages. This
page cache is used not just for the main database file but also for
transient indices and tables stored in temporary files"

Put it this way, if I create n temp tables will there be n_+ 1 page caches
or just the main cache containing a further  n temporary caches?

>Be aware that you cannot magically create more RAM in your computer by
defining enough temporary tables. 

I don't know how I managed to give you the impression I ever thought that
Simon. All I was trying to determine was how I could best manage available
memory while hoping sqlite would do it for me. From the tests I've done
increasing the cache_size or SQLITE_DEFAULT_TEMP_CACHE_SIZE has made no
difference yet the default sizes are hopelessly inadequate to hold a temp
table of that size. I would've liked to have known if sqlite took care of it
all for me or if it was down to the OS.

All I was able to determine was that setting temp_store = 2 (MEMORY) did
speed up the queries but I've no idea if using that setting is risky on a
lower spec pc.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[Simon Slavin]

On 21 Dec 2017, at 2:15pm, curmudgeon  wrote:

> Can someone tell me, if I create a temporary table does is its 'separate
> cache' created within the cache_size cache or is it completely separate from
> that?

According to the documentation you quoted,

"Each temporary table and index is given its own page cache"

every temporary table gets its own cache.  Each cache can grow to a maximum 
size of SQLITE_DEFAULT_TEMP_CACHE_SIZE .

Be aware that you cannot magically create more RAM in your computer by defining 
enough temporary tables.

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

Re: [sqlite] What happens if an in memory database runs out of memory

[curmudgeon]

*"SQLite uses a page cache of recently read and written database pages. This
page cache is used not just for the main database file but also for
transient indices and tables stored in temporary files. If SQLite needs to
use a temporary index or table and the SQLITE_TEMP_STORE compile-time
parameter and the temp_store pragma are set to store temporary tables and
index on disk, the information is still initially stored in memory in the
page cache. The temporary file is not opened and the information is not
truly written to disk until the page cache is full. 
This means that for many common cases where the temporary tables and indices
are small (small enough to fit into the page cache) no temporary files are
created and no disk I/O occurs. Only when the temporary data becomes too
large to fit in RAM does the information spill to disk. 
Each temporary table and index is given its own page cache which can store a
maximum number of database pages determined by the
SQLITE_DEFAULT_TEMP_CACHE_SIZE compile-time parameter. (The default value is
500 pages.) The maximum number of database pages in the page cache is the
same for every temporary table and index. The value cannot be changed at
run-time or on a per-table or per-index basis. Each temporary file gets its
own private page cache with its own SQLITE_DEFAULT_TEMP_CACHE_SIZE page
limit." *

Can someone tell me, if I create a temporary table does is its 'separate
cache' created within the cache_size cache or is it completely separate from
that?




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[x]

Just seen this

Each temporary table and index is given its own page cache which can store a 
maximum number of database pages determined by the 
SQLITE_DEFAULT_TEMP_CACHE_SIZE compile-time parameter. (The default value is 
500 pages.) The maximum number of database pages in the page cache is the same 
for every temporary table and index. The value cannot be changed at run-time or 
on a per-table or per-index basis. Each temporary file gets its own private 
page cache with its own SQLITE_DEFAULT_TEMP_CACHE_SIZE page limit.

That suggests the cache for the temp table isn’t affected by setting cache_size 
at runtime and so it would seem all the above tests were run with the temp 
table cache having a default size of 500 KB. I can only guess the slower 
results were down to some work sqlite had to do within the ordinary cache and 
that was slowed down when cache_size was set to less than -500. I don’t know 
though.

I ran the tests again using all the sqlite default settings so that temp_store 
would be 1 (i.e. FILE). The results were slower but not by much. For the temp 
table with 112 million records it slowed down by ~12%. For a temp table with 
2.4 million records it was slower by more like 33% but negligible in real time.

I also tried running the test involving the temp table with 112 million records 
in win32 with temp_store = 2 (MEMORY) to see if it would crash due to the 
restricted memory but it didn’t. It was around 12% faster than when temp_store 
= 1. I even tried setting the SQLITE_DEFAULT_TEMP_CACH_SIZE to 5 pages and then 
0 pages but it made no difference to the results.

For one last win64 test I tried setting temp_store = 2 with 
SQLITE_DEFAULT_TEMP_CACHE = 1,000,000 ( giving a temp table cache size of 1 GB) 
but it didn’t speed it up any.

Given the TEMP_CACHE_SIZE was inadequate for the temp table in all the tests I 
wonder why the SQLITE_TEMP_STORE default isn’t 2 (MEMORY) as it does seem to 
give speed gains of between 12 & 33% yet didn’t crash due to the inadequate 
size of the temp cache. As Simon said though, the OS mode of operation muddies 
the water.

Q. So, after all that what’s the conclusion Tom?

A. I don’t know Tom. ☹

___
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] What happens if an in memory database runs out of memory

[Keith Medcalf]

>>Until the underlying system memory allocator fails and then it go
>boom.  How
>much goes boom is OS dependent.  Some OSes will only allow the errant
>process go boom.  Others (such as those from Microsoft) the entire OS
>go
>boom if the out of memory condition encompases the entire V=V address
>space.
>
>
>Thanks Keith. So sqlite does look to increase the temp table's cache
>size if 
>it's not big enough? Looking at the results I posted earlier, why did
>that
>not happen until cache_size was set to -500? Why didn't sqlite just
>increased the cache_size from -400 to the required memory? Mind you,
>I'm
>assuming the test results for cache_size <= -400 were slower because
>sqlite
>resorted to HD but maybe that's not the case.

Actually I think it is dependant on exactly what you are doing.  If you are 
using a :memory: database, it will continue to grow until all memory is 
consumed.  I think for temp space, however, if you have set "memory only" then 
I think it will fail allocation when the temp size is used up (but I could be 
wrong here -- I have never actually run into that issue).  However, if you use 
disk-backed storage (ie, a db file or temp-files, then the memory cache will 
spill to disk when it is "full" which will generally (hopefully) happen long 
before you have run out of virtual memory.

I've actually ever only run out of system-wide virtual memory a few times in 
the last several years.  Memory is cheap and so I have huge page and temp sizes 
set by default and lots of memory, and third-level page redirection disabled 
(ie, no non-RAM backing store).  The most troubling thing is that one gets used 
to have NVMe drives that can I/O in excess of 2 GB/s which creates a real 
problem moving to machines that do I/O through a pinhole.  Those things can 
usually be fixed though without too much difficulty (usually by using more RAM 
so as to not do I/O in excess of the pinhole capabilities).




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

Re: [sqlite] What happens if an in memory database runs out of memory

[Simon Slavin]

On 19 Dec 2017, at 9:22pm, curmudgeon  wrote:

> Thanks Keith. So sqlite does look to increase the temp table's cache size if
> it's not big enough?

No.  Memory allocated to that cache is memory which can’t be used by anything 
else.  There’s probably a far better use for that memory than using it for one 
obscure SQLite table.

If the table gets so big it can’t all fit into the cache allocated to it, it’s 
written to disk instead.  Of course, it’s written to disk using OS calls, so 
there’s a chance that the OS will decide that that data should be cached.

Your ideas portray a world where a computer really has infinite RAM, if only it 
would just allocate it.

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

Re: [sqlite] What happens if an in memory database runs out of memory

[curmudgeon]

>Until the underlying system memory allocator fails and then it go boom.  How
much goes boom is OS dependent.  Some OSes will only allow the errant
process go boom.  Others (such as those from Microsoft) the entire OS go
boom if the out of memory condition encompases the entire V=V address space. 


Thanks Keith. So sqlite does look to increase the temp table's cache size if
it's not big enough? Looking at the results I posted earlier, why did that
not happen until cache_size was set to -500? Why didn't sqlite just
increased the cache_size from -400 to the required memory? Mind you, I'm
assuming the test results for cache_size <= -400 were slower because sqlite
resorted to HD but maybe that's not the case.
 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[Keith Medcalf]

Until the underlying system memory allocator fails and then it go boom.  How 
much goes boom is OS dependent.  Some OSes will only allow the errant process 
go boom.  Others (such as those from Microsoft) the entire OS go boom if the 
out of memory condition encompases the entire V=V address space.

---
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 Simon Slavin
>Sent: Tuesday, 19 December, 2017 11:12
>To: SQLite mailing list
>Subject: Re: [sqlite] What happens if an in memory database runs out
>of memory
>
>
>
>On 19 Dec 2017, at 6:01pm, curmudgeon  wrote:
>
>> 1) I read in those links that each temp table is given (by default)
>its own
>> page cache of 500 pages. Is this a separately created page cache or
>is it
>> 500 pages from THEE page chache? If it's the latter that will
>explain the
>> slowdown for cache_size < -500. Or does the cache_size pragma
>dictate the
>> size of the separately created page cache?
>
>Each temporary table gets its own cache.
>
>It works like this: Each attached file gets its own page cache, with
>a size governed by SQLITE_DEFAULT_CACHE_SIZE and/or "PRAGMA
>cache_size" .  Each temporary table declared is considered to be a
>separate attached file.
>
>> 3) Similar to the original question, if you set temp_store = 2
>(memory) and
>> there isn't enough memory for the table what happens?
>
>By 'memory' that web page is referring to whatever your operating
>system thinks is memory.  So the same thing happens as would happen
>to any application which tries to use a lot of memory: the virtual
>memory mechanism kicks in and memory is swapped to and from disk.
>
>Simon.
>___
>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] What happens if an in memory database runs out of memory

[curmudgeon]

>> 3) Similar to the original question, if you set temp_store = 2 (memory)
and 
>> there isn't enough memory for the table what happens? 

>By 'memory' that web page is referring to whatever your operating system
thinks is memory.  So the >same thing happens as would happen to any
application which tries to use a lot of memory: the virtual >memory
mechanism kicks in and memory is swapped to and from disk.

Thanks Simon but is that to say sqlite attempts to increase the temp table's
page cache size beyond the size it was originally allotted? If the answer to
that is yes then that also answers question 2).  




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[Simon Slavin]

On 19 Dec 2017, at 6:01pm, curmudgeon  wrote:

> 1) I read in those links that each temp table is given (by default) its own
> page cache of 500 pages. Is this a separately created page cache or is it
> 500 pages from THEE page chache? If it's the latter that will explain the
> slowdown for cache_size < -500. Or does the cache_size pragma dictate the
> size of the separately created page cache?

Each temporary table gets its own cache.

It works like this: Each attached file gets its own page cache, with a size 
governed by SQLITE_DEFAULT_CACHE_SIZE and/or "PRAGMA cache_size" .  Each 
temporary table declared is considered to be a separate attached file.

> 3) Similar to the original question, if you set temp_store = 2 (memory) and
> there isn't enough memory for the table what happens?

By 'memory' that web page is referring to whatever your operating system thinks 
is memory.  So the same thing happens as would happen to any application which 
tries to use a lot of memory: the virtual memory mechanism kicks in and memory 
is swapped to and from disk.

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

Re: [sqlite] What happens if an in memory database runs out of memory

[curmudgeon]

Thanks Simon/Gunter. I thought those sections cleared things up until I tried
a few tests.
I opened the DB, set temp_store to STORE, cache_size to CACHE and then
calculated the average secs taken (over 2 runs) to run the following

[Tbl has integer primary key ID, contains 10,570 records & is cross joined
to itself to create a (pointless and not distinct) large insert of
111,724,900 recs]

create temp table Key (ID int);
INSERT = secs to run 
insert into Key select ID from Tbl cross join Tbl using (ID);
DELETE = secs to run
delete from Key;

The results for different values of temp_store and cache_size were as
follows  

STORE, CACHE,   INSERT, DELETE
0, 0,   23.00, 1.87
2, 0,   21.10, 1.27
2, -8, 20.36, 1.30
2, -80, 20.61, 1.30
2, -200, 20.70, 1.30
2, -300, 20.72, 1.30
2, -400, 20.70, 1.30
2, -500, 13.58, 1.29
2, -800, 13.48, 1.29
2, -8000, 13.52,1.28
2, -800, 13.64, 1.29

At cache_size = -500 the timings come more into line with the memory results
from my old tests. Some things I need cleared up

1) I read in those links that each temp table is given (by default) its own
page cache of 500 pages. Is this a separately created page cache or is it
500 pages from THEE page chache? If it's the latter that will explain the
slowdown for cache_size < -500. Or does the cache_size pragma dictate the
size of the separately created page cache?

2) My DB page size is 1024. If the temp table is allocated a separate page
cache of 500 * 1024 bytes this means that sqlite managed to store
111,724,900 records in 500 KB? That's an average of 0.0046 bytes per record? 

3) Similar to the original question, if you set temp_store = 2 (memory) and
there isn't enough memory for the table what happens? 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[Simon Slavin]

On 19 Dec 2017, at 11:40am, Simon Slavin  wrote:

> See section 3 of
> 
> 

Sorry.  See section 4 too.

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

Re: [sqlite] What happens if an in memory database runs out of memory

[Simon Slavin]

On 19 Dec 2017, at 9:33am, curmudgeon  wrote:

> Running the same tests again using
> an actual table (TEMP keyword omitted) shows the in memory INSERT (and
> DELETE) to be twice as fast on the database loaded into memory compared to
> when it's accessed from the SSD.The timings in the original tests were
> similar to the in memory database suggesting the TEMP table for those tests
> were created in memory regardless of whether the actual database was loaded
> in memory or not. I can't see any documentation suggesting that though.
> Could someone clarify?

See section 3 of



A working assumption is that temporary files are created in memory and only if 
they get big enough to trouble virtual memory do they require disk access.

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

Re: [sqlite] What happens if an in memory database runs out of memory

[curmudgeon]

My apologies. The inserts in the above tests were made on a TEMP table which
I assumed was being created in the existing database. I've since discovered
that isn't the case.

*Tables created using the "CREATE TEMP TABLE" syntax are only visible to the
database connection in which the "CREATE TEMP TABLE" statement is originally
evaluated. These TEMP tables, together with any 
associated indices, triggers, and views, are collectively stored in a
separate temporary database file that is created as soon as the first
"CREATE TEMP TABLE" statement is seen.*

Running the same tests again using an actual table (TEMP keyword omitted)
shows the in memory INSERT (and DELETE) to be twice as fast on the database
loaded into memory compared to when it's accessed from the SSD.The timings
in the original tests were similar to the in memory database suggesting the
TEMP table for those tests were created in memory regardless of whether the
actual database was loaded in memory or not. I can't see any documentation
suggesting that though. Could someone clarify? 





--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[curmudgeon]

My apologies. The inserts in the above tests were made on a TEMP table which
I assumed was being created in the existing database. I've since discovered
that isn't the case.*Tables created using the "CREATE TEMP TABLE" syntax are
only visible to the database connection in which the "CREATE TEMP TABLE"
statement is originally evaluated. These TEMP tables, together with any
associated indices, triggers, and views, are collectively stored in a
separate temporary database file that is created as soon as the first
"CREATE TEMP TABLE" statement is seen. *Running the same tests again using
an actual table (TEMP keyword omitted) shows the in memory INSERT (and
DELETE) to be twice as fast on the database loaded into memory compared to
when it's accessed from the SSD.The timings in the original tests were
similar to the in memory database suggesting the TEMP table for those tests
were created in memory regardless of whether the actual database was loaded
in memory or not. I can't see any documentation suggesting that though.
Could someone clarify?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[x]

>To get clarity, are you saying the 33% speedup is the gain of the
>non-Indexed vs. Indexed table, or due to setting that cache size on the
>already in-memory DB?  (The latter would be worrying).


Ryan, It seemed unaffected by cache size. The slight gain in speed (~0.2 secs) 
for the 2 million inserts was the result of having the full database in memory 
versus SSD based. There was almost no difference for the 111 million inserts.




From: sqlite-users  on behalf of 
R Smith 
Sent: Monday, December 18, 2017 3:19:31 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] What happens if an in memory database runs out of memory

A big cache on an in-memory DB is a bit like insisting to sit inside a
row-boat while on a big ship. It has zero effect in helping you float
better - it's probably slightly worse even, considering the cache
computation cycles could have been avoided.

To get clarity, are you saying the 33% speedup is the gain of the
non-Indexed vs. Indexed table, or due to setting that cache size on the
already in-memory DB?  (The latter would be worrying).


On 2017/12/18 4:48 PM, curmudgeon wrote:
> You're definitely right about me wasting my time Simon. I loaded my entire
> database (1 GB) into memory and set cache_size = -8192 for an 8 GB cache
> size (using win64). I then ran my test (inserting the results of a query,
> returning 111 million bigints, into a non-indexed single column table) and
> there was no real difference. For lesser inserts (2 million) the speedup was
> around 33% but would hardly be noticeable to the end user.
>
> I daresay it would've been more noticeable if my laptop had a hard drive but
> the moral of the story is get yourself an SSD and leave sqlite to take care
> of the hard stuff.
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> 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] What happens if an in memory database runs out of memory

[R Smith]

A big cache on an in-memory DB is a bit like insisting to sit inside a 
row-boat while on a big ship. It has zero effect in helping you float 
better - it's probably slightly worse even, considering the cache 
computation cycles could have been avoided.


To get clarity, are you saying the 33% speedup is the gain of the 
non-Indexed vs. Indexed table, or due to setting that cache size on the 
already in-memory DB?  (The latter would be worrying).



On 2017/12/18 4:48 PM, curmudgeon wrote:

You're definitely right about me wasting my time Simon. I loaded my entire
database (1 GB) into memory and set cache_size = -8192 for an 8 GB cache
size (using win64). I then ran my test (inserting the results of a query,
returning 111 million bigints, into a non-indexed single column table) and
there was no real difference. For lesser inserts (2 million) the speedup was
around 33% but would hardly be noticeable to the end user.

I daresay it would've been more noticeable if my laptop had a hard drive but
the moral of the story is get yourself an SSD and leave sqlite to take care
of the hard stuff.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
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] What happens if an in memory database runs out of memory

[curmudgeon]

Sorry cache_size should be -8,000,000. It didn't make any difference to the
results or conclusion though. 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[Simon Slavin]

On 18 Dec 2017, at 2:48pm, curmudgeon  wrote:

> You're definitely right about me wasting my time Simon. I loaded my entire
> database (1 GB) into memory and set cache_size = -8192 for an 8 GB cache
> size (using win64). I then ran my test (inserting the results of a query,
> returning 111 million bigints, into a non-indexed single column table) and
> there was no real difference. For lesser inserts (2 million) the speedup was
> around 33% but would hardly be noticeable to the end user.

I’m glad you figured it out.  Speed optimization for random hardware is hard 
and I’m glad I don’t have to do it.

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

Re: [sqlite] What happens if an in memory database runs out of memory

[curmudgeon]

You're definitely right about me wasting my time Simon. I loaded my entire
database (1 GB) into memory and set cache_size = -8192 for an 8 GB cache
size (using win64). I then ran my test (inserting the results of a query,
returning 111 million bigints, into a non-indexed single column table) and
there was no real difference. For lesser inserts (2 million) the speedup was
around 33% but would hardly be noticeable to the end user.

I daresay it would've been more noticeable if my laptop had a hard drive but
the moral of the story is get yourself an SSD and leave sqlite to take care
of the hard stuff.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[x]

I’m with you Simon but the program I’m working on will probably only ever be 
used by me. I just like to try and do things properly as a means of learning. 
I’m using win64 with 16GB RAM and 512GB SSD so everything’s already fast. I 
only tested in win32 so I’d run out of memory a lot quicker.




From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Friday, December 15, 2017 8:50:40 PM
To: SQLite mailing list
Subject: Re: [sqlite] What happens if an in memory database runs out of memory



On 15 Dec 2017, at 8:36pm, x  wrote:

> I’ll have to look into how to increase the in-memory pager cache.

Before you do anything like that, ask yourself two questions:

a) Is my program actually fast enough without any of these weird picky measures 
?  Or am I spending lots of time learning details of SQLite when I could be 
spending it improving my program’s functions ?

b) Is the environment I’m developing under identical to those my program is 
going to work under ?  You’re running under win32.  Are all your users going to 
run under win32 or some going to be running win64 ?  The two don’t do the same 
things at the same point.  Don’t do lots of work and then have to tell people 
"My program doesn’t work on your computer because you’re running 64-bit Windows 
10.".

Simon.
___
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] What happens if an in memory database runs out of memory

[Simon Slavin]

On 15 Dec 2017, at 8:36pm, x  wrote:

> I’ll have to look into how to increase the in-memory pager cache.

Before you do anything like that, ask yourself two questions:

a) Is my program actually fast enough without any of these weird picky measures 
?  Or am I spending lots of time learning details of SQLite when I could be 
spending it improving my program’s functions ?

b) Is the environment I’m developing under identical to those my program is 
going to work under ?  You’re running under win32.  Are all your users going to 
run under win32 or some going to be running win64 ?  The two don’t do the same 
things at the same point.  Don’t do lots of work and then have to tell people 
"My program doesn’t work on your computer because you’re running 64-bit Windows 
10.".

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

Re: [sqlite] What happens if an in memory database runs out of memory

[x]

Thanks Richard. I take it this is what you’re talking about



“Even though a disk file is allocated for each temporary database, in practice 
the temporary database usually resides in the in-memory pager cache and hence 
there is very little difference between a pure in-memory database created by 
":memory:" and a temporary database created by an empty filename. The only 
difference is that a ":memory:" database must remain in memory at all times 
whereas parts of a temporary database might be flushed to disk if database 
becomes large or if SQLite comes under memory pressure.”



I’ll have to look into how to increase the in-memory pager cache.




From: sqlite-users  on behalf of 
Richard Hipp 
Sent: Friday, December 15, 2017 7:35:14 PM
To: SQLite mailing list
Subject: Re: [sqlite] What happens if an in memory database runs out of memory

On 12/15/17, x  wrote:
>
> Is there any easy way of creating a table that will use mem for speed but
> revert to disc for backup if memory runs out?
>

Make the database filename be an empty string.

--
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[Richard Hipp]

On 12/15/17, x  wrote:
>
> Is there any easy way of creating a table that will use mem for speed but
> revert to disc for backup if memory runs out?
>

Make the database filename be an empty string.

-- 
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] What happens if an in memory database runs out of memory

[x]

Thanks for all the replies. I’ve just finished a test on win32.

Firstly I tried appending a int64_t to a vector until I got an exception. This 
happened at i = 60,540,698.

I then created a table in mem with a single integer column and appended 
values(2<<62) until I got an exception. This happened (‘out of memory’) at i = 
102,855,613 although it should be remembered sqlite would be appending a RowID 
as well as the int64_t value.

Is there any easy way of creating a table that will use mem for speed but 
revert to disc for backup if memory runs out?

From: Richard Hipp<mailto:d...@sqlite.org>
Sent: 15 December 2017 19:11
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] What happens if an in memory database runs out of memory

On 12/15/17, x  wrote:
> Suppose I execute “attach :memory: as mem” and then create a table in mem
> that requires more space than the available RAM can hold what will happen?

You will get an SQLITE_NOMEM error from SQLite.  This is well-tested behavior.

You can run tests yourself by compiling with -DSQLITE_ENABLE_MEMSYS5
and then starting the "sqlite3.exe" command-line shell with the
"--heap" argument to tell it how much memory to use.  Give it a few
megabytes.  Then start up your in-memory database and fill it up to
see what happens.

sqlite3 --heap 5MB

The --heap option causes the shell to do a single 5MB memory
allocation and then divy up that one allocation for all its memory
needs.  When the 5MB is gone, SQLite is out of memory and will start
reporting SQLITE_NOMEM errors.
--
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

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

Re: [sqlite] What happens if an in memory database runs out of memory

[Richard Hipp]

On 12/15/17, x  wrote:
> Suppose I execute “attach :memory: as mem” and then create a table in mem
> that requires more space than the available RAM can hold what will happen?

You will get an SQLITE_NOMEM error from SQLite.  This is well-tested behavior.

You can run tests yourself by compiling with -DSQLITE_ENABLE_MEMSYS5
and then starting the "sqlite3.exe" command-line shell with the
"--heap" argument to tell it how much memory to use.  Give it a few
megabytes.  Then start up your in-memory database and fill it up to
see what happens.

sqlite3 --heap 5MB

The --heap option causes the shell to do a single 5MB memory
allocation and then divy up that one allocation for all its memory
needs.  When the 5MB is gone, SQLite is out of memory and will start
reporting SQLITE_NOMEM errors.
-- 
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] What happens if an in memory database runs out of memory

[Keith Medcalf]

You will get an "Out of Memory" error, since you have run out of V=V memory to 
allocate ...

Depending on the vagaries of the OS, all sorts of other hell may break loose as 
well, if your "process" out-of-memory condition corresponds to a general OS 
out-of-memory condition.  That is to say that if your process has consumed all 
V=V memory in the system and there is no V=V memory left for allocation by 
*ANY* process or the OS itself, then AHWBL.  (All Hell Will Break Loose).  This 
is more typical of OS's of ill-conceived design (such as those from Microsoft). 
 OSes that are of well conceived design (basically everything else) generally 
do not suffer the same ill effects.

If only the "process" has hit its quota or V=V allocation limit, then only that 
process will be notified of the failure when it tries to allocate more memory, 
and the OS itself and other processes will be unafected since (presumably) 
there is still V=V memory available in the system -- you are just not allowed 
to have at it.

---
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 x
>Sent: Friday, 15 December, 2017 09:31
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] What happens if an in memory database runs out of
>memory
>
>Suppose I execute “attach :memory: as mem” and then create a table in
>mem that requires more space than the available RAM can hold what
>will happen?
>___
>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] What happens if an in memory database runs out of memory

[Jens Alfke]

> On Dec 15, 2017, at 8:30 AM, x  wrote:
> 
> Suppose I execute “attach :memory: as mem” and then create a table in mem 
> that requires more space than the available RAM can hold what will happen?

On most operating systems, the kernel will use virtual memory, so part of the 
table’s address space will get swapped out to disk. Everything will work the 
same, just slower. Beyond that, if the size starts to get ridiculous, the 
effects vary by OS. The OS might kill your process for using too much memory 
(Linux), or when the swap fills up the disk the OS may suspend your process and 
put up alerts telling you space is low (Mac OS).

Mobile OS’s are different. I can’t speak for Android, but iOS does not use 
backing store / swap space. If your process uses too much RAM the OS will first 
send it a warning notification by IPC telling it to free memory, and if that 
doesn’t help it will kill the process.

More relevant to SQLite: if you’re creating a very large database, I don’t 
think it makes sense to create it in-memory, for the above reasons. Put it in 
some temporary directory instead, and delete it when you’re done with it.

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

Re: [sqlite] What happens if an in memory database runs out of memory

[Simon Slavin]

On 15 Dec 2017, at 4:30pm, x  wrote:

> Suppose I execute “attach :memory: as mem” and then create a table in mem 
> that requires more space than the available RAM can hold what will happen?

The problem would not happen when you create the table.  SQLite reserves only a 
little space for each table.  It is far more likely to happen when you add a 
new row to an existing table.

SQLite relies on your OS to implement memory handling.  If your operating 
system implements virtual memory then it’s used.  If not you get either 
SQLITE_FULL or SQLITE_NOMEM depending on exactly what SQLite is doing when it 
runs out of space.

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

Re: [sqlite] What happens if an in memory database runs out of memory

[Paul Sanderson]

Try it

create a table and use the zeroblob(n) function to insert lots of blobs of
size n

ie
create table blobs (b blob);

insert into blobs values(zeroblob(1));
insert into blobs values(zeroblob(1));

etc.

interestingly the max blob size is specified as 2147483647 but on my
current test client 3.18.0

insert into blobs values(zeroblob(2147483647));

fails wih string or blob too big. Not had time to investigate :(



Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 15 December 2017 at 16:30, x  wrote:

> Suppose I execute “attach :memory: as mem” and then create a table in mem
> that requires more space than the available RAM can hold what will happen?
> ___
> 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] What happens if an in memory database runs out of memory

[x]

Suppose I execute “attach :memory: as mem” and then create a table in mem that 
requires more space than the available RAM can hold what will happen?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users