Cómo optimizar la base de datos de Home Assistant
Photo by Anna Auza on Unsplash

Cómo optimizar la base de datos de Home Assistant

En este post vamos a trabajar la optimización de base de datos sqlite en Home Assistant, y a obtener el máximo provecho de nuestro sistema domótico… con Paciencia

Optimización de base de datos sqlite en Home Assistant. Iremos viendo etapa por etapa las distintas mejoras que podemos realizar en la optimización de la base de datos sqlite en Home Assistant, y las herramientas que usaremos, de manera fácil para que podamos replicarlo en nuestro propio equipo, maximizando el rendimiento de la base de datos de Home Assistant

Sigue leyendo, pues entre otras cosas nos adentraremos con ejemplos en el mundo de las consultas SQL contra sqlite, la configuración con ficheros yaml, o cómo podemos alargar la vida útil de nuestra tarjeta microSD (si es nuestro caso).

Asimismo, si eres de los que empiezan ahora, sería conveniente revisar antes este otro post: Paciencia Digital. Domótica y datos para el hogar

El hardware necesario es el siguiente (bien opción todo incluido o solo lo principal) para poder realizar la optimización de base de datos sqlite en Home Assistant es el siguiente:


Paso 1. Optimización de base de datos sqlite en Home Assistant. Obtenemos el fichero de sqlite

Lo primero que necesitaremos para la optimización de base de datos sqlite en Home Assistant es ver el estado en el que se encuentra.

La sencillez de sqlite permite que toda la estructura (tablas, vistas, etc) y los propios datos se guarden en un único fichero. En Home assistant normalmente se usa sqlite, y dicho fichero se llama home-assistant_v2.db.

Para descargar este fichero, si no tenéis Samba instalado, una manera muy simple es descargalo usando el add-on File editor que seguramente ya tendréis instalado, pero por si acaso os dejo esta entrada con lo básico.

optimización de base de datos sqlite en Home Assistant

El fichero home-assistant_v2.db está en el directorio config. Simplemente seleccionamos en el menú y elegimos descargar:


Paso 2. Optimización de base de datos sqlite en Home Assistant. Navegando por nuestra base de datos

Con la base de datos ya descargada en nuestro ordenador, para seguir con la optimización de nuestra base de datos sqlite en Home Assistant, necesitaremos un visor de sqlite para poder navegar por la enorme cantidad de datos que tenemos acumulados.

Mi preferido, y el que usaremos, es este visor gratuito llamado «DB Browser for SQLite«, con opciones de descarga con instalación o sin ella.

En nuestro caso, al tratarse de un uso puntual, preferimos usar la versión portable sin instalación (descargar, descomprimir, usar y listo. Luego borramos todo y aquí no ha pasado nada, ni tocado registro, ni consumido espacio, ni dejamos cosas escondidas)

Desde esta aplicación, vamos a seleccionar en el menú File el fichero de base de datos anteriormente descargado.

En esta ocasión en modo «Read Only» para poder jugar sin preocuparnos de modificar nada, aunque también se puede previamente hacer una copia del fichero o descargar de nuevo desde Home Assistant:

Existen 2 tablas principales: una llamada states que guarda todo lo que son datos numéricos y atributos, y la tabla events que se ocupa de almacenar los eventos puntuales (por ejemplo cambios de estado).

Veamos la tabla states seleccionando en la pestaña Browse Data del visor de sqlite:

El funcionamiento del visor es muy intuitivo. Por ejemplo podemos filtrar y obtener los datos como fichero csv para un análisis posterior:

El tema de descarga de datos para usarlos en otros análisis, lo desarrollaremos mejor en otro post donde veremos para qué nos puede servir ese fichero .csv.

Ya os adelanto que es un post muy chulo , donde mezclamos conceptos de estadística y nuestro sistema domótico para hacer un poco de magia.


Paso 3. Optimización de base de datos sqlite en Home Assistant. Analizamos con consultas SQL

Por ahora sigamos con nuestro objetivo de optimización de base de datos sqlite en Home Assistant. No es extraño que el fichero ocupe varios gigas, pues por defecto Home Assistant guardará de todo (estados de sensores, de templates, ajustes de hora, etc, etc, etc.)… a menos que le digamos que no queremos.

El incluir todo es, por una parte muy útil, pero al no tener recursos infinitos, nuestro sistema cada vez ocupará más en el disco duro o la tarjeta, y se volverá más lento al tener que lidiar con un fichero tan grande o en los arranques, y no hablemos ya cuando queramos encontrar algo en las secciones Logbook o History de nuestro frontend en Home Assistant.

