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!
This is an interesting idea. I’m looking forward to learning more about it as you progress.
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))]
I do. Currently its set at 1 day.
@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…
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.
Is the MiraDB restarting for some reason at 2-3AM? Check /var/log/syslog for errors.
I’m not really seeing anything in the logs to identify a problem, however I have the MariaDB app on Synology back up each night about that time. I wonder if it shuts the server down while backing up. I’ll disable that back for tonight to see if it continues to happen.
Yup that’d cause it, in order to back it up it has to free the file lock so it shuts down the service, if you want to back it up you could setup a mirror database on your NAS and have it replicate nightly.
1 Like
Yup that was it! Everything working as expected.
Thanks!
Hi All,
A little late to the party , but I just moved the database to a MySQL database hosted on my network on a QNAP NAS.
I have HA in VirtualEnv on a pi and the user I use is hass for HA.
Here are the steps I took to get it working:
-
Create user in MySQL and create database (I used the same name ) and grant all privileges to that user on the database on the QNAP.
-
On the Pi (as pi user) run:
sudo apt-get install libmysqlclient-dev -
change to hass user:
sudo su -s /bin/bash hass -
Activate the VirtualEnv:
source /srv/hass/bin/activate -
Install the mySQL client libraries
pip install mysqlclient -
Exit the virtual environment and edit the configuration.yaml file:recorder:
db_url: mysql://ha_db:[email protected]:3306/ha_db?charset=utf8
purge_days: 14 -
Restart the HA services.
It worked for me 
Really quick now to load the history etc. I also deleted the original database to get back 5GB of data on the SD card.
2 Likes
So you have the database on another computer not the one running HASS right? It writes via the network to the database located on the other computer not on the SD card of the RPI? Do I understand it correctly?
Also I’d be interested on how to do the same stuff for InfluxDB/Graphana. (like having it on a separate single board computer).
Yep it is on a QNAP NAS drive over the network. Performance seems fine
Resurrecting this thread as I am finding that my home-assistant_v2.db is getting quite large. I’ve read several threads and did not find an answer that would match my needs. Although I could go down the path of installing some other databases, for now my needs are simple and could be quite satisfied with the sqlite that’s on the box by default.
Right now, my database is 1.35 GB in size. Two days ago, I’ve added the “purge_days: 1” to the recorder section of my configuration file it looks something like this:
recorder:
purge_days: 1
I haven’t seen a decrease in file size and just keep growing day to day. So my question is two fold.
Anyone had any issues with purge_days? Does it really work and when would the changes on the db file take place (once a day?) I’ve also rebooted the pi several times just to see if the action would take place at startup and since I made the changes two days ago, I would have assumed that the changes would have taken place by now.
Second, is there a way to manually purge the database? I had tried in the past to delete the file and recreate a blank file, but this never populated with new information.
Any hints/guidance would be appreciated
Deleting the file and restarting hass, creates a new empty database file.
Thanks sendorm, that worked this time. Most likely because I stopped the HA Service first, deleted the db file and restarted. Still seems to be populating quickly, I will see what I can do to exclude some items from being recorded. Still wondering if the purge_days will be working or if I will have to delete the file every so often.
Hi @berniebl, configure the logger with:
logger:
default: warn
logs:
homeassistant.components.recorder: debug
and you will see what’s going on with the recorder.
By default it purges every two days, resetting the timer on HA start.