Сравнение скоростей БД

[Artem.spb]

Введение
По нескольким причинам решил протестировать скорость работы баз данных, освежить свои знания в этой области так сказать.
Сразу отмечу, что я далеко не эксперт в базах, использую по принципу «появилась новая задача, надо понять, как делать». Возможно, каждый из серверов можно было настроить лучше, но все тесты проходили в режиме «установил-создал таблицы-запустил тест»
Тестирование не претендует на истину в последней инстанции и вполне возможно, в других обстоятельствах результаты будут кардинально отличаться.
Комментарии рекомендации новых тестов приветствуются. Виртуальную машину пока сохраню, может ещё тесты проведу.
Для удобства навигации разбил пост на несколько частей.

Содержание
Общее описание теста
Детальный обзор SQLite
Детальный обзор MySQL 8.0
Детальный обзор MSSQL 2014
Детальный обзор PostgreSQL 14
Сравнение результатов (сводные графики)
Выводы

[Artem.spb]

Общие условия тестирования
LAbVIEW 2015, 32 бит, Win7 64, виртуальная машина с 12 Гб памяти и тремя ядрами.
Проверял скорость взаимодействия БД с LabVIEW, в частности DB toolkit. Только для SQLite использовал персональную библиотеку.
В тесте участвовало 4 бесплатных сервера БД: SQLite, MySQL, MSSQL, Postgres.
В каждой базе создаю 2 таблицы:
Сенсоры (id, название, тип)
Результаты (id, время, значение, сенсор). Столбец «сенсор» ссылается на первую таблицу.
Т.о. при записи результатов база дополнительно должна выполнить проверку, что такой сенсор существует.
Кроме того, на каждом сервере существует ещё одна база, аналогичная первой, но для таблицы результатов создаётся индекс (сенсор-время).
В итоге имеем 8 сравнительных характеристик: на каждый тип сервера скорость работы с индексацией и без.
На виртуальную машину установил все три сервера и сконфигурировал их. После каждого прогона машину откатывал к последнему кадру для более-менее равномерного тестирования. Но при этом тест гонял на рабочей машине, так что в течение суток нагрузка разная, что отчасти могло сказываться на результатах (хотя критической загрузки процессора/диска не случалось).

Тест проводится в таком виде: в системе существует 10 типов сенсоров. Каждый сенсор «опрашивается» раз в секунду (случайное число). Каждое значение записывается в базу отдельной строкой.

В ходе тестирования записывается 10-минутный отрезок данных, при этом запись каждого значений – это отдельный запрос, на мой взгляд при реальной работе и таких скоростях это вполне естественно (а не создавать параметризованный запрос и писать пачку). Потом производится проверка скорости чтения: выборка произвольного (в пределах имеющегося диапазона данных) отрезка длиной в 5 минут. Кроме того, раз в сутки удаляются 10 000 случайных записей из базы (случайный набор id от 1 до max без проверки на существование, так что вероятны попытки удаления данных, которые уже удалены).

В итоге за «сутки» в базу записывается 864 000 строк и удаляется до 10 000.
И каждые 144 точки на графике – это сутки реального времени.

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

[Artem.spb]

SQLite
В отличие от других подопытных кроликов это даже не совсем сервер баз данных, но я достаточно часто использую эту базу, поэтому тоже включил в тест, чтобы сравнить скорость чтения-записи и в принципе устойчивость базы к объёмам данных (что на самом деле не особо проверил из-за скоростей).
Как я уже упоминал, в отличие от других БД здесь работа не через DB toolkit, а с помощью отдельной библиотеки. Второе отличие: в других тестах я записываю каждую строчку отдельным запросом, в SQLite создаю параметризованный запрос и в цикле загоняю переменные. Это вполне возможно, ускоряет запись, но это был первый тест, а т.к. он занял больше всего времени, переделывать уже лень. И в этом тесте я не замерял время удаления, потом только понял, что оно тоже интересно.
И есть ещё одна способность у SQLite – создавать базу прямо в памяти, без записи в файл. Этот вариант я тоже протестировал.
Итак, графики.
База без индексов.

