Optimize Your Home Assistant Database

Optimize Your Home Assistant Database


Home Assistant 默认的数据库是 sqlite，随着使用时间的增加，数据库文件越来越大，查看日志和历史的速度也变得越来越慢。

sqlite 数据库 内存模式 INMEMORY模式 文件内存中

曾经试着把这个 sqlite 数据库文件放到 /tmp 目录下，也就是内存中，响应速度快了很多，但是内存很快就塞满导致死机。

内存模式，sqlite3 支持内存模式，将数据库直接创建到内存中，打开地址传入”:memory:”即可，内存模式相比正常模式，可以省区IO的时间

关闭写同步，PRAGMA synchronous = OFF
在 sqlite3 中 synchronous 有三种模式，分别是 FULL，NORMAL 和 OFF，在系统意外终止的时候，安全性逐级减弱，FULL模式下，保证数据不会损坏，安全性最高，写入速度也最慢。OFF 模式会比 FULL 模式快50倍以上。

detail:https://www.cnblogs.com/sinpoo/p/15970396.html

Currently data is stored in JSON strings containing entity, states, attributes, etc. These could be several hundred bytes and are just stored as text blobs within the field.

The only way to reduce the database size is to change the way Home Assistant stores data.

Currently data is stored in JSON strings containing entity, states, attributes, etc. These could be several hundred bytes and are just stored as text blobs within the field.

This makes it extremely inefficient to store data as database native types are completely ignored, destroying all optimization the database engine may do. It also makes queries thousands of times slower than they should be because it causes a lot of string parsing during queries, limits what can be done in SQL, and pushes a lot of processing to the client.

Sort of like treating a relational database as a flat log file.

Entities should instead link to an other table holding attributes, states should like to the entity table, all attributes stored as their native data types, and indexes set up for all common queries.

https://community.home-assistant.io/t/speed-up-home-assistant-by-reducing-the-database-size/175094/24


solved 1 :

If you delete it and restart home assistant it will be recreated.

detail step by step

https://www.msly.cn/boards/topic/13373/optimize-your-home-assistant-database#17601

solved 2 :

Recoder

https://www.msly.cn/boards/topic/13373/optimize-your-home-assistant-database#17599

solved 3 :

change default database to mongdb etc.

https://www.msly.cn/boards/topic/13374/change-the-default-database-library-for-home-assistant

Home Assistant uses a database to store events and parameters for history and tracking of your entities. The default database used in a fresh install is SQLite, which creates a file in your config directory (e.g config/home-assistant_v2.db).


SQLite是一种嵌入式数据库，它跟微软的Access差不多，只是一个.db格式的文件。但是与Access不同的是，它不需要安装任何软件，非常轻巧。

很多软件都有用到这个家伙，包括腾讯QQ、迅雷(你在迅雷的安装目录里可以看到有一个sqlite3.dll的文件，就是它了)，以及现在大名鼎鼎的android等。SQlite3是它的第三个主要版本。就是SQLite3.0的意思。对了，金山词霸也有用到SQLite，其实太多软件用那玩意儿了。

The database is stored at <path to config dir>/.homeassistant/home-assistant_v2.db as SQLite database. In all examples we are going to use the path: /home/ha/.homeassistant/home-assistant_v2.db

If you are just curious what’s stored in your database then you can use the sqlite3 command-line tool or a graphical one like DB Browser for SQLite.

The sql sensor platform enables you to use values from an SQL database supported by the sqlalchemy library, to populate a sensor state (and attributes). This can be used to present statistics about Home Assistant sensors if used with the recorder integration database. It can also be used with an external data source.

https://www.home-assistant.io/integrations/sql/

command-line tool 
 

 graphical one like DB Browser for SQLite

Using the SQLite Web add-on

SQL integration 

To change the default database library, we need to use the recorder component in Home Assistant.

Recorder vs History vs Logbook

Before we go on any further with this tutorial, we feel like it’s important to clarify the difference between Home Assistant’s recorder, history and logbook integration.

The Recorder integration is responsible for storing events, states and data in the main SQL database. It simply handles the backend logic of the database
The History integration is responsible for displaying data from the recorder in the UI using Google Graphs
The Logbook integration is responsible for showing changes and events in a list sorted reverse chronologically

Settings->system->logs   /home/ha/.homeassistant/home-assistant.log
System->Configure- 》 log    --   http://192.168.101.50:8123/config/logs   --  home-assistant.log


History 《=》 /home/ha/.homeassistant/home-assistant_v2.db
Logbook  《=》 /home/ha/.homeassistant/home-assistant_v2.db

What’s the default # days the DB stores? I never changed anything, and DB size is ~2.3GB.

Btw, if you want a sensor to monitor it (for SQLite installs):

  

