На 35% быстрее, чем файловая система

1. Резюме

SQLite читает и записывает небольшие двоичные данные (например, миниатюры изображений) на 35% быстрее¹, чем те же двоичные данные могут быть прочитаны или записаны в отдельные файлы на диске с использованием fread() или fwrite().

Кроме того, единственная база данных SQLite, содержащая двоичные данные размером 10 килобайт, занимает примерно на 20% меньше места на диске, чем хранение этих двоичных данных в отдельных файлах.

Разница в производительности возникает (как мы предполагаем) из-за того, что при работе с базой данных SQLite вызовы систем open() и close() вызываются только один раз, тогда как при использовании двоичных данных, хранящихся в отдельных файлах, вызовы open() и close() вызываются один раз для каждого двоичного объекта. Похоже, что накладные расходы вызова open() и close() больше, чем накладные расходы использования базы данных. Уменьшение размера происходит от того, что отдельные файлы дополняются до ближайшего кратного размера блока файловой системы, тогда как двоичные данные упаковываются более плотно в базу данных SQLite.

Измерения в этой статье были проведены на неделе с 2017-06-05 с использованием версии SQLite между 3.19.2 и 3.20.0. Вы можете ожидать, что будущие версии SQLite будут работать еще лучше.

1.1. Оговорки

¹Указанная выше цифра 35% приблизительная. Фактические замеры времени зависят от аппаратного обеспечения, операционной системы и деталей эксперимента, а также из-за случайных колебаний производительности на реальном оборудовании. Более подробную информацию см. в тексте ниже. Попробуйте провести эксперименты самостоятельно. Сообщите о значительных отклонениях на форуме SQLite.

Цифра 35% основана на результатах тестирования на каждом компьютере, который автору легко доступен. Некоторые рецензенты этой статьи сообщают, что SQLite имеет более высокую задержку, чем прямой ввод-вывод в своих системах. Мы пока не понимаем причину различий. Мы также наблюдаем признаки того, что SQLite не работает так же хорошо, как прямой ввод-вывод, когда эксперименты выполняются с использованием кэша файловой системы.

Итак, сделайте вывод: задержка чтения/записи для SQLite конкурентоспособна с задержкой чтения/записи отдельных файлов на диске. Часто SQLite быстрее. Иногда SQLite почти так же быстро. В любом случае, эта статья опровергает общее предположение о том, что реляционная база данных должна быть медленнее, чем прямой ввод-вывод файловой системы.

1.2. Связанные исследования

Исследование 2022 года (альтернативная ссылка на GitHub) на определенных нагрузках показало, что SQLite примерно вдвое быстрее, чем Btrfs и Ext4 в Linux.

Джим Грей и другие изучали производительность чтения BLOB по сравнению с вводом-выводом файлов для Microsoft SQL Server и обнаружили, что чтение BLOB из базы данных было быстрее для размеров BLOB, меньших чем от 250KiB до 1MiB. (Статья). В этом исследовании база данных по-прежнему хранит имя файла содержимого, даже если содержимое хранится в отдельном файле. Таким образом, база данных используется для каждого BLOB, даже если это только для извлечения имени файла. В этой статье ключ BLOB — это имя файла, поэтому предварительного доступа к базе данных не требуется. Поскольку база данных вообще не используется при чтении содержимого из отдельных файлов в этой статье, порог, при котором прямой ввод-вывод файлов становится быстрее, ниже, чем в статье Грея.

Статья Внутренние против внешних BLOB на этом сайте — более раннее исследование (приблизительно 2011 год), которое использует тот же подход, что и статья Джима Грея — хранение имен файлов BLOB как записей в базе данных — но для SQLite вместо SQL Server.

2. Как проводятся эти измерения

Производительность ввода-вывода измеряется с помощью программы kvtest.c из исходного кода SQLite. Чтобы скомпилировать эту программу-тест, сначала поместите исходный файл kvtest.c в каталог с исходными файлами объединения SQLite "sqlite3.c" и "sqlite3.h". Затем в Unix выполните команду, например:

gcc -Os -I. -DSQLITE_DIRECT_OVERFLOW_READ \
  kvtest.c sqlite3.c -o kvtest -ldl -lpthread

Или в Windows с MSVC:

cl -I. -DSQLITE_DIRECT_OVERFLOW_READ kvtest.c sqlite3.c

Инструкции по компиляции для Android показаны ниже.

Используйте полученную программу "kvtest", чтобы создать тестовую базу данных с 100 000 случайными несжимаемыми двоичными данными, каждый размером от 8 000 до 12 000 байт, используя команду, например:

./kvtest init test1.db --count 100k --size 10k --variance 2k

При необходимости вы можете проверить новую базу данных, выполнив эту команду:

./kvtest stat test1.db

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

./kvtest export test1.db test1.dir