Время записи (всего пакета 10 минут) от 50 до 500 мс. В среднем 50-100 мс, т.е. меньше 0,017 мс на строку. И не меняется на протяжении теста.

Время чтения линейно растёт от «нуля» до 22 секунд.
Система «проработала» около трёх месяцев, дальше я прервал тест.

Эта же база, но с индексацией.

Время записи увеличилось примерно в два раза, при этом роста в процессе работы не заметно.

Время чтения упало практически до нуля и в среднем составляет 1-2 мс и так же не меняется в процессе работы.

База в памяти.

Запись раза в два быстрее, разброс не такой большой, но похоже, что есть небольшой рост времени обработки.

Время чтения так же сократилось примерно в два раза, и так же линейно растёт с увеличением размера базы.

База в памяти с индексацией.

Время записи выросло совсем немного. Но система прожила совсем мало, т.к. база быстро переполнила память (32 bit), и программа прервала исполнение.

С выборкой никаких неожиданностей.

[Artem.spb]

MySQL 8.0
Как уже упоминал, при работе с DB toolkit каждую строку писал отдельно, так что сравнивать время записи с предыдущим разделом не совсем корректно.

Среднее время записи 50 сек, или почти 10 мс на строку.
Возможно, есть рост времени выполнения запроса, но достоверно утверждать не возьмусь.
При этом время чтения ведёт себя очень странно

Два дня система вела себя прилично, хотя время чтения плавно росло, но на третий день произошло что-то странное и время начало резко расти. В итоге к концу третьего дня система вылетела с ошибкой «таймаут».
Решил проверить, не глюк ли это. Похоже, что не глюк. При втором заходе система проработала немного дольше, но резкий рост начался в том же месте, т.е. для MySQL какой-то критический размер таблицы около 1,5 млн строк (с учётом удалённых). А при количестве строк больше 2-2,5 млн время выполнения запроса превышает таймаут.

Время удаления составляет примерно 1 минуту, т.е. 5-6 мс на строку.

MySQL + index

Время записи в индексируемую таблицу не сильно отличается от неиндексированной. Разве что разброс больше – до 2 минут на запрос.

Время чтения выглядит гораздо лучше. Система проработала месяц, после чего я прервал тестирование. Система выдержала ~27 млн строк.

Время удаления ~1 минуты и вполне чётко, хоть и плавно растёт.

[Artem.spb]

MS SQL 2018
Сервер показал себя немного лучше, но именно что немного.

Время записи около 15 сек, т.е. 2-3 мс на строку. Что за странное резкое падение скорости при увеличении объёма, сказать не могу, возможно звёзды так сложились и именно в это время я оставил машину в покое.

Время чтения линейно растёт до 1 секунды, и в точке 1300 так же заметно снижение времени обработки запроса. Но есть неприятная особенность. Система прожила всего 12 дней, после чего вылетела с ошибкой преобразования типов.

Повторил тест, картина та же, только под конец уже небольшой прирост времени.

Удаление порядка 1 минуты

Похоже, система способна обрабатывать только 10 млн строк.

Посмотрим, что будет при наличии индекса.

Время записи практически такое же, и та же критическая точка «1800».
А вот время чтения ведёт себя крайне любопытно, на некоторых отрезках всего несколько мс. Такое впечатление, что индекс перестраивается не каждый раз.

Запрос на удаление 60-70 с

[Artem.spb]

PostgreSQL 14
По совместительству это моё первое знакомство с этой базой. Когда пишу эти строки, система работает третий год без заметных проблем.
База без индексации
Запись

На запрос ~1,5 м, т.е. 0,25 мс на строку. Впрочем, пару раз были забросы до 1 минуты. Не очень понятно, есть ли там наклон, но даже если есть, рост совсем незначительный.
Чтение
Время обработки запроса растёт линейно, под конец составляло 2 сек.

