Large homeassistant database files

[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)

[Marius]

Try changing the db_name to “hass_db”

[anon35356645]

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)

[Tyfoon]

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:

Large homeassistant database files

My db grew to nearly 1.7GB since installing Home Assistant just a few weeks ago. My graphs started getting incredibly slow, more than 60 seconds to load. I’ve followed these instructions from @MojaveTom more or less. Had to make some small changes to fit my install, and also I was not interested in migrating my data so I could skip that part. Here is how I did it: sudo apt-get update && sudo apt-get upgrade sudo apt-get install mysql-server && sudo apt-get install mysql-client sudo apt-get ins…

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)

And when I do: systemctl status mysql.service

I get:

[Tyfoon]

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

[rpitera (Robert Pitera)]

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

[BendedArrow]

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?

[Corey_Johnson]

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

[firstof9]

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

[sanders]

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

sander76/hass-aggregator

[LaStrada (Ståle Storø Hauknes)]

Wow, good job! I’ve been thinking to implement something like this, but I never get the time to do it… Will try this later today!

[rpitera (Robert Pitera)]

This is an interesting idea. I’m looking forward to learning more about it as you progress.

[Jtmoore81]

I’ve converted the SQLlite database to a MySQL (MariaDB) and it works fairly well. Definitely makes rendering the history faster.

I’ve noticed that each night around 2-3AM something happends to the SQL server and the recorder component fails. HASS keeps working but without any data being recorded. Below is the error message I get each morning. Has anyone else seen this happen?

2017-06-10 08:00:51 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (_mysql_exceptions.OperationalError) (2006, 'MySQL server has gone away') [SQL: 'INSERT INTO states (domain, entity_id, state, attributes, event_id, last_changed, last_updated, created) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: ('sensor', 'sensor.ram_free', '14214.2', '{"unit_of_measurement": "MiB", "friendly_name": "RAM Free", "icon": "mdi:memory"}', None, datetime.datetime(2017, 6, 10, 8, 0, 50, 986400, tzinfo=<UTC>), datetime.datetime(2017, 6, 10, 8, 0, 50, 986400, tzinfo=<UTC>), datetime.datetime(2017, 6, 10, 8, 0, 51, 9819))]

[BendedArrow]

@Jtmoore81 Do you have purge enabled?

[Jtmoore81]

I do. Currently its set at 1 day.

[BendedArrow]

@Jtmoore81 I have the same issue as you. Removing purge did not working. Removing all of my inclusions now shows everything, but…that is not what I want, of course…

[Jtmoore81]

I’m hoping it’s a timeout issue. I’ve increased the timeout and well see what happens tonight.

Edit: Increasing the timeout didn’t fix the issue.Next step delete the database and set it up again.

[BendedArrow]

@Jtmoore81 I am using the default sqlite database internally on the PI…

[firstof9]

Is the MiraDB restarting for some reason at 2-3AM? Check /var/log/syslog for errors.