Сравнение скорости баз данных SQLite

Сравнение скорости баз данных

Краткое изложение

Была проведена серия тестов для измерения относительной производительности SQLite 2.7.6, PostgreSQL 7.1.3 и MySQL 3.23.41. Ниже приведены общие выводы, сделанные на основе этих экспериментов:

• SQLite 2.7.6 значительно быстрее (иногда в 10 или 20 раз быстрее), чем стандартная установка PostgreSQL 7.1.3 на RedHat 7.2 для большинства распространенных операций.

• SQLite 2.7.6 часто быстрее (иногда более чем вдвое быстрее), чем MySQL 3.23.41 для большинства распространенных операций.

• SQLite не выполняет CREATE INDEX или DROP TABLE так же быстро, как другие базы данных. Но это не считается проблемой, поскольку такие операции выполняются нечасто.

• SQLite работает лучше, если вы группируете несколько операций вместе в одну транзакцию.

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

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

• Эти тесты выполнялись на сравнительно небольшой базе данных (примерно 14 мегабайт). Они не измеряют, насколько хорошо движки баз данных масштабируются на более крупные проблемы.

Среда тестирования

Платформа, используемая для этих тестов, — это Athlon с частотой 1,6 ГГц, 1 ГБ оперативной памяти и жестким диском IDE. Операционная система — RedHat Linux 7.2 с базовым ядром.

Используемые серверы PostgreSQL и MySQL были предоставлены по умолчанию в RedHat 7.2 (PostgreSQL версия 7.1.3 и MySQL версия 3.23.41). Не предпринимались попытки настроить эти движки. Обратите внимание, что по умолчанию конфигурация MySQL в RedHat 7.2 не поддерживает транзакции. Отсутствие поддержки транзакций дает MySQL большое преимущество в скорости, но SQLite все еще может держать марку в большинстве тестов.

Мне сообщили, что стандартная конфигурация PostgreSQL в RedHat 7.3 излишне консервативна (она разработана для работы на машине с 8 МБ оперативной памяти), и что PostgreSQL может работать намного быстрее при настройке с использованием знаний. Мэтт Серджиент сообщает, что настроил свою установку PostgreSQL и повторно выполнил представленные ниже тесты. Его результаты показывают, что PostgreSQL и MySQL работают примерно с одинаковой скоростью. Результаты Мэтта можно найти по ссылке

Устаревший URL: http://www.sergeant.org/sqlite_vs_pgsync.html

SQLite тестировался в той же конфигурации, что и на веб-сайте. Он был скомпилирован с оптимизацией -O6 и флагом -DNDEBUG=1, который отключает многочисленные утверждения "assert()" в коде SQLite. Параметр компилятора -DNDEBUG=1 примерно удваивает скорость SQLite.

Все тесты проводятся на машине без других нагрузок. Для генерации и запуска всех тестов использовался простой скрипт Tcl. Копия этого скрипта Tcl находится в дереве исходного кода SQLite в файле tools/speedtest.tcl.

Все представленные во всех тестах временные значения представляют время выполнения в секундах. Для SQLite отображаются два отдельных значения времени. Первое значение — для SQLite в стандартной конфигурации с включенной полной синхронизацией диска. При включенной синхронизации SQLite выполняет системный вызов fsync() (или эквивалент) в ключевые моменты, чтобы убедиться, что критические данные действительно были записаны на поверхность жесткого диска. Синхронизация необходима для обеспечения целостности базы данных, если операционная система аварийно завершит работу или компьютер неожиданно выключится во время обновления базы данных. Второе время, указанное для SQLite, относится к случаю, когда синхронизация выключена. Без синхронизации SQLite иногда значительно быстрее, но существует риск, что сбой операционной системы или неожиданное отключение электропитания могут повредить базу данных. Как правило, синхронные времена SQLite предназначены для сравнения с PostgreSQL (который также синхронизирован), а асинхронные времена SQLite — для сравнения с асинхронным движком MySQL.

Тест 1: 1000 INSERT

CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t1 VALUES(1,13153,'тринадцать тысяч сто пятьдесят три');
INSERT INTO t1 VALUES(2,75560,'семьдесят пять тысяч пятьсот шестьдесят');
... 995 строк пропущено
INSERT INTO t1 VALUES(998,66289,'шестьдесят шесть тысяч двести восемьдесят девять');
INSERT INTO t1 VALUES(999,24322,'двадцать четыре тысячи триста двадцать два');
INSERT INTO t1 VALUES(1000,94142,'девяносто четыре тысячи сто сорок два');

Поскольку у SQLite нет центрального сервера для координации доступа, для каждой транзакции он должен закрыть и переоткрыть файл базы данных, а также сделать недействительным свой кэш. В этом тесте каждое SQL-выражение представляет собой отдельную транзакцию, поэтому файл базы данных должен открываться и закрываться, а кэш должен очищаться 1000 раз. Несмотря на это, асинхронная версия SQLite по-прежнему почти так же быстра, как MySQL. Однако обратите внимание, насколько медленнее синхронная версия. SQLite вызывает fsync() после каждой синхронной транзакции, чтобы убедиться, что все данные безопасно находятся на поверхности диска, прежде чем продолжить. В течение большей части 13 секунд в синхронном тесте SQLite простаивало, ожидая завершения ввода-вывода на диск.

