77. Оптимизация базы данных в Home Assistant.

В системе управления умным дома Home Assistant, одним из ключевых элементов является база данных. От неё зависит многое в работе как программной, так и аппаратной части сервера.

Теоретическая часть.

Recorder.

Это компонент системы,  записывающий в Базу Данных историю изменений состояний устройств и событий, и работающий как "журнал". А так же, он берёт из БД информацию для её отображения в виде текста и/или графиков.
По умолчанию, запись происходит в базу данных SQLite, но может быть настроена работа и с другими, например, MySQL, PostgreSQL, MariaDB и т.д.

База данных Home Assistant - отдельный объект, в который попадают данные представленные Recorder'ом. Из него же эти же данные и берутся чтобы быть отображёнными в системе в качестве истории событий.

Home Assistant по умолчанию использует SQLite в качестве базы данных. Это легковесная реляционная база данных, которая не требует отдельного сервера.

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


Встроенная по умолчанию БД SQLite, в свою очередь состоит из 3 файлов:
Все они находятся по адресу /homeassistant/ и имеют одно и то же имя home-assistant_v2, но разные расширения.
.db - основной файл базы данных
.db-wal - Write-Ahead Logging
.db-shm - Shared Memory

Принцип работы БД SQLite в Home Assistant.

Файлы.

WAL файл (.wal):

Представим, что база данных Home Assistant - это большая книга, в которую записывается всё, что происходит в системе.

Без WAL:
Каждый раз, когда что-то меняется (например, включается свет или меняется температура), нужно открыть эту огромную книгу, найти нужную страницу и внести изменения. Это занимает много времени и может привести к ошибкам, если вдруг в процессе записи выключится свет.

С WAL:
Вместо этого у есть маленький блокнот (WAL файл). Когда что-то меняется, можно быстро записать это в блокнот. Такой способ намного быстрее, чем искать нужное место в большой книге. Время от времени записи из блокнота переносятся в основную книгу (это называется "checkpoint").

Преимущества:
Скорость: Записывать в маленький блокнот быстрее, чем в большую книгу.
Безопасность: Если вдруг выключится свет, информацию не будет потеряна, так как она уже записана в блокнот.

Пример:
Представьте, что ваша умная лампочка включается и выключается 100 раз в минуту. Без WAL вам пришлось бы 100 раз открывать большую книгу и вносить изменения. С WAL вы просто 100 раз быстро записываете это в блокнот. И потом система синхронизирует эти записи с основным файлом БД. 

SHM файл (.shm):

Если продолжать аналогии представления что WAL - это блокнот, то SHM - это закладка в этом блокноте.

Аналогия:
Вы работаете в большом офисе, и у вас есть общий блокнот (WAL), куда все записывают изменения.
SHM - это как специальная закладка, которая показывает, где сейчас можно писать, чтобы никто не писал поверх чужих записей.

Преимущества:
  • Координация: Все знают, где сейчас можно писать, не мешая друг другу.
  • Скорость: Не нужно каждый раз проверять весь блокнот, чтобы найти свободное место.

Пример:
В вашем умном доме есть 10 устройств, и все они хотят одновременно записать свое состояние. SHM помогает им делать это быстро и без конфликтов.


Почему это важно для Home Assistant:
  • Скорость: Ваш умный дом может иметь десятки устройств, которые постоянно меняют свое состояние. WAL и SHM позволяют Home Assistant быстро записывать все эти изменения.
  • Надежность: Если вдруг произойдет сбой питания, вы не потеряете последние данные о состоянии вашего умного дома, так как они уже записаны в WAL.
  • Эффективность: Вместо того, чтобы постоянно обновлять большую базу данных, изменения накапливаются в WAL и потом переносятся в основную базу данных более эффективно.

Однако, есть и нюансы:
WAL файл может расти, занимая место на диске. Это как если бы ваш блокнот для быстрых заметок, со временем, стал толще основной книги.
Для этого система производит обслуживание: "чистит" эти файлы, перенося всё из блокнота в основную книгу и начав новый блокнот. В терминологии базы данных это называется выполнением checkpoint и VACUUM.

Таблицы.

