En este post vamos a trabajar la optimización de la base de datos SQLITE en Home Assistant, y a obtener el máximo provecho de nuestro sistema domótico… con Paciencia
Iremos viendo etapa por etapa las distintas mejoras que podemos realizar en la optimización de sqlite en Home Assistant, y las herramientas que usaremos, de manera fácil para que podamos replicarlo en nuestro propio equipo, maximizando el rendimiento
Desde la versión 2022.4 de HA, muchas han sido las mejoras para reducir el número de accesos de escritura , para alargar la vida útil de las tarjetas SD, pero todavía merece la pena más que nunca mejorar la optimización para que el acceso sea rápido y la sensación de usuario fluida.
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
Entre el hardware necesario puedes elegir las siguientes opciones totalmente validas:
Tabla de contenidos
Paso 1. Obtenemos el fichero de sqlite
Lo primero que necesitaremos para la optimización de sqlite 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.
El fichero home-assistant_v2.db está en el directorio config. Simplemente seleccionamos en el menú y elegimos descargar:
Con la base de datos ya descargada en nuestro ordenador, para seguir con la optimización, 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. También podéis usar sqlitestudio para la misma tarea.
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:
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. Analizamos con consultas SQL
Por ahora sigamos con nuestro objetivo de optimización de sqlite. 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.
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:
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. Vamos de limpieza y vemos los resultados
Pues bien, ya podemos decirle a nuestro sistema que entidades no queremos para la optimización de la base de datos.
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:
- 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. Reduciendo el tamaño del fichero
Visto todo lo anterior sobre la optimización de base de datos sqlite, 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.
Energy. Información sobre Energía
Desde la versión 2021.8 de Home Assistant podemos ver una sección dedicada en exclusiva a la gestión de la energía. Está muy bien diseñada y supone un antes y un después en la medición de estos valores en nuestro hogar.
Desde el punto de vista de la base de datos, dichos valores mostrados se almacenan en una tabla específica de la base de datos llamada «statistics«, y los sensores que se almacenan en esta tabla statistics los encontraremos listados en otra tabla llamada «statistics_meta«
En el caso de valores que no sean contadores, cada hora exacta se almacena la media, el mínimo y el máximo. Y en el caso de entidades que representen valores acumulados (como un medidor de energía) se almacena el valor actual cada hora.
No solo se almacenan los datos de Energía, sino cualquier otro que queramos- Podéis consultar cómo ponerlo en el fichero de configuración en el enlace oficial.
En cualquier caso, y de cara al tamaño de la base de datos, hay que tener en cuenta que nunca se purgan dichos valores. (tened en cuanta que por ejemplo para un contador de kWh, por ejemplo, solo se almacenarán 24 datos por día, y la idea es precisamente guardar datos a largo plazo).
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.
Y por último, os dejo otros enlaces interesantes:
- Accede desde cualquier lugar a tu hogar inteligente con DuckDNS y Home Assistant. Y totalmente gratis !!
- Los juegos que potencian la filosofía STEM (Ciencia, Tecnología, Ingeniería y Matemáticas) tienen beneficios a medio y largo plazo. Invierte en conocimiento y nunca te arrepentirás.
- Las notificaciones son importantes en un hogar inteligente, pero cuando unimos Home Assistant y Telegram, el resultado supera todo lo esperado. Fiabilidad y flexibilidad
- IFTTT con Home Assistant. Guarda datos en Google Drive
- Te presentamos todas las opciones de hardware para que Home Assistant funcione a la perfección.
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
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.
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!
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).
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
He quitado los que no me interesa almacenar el histórico.
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
Hola Luis, la configuración de recorder es indiferente de la base de datos que uses.
Un saludo.
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!
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.
¡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
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.
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
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
muchisimas gracias !
Hola Luis, puede que te interese este otro post donde juego un poco con los datos almacenados.
https://www.pacienciadigital.com/analisis-de-consumo-de-energia-con-home-assistant/
Saludos
Hola buenas, tengo un gran problema y es que no tengo suficiente espacio para que se realice el repack debido a que la base de datos ha engordado de manera exagerada. Hay alguna forma de hacerlo decrecer sin el repack o solo me queda la opción de reinstalar el sistema? Gracias
Nada me he pasado a Maríadb y eliminé el archivo.
Hola Alfonso, perdón por la demora. Se me ocurre que puedes intentar crear una copia imagen de la tarjeta o disco duro actual con Clonezilla, driveimage o similar, y tratar de volcarla en otra tarjeta/disco de mayor tamaño, pero no lo hemos probado. En todo caso siempre podrías volver a la situación actual si no arranca con la tarjeta/disco habitual.
Hola, muchísimas gracias por toda la información y tan bien explicada.
Hace nada que he conseguido que me contabilice en el apartado de energía el consumo total de mi vivienda.
La cuestión es que desconzco si ponemos lo de purgar etc….
¿Home Assistant guardará los datos diarios y mensuales en el apartado de Energia?
Puesto que han puesto botones para consultar días, semanas, meses y años.
En la documentación hay un apartado que hablan en long term data o algo así pero no lo llego a enterder 🙁
Saludos.
Hola Antonio, pues es una muy buena pregunta la que planteas. Hemos incluido una sección al final del artículo donde se da explicación a la misma, pero la respuesta corta es que no se purga nunca. Gracias por colaborar con comentarios y a seguir creando comunidad. Un saludo.