Large homeassistant database files

Configuration
181

This is from my home assistant notes on when I setup MySQL on my synology nas:

I’ve installed a docker mysql container instead of relying on Home Assistant’s built in. I pulled sameersbn/mysql via ssh and used the line below to initialise:

  • docker run --net=host --restart=always --name mysql -d -v /volume1/docker/mysql/data:/var/lib/mysql -e 'DB_USER=hass' -e 'DB_PASS=12345' -e 'DB_NAME=homeassistant' sameersbn/MySQL

Then adding the following to my config yaml file:

`-	recorder:

purge_days: 6
db_url: ‘mysql://hass:[email protected]/homeassistant?charset=utf8’`

182

Looks like this is what I am looking for, but I get this error:

Password:
ash-4.3# docker run --net=host --restart=always --name mysql -d -v /volume1/docker/mysql/data:/var/lib/mysql -e 'DB_USER=hass' -e 'DB_PASS=12345' -e 'DB_NAME=homeassistant' sameersbn/MySQL
docker: Error parsing reference: "sameersbn/MySQL" is not a valid repository/tag

BTW, I downloaded the container from Docker GUI.

183

Here’s what worked for me (hopefully, it can help others):

  1. SSH into Synology NAS and pull the image using docker pull sameersbn/mysql:latest. After you login as admin, you may have to run sudo su if you keep getting permission errors.
  2. Make sure that the folder /volume1/docker/mysql/data exists (or create one, if it does not).
  3. Run, the command docker run --net=host --restart=always --name mysql -d -v /volume1/docker/mysql/data:/var/lib/mysql -e 'DB_USER=hass' -e 'DB_PASS=12345' -e 'DB_NAME=homeassistant' sameersbn/mysql:latest
  4. Add the following to the config (where 192.168.2.113 is the IP address of my NAS):
recorder:
  purge_days: 3
  db_url: mysql://hass:[email protected]/homeassistant?charset=utf8
184

Hi. How is your DB? Is inno_db file reduces after purges?

185

I haven’t really been checking that file specifically but it is staying around 200mb which is a major improvement over the 3gb it used to be. And I’m definitely happy with the performance improvement.

1 Like
186

How did you do that? Mine is very large and slow to call up in HA.

187

You could add that to your config:

recorder:
  purge_days: 3

So that it only keep the data from the last 3 days.

188

Or you can exclude certain domains or sensors from being tracked. Do you need to keep track of time or date sensors? How about the sun sensor? Things like this add a LOT of useless data you’ll never track as well as the overhead penalties of poor performance.

That, plus moving to innodb_file_per_table helped enormously and both things were fairly easy to implement even with limited MySQL skills.

189

I got some alarms from unraid today and shows my log and db blowing up. I’ve added purge after 90 days to the config. I see that the db is storing the historal component, but how did the log file grow to be 12 gigs???

12G	/mnt/cache/appdata/home-assistant/home-assistant.log
3.9G	/mnt/cache/appdata/home-assistant/home-assistant_v2.db
190

I don’t think that home-assistant log have a maximal size or conservation period.
They’ll grow forever.
My home-assistant run in a docker container, it’s easy to limit the log size if you’re running in a docker container by using:

 --log-opt max-size=10m
191

On a HASSBIAN installation I followed the following instructions

changing the recorder to

recorder:
  purge_days: 1
  db_url: mysql://hassuser:[email protected]/db_name?charset=utf8

I receive the following error

17-05-19 16:22:41 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (_mysql_exceptions.OperationalError) (1049, “Unknown database ‘db_name’”) (retrying in 3 seconds)

192

Try changing the db_name to “hass_db”

193

yep thanks I got that.

HWere is this stored, should be /var/lib/mysqlite correct?

I try to go in with WINSCP but it says PERMISSION DENIED (although I am in with my pi password, and can access all)

194

After installing .46 HASS was not able yo use MySQL anymore. After fully uninstalling everything related to MySQL (debian - How to remove MySQL completely with config and library files? - Stack Overflow first answer) I did a new install according to:

However during installing (sudo apt-get install mysql-server && sudo apt-get install mysql-client) I get below. Any ideas? (there seems to be a relation to HAbridge but same result if I stop HABridge)

2014×1004 33.5 KB

And when I do: systemctl status mysql.service

I get:

1396×180 7.3 KB

195

After hours and hours of tinkering I finally was able to fix this;

First of all remove current MySql. To do this well you really need to follow this. See the first answer.

I tried a lot of different ways but above was the only way to really remove all.

Then install MySql again but without the dependencies:
sudo aptitude install mysql-server

This in turn took ages but this in the end installed it.

Then continue with: https://github.com/sjorsjes/home-assistant#install-mysql-db (third item, so start with mysql -u root -p and then follow the rest.

For reference; I’m using AIO installer pre dec 2016 and Hass .46

2 Likes
196

Thanks for sharing this important info, @Tyfoon! Sorry you had to go through this but your knowledge will certainly help someone in the future.

197

We know that using mysql instead of the sqllite improves performance. Has anyone used mysql, but put the database on a different host and observed the performance improvement over just having it on the same host?

198

Could we get mysql setup as part of the actual install/setup process rather than sqllite?

199

I run my MySQL (MiraDB) on a seperate server on my network, and have noticed better performance than using SQLite.

2 Likes
200

An attempt to reduce sensor data written to the hass database by aggregating it.
Any comment is welcome

2 Likes