Введение
На случай, если ещё не встречались с HA (Home Assistant) - это opensource веб сервис для умного дома, который позволяет подключить к себе кучу всяких устройств и настроить для них любые желаемые автоматизации. Например, открывать ворота при вашем приближении, заваривать кофе, когда ваш умный браслет понял, что вы проснулись, или автоматически кормить кошку по праздничным дням календаря.
Сегодня мы поговорим о том, какую СУБД (Систему Управления Базы Данными) для него лучше выбрать. Потому что очень часто в чат по HA приходят новички, и спрашивают, что им делать с MySQL, а им в ответ говорят, что они наркоманы и нанюхались одного известного видео с ютуба. А почему такая реакция, и что делать - начинающему автоматизатору понять довольно сложно без довольно специфического багажа знаний в айти. Так что надеюсь, что эта статья кому-то поможет.
Итак, Home Assistant написан с использованием SQLAlchemy - грубо говоря, это библиотека, которая в теории позволяет приложению использовать любую СУБД. Все возможные на свете варианты мы рассматривать не будем и ограничимся тремя вариантами - MariaDB, PostgreSQL, и вариант, который идёт в HA по умолчанию - SQLite. MySQL отдельно упоминать не буду т.к. различия с MariaDB до сих пор не такие значительные.
Частые аргументы
В статьях и видео, которые рекомендуют установку MariaDB/MySQL, в лучшем случае говорится, что она быстрее, чем SQLite. На этом сравнение заканчивается, и мы бодро идём ставить MariaDB. Но... Так ли это?
Если мы посмотрим бенчмарки на чтение данных, то совершенно внезапно оказывается... Что SQLite как минимум не медленнее, чем остальные два наших конкурента. В теории, он может оказаться медленнее на больших наборах данных, однако сколько данных - много? На практике оказывается, что много - это порядок десятков гигабайтов. А теперь посмотрите на свой размер базы. Моя, при условии большого количества устройств и истории, весит не более 200 MB...
Ну ладно, наверное не просто так люди MariaDB ставят. Видимо, есть какие-то ещё преимущества.
Хмм, может, то, что SQLite не очень хорошо работает с параллельными процессами записи?
Неа. Довольно сложно представить себе умный дом с настолько частыми операциями записи. Кроме того, бутылочным горлышком фактически будет ваш HA, который написан на питоне, который обычно использует довольно мало потоков. Более того, используется SQLAlchemy, который в целом умеет в многопоточность, но только если вы специально писали код с расчётом на неё. Как вы думаете, занимались ли этим разработчики HA? Честно скажу - свечку не держал - но навряд ли.
Ну, ладно. Может быть, другие решения надёжнее, чем SQLite, и ваша база упадёт с меньшей вероятность?
Ммм. Ну ваще нет. Нужно очень сильно постараться, чтобы запороть SQLite базу. Она разработана как раз с рассчётом на внезапное падение программы, ОС или выключение устройства. Между прочим, SQLite точно используется сейчас в вашем телефоне на андроиде - представляете, что она там пережила?
В то время как MariaDB и PostgreSQL в конфигурации по умолчанию имеют довольно неплохие шансы накрыться при некорректном завершении работы - например, при выключении сервера кнопкой или отключении электричества.
Хорошо. Ну может хотя бы с бекапами будет всё лучше?
Боюсь расстроить, но тут тоже подстава. Для бекапа SQLite достаточно на горячую (при запущенном HA) скопировать файлик базы. А вот если вы скопируете файлы PostgreSQL или MySQL на горячую, то они у вас просто не заведутся. И нужно или останавливать СУБД или пользоваться специальными инструментами экспорта...
Если вам этого мало
Помимо этого, есть ещё не очевидные плюшки:
Установка с видео выглядит легко и просто для пустого HA. Если у вас уже есть данные, то всё становится гораздо интереснее!
Обратная миграция на SQLite будет далеко не простой. Если, конечно, вы хотите сохранить данные базы. Если нет - просто снесите
recorder.db_urlи выключите эддоны, если они были включены - и после перезапуска жизнь начнётся с нового листа с записью в SQLite.Разработчики HA тестируют новые релизы на SQLite. И если у вас вдруг что-то сломается, то вам посочувствуют полтора инвалида, и разбираться с этим вы будете самостоятельно.
Когда вы разделяете конфиг и базу, вам придётся заботиться о консистентности бэкапов. Иначе вы получите базу с лишними сущностями, или же наоборот - конфиг без данных.
Так почему все не используют SQLite?
Может возникнуть вопрос, почему же тогда SQLite не используется везде, раз остальные СУБД такие плохие.
Они не плохие. У них просто принципиально другое назначение:
Они могут использоваться с множества других машин.
Поддерживают многопоточную запись данных.
Поддерживают шардирование и репликацию.
Могут восстановить данные на произвольную точку времени.
Рассчитаны на использование сотнями, тысячами и даже большим количеством клиентов.
Могут успешно оперировать терабайтами данных.
Предлагают дополнительные типы данных, которые часто бывают востребованы в разработке всякой конкретики.
И ещё много-много других вещей. Которые никак вам не пригодятся в случае HA.
И всё же - в каком случае мне стоит поставить Maria/PostgreSQL?
Use cases бывают разные, и, может быть, вы - один из тех, кому это надо? Я накидал примерный чек лист:
Вы являетесь квалифицированным разработчиком, DevOPS или DBA.
У вас уже есть Maria/PostgreSQL, и вы уже его настроили и бэкапите.
У вас база данных HA на десятки гигабайт (если да, скажите, пожалуйста, в комментариях, зачем).
Вы используете БД для каких-то внешних запросов из другого приложения.
Вы хотите получать интересный незабываемый опыт.
В таких случаях - да, может быть и стоит.
А сам ты чего добился?
Всё это может прозвучать тухло без указания личного опыта. Да, давайте признаюсь - у меня HA стоит на PostgreSQL. Ровно потому что
Мне хотелось посмотреть, как это будет работать
У меня уже крутится несколько инстансов Postgres
Сам я являюсь веб разработчиком и DBA
Так же умею настраивать и восстанавливать бэкапы Postgres
И в целом люблю делать троллейбус из буханки хлеба
При этом, смигрировав свою базу на 200MB, я не заметил ровно никакого эффекта по росту производительности. И прекрасно отдаю себе отчёт в том, что в любой момент это может выстрелить мне в ногу и придётся смигрировать обратно или снести все данные и начать жизнь с нового листа.
Что делать, если я уже на альтернативной СУБД?
Мигрировать обратно будет довольно больно. Если у вас пока мало данных - лучше снести всё и поставить заново.
Если много, то лучше озаботиться регулярным бэкапом, дополнительно его делать перед каждым обновлением и быть готовым к тому, что всё превратится в тыкву.
Но у меня всё работает медленно, как ускориться?!
Не факт, что виновата СУБД. Можно попробовать следующее:
Писать показания датчиков реже.
Поиграть с настройками recorder и перестать записывать лишнее.
Сменить устройство, на котором у вас стоит HA, если это китайский огрызок компьютера, который не вытягивает по ресурсам.
Проверить, используется ли у вас своп. Если да - поиграть с настройками или добавить оперативной памяти.
Если ничто не помогает - посмотреть, наконец, что пишется в логах :)
Страшные ссылки
Если вы думаете, что я сгущаю краски, то вот подборка:
На форуме HA до сих пор нет ответа, как бэкапить базу на MariaDB
Ну ладно, на самом деле есть, но вы можете посмотреть, как это недружелюбно выглядит.
Можете посмотреть пост про миграцию на postgres. Главное - не забудьте открыть комментарии и понять, что на самом деле там куча возни с внезапно лишними полями и не импортированными нормально sequences.
Или посмотреть на другую историю миграции и количество плясок с бубном.
На закуску - тред боли, где при обновлении у многих людей полегла MariaDB и они искали способ откатиться обратно.
Заключение
Почему я не стал писать здесь историю своей миграции? Просто для абсолютного большинства пользователей очевидный win-win выбор это SQLite. А остальные в целом должны обладать достаточной квалификацией, чтобы смигрировать сами. Потому что если они ей не обладают - то не смогут поддерживать такую установку и оперировать бэкапами базы.
На этом, наверное, всё. Буду рад, если вы расскажете мне о своих причинах, успехах и неудачах использования альтернативных СУБД. Если история будет убедительной, то готов выложить инструкцию по миграции на PostgreSQL с сохранением данных и с автоматизированными бекапами :)
Комментарии (53)
Iron_Butterfly
13.01.2024 04:25Слышал такую историю, что для внешнего доступа ставят Nginx Proxy Manager, который для своей работы требует наличие работающей MariaDB. Ну раз её приходится ставить, то и почему бы и базу заодно на неё не перенести, чтобы зазря не простаивала :) Понятно, что для внешнего доступа можно разные решения использовать, в том числе и без необходимости БД, но вот такой кейс встречался.
jehy Автор
13.01.2024 04:25Вполне валидный вариант, да. Поэтому я указал, что если база всё равно есть, и вы её умеете обслуживать и делаете это, то почему бы и нет.
empenoso
13.01.2024 04:25+2А зачем что-то менять?
По умолчанию в HA SQLite - вроде без нареканий всё работает...
jehy Автор
13.01.2024 04:25Вы абсолютно правы, про это собственно и есть статья. Просто многие ставят по гайдам, где с наскоку без объяснений ставится Мария, а потом страдают, хотелось помочь этим людям.
Allineer
13.01.2024 04:25Нужно очень сильно постараться, чтобы запороть SQLite базу. Потому что это всего лишь файл с запросами в текстовом виде.
Чего? О_о
jehy Автор
13.01.2024 04:25Ответ ниже, к сожалению, я перепутал комментатора с другим и таки ответил ему. Хотя такие формулировки вопросов крайне демотивируют к пояснению чего-либо.
Chupaka
13.01.2024 04:25Переформулирую в мотивирующем ключе: где у SQLite файл в текстовом виде?
jehy Автор
13.01.2024 04:25Сходите по ссылке, там всё подробно объяснено.
jehy Автор
13.01.2024 04:25UPD. Спасибо @Chupaka, показал, где я не прав, в тексте статьи некорректное утверждение удалено.
peleccom
13.01.2024 04:25После нескольких лет и выброшенных sdcard на raspberry pi запустил mariadb в google cloud. Теперь записей на диск стало меньше и карта живёт значительно дольше. Осталось только что то подобное с логами провернуть
Heggi
13.01.2024 04:25+1Зачем иметь локальный HA с базой в облаке? Засуньте и HA в облако тогда. Весь смысл локального HA в том, чтобы даже без интернета ваш умный дом не превращался в тыкву.
jehy Автор
13.01.2024 04:25Ну кстати тут интересный вопрос того, как HA использует базу. Кажется, он подтягивает в себя оперативные данные, а в базу лезет только для периодической записи и давних исторических данных. Если это так, то отвал базы на некоторое время не особо повредит и не сломает текущие сценарии. Но это только гипотеза.
buldo
13.01.2024 04:25Интересно. Ваш опыт кардинально отличается от моего. Моя первая установка HA была на rpi. Ставил просто оригинальный образ. Не помню, что за БД была выбрана, но дополнительно стоял ещё Influx. Проблем с флешкой не было.
AlexDi99
13.01.2024 04:25Привет! Например хочу вернуться с Марии на стандартный sqlight. Пусть все пишется с нуля...
Что надо сделать для этого? Как выкарчевать? )
jehy Автор
13.01.2024 04:25Болезненной будет миграция с сохранением данных.
Без неё достаточно recorder. db_url снести - и жизнь начнётся с нового листа с записью в sqlite.
RichardBlanck
13.01.2024 04:25+1Довольно странная статья. Сравнивать базы данных по объёму в байтах - это, скажем так, странный подход. Заставляющий усомниться в компетентности автора.
Да, SQLite действительно медленнее MySQL, и сильно. Разница в скорости чтения становится заметной вполне отчётливо при размерах таблиц в первые сотни тысяч записей. Разница в скорости записи - при размерах в первые десятки тысяч. При этом объём базы данных может быть мегабайты, не больше. Параллельный доступ к одной базе в SQLite вообще невозможен.
При этом это всё совершенно не аргументы против использования SQLite - это замечательная вещь, поистине гениальная. Но она имеет вполне определённую область применения, вне которой её использовать не надо.jehy Автор
13.01.2024 04:25+1Интернет с вами не согласен - люди вполне успешно используют SQLite для баз данных в гигабайты, а на найденных мной бенчмарках по чтению он оказывается быстрее, чем mysql. Но конечно бенчмарки можно очень по разному делать, поэтому над официальными на сайте sqlite даже подписано большими буквами, что это старая информация. А новой нет - видимо, чтобы не провоцировать срачи :)
Но у меня нет цели заводить холивар, я написал ровно то же самое, что вы - что разные базы подходят для разных способов использования. И рассматривал именно контекст применения в стандартной инсталляции Home Assistant.
Опять же, размер становится понятной метрикой, когда мы говорим о конкретной структуре таблиц и хранении данных, которая в данном случае у нас имеется (HA). Так-то понятно, что забивать базу неиндексируемыми блобами или искать по миллионам текстовых записей - принципиально разные задачи :)
RichardBlanck
13.01.2024 04:25Вы упорствуете в измерении размера базы данных в байтах. Т.е., вы очевидно не понимаете, что это полностью бессмысленно.
И именно в контексте Home Assistant я вижу, что SQLite -- не лучшее решение, а MySQL подходит гораздо лучше. Я бы даже сказал, что для Home Assistant SQLite не годится, а применять следует исключительно MySQL.
stigory
13.01.2024 04:25Вобщем все равно на разницу в скоростях СУБД. У НА бутылочное горлышко явно не там. А глядеть в History выборку по всем объектам за все время - то ещё извращение.
Но, лично у меня SQLite в прошлом году валилась дважды с потерей данных, заставив меня обратиться к документации по recorder и тому как вообще устроено хранение метрик и истории в хоумассистант. Что сильно разочаровало. Я то думал что хранится все и навсегда.
Так что теперь рекордим в MariaDB, который и так вертится на отдельном хосте. И потихоньку мастерим свои RP и CQ в InfluxDB для увековечивания важных метрик для потомков.jehy Автор
13.01.2024 04:25А вы выяснили, почему sqlite валился? И как он валился? Частичная потеря или прям всё в Тартар?
И чисто из любопытства - если не секрет, что за метрики вы хотите хранить для потомков? :)
stigory
13.01.2024 04:25+1Database corrupt. Стандартные средства SQLite по восстановлению не справились.
Глубоко не копал. Полез на форума HA, увидел там вой на лайт и направление в сторону "мускула". Меня устроило. Мария уже есть. Репликация, бэкап, способы лечения понятны. Долго выбором не страдал.Хранить хочу данные по энергопотреблению здания и климату. Чтобы хотя бы руками потом делать аналитику по климату и теплопотерям.
jehy Автор
13.01.2024 04:25Не думал, что в РФ такие суровые потребители HA. Рассчитывал, что в среднем по больнице народ просто лампочки и розетки выключает. Хотя это наверное так и есть, тут играет ошибка выжившего - писать комментарии на хабре доходят только довольно суровые домовладельцы :)
petropavel
13.01.2024 04:25+1Выводы правильные, "просто используйте SQLite". Аргументы — неправильные.
MariaDB/MySQL быстрее, чем SQLite — и да и нет. Вопрос в деталях. Если запустить бенчмарк (тот же sysbench) c тысячами запросов в секунду и сотнями подключений — SQLite будет вообще не конкурент. Для HA же SQLite более чем достаточно почти всегда.
SQLite не очень хорошо работает с параллельными процессами записи?
Неа. Вот как раз Да-а. Но где вы в HA видели сотни параллельных процессов записи? То есть это реальный недостаток SQLite, но в данном случае он не играет роли.Нужно очень сильно постараться, чтобы запороть SQLite базу. Потому что это всего лишь файл с запросами в текстовом виде. Про это уже писали выше, полная чушь. SQLite — полноценная база данных, со страничной оранизацией данных. db разбит на страницы, там всё бинарное. Есть undo log и wal, как положено, для восстановления, если что. Вот как раз текстовый файл запороть — как два пальца.
В то время как MariaDB и PostgreSQL имеют довольно неплохие шансы накрыться при некорректном завершении работы. Плохие-плохие, восстановление данных после краша — это стандартная функциональность и MariaDB и PostgreSQL, она регулярно тестируется и регулярно же используется в проде (к сожалению). Это буква D в "ACID". Накрыться может всё, SQLite тоже, но шансы невелики.
Для бекапа SQLite достаточно на горячую (при запущенном HA) скопировать файлик базы. Как почти с любой базой данных — не-а, это хороший способ запороть базу (если уж выключение питания не помогло).
нужно или останавливать СУБД или пользоваться специальными инструментами экспорта — везде так, SQLite не исключение. Но там ничего сложного, запустил команду — получил бэкап, всё легко заскриптовывается.
jehy Автор
13.01.2024 04:25-1Ох, вы точно мою статью читали?
Для HA же SQLite более чем достаточно почти всегда.
Так я ровно это и писал.
Но где вы в HA видели сотни параллельных процессов записи
И именно это у меня тоже и написано.
Про это уже писали выше, полная чушь.
Окей, тут я старался упростить максимально и видимо получилось плохо. Подумаю, как переписать. Имелось в виду, что в sqlite текстовый формат хранения базы в одном файлике, в котором имхо сложнее продолбать данные неискушённому пользователю, чем в бинарном.
Плохие-плохие, восстановление данных после краша — это стандартная функциональность и MariaDB и PostgreSQL
Хорошо восстанавливается только корректно настроенная СУБД. К сожалению, дефолтные настройки рассчитаны на некритичные данные, и на них база с лёгкостью помирает невозвратно. Можете посмотреть SO - там у народа регулярно безвозвратно погибает база, и они "лечат" это её удалением.
не-а, это хороший способ запороть базу
Это должно прям хорошо повести, чтобы в момент записи копию снять. Рекомендация говорит переписывать файл БД раз в минуту, кажется. Ну и опять же - бэкап должен быть не в единственном числе, так что если в этом вы умудрились попасть в интервал - возьмите соседний бэкап. Так что можно считать, что снимать копию на горячую - норм. С полноценными СУБД это невозможно в принципе.
Но там ничего сложного, запустил команду — получил бэкап, всё легко заскриптовывается.
Для вас и меня - ничего сложного. Но я рассчитывал на аудиторию, которая по ютубу HA ставит и не умеет в консоль и скрипты.
Allineer
13.01.2024 04:25Имелось в виду, что в sqlite текстовый формат хранения базы в одном файлике
Чего? о_О
Я не понимаю, вы серьезно в это верите и с этим пришли на хабр со статьей о выборе оптимальной СУБД?
jehy Автор
13.01.2024 04:25-2Ох, что ж вы так душните-то, неужели надо разжевать всё?
Хорошо, раз надо - HA периодически экспортирует базу SQLite из её бинарного стораджа в текстовый файл с SQL запросами. Пользователи при этом вообще никогда не взаимодействуют с чем-либо кроме текстового файла, и даже в документации по HA написано
The default, and recommended, database engine is SQLite which does not require any configuration. The database is stored in your Home Assistant configuration directory (’/config/’) and is named home-assistant_v2.db.
Пишут они это ровно по той же причине, почему я это пишу у себя в статье в таком виде - пользователю HA никогда не понадобится работать с базой SQLite в любом другом виде, и для него файл СУБД - это именно текстовый файл.
Allineer
13.01.2024 04:25Вы точно хотели процитировать именно эту часть документации в качестве аргументации вашей позиции?
Allineer
13.01.2024 04:25Коль уж меня душным назвали.
Все три упоминаемых вами СУБД используют бинарный формат хранения данных.
За исключением наверное csv storage engine для таблиц mysql/mariadb. Но там он только для отдельной таблицы и совсем не в виде запросов.
mysql так же умеет экспортировать базу в файл с тектовыми запросами - это называется дампом, но не является базой данных.
Поэтому ваше утверждение в статье заведомо некорректно:
Нужно очень сильно постараться, чтобы запороть SQLite базу. Потому что это всего лишь файл с запросами в текстовом виде.
Ваш последующий комментарий тоже некорректен:
в sqlite текстовый формат хранения базы в одном файлике
Но вы решили спорить дальше.
jehy Автор
13.01.2024 04:25-2Вот вы упорный.
Ладно, давайте совсем на пальцах.При выборе настоящих СУБД в HA пользователь получает только бинарный формат данных, и делать дампы ему приходится самому.
При выборе SQLite он получает из коробки автоматический дамп, который и считает файлом своей базы данных.
Так понятно?
Chupaka
13.01.2024 04:25+2В какой текстовый файл HA экспортирует базу SQLite? Или вы в Midnight Commander нажали на файле F3 и увидели текстовые запросы? Тогда Shift+F3 вас сильно удивит
jehy Автор
13.01.2024 04:25А вы правы, ровно так и было, однако. У меня и мысли не возникло, что в mc есть встроенный вьювер для этого формата. Так что да, фигни написал, поправлю. Спасибо большое за конструктив :)
petropavel
13.01.2024 04:25Конечно, читал. Я ж с этого комментарий и начал: выводы правильные ☺
И, опять, SQLite — полноценная база данных. В MariaDB, например, если подождать, пока она ничего не будет делать, тоже прекрасно можно файлы копировать, ничего не сломается. От нагрузки зависит, повезёт или нет.
RicoX
13.01.2024 04:25+1Для всех метрик использую викторию, т.к. умеет нормально жать данные и можно хранить часто меняющиеся метрики очень долго не забивая место на карте памяти, для остального мария, т.к. она все равно нужна для других целей мне удобней все держать в одной базе и бэкапить целиком. Хотелось бы конечно плагин для работы с ClickHouse чтоб иметь единую базу и для метрик и для данных, но тут вариант только напилить самому, т.к. комьюнити это не особо интересно.
jehy Автор
13.01.2024 04:25Драйвера ClickHouse+sqlAlchemy есть, так что в целом не должно быть сложно. Возможно, прокатит просто доставить пакет через pip и прописать адрес кликхаус сервера.
Chupaka
13.01.2024 04:25Я когда переезжал из SQLite на PostgreSQL, через пару недель заметил непонятную хрень. Присмотрелся — там действительно было что-то вроде подстановки одних исторических данных в графики других энтити. Пытался редактированием базы проблему решить, но полностью избавился лишь переездом на чистую базу, без сохранения истории. Благо, исторические данные в Victoria Metrics лежат.
jehy Автор
13.01.2024 04:25А чем вы данные переносили? Возможно, sequences некорректно выставились, данные могли куда попало привязываться в результате.
Chupaka
13.01.2024 04:25Наверное, PgLoader, как и все. От 15-й версии Постгри.
jehy Автор
13.01.2024 04:25Ну вот он у меня без маппига руками не смог перенести автоинкрементные поля, и сделал их обычными числовыми. При этом я PgLoader пробовал и версию из репозитория убунты и самую последнюю. Маппинг мне было лень делать - легко ошибиться - поэтому я структуру взял с новой инициализации, поверх пролил данные (чуть пострадав из-за разного количества полей) и поправил счётчики сиквенсов. Так норм.
Chupaka
13.01.2024 04:25Ну, я тоже инициализировал базу самим hass и в готовую лил данные. В счётчики никакие не лез...
sHaggY_caT
у меня mysql, потому, что Home Assistent живет в k3s, и уже есть HA MySQL (от гугла, у него есть дешёвый cloudsql), и потому, что были планы жить в stateless deployment, не в statefulset, но это пока у меня не реализовано. Хочу попробовать InfluxDB добавить, кажется, тогда можно будет отказаться от pvc, сделать сам home assistent Stateless.
Тогда будет не важно что там при бэкапе сломается - бэкапить средствами MySQL и influxdb
Allineer
Посмотрите в сторону VictoriaMetrics вместо InfluxDB.
jehy Автор
Ну вот по вашему комментарию видео, что вы справитесь с кастомной базой данных, да :)