На этом этапе вы можете измерить размер, занимаемый базой данных test1.db, и размер, занимаемый каталогом test1.dir и всем его содержимым. На стандартном рабочем столе Ubuntu Linux размер файла базы данных составит 1 024 512 000 байт, а каталог test1.dir займет 1 228 800 000 байт (согласно «du -k»), примерно на 20% больше, чем база данных.

Каталог «test1.dir», созданный выше, помещает все двоичные данные в одну папку. Было предположено, что некоторые операционные системы будут плохо работать, когда один каталог содержит 100 000 объектов. Для проверки этого программа kvtest также может хранить двоичные данные в иерархии папок с не более чем 100 файлами и/или подкаталогами в каждой папке. Альтернативное представление двоичных данных на диске можно создать с помощью параметра командной строки --tree для команды «export», например:

./kvtest export test1.db test1.tree --tree

Каталог test1.dir будет содержать 100 000 файлов с именами, такими как «000000», «000001», «000002» и так далее, а каталог test1.tree будет содержать те же файлы в подкаталогах, таких как «00/00/00», «00/00/01» и так далее. Каталоги test1.dir и test1.test занимают примерно одинаковый объем памяти, хотя test1.test немного больше из-за дополнительных записей в каталоге.

Все последующие эксперименты работают одинаково с «test1.dir» или «test1.tree». В любом случае, независимо от операционной системы, наблюдается очень незначительная разница в производительности.

Измерьте производительность чтения двоичных данных из базы данных и из отдельных файлов с помощью этих команд:

./kvtest run test1.db --count 100k --blob-api
./kvtest run test1.dir --count 100k --blob-api
./kvtest run test1.tree --count 100k --blob-api

В зависимости от вашего аппаратного обеспечения и операционной системы, вы должны заметить, что чтение из файла базы данных test1.db примерно на 35% быстрее, чем чтение из отдельных файлов в каталогах test1.dir или test1.tree. Результаты могут существенно отличаться от одного запуска к другому из-за кэширования, поэтому рекомендуется выполнять тесты несколько раз и брать среднее значение или наихудший или наилучший случай, в зависимости от ваших потребностей.

Опция --blob-api при чтении из базы данных заставляет kvtest использовать функцию sqlite3_blob_read() SQLite для загрузки содержимого двоичных данных, а не выполнения чистых SQL-запросов. Это помогает SQLite немного быстрее выполнять тесты чтения. Вы можете пропустить этот параметр, чтобы сравнить производительность SQLite при выполнении SQL-запросов. В этом случае SQLite все равно превосходит прямое чтение, хотя не так сильно, как при использовании sqlite3_blob_read(). Опция --blob-api игнорируется для тестов чтения из отдельных файлов на диске.

Измерьте производительность записи, добавив опцию --update. Это приведет к перезаписи двоичных данных на месте другим случайным двоичным данными ровно того же размера.

./kvtest run test1.db --count 100k --update
./kvtest run test1.dir --count 100k --update
./kvtest run test1.tree --count 100k --update

Приведенный выше тест записи не является полностью справедливым, так как SQLite выполняет защищенные транзакции, в то время как прямая запись на диск не делает этого. Чтобы сделать тесты более равными, добавьте опцию --nosync к операциям записи в SQLite, чтобы отключить вызов fsync() или FlushFileBuffers(), чтобы принудительно записать содержимое на диск, или используйте опцию --fsync для тестов прямой записи на диск, чтобы принудительно вызвать fsync() или FlushFileBuffers() при обновлении файлов на диске.

По умолчанию kvtest выполняет все измерения ввода-вывода базы данных в рамках одной транзакции. Используйте опцию --multitrans, чтобы выполнять каждое чтение или запись двоичных данных в отдельной транзакции. Опция --multitrans делает SQLite намного медленнее и неконкурентоспособным по сравнению с прямым вводом-выводом на диск. Эта опция еще раз доказывает, что для достижения максимальной производительности SQLite следует объединять как можно больше взаимодействий с базой данных в одной транзакции.

Существует много других параметров тестирования, которые можно увидеть, выполнив команду:

./kvtest help

2.1. Измерения производительности чтения

Ниже приведена диаграмма, собранная с помощью kvtest.c на пяти различных системах:

• Win7: Ноутбук Dell Inspiron, примерно 2009 года, процессор Pentium dual-core с частотой 2,30 ГГц, 4 ГБ оперативной памяти, Windows 7.
• Win10: Ноутбук Lenovo YOGA 910, 2016 года, процессор Intel i7-7500 с частотой 2,70 ГГц, 16 ГБ оперативной памяти, Windows 10.
• Mac: MacBook Pro, 2015 года, процессор Intel Core i7 с частотой 3,1 ГГц, 16 ГБ оперативной памяти, macOS 10.12.5.
• Ubuntu: Рабочий стол, собранный на базе процессора Intel i7-4770K с частотой 3,50 ГГц, 32 ГБ оперативной памяти, Ubuntu 16.04.2 LTS.
• Android: Galaxy S3, ARMv7, 2 ГБ оперативной памяти.