Если вспомнить структуру объектов Home Assistant, то первым делом вспоминаются сущности и их атрибуты.

  • Сущности (Entities): Представьте, что каждая сущность - это карточка в картотеке. На каждой карточке есть основная информация:
    • ID сущности (например, "light.living_room")
    • Текущее состояние (например, "on" или "off" для лампочки)
    • Время последнего обновления
  • Атрибуты: Атрибуты - это дополнительная информация на карточке сущности. Например, для лампочки это может быть яркость, цвет, и т.д.

    • База Данных "видит" эти объекты и связывает их между собой благодаря набору таблиц, в которых содержится вся поступающая информация.

    Таблицы в базе данных — это структуры, которые организуют и хранят данные в виде строк и столбцов, как например в таблице Excel. У каждой таблицы есть определённое назначение и тип данных, которые она содержит.

    Использование нескольких таблиц позволяет лучше организовать данные, обеспечивая быстрый доступ и обработку. Например, чтобы посмотреть историю изменений температуры, Home Assistant будет обращаться только к таблице "states", а не ко всей базе данных сразу. Это ускоряет работу и упрощает хранение данных.
    Таким образом, таблицы помогают структурировать информацию и делают работу с базой данных более эффективной.

    Пример работы таблиц.

    Когда Home Assistant получает данные от устройства, эти данные записываются в соответствующие таблицы. Например, если датчик температуры обновил показания, новое значение будет добавлено в таблицу "states". Если в системе произошло событие, оно попадёт в таблицу "events".

    Таблица "states": Эта таблица хранит историю состояний всех сущностей (устройств) в Home Assistant. Каждая строка в таблице представляет одно состояние устройства, например, когда свет включён или температура изменяется.

    Таблица "events": В этой таблице записываются все события, такие как срабатывание автоматизаций или изменения состояния устройств. Каждая строка — это отдельное событие с указанием времени и типа события.

    Таблица "attributes": Если у сущности есть дополнительные данные (например, цвет лампочки или уровень заряда батареи), они хранятся в этой таблице.

    Таблицы и их размеры после оптимизации

    Запросы к таблицам.

    Взаимодействие с таблицами в базах данных осуществляется с помощью запросов. Запросы представляют собой специальные команды, которые позволяют получать, добавлять, изменять или удалять данные. Язык запросов — это набор правил, по которым строятся эти команды. Существует несколько различных языков запросов, каждый из которых разработан для работы с определенными типами баз данных.

    Примеры языков запросов:

    - SQL (Structured Query Language): Самый популярный язык для реляционных баз данных. Он используется в MySQL, PostgreSQL, Oracle, Microsoft SQL Server и других подобных системах.

    - NoSQL: Включает в себя языки запросов, предназначенные для нереляционных баз данных:
    - MongoDB Query Language (для MongoDB)
    - Cassandra Query Language (CQL для Apache Cassandra)
    - N1QL (для Couchbase)

    - GraphQL: Язык запросов для API, часто применяемый для работы с графовыми базами данных.

    - XQuery: Используется для работы с XML-базами данных.

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

    Каждая система управления базами данных может иметь свои уникальные черты в использовании SQL, которые принято называть "диалектами". SQLite использует свой собственный диалект, который очень близок к стандартному SQL, но имеет некоторые отличия. Например, при работе с базой данных Home Assistant, используя инструменты администрирования или консоль, запросы формулируются в соответствии с особенностями SQLite.

    SQL запросы.

    Запросы к базе данных — это аналог того, как если бы мы давали инструкции библиотекарю в большой библиотеке. С их помощью мы можем попросить найти, добавить, изменить или удалить информацию. 

    Основные типы запросов:
    - SELECT (Выбрать): Аналогично просьбе к библиотекарю найти определенные книги. Например, "Покажи мне все лампочки, которые сейчас включены."
    - INSERT (Вставить): Похоже на добавление новой книги в библиотеку. Пример: "Добавь новое устройство: термостат в спальне."
    - UPDATE (Обновить): Можно представить как изменение информации в карточке книги. Пример: "Измени состояние лампочки в гостиной на 'выключено'."
    - DELETE (Удалить): Это как попросить убрать книгу из библиотеки. Пример: "Удали информацию о старом датчике, который мы больше не используем."

    Как работают запросы:
    1. Формулируем запрос, например: "Покажи мне все лампочки, которые сейчас включены."
    2. База данных переводит запрос на свой внутренний язык.
    3. Затем она ищет нужную информацию в таблицах.
    4. И наконец, возвращает результат, показывая найденные данные.

    Пример запроса:
    Запрос: `SELECT * FROM states WHERE entity_id LIKE 'light.%' AND state = 'on';`

    Расшифровка:
    - `SELECT *`: Показать все детали
    - `FROM states`: Из таблицы состояний
    - `WHERE entity_id LIKE 'light.%'`: Где идентификатор начинается с 'light.' (то есть, это лампочка)
    - `AND state = 'on'`: И состояние — 'включено'

    Этот запрос можно представить как просьбу к библиотекарю: "Покажи мне все книги про лампочки, которые сейчас горят."

    Промежуточный итог.

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

    Почему это может быть плохо? 
    • Для пользователя: Визуальный и смысловой перегруз большим количеством ненужной информации. Легче потеряться и сложнее найти нужное в истории.
    • Для системы: Чем больше сущностей и их атрибутов, тем больше информации пишется в базу данных. Чем больше БД, тем больше времени системе нужно чтобы найти в ней нужную информацию. Чем больше файл БД, тем меньше свободного места на диске, которое можно было бы использовать для других вещей.
    • Для жёсткого диска. Если система записывает условно "каждый чих и вздох", то это увеличенное количество обращений к жёсткому диску. Если записывается информация о ненужных событиях и/или сущностях, то получается неоправданный износ жёсткого диска (у каждого диска есть не только гарантийное время по часам работы, но и количество обращений к нему на чтение/запись). При этом не имеет значения установлена система сразу на железо или же речь идёт о виртуальной машине или контейнере.

    Теория решения проблемы.

    Есть 2 ключевых подхода, со своими плюсами и минусами. Рассмотрим подробнее каждый из них.

    1. Настройка Recorder на запись меньше чем 10 дней истории.
    Преимущества:
    - Максимальная простота и минимальное время настройки.

    Недостатки:
    - Нет долгосрочной истории ни у какого события/сущности.
    - Записи в БД происходят с той же частотой как и до изменения.
    - Файл БД будет расти по мере увеличения количества сущностей и информации ими предоставляемой.
    - Как следствие предыдущих двух пунктов, "износ" жёсткого диска остаётся на том же уровне.

    2. Настройка Recorder с фильтрацией.
    Преимущества:
    - Остаётся долгосрочная история у событий/сущностей.
    - Время хранения истории можно увеличить. 
    - Записи в БД происходят гораздо реже.
    - Файл БД растёт крайне медленно.
    - Как следствие предыдущих двух пунктов, "износ" жёсткого диска значительно уменьшается.

    Недостатки:
    - Требует много времени на изучение того что фильтровать, а что нет.

    Практическая часть.

    Т.к. цель статей на этом сайте это не только знания, но и получение максимально стабильной системы, то рассматривать в качестве решения будем конечно же второй подход. На самом деле, всё описано в документации по ссылке приведённой в начале этой статьи. А именно, тут.
    Существуют всего 2 основных правила фильтрации: ВКЛЮЧИТЬ (include) и ИСКЛЮЧИТЬ (exclude) тот или иной объект из/в записи в БД.

    Как можно понять из прилагаемого скриншота, эти правила распространяются на 3 типа объектов:
    1. Домены целиком (сенсоры, бинарные сенсоры, переключатели и т.д.)
    2. Сущности по отдельности.
    3. Глобальные сущности по маске.

    Чтобы знать какие из объектов занимают больше всего места в базе данных, необходимо установить соответствующий инструмент - SQLite Web. Это дополнение (addon), которое позволяет работать с базой данных.


    Можно установить его из каталога дополнений, или же сразу по этой кнопке


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


    В нём видно как список таблиц, так и их количество, и общий размер самой БД. Есть возможность при желании создать новую таблицу. И самое главное - в нижней части, окно ввода запроса.

    Для начала можно выполнить запрос отображающий топ 30 сущностей, данные которых занимают больше всего места:



    SELECT
      states_meta.entity_id,
      COUNT(states.state_id) AS record_count,
      ROUND(SUM(LENGTH(state_attributes.shared_attrs)) / 1024.0 / 1024.0, 2) AS data_size_mb
    FROM
      states
    LEFT JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id
    LEFT JOIN states_meta ON states.metadata_id = states_meta.metadata_id
    GROUP BY
      states_meta.entity_id
    ORDER BY
      data_size_mb DESC
    LIMIT 30;

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

    Крайне важно!!!
    После добавления исключений в файл конфигурации, сохранить его и перезагрузить систему. 

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

    Дополнительные примеры.

    Так же можно выполнить запрос, который покажет размер каждой таблицы внутри БД: 

    SELECT 
        name AS table_name,
        ROUND(CAST(SUM(pgsize) AS FLOAT) / 1024 / 1024, 2) AS size_mb
    FROM 
        (SELECT name, pgsize FROM dbstat WHERE name IN (
            'event_data', 'event_types', 'events', 'migration_changes', 
            'recorder_runs', 'schema_changes', 'sqlite_stat1', 
            'state_attributes', 'states', 'states_meta', 'statistics', 
            'statistics_meta', 'statistics_runs', 'statistics_short_term'
        ))
    GROUP BY 
        name
    ORDER BY 
        size_mb DESC;

    Сумма этих размеров должна быть равна размеру самой БД.

    Если по какой-то причине сумма размеров таблиц (значительно) меньше размера БД, это говорит о том, что часть данных ещё не была перенесена из WAL файла в основной файл. 
    Чтобы это проверить, выполняем запрос ускорить выполнение этой процедуры, необходимо выполнить checkpoint:

    PRAGMA wal_checkpoint(FULL);


    busy: 0 - означает, что в данный момент нет активных транзакций, которые блокируют выполнение checkpoint.
    log: 227 - количество фреймов (записей) в WAL файле, которые содержат изменения.
    checkpointed: 227 - количество фреймов, которые уже были перенесены (checkpointed) в основную базу данных.

    Все записи в WAL (227) уже были перенесены в основную базу данных (также 227), что означает, что WAL файл не содержит не применённых изменений.
    Несмотря на то, что все изменения применены, WAL файл все еще существует и может занимать значительное место на диске.
    Поэтому выполним полный checkpoint:

    PRAGMA wal_checkpoint(TRUNCATE);

    Это не только применит все изменения (что уже сделано), но и попытается уменьшить размер WAL файла.
    После выполнения checkpoint, следует проверить размер WAL файла, он должен значительно уменьшиться или даже стать нулевым.
    Теперь можно выполнить команду :
    VACUUM;
    Это поможет оптимизировать основной файл базы данных.

    Внутренний инструмент Home Assistant для работы с БД.

    После того как была проделана тяжёлая работа по отсеиванию ненужных сущностей, можно перепаковать базу данных. Однако перед финальной очисткой стоит понимать пару важных вещей:
    - Если какая-то сущность писала много информации в БД, и сейчас добавлена в список исключений, то предыдущие данные записанные ей всё ещё находятся в базе. А соответственно занимают место и влияют на размер БД.
    - Чтобы избавится от этих данных естественным путём, необходимо чтобы прошло время указанное в настройках по удалению старых данных (10 дней по умолчанию).
    - Можно написать запрос (ИИ чатботы в помощь) на удаление данных для каждой исключённой сущности.
    - Можно очистить (обнулить) базу от всех записей и начать с "чистого листа". 

    Обнуление базы можно сделать как с помощью запроса, так и с помощью средств самого Recorder. Кстати, очистить базу от данных старше Х дней тоже можно с помощью него же.

    Переходим в раздел действий средств разработчика и ы вызываем соответствующий сервис. В котором указываем всё то, что до этого указали в конфигурационном файле. Тыц


    Выбираем объекты для удаления, указываем 0 дней и готово.


    После чего обратимся к другому сервису того же инструмента. Тыц


    Здесь количество дней не трогаем, но отмечаем перепаковку и запускаем.

    Все, особенно первичные, процедуры по удалению информации из Базы Данных и её перепаковке могут занять длительно время. Это зависит как и от количества удаляемых объектов, та и дополнительной информации к ним относящейся.

    Комментарии

    Отправить комментарий