Large homeassistant database files

Configuration
101

This is actually achiles heel of home assistant. Depending on the type of installation you end up with either hass or homeassistant as the HA user.
In your case i think it’s hass. Also check the dir structure as that is also different.
And to ease up on DB management check out webmin.

And just for the report. Switching to Postgre SQL didn’t do the trick. Still have to wait up to several minutes for the history window to render. Overally feels the same as MySQL backend

102

Thanks for that tip @moskovskiy82, that was exactly it. My virtual environment was hass, not home assistant and the source directory was different. I probably should have followed the instructions by @dimmanramone as his have the correct paths.

So that basically just leaves me with the issues I had in setting up the MySQL section. If anyone has any input on that and anything I can check to make sure all is good there, I would appreciate it.

103

I guess I still have some issues left to sort out:

17-01-06 00:17:37 homeassistant.components.recorder: Error during connection setup: (_mysql_exceptions.OperationalError) (1045, "Access denied for user 'hassuser'@'localhost' (using password: YES)")
17-01-06 00:17:47 homeassistant.components.recorder: Error during connection setup: (_mysql_exceptions.OperationalError) (1045, "Access denied for user 'hassuser'@'localhost' (using password: YES)")
17-01-06 00:17:57 homeassistant.components.recorder: Error during connection setup: (_mysql_exceptions.OperationalError) (1045, "Access denied for user 'hassuser'@'localhost' (using password: YES)")

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

This is what I get when trying to grant privileges:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'hassuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

I think I would think something is broken in HA if I tried something and it just worked without any issues.

EDIT:
I went back in and deleted the hassuser and verified it was no longer listed. Then I re-added hassuser as instructed above and granted all privileges, got the same response as above and still getting the same error in the log as listed above.

I also tried changing the configuration.yaml:

recorder:
  purge_days: 30
  db_url: mysql://hassuser:mysecretpassword@localhost/hass_db

Still getting the same thing.

I guess at this point I will have to wait and see if anyone has some input because I am not sure where things went wrong at now. You don’t have to restart MySQL after such actions, do you?

104

Just to make sure you haven’t missed anything…

  • Have you actually created the database ‘hass_db’ ?
  • Did you set up a password for hassuser ?
  • Are you using that specific password in your db_url ?
105

Yes to all of your questions.

106

Alrighty, I do believe I have this up and working now. I wiped out the database and the user and started over. Nothing changed in the response I got to the issued commands. I reformatted the configuration pointer though to be:

recorder:
  db_url: mysql://hassuser:mypassword@localhost:3306/hass_db?charset=utf8

Seems to be working like a champ now. I have some history information and it populates lightning fast, but we will have to see how that holds up over time. I have it set on a 30-day purge. I have yet been able to go that long with the old database and it was typically a week or so and the file would be in excess of 400mb.

107

One of the main issues of Homeassistant is still the data it writes to the database. This data contains a lot of redundant information which causes any db to explode with information (I get around 2 gigabytes of data in 30 days and yes; I have quite a lot of sensors).

As an example the following is logged every six seconds at my system:

{“old_state”: {“last_updated”: “2017-01-08T18:16:30.801060+00:00”, “last_changed”: “2017-01-08T17:19:30.726020+00:00”, “entity_id”: “sensor.statistics_op_wasmachine_value_mean”, “attributes”: {“count”: 1282, “icon”: “mdi:calculator”, “median”: 0.0, “mean”: 0.0, “unit_of_measurement”: “W”, “friendly_name”: “statistics op wasmachine value mean”, “min_value”: 0.0, “sampling_size”: 20, “variance”: 0.0, “max_value”: 0.0, “total”: 0.0, “standard_deviation”: 0.0}, “state”: “0.0”}, “entity_id”: “sensor.statistics_op_wasmachine_value_mean”, “new_state”: {“last_updated”: “2017-01-08T18:17:00.621498+00:00”, “last_changed”: “2017-01-08T17:19:30.726020+00:00”, “entity_id”: “sensor.statistics_op_wasmachine_value_mean”, “attributes”: {“count”: 1283, “icon”: “mdi:calculator”, “median”: 0.0, “mean”: 0.0, “unit_of_measurement”: “W”, “friendly_name”: “statistics op wasmachine value mean”, “min_value”: 0.0, “sampling_size”: 20, “variance”: 0.0, “max_value”: 0.0, “total”: 0.0, “standard_deviation”: 0.0}, “state”: “0.0”}}

