Миллиард таблиц в базе SQLite

Прочитал давеча на Хабре пост про попытку создать миллиард таблиц в базе PostgreSQL. Загорелся идеей провести собственный эксперимент. Поскольку PostgreSQL я не юзаю, проверять буду SQLite, с которым тесно работаю последние пару лет. Ну и опыты буду ставить не над сервером, а над домашним компом. Правда, ради повышения чистоты эксперимента, не буду заморачиваться потоками и иже с ним — пусть программа работает на 1 ядре в 1 поток (один чёрт можно заранее предсказать, что узким местом в плане скорости работы станет жёсткий диск).

Есть программа, написанная на Lazarus. Используется последняя доступная версия sqlite.dll и класс-обёртка к ней, адаптированный к Lazarus. Для пущей наглядности добавил на форму прогресс-бар и немного информации (время работы, процент выполнения). Ну а поскольку потоков делать не стал, добавил банальный Application.ProcessMessages — чтобы форма не повисла на всё время работы программы. Собственно таблицы будут создаваться запросом следующего вида:
'CREATE TABLE IF NOT EXISTS [users'+IntToStr(i)+'] ([id] INTEGER, [name] CHAR, [pass] CHAR, [type] INTEGER)',
где i — номер таблицы (он же — переменная цикла).
Запрос взят из реального приложения, хотя на практике, конечно же, трудно представить необходимость создания большого числа однотипных таблиц такого рода (впрочем, вообще трудно придумать ситуацию, когда могут понадобиться таблицы в таком количестве).

Ну, поехали! По мере эксперимента буду обновлять эту запись.

UPD №1. Прошло 15 минут. На данный момент создано 55000 запросов (~0,48). Файл БД весит около 62 Мбайт. Программа начинает медленно отъедать оперативную память (объём занимаемой памяти держится на отметке чуть меньше объёма, занимаемого файлом БД на диске).

UPD №2. Подробный анализ показал, что примерно после 15000 созданных таблиц время на обработку одного запроса «CREATE» заметно (и скачкообразно) увеличивается. Причём, в дальнейшем время на обработку 1 запроса продолжает увеличиваться, но уже менее заметно. На небольших интервалах (5-10 тысяч запросов) заметить разницу во времени обработки запроса без использования точных измерений (которые в рамках одного потока будут, вероятнее всего, давать слишком большую погрешность) невозможно. Градация примерно следующая: первые 15000 таблиц были созданы менее, чем за 1 минуту. На следующие 35 тысяч (итого — отметка в 50000 таблиц) ушло примерно 11 минут (итого ~12 минут работы).
Также в ходе побочных экспериментов выявлено, что в случае созданий большого массива таблиц одной транзакцией наблюдается заметная утечка памяти и почти 100% загрузка процессора (или, в моём случае — одного из ядер). При создании каждой таблицы отдельной транзакцией нагрузка на CPU снижается до минимума (1-2%), а утечка памяти практически исчезает (наблюдается несущественный рост расхода памяти, возможно, связанный с побочными операциями по отрисовке формы или особенностями отладчика GDB, под которым запускался процесс во избежание «зависаний» намертво).

UPD №3. После создания примерно 60000 таблиц вновь началось замедление. Результата в 70000 таблиц программа достигла на отметке в 26 минут от начала работы. Таким образом, на очередные 20000 таблиц ушло порядка 13 минут. В принципе, уже можно делать первые (неутешительные) прогнозы относительного общего времени работы.

