Large homeassistant database files

Configuration
76

This is how i got it working with the AIO Installer. Hopefully it’s of any help for you guys

7 Likes
77

Nicely done - I like your idea of keeping these configs and commands on your github page for reference as well.

For me, the whole key was not having mysqlclient installed in my HA venv. Everything else was straightforward but this was the missing piece.

78

I’ve followed your guide, and I think it works.
Is there a way to check if the new db is used?
And what is the location of the db?

79

If you enter this:
$ mysql -udbname -p
$ status;

You should get something like this:
Threads: 3 Questions: 63484 Slow queries: 0 Opens: 51 Flush tables: 1 Open tables: 44 Queries per second avg: 0.829

Thats how I made sure the DB was in use. And if you deleted the .db file from you hass dir it should not be recreated on startup.

I think the DB is located at its default location /var/lib/mysql

80

Confirmed, same as mine.

81

With 14 days retention my SQLlite hovers around 450mb.

Does moving to MySQL or Postgresql (Howto: Postgresql on Raspberry Pi All-In-One Installer) make a big difference in performance? Would love to her some experiences.

82

YMMV, but for me it was night and day. At about 250mb with SQLite, bringing up detail on cards was agonizingly slow and I was purging on a 7 day basis. Almost 15-18 seconds before the graph data appeared.

Now, with a 14 day purge cycle and about 400+mb on MySQL there is about a 2-3 second wait at most.

83

I switched over to using MariaDB which is running on a different machine in my home network and graphs load much faster now. Also, HA seems a bit snappier too, particularly at startup, but I’m not sure if that is due to the new database or improvements that the devs are making (or both).

84

My db is in use:

Threads: 4  Questions: 54828  Slow queries: 0  Opens: 54  Flush tables: 1  Open tables: 44  Queries per second avg: 5.040

But when I do: pi@homepi:~ $ cd /var/lib/mysql
I get this: -bash: cd: /var/lib/mysql: Permission denied

85

I did try to make it work with mariadb in my nas but it didn’t work. I’m out of ideas. Do you remember the steps that you followed?

86

I followed the Arch Linux wiki entry for MySQL here (the Arch wiki entry for MariaDB redirects to the MySQL entry). My HA instance runs on a Pi2 under the “Pi” user so I followed the Arch Wiki for database installation, created a “Pi” user for the database, created the database for HA, and then pointed HA to the new database and it seems to be working smoothly.

87

I can confirm that @sjorsjes guide works perfectly on the AIO installer!

1 Like
88

I finally decided to do all from scratch, reinstalled raspbian and HA, factory reset and reinstalled MariaDB on NAS and everything work fine at once following exactly the same steps as I described in a previous post based on the quides/recommendations from @sjorsjes @rotor.

In the AIO installer I did the following steps:

sudo apt-get install libmysqlclient-dev
su -s /bin/bash hass
source /srv/hass/hass_venv/bin/activate
pip3 install --upgrade mysqlclient

and then I just made the configuration of the recorder for the database instance on my NAS:

db_url: mysql://hassuser:YOURPASSWORDHERE@IP:Port/hass_db
1 Like
89

Hi all!
I have a problem with HA and MySQL.
Successfully connected HA and MySQL, but HA writes values in database only when starting and I have some errors about MySQLdb in log, last of them is:
UnicodeEncodeError: 'latin-1' codec can't encode characters in position 0-6: ordinal not in range(256)

And after that HA don’t write values at all, until next restart.
I think I’m getting this because I have four template sensors which states are having unicode symbols and in MySQL HA writes that states like “unknown”.
I tried to create database with UTF-8 collation but that didn’t help.
Is there a way to tell HA not to write this values in database at all? I have excluded these sensors from history and logbook, despite that HA writes just everything.

UPDATE:
Found the solution:

  • database should be in utf8 collation;
  • in recorder section, db_url parameter should look like
    mysql://user:pass@hostname/db_name?charset=utf8
13 Likes
90

I had the exact same problem as many people; correct setup but No data flowing into the database. I Made the same change as @omeasire and now everything works.

Moving from sqlite to mysql makes a HUGE change in speed on my pi B for graphing and history!

91

Thanks Rotor! This also solved my issue!

92

@sjorsjes tanks for sharing this. I think it would be great if you could add it to the official docs too.

93

Thank you for the

mysql://user:pass@hostname/db_name?charset=utf8

Did th magic. Can you put this into HA documentation as other wise it woul be hard to find!

1 Like
94

I don’t have a Github account, and don’t know how to do this at all :confused:

95

You don’t need Github; you can simply include it in your configuration.yaml