# HA database size
  - platform: sql
    # Not needed when using recorder, because we already have a connection.
    # Absolute path begins after the 3rd "/" (location for Hassbian).
    #db_url: sqlite:////home/homeassistant/.homeassistant/home-assistant_v2.db
    scan_interval: 600
    queries:
      - name: Database
        # This is the fastest solution, available since sqlite 3.16.
        # It’s the same info as returned by ".dbinfo" in the sqlite3 cmdline client.
        query: 'SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size();'
        column: 'size'
        unit_of_measurement: 'Bytes'

Hint: You can always display this value in a template in a nice-to-read way:

{{ (state_attr('sensor.database','size') | filesizeformat(binary=false)) }}

How to manually use recorder purge service

https://community.home-assistant.io/t/how-to-manually-use-recorder-purge-service/66738/5

service: recorder.purge
data: {"keep_days":"2"}

Reult


Recorder doesn’t purge   home-assistant_v2.db


https://community.home-assistant.io/t/recorder-doesnt-purge/462120/2


Today I ran the following SQL against the database:

DELETE FROM statistics WHERE start < DATE('now','-4 day');

root@raspberrypi:/home/homeassistant/.homeassistant# ls -l
总用量 2504

-rw-r--r-- 1 homeassistant homeassistant    8827 10月 29 23:18 automations.yaml
drwxr-xr-x 4 homeassistant homeassistant    4096  3月  3  2022 blueprints
-rw-r--r-- 1 homeassistant homeassistant    5722 10月 18 03:20 configuration.yaml
drwxr-xr-x 7 root          root             4096 10月  4 01:09 custom_components
drwxr-xr-x 2 homeassistant homeassistant    4096  3月  3  2022 deps
-rw-r--r-- 1 homeassistant homeassistant  116762 11月  1 23:21 home-assistant.log
-rw-r--r-- 1 homeassistant homeassistant 2107674 11月  1 23:20 home-assistant.log.1
-rw-r--r-- 1 homeassistant homeassistant       0 11月  1 23:21 home-assistant.log.fault
-rw-r--r-- 1 homeassistant homeassistant    4096 11月  1 23:21 home-assistant_v2.db
-rw-r--r-- 1 homeassistant homeassistant   32768 11月  1 23:21 home-assistant_v2.db-shm
-rw-r--r-- 1 homeassistant homeassistant  238992 11月  1 23:21 home-assistant_v2.db-wal
drwxr-xr-x 3 homeassistant homeassistant    4096  5月 23 01:56 image
-rw-r--r-- 1 homeassistant homeassistant    1141 10月 18 03:22 known_devices.yaml
drwxr-xr-x 2 root          root             4096  3月 24  2022 media
-rw-r--r-- 1 homeassistant homeassistant    2136  8月 31 04:10 scenes.yaml
-rw-r--r-- 1 homeassistant homeassistant       0  3月  3  2022 scripts.yaml
-rw-r--r-- 1 homeassistant homeassistant     161  3月  3  2022 secrets.yaml
drwxr-xr-x 2 homeassistant homeassistant    4096  3月  3  2022 tts

login rp as root


Step 1：Stop ha

sudo systemctl stop [email protected]

Step 2：delete  database and log

-rw-r--r-- 1 homeassistant homeassistant  116762 11月  1 23:21 home-assistant.log
-rw-r--r-- 1 homeassistant homeassistant    4096 11月  1 23:21 home-assistant_v2.db
-rw-r--r-- 1 homeassistant homeassistant   32768 11月  1 23:21 home-assistant_v2.db-shm
-rw-r--r-- 1 homeassistant homeassistant  238992 11月  1 23:21 home-assistant_v2.db-wal

Step 3：Restart Ha

sudo systemctl restart [email protected]

这四个文件会重新创建

Database is growing since switched to Zigbee2MQTT

https://community.home-assistant.io/t/database-is-growing-since-switched-to-zigbee2mqtt/428087/4

i have switched from ZHA to MQTT for about 2 weeks. Before i had cleaned up my recorder settings, made a fresh start (deleted DB and began from beginning, purge Settings 7 days, auto_purge:true, mariadb) which leaded to a database size for about ~350MB.
Since then my DB is growing day by day to now about ~650MB.

In the Z2M’s HA settings 19, make sure to set legacy_entity_attributes to false. This option defaults to true and it causes Z2M to replicate everything in all sensors.

By default if you look at any of the entities for a device you’ll see the attributes of that entity contain the state of all other entities of that device. For example if you have a motion sensor with a primary sensor of occupancy but that also reports illuminance and temperature you’ll see you not only have sensors for illuminance, temperature and occupancy but each also has illuminance, temperature and occupancy in their attributes.

Replicating the same information to the attributes of the others is hugely wasteful with DB resources. One of the main optimizations made in recently releases was to strip down the number of attributes since they require so much more space. This change should make a huge difference if you currently have legacy_entity_attributes unspecified or set to true


https://www.zigbee2mqtt.io/guide/configuration/homeassistant.html