Все машины используют SSD, за исключением Win7, на котором жесткий диск. Тестовая база данных состоит из 100 000 двоичных данных со случайным размером от 8 КБ до 12 КБ, в общей сложности около 1 гигабайта содержимого. Размер страницы базы данных составляет 4 КБ. Для всех этих тестов использовался параметр компиляции -DSQLITE_DIRECT_OVERFLOW_READ. Тесты выполнялись несколько раз. Первый запуск использовался для разогрева кэша, и его измерения были отброшены.

Ниже приведена диаграмма, показывающая среднее время чтения двоичных данных непосредственно с файловой системы по сравнению со временем, необходимым для чтения тех же двоичных данных из базы данных SQLite. Фактические временные интервалы значительно различаются от одной системы к другой (например, настольный компьютер Ubuntu намного быстрее, чем телефон Galaxy S3). Эта диаграмма показывает отношение времени, необходимого для чтения двоичных данных из файла, к времени, необходимому для чтения их из базы данных. Самый левый столбец диаграммы — это нормированное время чтения из базы данных для справки.

На этой диаграмме SQL-запрос («SELECT v FROM kv WHERE k=?1») готовится один раз. Затем для каждого двоичного объекта значение ключа двоичного объекта связывается с параметром ?1, и запрос оценивается для извлечения содержимого двоичного объекта.

График показывает, что в Windows10 содержимое базы данных SQLite можно читать примерно в 5 раз быстрее, чем непосредственно с диска. В Android SQLite быстрее примерно на 35% по сравнению с чтением с диска.

График 1: Задержка чтения SQLite по отношению к чтению с файловой системы.
100K блоков, средний размер 10 КБ, случайный порядок с использованием SQL

Производительность можно незначительно улучшить, пропустив слой SQL и прочитав содержимое блока напрямую с помощью интерфейса sqlite3_blob_read(), как показано на следующем графике:

График 2: Задержка чтения SQLite по отношению к чтению с файловой системы.
100K блоков, средний размер 10 КБ, случайный порядок
с использованием sqlite3_blob_read().

Дальнейшее повышение производительности можно достичь, используя функцию «отображения в памяти» (memory-mapped I/O) SQLite. На следующем графике весь файл базы данных объемом 1 ГБ отображается в памяти, а блоки читаются (в случайном порядке) с помощью интерфейса sqlite3_blob_read(). С этими оптимизациями SQLite работает вдвое быстрее, чем в Android или MacOS-X, и более чем в 10 раз быстрее, чем в Windows.

График 3: Задержка чтения SQLite по отношению к чтению с файловой системы.
100K блоков, средний размер 10 КБ, случайный порядок
с использованием sqlite3_blob_read() из базы данных с отображением в памяти.

Третий график показывает, что чтение содержимого блока из SQLite может быть в два раза быстрее, чем чтение из отдельных файлов на диске для Mac и Android, и в десять раз быстрее для Windows.

2.2. Измерения производительности записи

Запись происходит медленнее. На всех системах, используя как прямой ввод/вывод, так и SQLite, производительность записи в 5-15 раз ниже, чем чтение.

Измерения производительности записи проводились путем замены (перезаписи) всего блока другим блоком. Все блоки в этих экспериментах случайны и несжимаемы. Поскольку запись намного медленнее чтения, только 10 000 из 100 000 блоков в базе данных заменяются. Блоки для замены выбираются случайным образом и не имеют определённого порядка.

Запись непосредственно на диск осуществляется с помощью fopen()/fwrite()/fclose(). По умолчанию и во всех представленных ниже результатах кэши файловой системы ОС никогда не сбрасываются в постоянное хранилище с помощью fsync() или FlushFileBuffers(). Другими словами, нет попытки сделать записи на диск транзакционными или защищенными от потери питания. Мы обнаружили, что вызов fsync() или FlushFileBuffers() для каждого записываемого файла приводит к тому, что запись на диск непосредственно на 10 и более раз медленнее, чем запись в SQLite.

Следующий график сравнивает обновления базы данных SQLite в режиме WAL с прямой перезаписью на диск отдельных файлов на диске. Параметр PRAGMA synchronous равен NORMAL. Все записи в базе данных выполняются в одной транзакции. Таймер для записей в базе данных останавливается после подтверждения транзакции, но до запуска контрольной точки. Обратите внимание, что записи в SQLite, в отличие от записей непосредственно на диск, являются транзакционными и защищенными от потери питания, хотя из-за того, что значение synchronous установлено в NORMAL, а не FULL, транзакции не являются долговременными.