Ну и на удаление нужно до 10 сек, т.е. меньше 1 мс на запрос.

И всё шло хорошо, как неожиданно под конец лета система тоже рухнула (20 млн строк)

Есть и ещё «минус». База сильно грузит систему. Все предыдущие тесты процессор работал на уровне 30%, в этом случае загрузка в 2-3 раза больше.

И включаем индексацию.
Запись

те же 1,5 с на запрос, с забросами до 22 сек (думаю, это случайность, что без индекса забросы больше). И такое впечатление, что время выполнения убывает, также незаметно, как оно росло в предыдущем случае.
Чтение

А с чтением всё прекрасно. Стабильно 10 мс, как я сказал, уже третий год, 70 млн строк
Удаление

Слегка дольше, но в целом те же 10 сек на 10К запросов.

[Artem.spb]

Сравнение.
Теперь всё это на одном графике. Точнее, на трёх.
К сожалению, некоторые претенденты довольно быстро сходили с дистанции, поэтому есть что сравнивать только в начале тестирования, но всё равно цифры характерны.
В сравнение не включаю базу в памяти, потому что кардинального отличия от файловой там нет.
Написал это, сделал картинку, и понял, что сравнение не задалось
Запись

Поэтому два графика отдельно.

Аналогичные графики для чтения

Ну и графики для запросов удаления

[Artem.spb]

Итоги.
SQLite довольно стабильная, но из-за медлительности без индекса тестировал не долго, не могу утверждать, будет ли она падать из-за «переполнения». При этом скорости самые большие (с индексацией).
Сервер MySQL показала себя самым слабым в отношении скорости, хотя по «вместимости» обошёл MS.
MS SQL немного лучше по скорости, но имеет предел вместимости.
PG считает, что 2 сек – это уже много, в то время как другие сервера позволяют себе задуматься на 30 сек.
Главное открытие для меня – наличие индекса не сильно тормозит систему. По крайней мере в такой конфигурации, как я использовал. А я до сих пор был уверен, что индекс – это «очень страшно».
Выводы: а выводы делайте сами.
На мой взгляд для небольших локальных систем – SQLite – идеально. Для чего-то более серьёзного - PostgreSQL

[Kosist]

Круто, спасибо за такое исследование
Вот тут бы я немного поспорил:

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

Тут и лишняя перегрузка запросами (10xN запросов на запись вместо 1), и плохая масштабируемость решения. Ведь сегодня будет 10 сенсоров, а через год - 100, например (хотя понимаю, что в Вашем случае такого не было, но все же).

Т.о. при записи результатов база дополнительно должна выполнить проверку, что такой сенсор существует.

Это Вы делали при записи каждого сэмпла с сенсора? Если да, то это лишнее - т.к. список сенсоров с базы данных можно вытащить один раз, а потом лишь использовать их ID. Тогда уж лучше обрабатывать ошибку при записи (напр., если ID сенсора отсутствует - то создать запись в БД для сенсора, считать его ID, запомнить и использовать дальше).

Потом производится проверка скорости чтения: выборка произвольного (в пределах имеющегося диапазона данных) отрезка длиной в 5 минут.

А как Вы конкретно читали данные? По одной строке, или одним запросов все сразу?

[IvanLis]

Это Вы делали при записи каждого сэмпла с сенсора? Если да, то это лишнее - т.к. список сенсоров с базы данных можно вытащить один раз, а потом лишь использовать их ID. Тогда уж лучше обрабатывать ошибку при записи (напр., если ID сенсора отсутствует - то создать запись в БД для сенсора, считать его ID, запомнить и использовать дальше).

Тут скорее не вручную это делалось, а возлагалось на СУБД.
Она же отвечает за целостность данных и соответственно при записи нового значения, должна проверить, что такой id существует, к которому она привязывается по foreign key.

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