As the time is already logged in a separate column this could be transformed into something like only the value (backwards calculating all the other metrics) bringing the current entry from 1015 characters back to just 5 (reducing the size of the database with a factor 200).

Unfortunately I can just complain and am in no way ‘hindered’ by any real database knowledge…

108

Why not put it up on the suggestions page of the forum? Also see around 500mb of data per 5 days. As History and Logbook is rendered at the client - this makes them completely unusable

109

A WIP (work in progress) issue has already been opened for this on Github: WIP: Recorder component optimizations #4576

1 Like
110

I also moved to MySQL. My database was also getting bigger (1.5GB). Now it runs smooth and perfect…

111

Thanks for the ?charset=utf8

112

I am having the same problem with my database getting too big and history and graphs taking forever to load. I want to set up MySQL aswell. I installed Home Assistant using All-In-One Installer, my username is hass.

Would anybody be so kind and write instructions, how to set up MySQL, because here information is allover the place, some working some not and I think it would be much easier if we had them in one place (in documentation would be the best). Great work otherwise, figuring it all out!

113

Thanks! Worked like a charm :slight_smile:

114

Thanks phil. I’m running a docker setup also and your run command worked in addition to adding a -v path to mount the folder.

115

Anyway, how do you browse/access and even backup your MySQL database? Do you use phpmyadmin?

116

I have an issue with mySQL. When I switch to MySQL history component works fine but logbook do not show anything.
When I switch back yo SQLLite everything works fine. What am I missing?

117

I was finally able to set up MySQL and all is working perfectly for now. I followed @sjorsjes tutorial and only added ?charset=utf8 at the end of configuration, so now it is:

recorder:
  db_url: mysql://dbuser:password@localhost/dbname?charset=utf8

Logbook also works for me…

118

I’m trying to avoid installing mysql. Here are the top counts from my sqlite3 database. I have my recorder set to 7 days and just excluded sun and ATV.

   2488 sensor.temp_wireless
   2545 sensor.temp_living_room
   4736 climate.thermostat
   4799 sensor.temp_home
   6530 sensor.humidity_wireless
  10299 sun.sun
  15347 media_player.myatv

Here is the command I used:
sqlite3 -line home-assistant_v2.db 'select * from states'|grep entity_id|awk '{print $3}'|sort |uniq -c |sort -n|tail -20

1 Like
119

Raspberry Pi 3 Model B, hassbian from “HASSbian 1.1 - The Toy-box”
As described in https://home-assistant.io/getting-started/hassbian-installation/

Home Assistant 0.38.3; Path to configuration.yaml: /home/homeassistant/.homeassistant

These are the steps I used to get Mysql up and integrated into homeassistant. Collected together here for easy reference.

mysql-connector is from Oracle, the maintainer of Mysql; but the recorder component depends on mysqlclient. You might want mysql-connector for your Mysql python3 projects.

#####as user pi:

sudo apt-get install libmysqlclient-dev
sudo apt-get install python3-dev
sudo apt-get install mysql-client  # Needed to access Mysql from command line.

# sudo pip3 install --upgrade mysql-connector
sudo pip3 install  mysqlclient

Environment variables are used for database parameters so this script can be publicly available. Either define the variables appropriately, or substitute text.

I decided to name my Mysql schema “homeassistant” use whatever suits you, but be consistent.

Create Mysql database on host machine; create empty schema “homeassistant.” Create your DBUser in Mysql, giving it all privileges for the “homeassistant” schema. I used MySQL Workbench running on a Mac to do these tasks.


has a good description of how to do this.

