Size of memory allocated to a SQLite in-memory database

Question

If a create a in-memory sqlite database using syntax below, then what is the size of maximum memory allocated to it?

my $dbh = DBI->connect('dbi:SQLite:dbname=:memory:');

What will happen if size of in-memory database becomes greater than max available memory.

Suppose I have 1 GB free and the database size become 1.1 GB, then will some data be written to disk or what?

Is there a way to change the value of max-memory allocated to a in-memory sqlite database?

Answer 1

It will use virtual memory, yes it will write out to the disk once you've exceeded all your freely available memory, when that's exceeded, it may throw an exception, have an undefined behavior, or it may be OS dependent. These are SQLite limits: http://www.sqlite.org/limits.html.

If you want to set a maximum database size you can execute the following SQL command:

PRAGMA page_size = 512
PRAGMA max_page_count = 195313

In Perl:

$dbh = DBI->connect('dbi:SQLite:dbname=:memory:');
$query_handle = $dbh->prepare("PRAGMA page_size = 512");
$query_handle->execute();
$query_handle = $dbh->prepare("PRAGMA max_page_count = 195313");
$query_handle->execute();

You can test it by creating a temporary table and using a loop to insert a large number of objects, until it hits the limit.

This will set your max database size to 100,000,256 bytes.

SQlite database are stored in pages, this will set the page size and the max number of pages for your database. you can play with either parameter to suit your needs; bigger pages means more performance, but faster growth.

These are all the PRAGMAS that sqlite supports: http://www.sqlite.org/pragma.html#pragma_page_size.

This is a simple test in Python:

import sqlite3

con = sqlite3.connect(':memory:')
cur = con.cursor() 
cur.execute("PRAGMA max_page_count = 195313")
cur.execute("PRAGMA page_size = 512")
cur.execute("CREATE TABLE test (random_values TEXT)")
index = 0
query = "INSERT INTO test (random_values) VALUES ('%s')" % "".join(['a' for index in xrange(1000)])
while True:
    try:
        c = cur.execute(query)
        index += 1
    except Exception as ex:
        print str(ex)  
        break       

print index * 1000

Fairly quickly you'll get something like this:

sqlite3.OperationalError: database or disk is full

I get 93915000 bytes due to the overhead, so play with the settings if you want exactly 100 megabytes.