如何减少SQLite内存消耗？

Question

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

每次执行时，它都会创建一个具有以下模式的表：

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

接下来，数据库每秒填充5万个操作。仅写入。

当项目已经存在时，使用更新查询更新“计数”（我认为这称为UPSERT）。以下是我的查询：

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

这样，每次交易进行500万次操作，我可以快速地向数据库写入。对于这个问题，我并不关心磁盘空间，但我的RAM空间非常有限。因此，我不能浪费太多内存。

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

我不得不将缓存大小提高到1M（页面大小默认）才能达到理想的性能水平。

我该怎么做才能减少内存消耗？

- Pedro Alves

---

表有多大？ - CL.

@CL. 有3500万行数据，每个主要条目都是一个包含30多个字符的字符串。 - Pedro Alves

Answer 1

看起来高内存消耗可能是因为太多的操作集中在一个大事务里引起的。尝试提交更小的事务，如每100万次操作一个事务，可以帮助减少内存消耗。每个事务5M操作会消耗过多的内存。

然而，我们需要平衡操作速度和内存使用。

如果较小的事务不可行，PRAGMA shrink_memory 可以是一个选择。

使用 sqlite3_status() 和 SQLITE_STATUS_MEMORY_USED 跟踪动态内存分配并定位瓶颈。

- Peixu Zhu

---

我已经尝试将交易减少到1M。它仍然消耗相同的RAM，但变得更加缓慢。 - Pedro Alves

有一个名为 PRAGMA shrink_memory 的命令，它可以使数据库连接释放尽可能多的内存。 - Peixu Zhu

你的评论让我意识到我正在使用旧版本的sqlite3。我有3.7.9，它不支持PRAGMA shrink_memory或sqlite3_db_release_memory()。谢谢 =) 使用PRAGMA shrink_memory + sqlite3_db_memory_release()，当调用此函数时，我看到内存消耗迅速下降。我认为这只是释放了所有缓存和堆内存，因为插入性能也会下降，直到缓存再次填满并且内存消耗接近之前的状态。这不是完美的解决方案，但这可能是一个有用的解决方案 =) - Pedro Alves

运行 sqlite3_status(); 时，我遇到了语法错误。 - chovy

Answer 2

我会:

• 准备语句（如果你还没有这样做）
• 降低每个事务的INSERT数量（10秒= 500,000听起来很合适）
• 如果可以，使用PRAGMA locking_mode = EXCLUSIVE;

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

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

您还可以尝试基准测试PRAGMA temp_store = FILE;是否有所不同。

当您的数据库未被写入时：

• PRAGMA shrink_memory;
• VACUUM;

根据您对数据库的使用情况，这些可能也有所帮助：

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

---

我使用以下pragma运行了一些测试：

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 = ?;

每秒更新次数达到了约109,000次的峰值。

测试#2：

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

更新速度峰值约为每秒120k。

---

我还尝试了 PRAGMA temp_store = FILE;，更新速度下降了约1-2k每秒。

---

对于一个包含7M条记录的事务，journal_mode=WAL 比其他所有模式都要慢。

---

我用35839987个记录填充了一个数据库，现在我的设置每批65521个更新需要近4秒钟，但它甚至没有达到16MB的内存消耗。

---

好的，这里还有一个:

不要在 INTEGER PRIMARY KEY 列上创建索引

当你创建一个 INTEGER PRIMARY KEY 列时，SQLite使用该列作为表结构的键（索引）。这是一个隐藏的索引（因为它不显示在SQLite_Master表中），作用于该列上。在该列上添加另一个索引是不必要的，也永远不会被使用。此外，它还会减慢 INSERT、DELETE 和 UPDATE 操作的速度。

你似乎将PK定义为NOT NULL + UNIQUE。PK 在隐式情况下就是唯一的。

- Alix Axel

Answer 3

假设一笔事务中的所有操作都分布在整个表格中，以至于需要访问表格的所有页面，那么工作集的大小为：

• 表格中数据约为1 GB，加上
• main列索引约为1 GB，再加上
• 在事务中更改的所有表格页面的原始数据（可能是全部）约为1 GB。

---

您可以尝试将count列移动到单独的表格中，以减少每个操作所更改的数据量：

CREATE TABLE main_lookup(main TEXT NOT NULL UNIQUE, rowid INTEGER PRIMARY KEY);
CREATE TABLE counters(rowid INTEGER PRIMARY KEY, count INTEGER DEFAULT 0);

然后，对于每个操作：

SELECT rowid FROM main_lookup WHERE main = @SEQ;
if not exists:
    INSERT INTO main_lookup(main) VALUES(@SEQ);
    --read the inserted rowid
    INSERT INTO counters VALUES(@rowid, 0);
UPDATE counters SET count=count+1 WHERE rowid = @rowid;

在 C 中，使用sqlite3_last_insert_rowid函数读取插入的 rowid 。

执行单独的 SELECT 和 INSERT ，与 INSERT OR IGNORE 执行速度相同；SQLite 在两种情况下进行相同的工作。

此优化仅在大多数操作更新已存在的计数器时才有用。

- CL.

---

没起作用。它变得更慢了，内存消耗也更高了。 - Pedro Alves

Answer 4

为了进行头脑风暴，我将冒险给出答案。我没有像这位朋友那样做过任何相关测试： 如何提高SQLite的“INSERT每秒”性能？ 我的假设是，文本主键上的索引可能比两个整数列上的一对索引（你需要模拟哈希表）更加消耗 RAM。

编辑：实际上，你甚至不需要一个主键：

      create table foo( slot integer, myval text, occurrences int);
      create index ix_foo on foo(slot);  // not a unique index

一个整数主键（或者在slot上的非唯一索引）将无法快速确定您的文本值是否已经存在于文件中。因此，为了满足这个要求，您可以尝试实现我建议另一个帖子的东西，模拟哈希键：

SQLite Optimization for Millions of Entries?

哈希键函数将允许您确定文本值如果存在将存储在哪里。

http://www.cs.princeton.edu/courses/archive/fall08/cos521/hash.pdf http://www.fearme.com/misc/alg/node28.html http://cs.mwsu.edu/~griffin/courses/2133/downloads/Spring11/p677-pearson.pdf

- Tim