What happens if an in memory database runs out of memory

classic Classic list List threaded Threaded
42 messages Options
123
Reply | Threaded
Open this post in threaded view
|

What happens if an in memory database runs out of memory

curmudgeon
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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(100000000));
insert into blobs values(zeroblob(100000000));

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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
In reply to this post by curmudgeon


On 15 Dec 2017, at 4:30pm, x <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Jens Alfke-2
In reply to this post by curmudgeon


> On Dec 15, 2017, at 8:30 AM, x <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Keith Medcalf
In reply to this post by curmudgeon

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-
>[hidden email]] On Behalf Of x
>Sent: Friday, 15 December, 2017 09:31
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Hipp-3
In reply to this post by curmudgeon
On 12/15/17, x <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

curmudgeon
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:[hidden email]>
Sent: 15 December 2017 19:11
To: SQLite mailing list<mailto:[hidden email]>
Subject: Re: [sqlite] What happens if an in memory database runs out of memory

On 12/15/17, x <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Hipp-3
On 12/15/17, x <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

curmudgeon
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 <[hidden email]> on behalf of Richard Hipp <[hidden email]>
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 <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3


On 15 Dec 2017, at 8:36pm, x <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

curmudgeon
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 <[hidden email]> on behalf of Simon Slavin <[hidden email]>
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3


On 18 Dec 2017, at 2:48pm, curmudgeon <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

curmudgeon
In reply to this post by 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

R Smith
In reply to this post by curmudgeon
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

curmudgeon
>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 <[hidden email]> on behalf of R Smith <[hidden email]>
Sent: Monday, December 18, 2017 3:19:31 PM
To: [hidden email]
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
In reply to this post by curmudgeon


On 19 Dec 2017, at 9:33am, curmudgeon <[hidden email]> 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

<https://sqlite.org/tempfiles.html>

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3


On 19 Dec 2017, at 11:40am, Simon Slavin <[hidden email]> wrote:

> See section 3 of
>
> <https://sqlite.org/tempfiles.html>

Sorry.  See section 4 too.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
123