Por poner un ejemplo concreto, no creo que nos interese llenar nuestra base de datos con registros cada vez que sincronicemos hora en internet.

Por tanto, vamos a aprovechar para identificar si hay sensores que no nos interesan y que nos ocupan mucho sitio, y le diremos al sistema que los excluya.

En la pestaña Execute SQL vamos a ejecutar la siguiente consulta SQL que nos mostrará las 10 entidades que más registros tienen en el fichero descargado. Simplemente escribimos o copiamos la consulta y ejecutamos

SELECT entity_id, COUNT(*) as count FROM states GROUP BY entity_id ORDER BY count DESC LIMIT 10

SQL es un lenguaje de consultas de bases de datos universalmente usado. En la que hemos usado le decimos que nos muestre entity_id y el conteo de registros (SELECT) de cada uno de ellos, de la tabla states (FROM) y agrupados por entity_id (GROUP BY). Por último le decimos que lo ordene todo por el conteo de registros (ORDER BY) con orden descendente (DESC) y con un límite de 10 resultados (LIMIT).

Dependiendo del tamaño de vuestra base de datos el resultado puede tardar varios minutos.

El listado que os aparecerá en la aplicación, os lo presento una vez copiado y puesto en un gráfico de excel con más de 10 resultados para que se aprecie mejor en nuestra tarea de optimización de base de datos sqlite en Home Assistant:

Lo que ya nos imaginábamos: unos pocos sensores están llenando de datos nuestro preciado espacio, y la mayoría no parece que sean muy interesantes.

Por último, os dejo otra consulta SQL con la que podemos analizar un día concreto (por ejemplo para el 7 de mayo con un límite de 15 entidades):

SELECT entity_id, COUNT(*) as count FROM states WHERE strftime('%m.%d', created)='05.07' GROUP BY entity_id ORDER BY count DESC LIMIT 15

Paso 4. Optimización de base de datos sqlite en Home Assistant. Vamos de limpieza y vemos los resultados

Pues bien, ya podemos decirle a nuestro sistema que entidades no queremos para la optimización de base de datos sqlite en Home Assistant

Se puede hacer directamente en el fichero configuration.yaml, pero poco a poco este fichero se vuelve complicado de leer si es muy largo. En vez de eso, le diremos que incluya el contenido de otro. En configuration.yaml anotaremos:

recorder: !include recorder.yaml

Y crearemos, en el mismo directorio config, el fichero recorder.yaml.

Tened en cuenta que hay poner vuestros sensores, no copiar de este ejemplo:

  purge_keep_days: 30
  auto_purge: true
  commit_interval: 10
  exclude:
    entities:
      - sensor.router_packets_received
      - sensor.router_b_received
      - sensor.router_packets_sent
      - sensor.router_b_sent
      - sensor.router_packets_s_sent
      - sensor.router_packets_s_received
      - sensor.router_kib_s_sent
      - sensor.enchufe_blitzwolf_1_linkquality
      - sensor.enchufe_blitzwolf_3_linkquality
      - sensor.enchufe_blitzwolf_2_linkquality
      - sensor.enchufe_blitzwolf_1_voltage
      - sensor.enchufe_blitzwolf_3_voltage
      - sensor.enchufe_blitzwolf_1_current
      - sensor.enchufe_blitzwolf_2_voltage
      - sensor.date_time
      - sensor.date_time_iso
      - sensor.date_time_utc
      - sensor.time
      - sensor.time_date
      - sensor.time_utc

Aquí paramos un poco y explicamos los valores que hemos elegido para configurar el recorder a la hora de afrontar la optimización de base de datos sqlite en Home Assistant:

  • purge_keep_days: 30 – Le decimos que guarde 30 días… total, ahora lo vamos a tener optimizado y tenemos sitio. Por defecto la base de datos guarda 10 días.
  • auto_purge: true – Para decirle a Home Assistant que purge la base de datos 1 vez/día y no crezca indefinidamente.
  • commit_interval: 10 – Por defecto este valor es 1 seg. Nosotros preferimos que haga bloques de escritura cada 10 segundos. Es conocida la degradación de las memorias flash (como por ejemplo una tarjeta microSD) cuando se alcanza un número dado de operaciones de lectura/escritura (usualmente unas 100.000). Preferimos tener el histórico de eventos y estados con 10 segundos de retraso que ver como nuestro sistema se va degradando prematuramente.