Create schema in mysql database: (The schema itself already exists since you created it in order to give your DBUser privileges on it. The CREATE SCHEMA below is just for insurance. You could run this script as some other mysql root user and give DBUser privileges on the schema after this is run.)

#!/bin/bash
mysql -u${DBUser} -p${DBPassword} -h${DBHost} homeassistant <<EOF
-- MySQL Workbench Synchronization
-- Generated: 2017-02-21 15:57
-- Model: homeassistant
-- Version: 1.0
-- Project: homeassistant
-- Author: Thomas Demay

/*
homeassistant recorder service creates tables with time objects of data type "DATETIME."  Sqlite DATETIME type apparently includes micro sec.  The corresponding mysql data type is DATETIME(6).  For this reason, the tables are created before starting homeassistant with a mysql recorder.

Mysql TIMESTAMP(6) data type stores the time in the database as as UTC; converting on input and output to local time.  Homeassistant stores all times in UTC; so mysql TIMESTAMP(6) gets very confused.  Mysql DATETIME(6) does not do UTC conversions; so is what homeassistant expects.
*/

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `homeassistant` DEFAULT CHARACTER SET utf8 ;

CREATE TABLE IF NOT EXISTS `homeassistant`.`recorder_runs` (
  `run_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `start` DATETIME(6) NULL DEFAULT NULL,
  `end` DATETIME(6) NULL DEFAULT NULL,
  `closed_incorrect` TINYINT(4) NULL DEFAULT NULL,
  `created` DATETIME(6) NULL DEFAULT NULL,
  PRIMARY KEY (`run_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `homeassistant`.`events` (
  `event_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `event_type` VARCHAR(32) NULL DEFAULT NULL,
  `event_data` TEXT NULL DEFAULT NULL,
  `origin` VARCHAR(32) NULL DEFAULT NULL,
  `time_fired` DATETIME(6) NULL DEFAULT NULL,
  `created` DATETIME(6) NULL DEFAULT NULL,
  INDEX `ix_events_time_fired` (`time_fired` ASC),
  INDEX `ix_events_event_type` (`event_type` ASC),
  PRIMARY KEY (`event_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `homeassistant`.`states` (
  `state_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `domain` VARCHAR(64) NULL DEFAULT NULL,
  `entity_id` VARCHAR(255) NULL DEFAULT NULL,
  `state` VARCHAR(255) NULL DEFAULT NULL,
  `attributes` TEXT NULL DEFAULT NULL,
  `event_id` INT(11) NULL DEFAULT NULL,
  `last_changed` DATETIME(6) NULL DEFAULT NULL,
  `last_updated` DATETIME(6) NULL DEFAULT NULL,
  `created` DATETIME(6) NULL DEFAULT NULL,
  PRIMARY KEY (`state_id`),
  INDEX `event_id_idx` (`event_id` ASC),
  INDEX `states__state_changes` (`last_changed` ASC, `last_updated` ASC, `entity_id` ASC),
  INDEX `states__significant_changes` (`domain` ASC, `last_updated` ASC, `entity_id` ASC),
  CONSTRAINT `event_id`
    FOREIGN KEY (`event_id`)
    REFERENCES `homeassistant`.`events` (`event_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `homeassistant`.`schema_changes` (
  `change_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `schema_version` INT(11) NULL DEFAULT NULL,
  `changed` DATETIME(6) NULL DEFAULT NULL,
  PRIMARY KEY (`change_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
EOF

#####Switch to homeassistant virtual environment:

(I created .profile and .bashrc files in /home/homeassistant to setup the environment as desired; the last step is to activate the virtual environment. In this case, the first two lines of the code below become:
“sudo su -l homeassistant”
If somebody knows that this is a bad idea, please let me know.)

#!/bin/bash
sudo su -s /bin/bash homeassistant
source /srv/homeassistant/bin/activate
pip3 install --upgrade mysqlclient

edit configuration.yaml:
(Don’t forget the ?charset=utf8 specification on the db_url.)

recorder:
  db_url: !secret recorder_db_url # recorder_db_url: mysql://[YOUR MYSQL USERNAME]:[YOUR MYSQL PASSWORD]@[YOUR MYSQL HOST]/homeassistant?charset=utf8
  exclude:
    domains:
      - automation
      - weblink
      - updater
    entities:
      - sun.sun   # Don't record sun data
      - sensor.last_boot
      - sensor.date

#####Switch back to user pi;
stop homeassistant;
[optional] copy records from sqlite database to mysql database;
start homeassistant.

#!/bin/bash
exit    # exit homeassistant user

echo "Begin    stopping homeassistant @ $(date)"; sudo systemctl stop [email protected]; echo "Finished stopping homeassistant @ $(date)"

#  Copying current Sqlite database to Mysql database is optional.  If copying
#  first delete any table entries that you created testing the Mysql database.
mysql -u${DBUser} -p${DBPassword} -h${DBHost} -e "delete from states; delete from schema_changes; delete from recorder_runs; delete from events;" homeassistant

#  Extract data from sqlite database; massage to be mysql compatible, then
#  feed into Mysql database.  Do tables in this order so that events records
#  exist before inserting states records that reference events.
sqlite3 /home/homeassistant/.homeassistant/home-assistant_v2.db <<EOF \
| sed 's/PRAGMA.*/SET FOREIGN_KEY_CHECKS=0; SET time_zone="+00:00";/g' \
| sed 's/TRANSACTION//' \
| sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' \
| sed 's/"\(\<.*\>\)" /`\1` /g' \
| sed 's/INSERT INTO/INSERT IGNORE INTO/g' \
| sed 's/CREATE INDEX.*//g' \
| tee schema_changes.sql \
| mysql -u${DBUser} -p${DBPassword} -h${DBHost} homeassistant &
.dump schema_changes
.exit
EOF
wait    # copy operations run in background then wait; so order of operations is preserved.
sqlite3 /home/homeassistant/.homeassistant/home-assistant_v2.db <<EOF \
| sed 's/PRAGMA.*/SET FOREIGN_KEY_CHECKS=0; SET time_zone="+00:00";/g' \
| sed 's/TRANSACTION//' \
| sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' \
| sed 's/"\(\<.*\>\)" /`\1` /g' \
| sed 's/INSERT INTO/INSERT IGNORE INTO/g' \
| sed 's/CREATE INDEX.*//g' \
| tee recorder_runs.sql \
| mysql -u${DBUser} -p${DBPassword} -h${DBHost} homeassistant &
.dump recorder_runs
.exit
EOF
wait
sqlite3 /home/homeassistant/.homeassistant/home-assistant_v2.db <<EOF \
| sed 's/PRAGMA.*/SET FOREIGN_KEY_CHECKS=0; SET time_zone="+00:00";/g' \
| sed 's/TRANSACTION//' \
| sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' \
| sed 's/"\(\<.*\>\)" /`\1` /g' \
| sed 's/INSERT INTO/INSERT IGNORE INTO/g' \
| sed 's/CREATE INDEX.*//g' \
| tee events.sql \
| mysql -u${DBUser} -p${DBPassword} -h${DBHost} homeassistant &
.dump events
.exit
EOF
wait
sqlite3 /home/homeassistant/.homeassistant/home-assistant_v2.db <<EOF \
| sed 's/PRAGMA.*/SET FOREIGN_KEY_CHECKS=0; SET time_zone="+00:00";/g' \
| sed 's/TRANSACTION//' \
| sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' \
| sed 's/"\(\<.*\>\)" /`\1` /g' \
| sed 's/INSERT INTO/INSERT IGNORE INTO/g' \
| sed 's/CREATE INDEX.*//g' \
| tee states.sql \
| mysql -u${DBUser} -p${DBPassword} -h${DBHost} homeassistant &
.dump states
.exit
EOF
wait

echo "Begin    starting homeassistant @ $(date)"; sudo systemctl start [email protected]; echo "Finished starting homeassistant @ $(date)"
5 Likes
120

You might look at the instructions for using PostgreSQL, it is much simpler and also resolves the performance issues.