Large homeassistant database files

[rpitera (Robert Pitera)]

I use WinSCP myself. That or the configurator editor that @danielperna84 created.

[Dayve67 (Dave)]

Downloaded WinSCP and tried. I dont understand.

[Dayve67 (Dave)]

Got it. I my permissions were not letting me see it.

[Bob_NL (Bob Visser)]

@Dayve67

my permissions were not letting me see it

Sorry for the noob question but how did you fix the permissions?

I’m having the same issue:

pi@raspberrypi:~ $ cd /var/lib/mysql/

-bash: cd: /var/lib/mysql/: Permission denied

[Dayve67 (Dave)]

I used the:

sudo chmod -R ugo+rw /var/lib/mysql/

[rpitera (Robert Pitera)]

Just a follow up on this post:

Large homeassistant database files

Purges run - as @arsaboo said - every 48 hours or so. I say “or so” because in testing this I discovered it to actually happen somewhere towards the end of hour 48 and hour 49. This is going to change at some point soon according to this discussion: https://github.com/home-assistant/home-assistant/issues/6302 But there’s no PR associated so I can’t tell you when this will happen. If you’re using MySQL and still running low on space, even after purges run, it might be due to the size of your…

So I finally got up the nerve to test out the fix for the large ibdata1 file size and moving to innodb_file_per_table, which is now the default for new MySQL installations.

I went back and first re-read this article a couple of times to make myself comfortable:

How to Shrink MySQL ibdata1 Size using innodb_file_per_table

I followed EVERY instruction to the letter including doing a cold backup of the /var/lib/mysql dir and then db exports.

Backups and exports took a bit of time due to the file sizes involved; my ibdata1 file was well over 3.5gb. It was a little tense at first as I worried about dropping the HASS table even though I knew I had a few backups. But all in all it went quite smoothly and my ibdata1 file is a fraction of it’s former size and the performance on queries seems to be improved as well.

One thing I will say is in the restore directions, he has the following command:

Import all the database from the mysqldump backup that we took earlier.

mysql -u root -ptmppassword --all-databases < /backup/all-database.sql

And I found it had to be

mysql -u root -ptmppassword < /backup/all-database.sql

for it to work. Also, if it’s not clear; -ptmpassword option is -p followed by your password for the mysql user running the commands.

All in all I’m quite happy with the results as well as the 3gb + space savings!

I did this now because 0.42 introduces some changes to the db structure; specifically the indexing and I wanted to make the changes ahead of that update to take advantage of the structural improvements before updating.

[touliloup (Romain)]

If that help I’ve just setup a MySQL docker for my home-assistant.

The docker run command to create/start the MySQL container:

docker run \
--name mysql-hass \
-e MYSQL_ROOT_PASSWORD=12345 \
-e MYSQL_DATABASE=hass_db \
-e MYSQL_USER=hassuser \
-e MYSQL_PASSWORD=12345 \
-e TZ=Europe/Berlin \
-p 3306:3306 \
-v /etc/localtime:/etc/localtime:ro \
-d mysql:8.0 --bind-address=0.0.0.0

And the recorder in HASS configuration.yaml

recorder:
  purge_days: 7
  db_url: mysql://hassuser:[email protected]/hass_db

Change 12345 with the password you’d like to use.

Home-Assistant also run in a Docker container with network --net=host, it should also work with a standard hass instance as long as they run on the same machine.

[arsaboo]

@touliloup Where are you running the Docker container? I tried installing the MySQL docker container on Synology NAS and was not quite successful.

[touliloup (Romain)]

@arsaboo on the same machine as Home-Assistant, an HP Gen8 running under CentOs 7.0.

What doesn’t work on you NAS? Does docker run correctly? Does the container start?

[gbboy]

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’`

[arsaboo]

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.

[arsaboo]

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

[Lapatoc]

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

[rpitera (Robert Pitera)]

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.

[Dayve67 (Dave)]

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

[touliloup (Romain)]

You could add that to your config:

recorder:
  purge_days: 3

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

[rpitera (Robert Pitera)]

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.

[poldim]

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

[touliloup (Romain)]

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

[anon35356645]

On a HASSBIAN installation I followed the following instructions

Large homeassistant database files

Hi All, Seemed to get this installed without too many issues on a HASSBIAN image. Only been running for approx 10 mins so far though. Details below: Complete the following outside the venv: sudo apt-get install libmysqlclient-dev sudo apt-get install python-dev python3-dev sudo apt-get install mysql-server ** create root password when prompted ** mysql -u root -p ** enter root password ** CREATE DATABASE hass_db; CREATE USER 'hassuser'@'localhost' IDENTIFIED BY 'XXX'; <-------XX…

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)