Тест 2: 25000 INSERT в транзакции

BEGIN;
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t2 VALUES(1,59672,'пятьдесят девять тысяч шестьсот семьдесят два');
... 24997 строк пропущено
INSERT INTO t2 VALUES(24999,89569,'восемьдесят девять тысяч пятьсот шестьдесят девять');
INSERT INTO t2 VALUES(25000,94666,'девяносто четыре тысячи шестьсот шестьдесят шесть');
COMMIT;

Когда все INSERTы помещаются в транзакцию, SQLite больше не нужно закрывать и переоткрывать базу данных или делать недействительным кэш между каждой операцией. Ему также не нужно выполнять никаких fsync() до самого конца. При освобождении от этих ограничений SQLite работает значительно быстрее, чем PostgreSQL и MySQL.

Тест 3: 25000 INSERT в таблицу с индексом

НАЧАЛО;
СОЗДАТЬ ТАБЛИЦУ t3(a ЦЕЛОЕ, b ЦЕЛОЕ, c VARCHAR(100));
СОЗДАТЬ ИНДЕКС i3 НА t3(c);
... пропущено 24998 строк
ВСТАВИТЬ В t3 ЗНАЧЕНИЯ(24999,88509,'восемьдесят восемь тысяч пятьсот девять');
ВСТАВИТЬ В t3 ЗНАЧЕНИЯ(25000,84791,'восемьдесят четыре тысячи семьсот девяносто один');
ЗАВЕРШИТЬ;

Были сообщения о том, что SQLite не работал так же хорошо на таблице с индексом. Этот тест был недавно добавлен, чтобы опровергнуть эти слухи. Действительно, SQLite не так быстро создает новые записи индекса, как другие движки (см. Тест 6 ниже), но его общая скорость все же выше.

Тест 4: 100 SELECT без индекса

НАЧАЛО;
ВЫБРАТЬ COUNT(*), AVG(b) ИЗ t2 ГДЕ b>=0 И b<1000;
ВЫБРАТЬ COUNT(*), AVG(b) ИЗ t2 ГДЕ b>=100 И b<1100;
... пропущено 96 строк
ВЫБРАТЬ COUNT(*), AVG(b) ИЗ t2 ГДЕ b>=9800 И b<10800;
ВЫБРАТЬ COUNT(*), AVG(b) ИЗ t2 ГДЕ b>=9900 И b<10900;
ЗАВЕРШИТЬ;

Этот тест выполняет 100 запросов к таблице с 25000 записей без индекса, поэтому требуется полный перебор таблицы. Предыдущие версии SQLite были медленнее, чем PostgreSQL и MySQL в этом тесте, но недавние улучшения производительности увеличили его скорость, так что теперь он самый быстрый в группе.

Тест 5: 100 SELECT по строковому сравниванию

НАЧАЛО;
ВЫБРАТЬ COUNT(*), AVG(b) ИЗ t2 ГДЕ c ПОХОЖЕ НА '%one%';
ВЫБРАТЬ COUNT(*), AVG(b) ИЗ t2 ГДЕ c ПОХОЖЕ НА '%two%';
... пропущено 96 строк
ВЫБРАТЬ COUNT(*), AVG(b) ИЗ t2 ГДЕ c ПОХОЖЕ НА '%ninety nine%';
ВЫБРАТЬ COUNT(*), AVG(b) ИЗ t2 ГДЕ c ПОХОЖЕ НА '%one hundred%';
ЗАВЕРШИТЬ;

Этот тест всё ещё выполняет 100 полных переборов таблицы, но использует сравнения строк вместо числовых. SQLite здесь более чем в три раза быстрее, чем PostgreSQL и примерно на 30% быстрее, чем MySQL.

Тест 6: Создание индекса

СОЗДАТЬ ИНДЕКС i2a НА t2(a);
СОЗДАТЬ ИНДЕКС i2b НА t2(b);

SQLite медленнее при создании новых индексов. Это не большая проблема (поскольку новые индексы не создаются очень часто), но над этим работают. Надеюсь, будущие версии SQLite будут работать лучше в этом плане.

Тест 7: 5000 SELECT с индексом

ВЫБРАТЬ COUNT(*), AVG(b) ИЗ t2 ГДЕ b>=0 И b<100;
ВЫБРАТЬ COUNT(*), AVG(b) ИЗ t2 ГДЕ b>=100 И b<200;
ВЫБРАТЬ COUNT(*), AVG(b) ИЗ t2 ГДЕ b>=200 И b<300;
... пропущено 4994 строки
ВЫБРАТЬ COUNT(*), AVG(b) ИЗ t2 ГДЕ b>=499700 И b<499800;
ВЫБРАТЬ COUNT(*), AVG(b) ИЗ t2 ГДЕ b>=499800 И b<499900;
ВЫБРАТЬ COUNT(*), AVG(b) ИЗ t2 ГДЕ b>=499900 И b<500000;