График 4: Задержка записи SQLite по отношению к прямой записи на файловую систему.
10K блоков, средний размер 10 КБ, случайный порядок,
режим WAL с синхронизацией NORMAL,
без учета времени создания контрольной точки

Цифры производительности для Android в экспериментах по записи опущены, поскольку результаты тестов на Galaxy S3 слишком случайны. Два последовательных запуска одного и того же эксперимента дадут сильно различающиеся результаты. И, честно говоря, производительность SQLite в Android немного ниже, чем при записи непосредственно на диск.

Следующий график показывает производительность SQLite по сравнению с записью непосредственно на диск, когда транзакции отключены (PRAGMA journal_mode=OFF) и PRAGMA synchronous установлено в OFF. Эти параметры ставят SQLite на равные права с прямыми записями на диск, то есть делают данные уязвимыми к повреждению из-за сбоев системы и отключений питания.

График 5: Задержка записи SQLite по отношению к прямой записи на файловую систему.
10K блоков, средний размер 10 КБ, случайный порядок,
журнал отключен, синхронизация OFF.

Во всех тестах записи важно отключить антивирусное программное обеспечение перед запуском тестов производительности записи непосредственно на диск. Мы обнаружили, что антивирусное программное обеспечение замедляет прямой ввод/вывод на порядок, в то время как его влияние на записи в SQLite незначительно. Вероятно, это связано с тем, что записи непосредственно на диск изменяют тысячи отдельных файлов, которые необходимо проверять антивирусом, в то время как записи в SQLite изменяют только один файл базы данных.

2.3. Вариации

Временная опция компиляции -DSQLITE_DIRECT_OVERFLOW_READ заставляет SQLite пропустить кэш страниц при чтении содержимого из страниц переполнения. Это немного ускоряет чтение данных базы данных для 10K блоков, но не значительно. SQLite по-прежнему быстрее прямого чтения с файловой системы без опции компиляции SQLITE_DIRECT_OVERFLOW_READ.

Другие опции компиляции, такие как использование -O3 вместо -Os или использование -DSQLITE_THREADSAFE=0 и/или некоторые другие рекомендуемые опции компиляции, могут помочь SQLite работать еще быстрее по сравнению с прямым чтением с файловой системы.

Размер блоков в тестовых данных влияет на производительность. Файловая система обычно будет работать быстрее для больших блоков, так как накладные расходы на open() и close() амортизируются на большем количестве байтов ввода/вывода, в то время как база данных будет эффективнее как по скорости, так и по объему при уменьшении среднего размера блока.

3. Общие выводы

1. SQLite конкурентоспособен и обычно быстрее, чем блоки, хранящиеся в отдельных файлах на диске, как при чтении, так и при записи.

2. SQLite намного быстрее, чем прямые записи на диск в Windows, когда включена защита антивирусным программным обеспечением. Так как антивирусное программное обеспечение включено и должно быть включено по умолчанию в Windows, это означает, что SQLite, как правило, намного быстрее, чем прямые записи на диск в Windows.

3. Чтение примерно на порядок быстрее, чем запись, для всех систем и для SQLite, и для прямого ввода/вывода на диск.

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

5. Некоторые другие SQL-движки баз данных рекомендуют разработчикам хранить блоки в отдельных файлах, а затем хранить имя файла в базе данных. В этом случае, когда база данных должна быть проконсультирована для поиска имени файла перед открытием и чтением файла, простое хранение всего блока в базе данных обеспечивает значительно более высокую производительность чтения и записи с SQLite. См. статью Внутренние и внешние BLOB для получения дополнительной информации.

4. Дополнительные заметки

4.1. Компиляция и тестирование в Android

Программа kvtest компилируется и запускается в Android следующим образом. Сначала установите Android SDK и NDK. Затем подготовьте скрипт под названием «android-gcc», который примерно выглядит так:

#!/bin/sh
#
NDK=/home/drh/Android/Sdk/ndk-bundle
SYSROOT=$NDK/platforms/android-16/arch-arm
ABIN=$NDK/toolchains/arm-linux-androideabi-4.9/prebuilt/linux-x86_64/bin
GCC=$ABIN/arm-linux-androideabi-gcc
$GCC --sysroot=$SYSROOT -fPIC -pie $*

Сделайте этот скрипт исполняемым и поместите его в $PATH. Затем скомпилируйте программу kvtest следующим образом:

android-gcc -Os -I. kvtest.c sqlite3.c -o kvtest-android

Далее переместите получившийся исполняемый файл kvtest-android на устройство Android:

adb push kvtest-android /data/local/tmp

Наконец, используйте «adb shell», чтобы получить приглашение командной строки на устройстве Android, перейдите в каталог /data/local/tmp и начните выполнение тестов, как и на любом другом хосте Unix.

Эта страница была последний раз изменена 05.12.2023 14:43:20 UTC