Large homeassistant database files

Configuration
208

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

209

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

210

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.

211

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
212

Yup that was it! Everything working as expected.

Thanks!

213

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:

  1. 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.

  2. On the Pi (as pi user) run:
    sudo apt-get install libmysqlclient-dev

  3. change to hass user:
    sudo su -s /bin/bash hass

  4. Activate the VirtualEnv:
    source /srv/hass/bin/activate

  5. Install the mySQL client libraries
    pip install mysqlclient

  6. 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

  7. Restart the HA services.

It worked for me :slight_smile:

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
214

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

215

@marksev1 Have a look here

216

Yep it is on a QNAP NAS drive over the network. Performance seems fine

217

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

218

Deleting the file and restarting hass, creates a new empty database file.

219

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.

220

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.

221

Thanks @VDRainer I will definitely do that.
I wasn’t aware of the purge time (every 2 days) that’s really good to know. I am usually in the habit of rebooting my system after I make any significant changes just to make sure that it comes up fine, so I presume that I kept resetting the clock. Thanks for you input.

222

Here’s a closed issue about that problem, and a way you can set (don’t tell the devs :sunglasses:) the purge interval.

223

@markbajaj

Can you provide some more details on the above process, tried attempting this setup but not sure how/where to create user/db.

I tried using phpMyadmin but can’t login.

Thanks.

224

I used PHP admin on the QNAP (I needed to install that on the QNAP as well as MySQL). I did need to google the default password etc

225

Did you use localhost as the host name?

I’m getting the following recurring error, I even tried using the root account:

2017-08-05 13:30:02 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (_mysql_exceptions.OperationalError) (1130, "Host 'Hassbian_Pi3.home' is not allowed to connect to this MariaDB server") (retrying in 3 seconds)

226

Hi

Is that on a QNAP? i have not seen that screen before?

227

Yes phpAdmin on Qnap.