Все три движка баз данных работают быстрее, когда у них есть индексы. Но SQLite всё ещё самый быстрый.

Тест 8: 1000 UPDATE без индекса

НАЧАЛО;
ОБНОВИТЬ t1 УСТАНОВИТЬ b=b*2 ГДЕ a>=0 И a<10;
ОБНОВИТЬ t1 УСТАНОВИТЬ b=b*2 ГДЕ a>=10 И a<20;
... пропущено 996 строк
ОБНОВИТЬ t1 УСТАНОВИТЬ b=b*2 ГДЕ a>=9980 И a<9990;
ОБНОВИТЬ t1 УСТАНОВИТЬ b=b*2 ГДЕ a>=9990 И a<10000;
ЗАВЕРШИТЬ;

Для этого конкретного теста UPDATE MySQL постоянно в пять или десять раз медленнее, чем PostgreSQL и SQLite. Я не знаю почему. MySQL обычно очень быстрый движок. Возможно, эта проблема была решена в более поздних версиях MySQL.

Тест 9: 25000 UPDATE с индексом

НАЧАЛО;
UPDATE t2 SET b=468026 WHERE a=1;
UPDATE t2 SET b=121928 WHERE a=2;
... пропущено 24996 строк
UPDATE t2 SET b=35065 WHERE a=24999;
UPDATE t2 SET b=347393 WHERE a=25000;
COMMIT;

Ещё в версии 2.7.0 SQLite работало примерно с такой же скоростью, как MySQL в этом тесте. Но недавние оптимизации SQLite более чем удвоили скорость UPDATE.

Тест 10: 25000 текстовых UPDATE с индексом

НАЧАЛО;
UPDATE t2 SET c='сто сорок восемь тысяч триста восемьдесят два' WHERE a=1;
UPDATE t2 SET c='триста шестьдесят шесть тысяч пятьсот две' WHERE a=2;
... пропущено 24996 строк
UPDATE t2 SET c='триста восемьдесят три тысячи девяносто девять' WHERE a=24999;
UPDATE t2 SET c='двести пятьдесят шесть тысяч восемьсот тридцать' WHERE a=25000;
COMMIT;

Опять же, версия 2.7.0 SQLite работала примерно с такой же скоростью, как MySQL. Но теперь версия 2.7.6 работает более чем вдвое быстрее, чем MySQL и более чем в двадцать раз быстрее, чем PostgreSQL.

Если честно, PostgreSQL начало испытывать трудности в этом тесте. Опытный администратор, возможно, смог бы значительно ускорить работу PostgreSQL, немного настроив и натренировав сервер.

Тест 11: INSERT из SELECT

НАЧАЛО;
INSERT INTO t1 SELECT b,a,c FROM t2;
INSERT INTO t2 SELECT b,a,c FROM t1;
COMMIT;

Асинхронный SQLite немного медленнее, чем MySQL в этом тесте. (MySQL, похоже, особенно хорошо справляется с операциями INSERT...SELECT.) Двигатель PostgreSQL всё ещё испытывал трудности — большая часть 61 секунды, которые он использовал, ушла на ожидание ввода-вывода с диска.

Тест 12: DELETE без индекса

DELETE FROM t2 WHERE c LIKE '%fifty%';

Синхронная версия SQLite в этом тесте самая медленная, но асинхронная — самая быстрая. Разница заключается в дополнительном времени, необходимом для выполнения fsync().

Тест 13: DELETE с индексом

DELETE FROM t2 WHERE a>10 AND a<20000;

Этот тест важен, потому что это один из немногих, где PostgreSQL быстрее, чем MySQL. Однако асинхронный SQLite быстрее, чем оба других.

Тест 14: Большой INSERT после большого DELETE

INSERT INTO t2 SELECT * FROM t1;

Некоторые старые версии SQLite (до версии 2.4.0) демонстрировали снижение производительности после последовательности DELETE, за которой следовали новые INSERT. Как показывает этот тест, проблема теперь решена.

Тест 15: Большой DELETE, за которым следуют многочисленные небольшие INSERT

НАЧАЛО;
DELETE FROM t1;
INSERT INTO t1 VALUES(1,10719,'десять тысяч семьсот девятнадцать');
... пропущено 11997 строк
INSERT INTO t1 VALUES(11999,72836,'семьдесят две тысячи восемьсот тридцать шесть');
INSERT INTO t1 VALUES(12000,64231,'шестьдесят четыре тысячи двести тридцать один');
COMMIT;

SQLite очень хорошо справляется с операциями INSERT в рамках транзакции, что, вероятно, объясняет, почему оно намного быстрее, чем другие базы данных в этом тесте.

Тест 16: DROP TABLE

DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;

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

С другой стороны, удаление таблиц — не очень распространённая операция, поэтому, если SQLite тратит немного больше времени, это не считается большой проблемой.

Последнее изменение этой страницы 02.01.2023 14:22:42 UTC