UPD №4. В связи с тем, что утечка памяти продолжается, возникла мысль: можно было делать Commit для SQL-транзации, скажем, каждую 1000 таблиц. Это, вероятно, позволило бы сократить расход памяти, не сильно повышая нагрузку на дисковую подсистему.
Также исходя из текущих наблюдений можно утверждать, что выполнение операции по массированному созданию таблиц в нескольких потоках одновременно, вероятнее всего, не дало бы значительного прироста общей скорости. Как и следовало ожидать, самым узким местом оказалась дисковая подсистема. Возможно, некоторый прирост скорости даст использование комбинации ключей PRAGMA journal_size_limit, PRAGMA journal_mode=persist (и, как следствие, PRAGMA locking_mode=exclusive). При этом увеличится потребление дискового пространства, но скорость записи в БД должна возрасти (journal_size_limit нужно подбирать экспериментально). Правда, при этом не удастся работать с базой в несколько потоков (из-за locking_mode=exclusive нельзя будет подключиться к БД более 1 раза).
Теоретически, можно ускорить работу путём использования для непосредственной генерации таблиц несколько БД, размещённых в различных файлах (соответственно, ведя запись в них в несколько потоков) с последующим их объединением путём использования SQL-команды ATTACH и запроса с конструкцией вида INSERT INTO ... SELECT FROM .... Но насколько при этом увеличится общая скорость создания желаемого числа таблиц (миллиарда, ага!) — предсказать без дополнительных экспериментов трудно.

UPD №5. Результаты. На создание 100 тысяч таблиц у программы ушло 55 минут 34 секунды. При этом была создана БД размером 115 331 072 байт (~109 Мбайт), а расход оперативной памяти увеличился с ~2 Мбайт до ~84 Мбайт.
Таким образом, условно приняв скорость создания таблиц за константу (что на практике, как видно из выкладок, совершенно не так) можно посчитать примерные результаты эксперимента, если бы удалось довести его до конца. Собственно, просто умножим время и размер файла на 10000. Получим, что база с миллионом пустых таблиц заняла бы порядка 1077 Гбайт, а время её генерации составило бы порядка 385 дней (с учётом постоянного снижения скорости время, скорее всего, было бы значительно больше). Данные цифры позволяют заключить, что продолжение эксперимента с заданными параметрами в домашних условиях довольно затруднительно (если, конечно, у вас нет диска как минимум на 1,5 Тбайт и 1,5-2 лет свободного машинного времени).

UPD №6. Для чистоты эксперимента я изменил запрос на создание таблицы, исключив проверку на существование дублей:
'CREATE TABLE [users'+IntToStr(i)+'] ([id] INTEGER, [name] CHAR, [pass] CHAR, [type] INTEGER)'.
Попутно программа была пересобрана с использованием директивы компилятора -O3 (максимальная оптимизация) и с полным отключением функций отладки.
Как и следовало ожидать, данные манипуляции мало повлияли на общую картину.
Так, на начальном отрезке работы (первые 10 тысяч страниц) время выполнения изменилось в пределах 1-2% (что может быть погрешностью измерения, если учесть, что эксперимент проводился на рабочем компьютере). На следующем отрезке было отмечено небольшое увеличение скорости работы, и первый порог замедления, замеченный в первом опыте) проявился немногим позднее — после создания примерно 23-24 тысяч таблиц (против 15 тысяч в первом опыте). В дальнейшем скорости работы снижалась практически теми же темпами, что в первом опыте. Очевидно, что небольшой прирост скорости на первом отрезке связан с исключением проверки, однако по мере роста БД эта разница постепенно сходит на нет. Так, отметка в 50000 таблиц была пройдена примерно за 13 минут, что совпадает с показаниями первого замера. А финальный результат оказался даже несколько хуже — 57 минут 16 секунд. Таким образом, дополнительная проверка и отладчик не оказали заметного влияния на результаты эксперимента.
Что же касается роста потребления памяти, то во втором опыте он немного изменил динамику, и в результате потребление памяти выросло с ~2 Мбайт до ~63 Мбайт. Разница в 20 Мбайт в сравнении с первым опытов вызвана, вероятно, отсутствием влияния отладчика и использованием компиляции с максимальной оптимизацией. Применительно собственно к SQLite можно утверждать, что скорость создания таблиц во втором случае существенно не изменилась.

На этом эксперимент можно считать завершённым. Итоги вполне понятны: создать миллиард страниц, скорее всего, удастся без особых проблем. Однако, на это потребуется более терабайта дискового пространства и более года работы при условии выполнения запросов в один поток на обычном домашнем ПК.