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

Я буду:

• подготовьте заявления (если вы еще этого не делаете)
• уменьшить количество INSERT на транзакцию (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 М (миллион) приведет к 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 000 обновлений в секунду.

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

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

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

Хорошо, вот еще один:

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

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

Кажется, вы определяете свой PK как NOT NULL + UNIQUE. PK неявно является УНИКАЛЬНЫМ.

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

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

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

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

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

• около 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 делает то же самое в любом случае.

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

В духе мозгового штурма рискну ответить. Я не проводил никаких тестов, как этот парень:

Повысить производительность INSERT в секунду для SQLite?

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

РЕДАКТИРОВАТЬ: На самом деле, для этого вам даже не нужен первичный ключ:

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

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

Оптимизация 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