Minimizar los ciclos de lectura/escritura nos ayudará a prolongar la vida de nustra tarjeta MicroSD

Igualmente desde el visor de sqlite podremos agrupar el resultado por días, viendo el total de registros en cada uno de esos días con la siguiente consulta que nos muestra los últimos 15:

SELECT strftime("%m.%d", created) as 'dia', count(*) FROM states GROUP BY strftime("%m.%d", created) ORDER BY 'dia' LIMIT 15

De nuevo os lo he mostrado pasado a excel por comodidad y para que veáis la reducción drástica a partir del 5 de mayo, tras las modificaciones realizadas excluyendo las entidades más activas:

Paso 5. Optimización de base de datos sqlite en Home Assistant. Reduciendo el tamaño del fichero

Visto todo lo anterior sobre la optimización de base de datos sqlite en Home Assistant, puede que todavía nos quede un fichero de base de datos bastante grande. Gracias Luis por tus aportaciones en este tema.

Podemos en este caso llamar al servicio con la opción repack en la sección Developer Tools de nuestro Home Assistant:

Tras usar manualmente la opción repack, el fichero se puede reducir considerablemente, y ha tardado bastante (varias horas) en una RPI 4, depende de cada caso concreto.

Repack sí elimina los registros previamente marcados como borrados por el servicio purge (auto_purge) cada noche , pero lo hace creando una copia de trabajo de la base de datos y rehaciéndola, por lo que se debe tener espacio suficiente también para los ficheros temporales creados (home-assistant_v2.db-wal y home-assistant_v2.db-shm).


La evidencia de que se está realizando repack es la propia creación del fichero wal que irá creciendo. En mi caso la carga en CPU no ha subido mucho y no se ha notado lentitud en el sistema durante todo el proceso, que sí ha tardado bastante hasta que ya no ha reducido más el tamaño del fichero home-assistant_v2.db.

Durante el proceso de repack el contenido del fichero wal es transferido periódicamente a la base de datos .db (lo que denominan un checkpoint) pero no se garantiza cuándo ocurre esto, por lo tanto es posible que existan los dos (.db y .wal) durante bastante tiempo.

El fichero -shm, mucho más pequeño, es simplemente un caché temporal de intercambio con memoria.

Finalmente es bueno hacer un reset y ver que todo queda correctamente

Espero que con todo lo realizado sobre la optimización de base de datos sqlite en Home Assistant, nuestro sistema responda más fluido y alarguemos la vida de nuestros equipos con poco mantemiento.

Si tenéis cualquier duda decidme en este mismo post.

Optimización de base de datos sqlite en Home Assistant

