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.
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.
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).
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
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?
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.
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
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
recordersection,db_urlparameter should look like
mysql://user:pass@hostname/db_name?charset=utf8
13 Likes
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!
Thanks Rotor! This also solved my issue!
@sjorsjes tanks for sharing this. I think it would be great if you could add it to the official docs too.
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
I don’t have a Github account, and don’t know how to do this at all 
You don’t need Github; you can simply include it in your configuration.yaml
It was the answer to the previous post 
I already have a working mysql with hass
The only problem - still getting locked out. Too many
Error during connection setup: (_mysql_exceptions.OperationalError) (1129, “Host ‘xxxyyyzzz’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’”)
Can’t see any errors on the server side
Have purge set to 7 days and having MySQL database as a backend. Still very unhappy with the perfomance. Database gets 1,5M records in 7 days. History or logbook takes forever to render.
So one point is excluding as much as i can from history but at the same time - will switching to Postgre render any benefits?
I had no problems getting this to work with MariaDB running on a different host (Synology NAS). I did have to install the mysqlclient. Before I did that HASS would refuse to start with errors that looked like:
17-01-05 21:02:24 aiohttp.server: Error handling request
Traceback (most recent call last):
File "/home/homeassistant/.homeassistant/deps/aiohttp/web_server.py", line 61, in handle_request
resp = yield from self._handler(request)
File "/home/homeassistant/.homeassistant/deps/aiohttp/web.py", line 249, in _handle
resp = yield from handler(request)
File "/usr/lib/python3.4/asyncio/coroutines.py", line 143, in coro
res = yield from res
File "/usr/lib/python3.4/asyncio/coroutines.py", line 143, in coro
res = yield from res
File "/srv/homeassistant/lib/python3.4/site-packages/homeassistant/components/http/__init__.py", line 427, in handle
result = yield from result
File "/srv/homeassistant/lib/python3.4/site-packages/homeassistant/components/history.py", line 240, in get
self.filters)
File "/usr/lib/python3.4/asyncio/futures.py", line 388, in __iter__
yield self # This tells Task to wait for completion.
File "/usr/lib/python3.4/asyncio/tasks.py", line 286, in _wakeup
value = future.result()
File "/usr/lib/python3.4/asyncio/futures.py", line 269, in result
raise CancelledError
concurrent.futures._base.CancelledError`
etc. but after installing mysqlclient it works fine and the HASS interface is quite a bit faster and more responsive. Made a very noticeable difference!
One issue I did notice with configuring SQL on my NAS was that the default user permissions prevent remote host logins! I used phpmyadmin and got this as a warning in the interface. I had to drop two user permissions that allowed any login/any host/any user from localhost. HA would not connect to the MariaDB server until I did!
If you are trying to connect to SQL on your NAS, check the default logins in your database server!
3 Likes
Okay, I was attempting to do this tonight and I guess I mucked up something.
I got to the part:
CREATE USER 'hassuser'@'localhost' IDENTIFIED BY 'XXX';
I assume the password is the root password I set up when mysql installed? Then I forgot the stupid semi-colon and seemingly could not get out of there but somehow managed to do so. When I went back into mysql and tried to run the command again, I got an error:
ERROR 1396 (HY000): Operation CREATE USER failed for 'hassuser'@'localhost'
Though I could run:
SELECT User FROM mysql.user;
+------------------+
| User |
+------------------+
| root |
| root |
| root |
| debian-sys-maint |
| hassuser |
| root |
+------------------+
6 rows in set (0.00 sec)
and it appears the hassuser is there. So I moved on just in case. Then trying to grant the privileges:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'hassuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
And then when I tried to flush the privileges, even though it did not appear that any were granted in the previous command:
I forgot the blasted semi-colon again and had to escape out and went back in
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
I was not confident it went as it should but thought I will just finish out the venv part, then ran into this:
sudo su -s /bin/bash homeassistant
No passwd entry for user 'homeassistant'
So how bad did I muck things up? And whats with the errors?