Large DB size

Configuration
1

I have recorder configured to keep just 7 days of data but my home-assistant_v2.db file is still over 1GB.
How can I find out what is logging the most data so I can do exclude those entities? I looked inside with DB Browser and yes over 500k rows, but not sure how to find the highest hitters, any idea?

2

I use a whitelist of entities that I may care about their history, that shrinks the DB significantly. You could look at the Logbook or History pages to see which entities change state a lot

3

That is actually a better idea, I have just been excluding entities and I have a lot of them :frowning:

4

What I would recommend is to identify the entity having a lot of state changes ā€¦
This can be done by entering the Database (I am running on MySQL (MariaDB)):

Select the database where HA is storing the data (generally hass) and than run the following sql request:

MariaDB [hass]> select entity_id,count(*) from states group by entity_id;

You will have the number of lines per entity contained in the States table where state changes are recordedā€¦ Search for the ā€œbig numbersā€ and evaluate the possibility to move them on the exclude list ā€¦

5

** Offtopic **

What do you use for that?

7

is also valid for sqlite.

8

Found some, battery sensors for my 3 wall tablets, hammering the DB, added them to exclude list.
Thanks for the sql query that did the trick to find the offenders.
Also how do you setup recorder to only log entities I want instead of everything and me having to exclude them one by one?

1 Like
9

See Recorder include section.

10

What utility did you use to run the SQL command? Iā€™m only familiar with phpMyAdmin. Can that be used?

Disregard. Ran the command in DB Browser and it worked.

11

@sd_dracula, replace your recorder exclude list with an include list. Using include will exclude everything else from the db. It can be a hassle switching over from exclude to include, but once itā€™s done you will have better control over the behavior of your db.

On a side note if you use hassio, the mariadb add-on is more efficient/faster. A popular use case is replacing the default sql-litedb with mariadb for short term use (for faster lovelace history/logbook, and state persistence across reboots), then add influxdb for long term storage (several years), and use grafana to ā€˜look atā€™ influxdb data (fast, flexible, and beautiful graphs). For perspectiveā€¦ my mariadb with an abbreviated include list grows ~120MB per day (7d retention). OTOH influxdb grows a measly ~4MB per day with only 2 entities excluded (those same entities are excluded in mariadb as well). I use 1000d retention and it works well on a measly pi3+100gb usb ssd.

1 Like
12

Iā€™ve been wondering for awhile, how do you set the retention period in Influxdb?

13

I use the influxdb webui page on the sidebar/influxdb admin/edit the ā€˜homeassistant autogenā€™ retention policy and save. Your db may be named home-assistant or similar (I named mine homeassistant). The important take is you modify the retention policy for your ha database, not the _internal database policy (which is by default set to 7d).

1 Like
14

Using DB Browser, I was able to open home-assistant_v2.db and under the Execute SQL tab, enter the following code and push the play button to sort the entity IDā€™s by count with the highest first.

select entity_id,count(*) from states group by entity_id order by count(*) desc;

I was very surprised to see cover.garage in there along with others :astonished:. Some tweaking definitely needed!

image

2 Likes
15

You can also install the SQLite Web add-on in hassio and do it that way too.
(But obviously no need if you have DB Browser already!)

16

Yeah I think it would be helpful if we had a list of devices that have a habit of spamming. Most folks are familiar with how bad power meter plugs can be, but a garage cover, LOL? Top that with the fact that my spammy power meter plugs (ZW15RM) are supposed to be configurable so they arenā€™t so spammyā€¦ but sending those commands with HA doesnā€™t work consistently. Iā€™ve got one to shut up unless it sees more than 5W change, but the other one keeps sending 0.01W changes (very spammy even with the clothes dryer idling).

If you do get the power meter sorted, please share how you went about it.

17

select entity_id,count() from states group by entity_id order by count() desc;

I am using the last 3 years this command in HA addon sqlite to see the size of my database
Unfortunately, today is not workingā€¦

I am getting the following.

image

Does anyone knows what I should change in order to work please?

18

The row entity_id isnā€™t anymore in the states table (this was somewhere from 2023.3 - 2023.5) which saves a lot of space. Now you have to join the states_meta table. This SQL command works now as expected:

select states_meta.entity_id,count(*) from states left join states_meta on states.metadata_id = states_meta.metadata_id group by entity_id order by count(*) desc;
4 Likes
19

Hi thanks for the help.
However, I was getting error regarding the entity_id so I changed it to the following which is working.
Thanks

select states_meta.entity_id,count(*) from states left join states_meta on states.metadata_id = states_meta.metadata_id group by states_meta.entity_id order by count(*) desc;
4 Likes
20

@Makis

You are right, i only tried it within the states table in phpmyadmin (this worked for me), from other locations or as an sql device in HA it might fail without the table before the row. So your solution is the final one :wink:

1 Like