16 comentarios

  1. Enhorabuena por tu blog! posteas cositas super interesantes y útiles!!

    Acabo de realizar lo que comentas. No obstante veo en mi instalación de HA a través del plugin SQLite Web que mi DB /config/home-assistant_v2.db tiene un size de 1.3GB tanto antes de realizar los cambios en el configuration.yaml como después. Como podría aplicar dicho purge para reducir el tamaño de la DB?

    He reiniciado el host por completo pero sigue igual.

    Gracias

    1. Hola Luis. Gracias.
      Si acabas de realizar los cambios para reducir la carga de escritura debes esperar unos días (guarda 10 días por defecto) hasta ver reducir el tamaño. En mi caso, al reducir la carga también le puse que guardara 30 días, así que lo mismo no me baja, pero guardaré más..jeje

      Ya nos vas diciendo.

      1. Gracias por la rápida respuesta.

        La instalación la realicé desde 0 hace 2 meses (60 días), esperaba que al aplicar esta configuración, tras un reinicio el sistema purgara la DB para así mantener en el histórico solamente los últimos 30 días, reduciendose el tamaño a la mitad.

        ¡Gracias de nuevo!

        1. No hay problema. Es interesante ver si vas bajando.
          Asegúrate no tienes puesto auto_purge: false (la opción por defecto es true), y que tienes todo actualizado a su última versión.
          En teoría auto_purge realiza una purga cada noche a las 04:12 AM. y siempre puedes ejecutarla desde la opción Developer Tools. Vas a SERVICES y buscas el servicio recorder.purge.
          Saldrán varias opciones (una de ellas que reduce el tamaño, pero reescribe toda la base de datos y sinceramente no lo he probado (cuidadín y una buena copia de seguridad).

          1. Gracias de nuevo!
            Sabiendo como se comporta voy a esperar hasta mañana, para que esta noche haga su trabajo a ver si se ha ejecutado automáticamente y ha reducido su tamaño.

            En tu ejemplo, ¿Por qué has excluido algunos sensores?

            Saludos

  2. Ostras, lo había entendido como que lo excluía de los 30 días en el sentido de que lo registraba ilimitadamente …🤦‍♂️

    en mi caso le puse 35días y esta noche ha engordado 100MB. Voy a ponerle 5días para hacer la prueba a ver si la purga me funciona. Por cierto, una vez se migre la db a mariadb este ajuste del configurarion.yaml se mantiene igual?

    Gracias

      1. Gracias!
        entonces como quedaría adaptada esta configuración una vez migrada la db de sql lite a maria db?

        # PURGA DB +15 DIAS
        recorder:
        auto_purge: true
        purge_keep_days: 15
        commit_interval: 5

        Gracias de nuevo!

        1. Pues creo que quedaría igual, pero deberías incluir además db_url:.
          Yo lo sigo teniendo con sqlite, así que quizás otro lector pueda ayudarte con algo más específico. Todo depende del uso que le quieras dar y el volumen de datos. En mi caso para unos 30 días guardados y optimizando los sensores que quiero historizar, me está quedando por debajo de 1GB.

          1. ¡Hola!
            Sí, a veces lo más simple es lo más práctico y por ende mejor.

            En mi caso particular, sin poner ninguna excepción a excluir como en tu ejemplo, creo que no me está «haciendo» caso pues ha subido el tamaño de la db de ayer a hoy. (Actualmente 1.5GB)

            La instalación la realicé hace 2 meses, probé con 30 días. No ví diferencia en el tamaño. probé con 15, incluso me aumentó 100MB ( de 1,4GB a 1.5GB transcurridas 24h)

            Ahora le he puesto 5 días a ver que tal, pero me da que no está funcionando, porque debería ver un decremento del tamaño de la db verdad? ¿o es que solamente esta configuración hace que los nuevos históricos no se almacenen más de ese tiempo pero los antiguos no los toca?

            Gracias de nuevo

          2. Sí, por lo que indica la documentación la purga no hace decrecer inmediatamente el espacio ocupado, pero ralentizará el ritmo en el que aumenta. Esto unido a una buena elección de los sensores que no queremos almacenar hará que el ritmo sea más lento.
            Luego veo si en un hueco hago una purga con repack y os cuento en cuanto queda.

            Bueno, Tras llamar al servicio manualmente con la opción repack, el fichero se ha reducido casi un 60% aproximadamente y ha tardado bastante (varias horas) en una RPI 4.

            Repack sí elimina los registros previamente marcados como borrados por el servicio purge (auto_purge) cada noche , pero lo hace creando una copia de trabajo de la base de datos y rehaciéndola, por lo que se debe tener espacio suficiente también para los ficheros temporales creados (home-assistant_v2.db-wal y home-assistant_v2.db-shm)

            No he podido ver otra evidencia de que se está realizando repack más allá de la creación de estos ficheros. En mi caso la carga en CPU no ha subido mucho y no se ha notado lentitud en el sistema durante todo el proceso, que sí ha tardado bastante.

            Durante el proceso de repack el contenido del fichero wal es transferido periódicamente a la base de datos .db (lo que denominan un checkpoint) pero no se garantiza cuándo ocurre esto, por lo tanto es posible que existan los dos (.db y .wal) durante bastante tiempo.

            El fichero -shm, mucho más pequeño, es simplemente un caché temporal de intercambio con memoria.

            Saludos.

  3. Muchas gracias a ti por continuar con este tema.
    he dejado haciendo el repack. ¿como sabes con certeza cuando acaba? Tras darle a «call service» no he visto que salga un relojito de espera ni un log donde ver como avanza el proceso.

    Cuando comentas que «Finalmente es bueno hacer un reset y ver que todo queda correctamente» te refieres a reiniciar el host?

    Gracias de nuevo y saludos

    1. Efectivamente, yo tampoco he podido encontrar log o alguna otra indicación que no sea la creación de esos ficheros temporales y que el wal va creciendo y muy de vez en cuando el dichero .db va bajando. Cuando al cabo de varias horas ya no bajaba más, he entendido que había acabado.
      El reset del host, en mi caso ha sido necesario para terminar de borrar el fichero .wal, pero es que le di varias veces a repack y lo mismo se hizo un lío. Mi recomendación es que le des tiempo y si no termina de dejar el fichero .wal ocupando poco , le hagas un reinicio.
      He incluido esta información en el post. Gracias

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *