Как уменьшить потребление памяти SQLite?

Ответ 1

Я бы:

• подготовьте заявления (если вы этого уже не делаете)
• уменьшить количество ВСТАВКИ на транзакцию (10 секунд = 500 000 звуков)
• используйте PRAGMA locking_mode = EXCLUSIVE;, если вы можете

Кроме того, (я не уверен, что вы знаете) PRAGMA cache_size находится на страницах, а не в МБ. Убедитесь, что вы определили целевую память как PRAGMA cache_size * PRAGMA page_size или в SQLite >= 3.7.10, вы также можете сделать PRAGMA cache_size = -kibibytes;. Установка его на 1 М (illion) приведет к 1 или 2 ГБ.

Мне любопытно, как cache_size помогает в INSERT, хотя...

Вы также можете попробовать и сравнить, если значение PRAGMA temp_store = FILE; имеет значение.

И, конечно, всякий раз, когда ваша база данных не записывается:

• PRAGMA shrink_memory;
• VACUUM;

В зависимости от того, что вы делаете с базой данных, они также могут помочь:

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

Я провел несколько тестов со следующими прагмами:

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 тыс. обновлений в секунду.

Тест № 2:

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

Достигнуто на уровне ~ 120 тыс. обновлений в секунду.

Я также пробовал PRAGMA temp_store = FILE;, а обновления упали на ~ 1-2k в секунду.

Для обновлений 7M в транзакции journal_mode=WAL работает медленнее всех остальных.

Я заполнил базу данных с 35 839 987 записями, и теперь моя установка занимает почти 4 секунды на каждую партию обновлений 65521, однако она даже не достигает 16 МБ потребляемой памяти.

Хорошо, здесь еще один:

Индексы в столбцах INTEGER PRIMARY KEY (не делайте этого)

Когда вы создаете столбец с INTIMER PRIMARY KEY, SQLite использует это столбца в качестве ключа для (индекса) структуры таблицы. Это скрытая index (поскольку он не отображается в таблице SQLite_Master) в этом столбце. Добавление другого индекса в столбец не требуется и никогда не будет используемый. Кроме того, это замедлит операции INSERT, DELETE и UPDATE вниз.

Кажется, вы определяете свой ПК как NOT NULL + UNIQUE. PK UNIQUE неявно.

Ответ 2

Похоже, что потребление высокой памяти может быть вызвано тем, что слишком много операций сосредоточено в одной большой транзакции. Попытка совершить меньшую транзакцию, например, за 1M операций, может помочь. Операции 5M за транзакцию будут потреблять много памяти.

Однако мы бы сбалансировали скорость работы и использование памяти.

Поскольку меньшая транзакция не помогает, PRAGMA shrink_memory может быть выбором.

Используя sqlite3_status() с SQLITE_STATUS_MEMORY_USED для трассировки распределения динамической памяти и найдите точку.

Ответ 3

Предполагая, что все операции в одной транзакции распределены по всей таблице, так что все страницы таблицы должны быть доступны, размер рабочего набора:

• около 1 ГБ для данных таблицы, плюс
• около 1 ГБ для индекса в столбце main плюс
• около 1 ГБ для исходных данных всех страниц таблицы, измененных в транзакции (возможно, все из них).

Вы можете попытаться уменьшить количество данных, которые будут изменены для каждой операции, переместив столбец 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 вставленный rowid считывается с sqlite3_last_insert_rowid.

Выполнение отдельных SELECT и INSERT не медленнее, чем INSERT OR IGNORE; SQLite выполняет ту же работу в любом случае.

Эта оптимизация полезна только в том случае, если большинство операций обновляют счетчик, который уже существует.

Ответ 4

В духе мозгового штурма я отважусь ответить. Я не тестировал такого человека:

Улучшить производительность SQLite в секунду в секунду?

Моя гипотеза заключается в том, что индекс первичного ключа текста может быть более интенсивным, чем несколько индексов на двух целых столбцах (что вам нужно для моделирования хэшированной таблицы).

EDIT: На самом деле вам даже не нужен первичный ключ для этого:

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

Integer первичный ключ (или не уникальный индекс в слоте) не даст вам быстрого способа определить, было ли ваше текстовое значение уже в файле. Поэтому для решения этого требования вы можете попробовать реализовать что-то, что я предложил другому плакату, имитируя хешированный ключ:

Оптимизация SQLite для миллионов записей?

Функция хеш-ключа позволит вам определить, где будет храниться текстовое значение, если оно действительно существует.

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