What happens if an in memory database runs out of memory

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

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

Hick Gunter
Pragma temp_store and the preprocessor macro SQLITE_TEMP_STORE determine where temp tables and indices are stored.

See http://sqlite.org/pragma.html#pragma_temp_store


-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von curmudgeon
Gesendet: Dienstag, 19. Dezember 2017 11:35
An: [hidden email]
Betreff: [EXTERNAL] Re: [sqlite] What happens if an in memory database runs out of memory

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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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 Simon Slavin-3
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, -8000000, 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
[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 6:01pm, curmudgeon <[hidden email]> 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
[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
>> 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
[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 Simon Slavin-3

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-
>[hidden email]] 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 <[hidden email]> 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
>[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
>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
[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 9:22pm, curmudgeon <[hidden email]> 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
[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
>>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
[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 Simon Slavin-3
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
[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
*"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
[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 21 Dec 2017, at 2:15pm, curmudgeon <[hidden email]> 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
[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
>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
[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

>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
[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 21 Dec 2017, at 4:34pm, curmudgeon <[hidden email]> 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
[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
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
[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 Simon Slavin-3
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
[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

>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
[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 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
[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 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 <command line>". Compiles OK if I leave out the minus
sign.




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

Rowan Worth-2
On 23 December 2017 at 00:17, curmudgeon <[hidden email]> 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 <command line>". 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
123