分配给SQLite内存数据库的内存大小 [英] Size of memory allocated to a SQLite in-memory database

问题描述

如果使用下面的语法创建一个内存中的sqlite数据库，那么分配给它的最大内存大小是多少？

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.

假设我有1 GB的可用空间，并且数据库大小变为1.1 GB，那么将一些数据写入磁盘还是什么？

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

是否可以更改分配给内存sqlite数据库的max-memory值？

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

推荐答案

它将使用虚拟内存，是的，一旦超出所有可用内存，它就会写出到磁盘，超过该数量时，它可能会引发异常，具有未定义的行为或取决于操作系统。
以下是SQLite的限制： http://www.sqlite.org/limits.html。

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.

如果要设置最大数据库大小，可以执行以下SQL命令：

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

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

这会将您的最大数据库大小设置为100,000,256字节。

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

SQlite数据库是存储在页面中，这将设置数据库的页面大小和最大页面数。您可以使用任一参数来满足自己的需求；更大的页面意味着更高的性能，但增长更快。

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.

这些都是sqlite支持的PRAGMAS： http://www.sqlite.org/pragma.html#pragma_page_size 。

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

这是一个简单的Python测试：

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：数据库或磁盘已满

sqlite3.OperationalError: database or disk is full

我得到 93915000 个字节（由于开销），因此，如果要恰好为100兆字节，请使用设置。

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

这篇关于分配给SQLite内存数据库的内存大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！