如何减少 SQLite 内存消耗? [英] How to reduce SQLite memory consumption?

问题描述

我正在寻找减少应用程序中 SQLite3 内存消耗的方法.

I'm looking for ways to reduce memory consumption by SQLite3 in my application.

每次执行时，它都会创建一个具有以下架构的表:

At each execution it creates a table with the following schema:

(main TEXT NOT NULL PRIMARY KEY UNIQUE, count INTEGER DEFAULT 0)

此后，数据库每秒填充 50k 次操作.只写.

After that, the database is filled with 50k operations per second. Write only.

当一个项目已经存在时，它使用更新查询更新计数"(我认为这称为 UPSERT).这些是我的疑问:

When an item already exists, it updates "count" using an update query (I think this is called UPSERT). These are my queries:

INSERT OR IGNORE INTO table (main) VALUES (@SEQ);
UPDATE tables SET count=count+1 WHERE main = @SEQ;

通过这种方式，每个事务有 500 万次操作，我可以非常快速地写入数据库.

This way, with 5 million operations per transaction, I can write really fast to the DB.

我并不真正关心这个问题的磁盘空间，但我的 RAM 空间非常有限.因此，我不能浪费太多内存.

I don't really care about disk space for this problem, but I have a very limited RAM space. Thus, I can't waste too much memory.

sqlite3_user_memory() 通知其内存消耗在执行期间增长到近 3GB.如果我通过 sqlite3_soft_heap_limit64() 将其限制为 2GB，当达到 2GB 时，数据库操作的性能几乎为零.

sqlite3_user_memory() informs that its memory consumption grows to almost 3GB during the execution. If I limit it to 2GB through sqlite3_soft_heap_limit64(), database operations' performance drops to almost zero when reaching 2GB.

我不得不将缓存大小提高到 1M(默认页面大小)才能达到理想的性能.

I had to raise cache size to 1M (page size is default) to reach a desirable performance.

我可以做些什么来减少内存消耗?

What can I do to reduce memory consumption?

推荐答案

我会:

• 准备陈述(如果你还没有这样做)
• 降低每笔交易的 INSERT 数量(10 秒 = 500,000 听起来合适)
• 如果可以，请使用 PRAGMA locked_mode = EXCLUSIVE;

此外，(我不确定您是否知道)PRAGMA cache_size 以页为单位，而不是以 MB 为单位.确保将目标内存定义为 PRAGMA cache_size * PRAGMA page_size 或在 SQLite >= 3.7.10 中，您也可以执行 PRAGMA cache_size = -kibibytes;.将其设置为 1 M(illion) 将导致 1 或 2 GB.

Also, (I'm not sure if you know) the PRAGMA cache_size is in pages, not in MBs. Make sure you define your target memory in as PRAGMA cache_size * PRAGMA page_size or in SQLite >= 3.7.10 you can also do PRAGMA cache_size = -kibibytes;. Setting it to 1 M(illion) would result in 1 or 2 GB.

我很好奇 cache_size 如何帮助 INSERT...

I'm curious how cache_size helps in INSERTs though...

如果 PRAGMA temp_store = FILE; 有所作为，您也可以尝试进行基准测试.

You can also try and benchmark if the PRAGMA temp_store = FILE; makes a difference.

当然，只要您的数据库没有被写入:

And of course, whenever your database is not being written to:

• PRAGMA shrink_memory;
• VACUUM;

根据您对数据库的处理方式，这些可能也有帮助:

Depending on what you're doing with the database, these might also help:

• PRAGMA auto_vacuum = 1|2;
• PRAGMA secure_delete = ON;

我使用以下编译指示运行了一些测试:

I ran some tests with the following pragmas:

busy_timeout=0;
cache_size=8192;
encoding="UTF-8";
foreign_keys=ON;
journal_mode=WAL;
legacy_file_format=OFF;
synchronous=NORMAL;
temp_store=MEMORY;

测试 #1:

INSERT OR IGNORE INTO test (time) VALUES (?);
UPDATE test SET count = count + 1 WHERE time = ?;

每秒更新约 109k 次.

Peaked ~109k updates per second.

REPLACE INTO test (time, count) VALUES
(?, coalesce((SELECT count FROM test WHERE time = ? LIMIT 1) + 1, 1));

峰值约为每秒 120k 次更新.

Peaked at ~120k updates per second.

我也尝试过 PRAGMA temp_store = FILE; 并且更新速度下降了大约每秒 1-2k.

I also tried PRAGMA temp_store = FILE; and the updates dropped by ~1-2k per second.

对于事务中 7M 的更新，journal_mode=WAL 比其他所有更新都慢.

For 7M updates in a transaction, the journal_mode=WAL is slower than all the others.

我用 35,839,987 条记录填充了一个数据库，现在我的设置每批 65521 次更新需要近 4 秒 - 但是，它甚至没有达到 16 MB 的内存消耗.

I populated a database with 35,839,987 records and now my setup is taking nearly 4 seconds per each batch of 65521 updates - however, it doesn't even reach 16 MB of memory consumption.

好的，这是另一个:

INTEGER PRIMARY KEY 列的索引(不要这样做)

当你创建一个带有 INTEGER PRIMARY KEY 的列时，SQLite 使用这个column 作为(索引)表结构的键.这是一个隐藏此列上的索引(因为它未显示在 SQLite_Master 表中).在列上添加另一个索引是不需要的，永远不会用过的.此外，它会减慢 INSERT、DELETE 和 UPDATE 操作下来.

When you create a column with INTEGER PRIMARY KEY, SQLite uses this column as the key for (index to) the table structure. This is a hidden index (as it isn't displayed in SQLite_Master table) on this column. Adding another index on the column is not needed and will never be used. In addition it will slow INSERT, DELETE and UPDATE operations down.

您似乎将 PK 定义为 NOT NULL + UNIQUE.PK 是唯一的隐式.

You seem to be defining your PK as NOT NULL + UNIQUE. PK is UNIQUE implicitly.

这篇关于如何减少 SQLite 内存消耗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！