[Kosist]

Тут скорее не вручную это делалось, а возлагалось на СУБД.
Она же отвечает за целостность данных и соответственно при записи нового значения, должна проверить, что такой id существует, к которому она привязывается по foreign key.

Ну это да, но просто это весь смысл foreign key - база данных не может записать значение несуществующего foreign key.

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

А вот тут зависит от настроек БД. Можно сделать ON DELETE CASCADE опцию, и потом достаточно удалить запись в одной таблице - а остальные строки, которые ссылаются на ту запись тоже удалятся автоматически. А можно эту опцию не использовать, но придется все удалять вручную снизу вверх. Но это опять же, функционал БД движка.

[Artem.spb]

Тут и лишняя перегрузка запросами (10xN запросов на запись вместо 1), и плохая масштабируемость решения. Ведь сегодня будет 10 сенсоров, а через год - 100, например (хотя понимаю, что в Вашем случае такого не было, но все же).

И да и нет.
С одной стороны, согласен, что можно ускорить (это упоминал в начале), но с другой (почему остановился на таком решении), вот у нас есть 10 независимых сенсоров, каждый опрашивается "как-то". Как их писать? копить данные и кидать пачку? Собирать все? Тот же Modbus никак не детерменирован, там "секунда" - это размытое понятие.
Системы разные, к тому же уверен, что среднее время выполнения запроса "на строку" будет пропорционально нынешним результатам, просто все дружно станут быстрее.

Т.о. при записи результатов база дополнительно должна выполнить проверку, что такой сенсор существует.

Это Вы делали при записи каждого сэмпла с сенсора? Если да, то это лишнее - т.к. список сенсоров с базы данных можно вытащить один раз, а потом лишь использовать их ID. Тогда уж лучше обрабатывать ошибку при записи (напр., если ID сенсора отсутствует - то создать запись в БД для сенсора, считать его ID, запомнить и использовать дальше).

Вопрос не совсем понял. В базу отправляю три числа: время-значение-id. Просто id ссылается на другую таблицу и база по логике должна проверить, что вставка корректная. Я же при работе не запрашиваю список сенсоров каждый раз. Я один раз их записал, потом считал все id и при записи просто их по-очереди перебираю.

А как Вы конкретно читали данные? По одной строке, или одним запросов все сразу?

Одним
WHERE sID=%d
AND vtime BETWEEN %s AND %s
ORDER BY vtime;

[Artem.spb]

А вот тут зависит от настроек БД. Можно сделать ON DELETE CASCADE опцию, и потом достаточно удалить запись в одной таблице - а остальные строки, которые ссылаются на ту запись тоже удалятся автоматически. А можно эту опцию не использовать, но придется все удалять вручную снизу вверх. Но это опять же, функционал БД движка.

Я таблицу сенсоров не трогал, поэтому эта настройка не принципиальна :)

[Artem.spb]

Дополнение.
По ходу написания понял, что SQLite c индексом вполне шустро работает и запустил длинный тест.
В итоге за полтора года система забила диск (файл базы занимает 27 Гб), но продолжала работать (я остановил когда совсем мало места осталось).
Время записи стабильное

Время чтения имеет интересный перелом, после которого начинает расти.

Время удаления тоже имеет перлом, после которого начинает расти быстрее.

[Chupakabra]

Спасибо за исследование.
А вот интересно как быстро работает MS Access mdb БД в сравнении с SQLite.
Я где-то читал, что Access быстре SQLite. Понятно, что эта БД не обладает такой широтой SQL синтаксиса, как SQLite, но встроенных типов данных в ней побольше чем в SQLite. Например, в SQLite, чтобы хранить лабвьюшный SGL 32 бит нужно использовать либо real (64бит, оверхед по объему базы в 2 раза ), либо BLOB. А СУБД программы не умеют показывать BLOB как пользовательский формат данных из тех, которые я знаю, а иногда хотелось бы. Это немного расстроило.