This is my configuration, do not understand why the db grows up so fast. What to do??
recorder:
purge_days: 2
db_url: sqlite:///home/homeassistant/.homeassistant/home-assistant_v2.db
include:
entities:
- sensor.dark_sky_summary
- switch.closet
- binary_sensor.motion_sensor_158d000127acc3
# History setting is empty now
history:You need to run an SQL query on the database (probably stop hass first) to figure out what is being logged that often. 30GB is definitely not normal. It’s also possible that the sqlite is not reclaiming the space from the purged data.
do not undestand this … how to fix?
That’s assuming the purge is even being run. Purges currently run every 2 days but that’s two days of continuous uptime (even then, it actually runs the purge somewhere in the hour 49…). If you restart HA, you restart the clock.
Given that as a mod, I see claudio in many threads, excitedly trying out each new project that’s posted (BTW, nothing wrong with that!! I try out a lot of new stuff myself, simply because I can and doing so makes me a better mod); my guess is that he is restarting HA several times a day at the very least.
Claudio, read this thread below - while it’s kind of long, it will be of huge value to you. You should also look into using history and logger include/exclude options to have a better control over what you actually store in your db. Many things like sunrise/sunset data and weather states are things that are probably unnecessary but are filling up your db.
is there any fast solution to my problem. I just want the log not to become so big, I do not care about history, nor to see whats isnide the log (it will take hours to move it to a PC and look into it, and I really do not care what’s inside
I deleted the file, restarted HASS changed my configuration as below. And in few minutes is already 70MB
recorder:
purge_days: 1
db_url: sqlite:///home/homeassistant/.homeassistant/home-assistant_v2.db
exclude:
domains:
- automation
- weblink
- updater
- sensor
- switch
- media_player
- light
entities:
- sun.sun
# History setting is empty now
history:
exclude:
domains:
- automation
- weblink
- updater
- sensor
- switch
- media_player
- light
#I downloaded this program to open the database
but asks for a password (please enter the key used to encrypt the database)… which password??? (sudo or HA password is not)
If you really don’t care about either at the moment, how about just commenting out history and logger in your config completely. Then see where you’re at with size.
Based on what you showed in your excludes, 70mb in a few minutes is rather large; something else is going on here.
Sorry I can’t help you with the password; this is one of the other reasons I moved from SQLite to MySQL.
well I followed instructions and moved to mysql too
I did this
Add the following to your configuration.yaml file:
recorder:
db_url: mysql://hassuser:[email protected]/db_name?charset=utf8
and I have this error. Where do I give name to the DB?
17-05-19 16:22:41 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (_mysql_exceptions.OperationalError) (1049, "Unknown database 'db_name'") (retrying in 3 seconds)I understood hass_db
where is this stored?
I rewrote my automations / sensors so that they do not use time as a trigger or as a sensor update entity. This reduces the entries number is mysql database by 80%.
I also found entries in the database that seem to serve no purpose for the logbook or history and can be deleted. Although these entries are numerous, they don’t take a lot of room, but might slow searches.
Did you install MySQL and the requirements as laid out in the thread? Did you create the user, db, etc. as explained in the thread?
Or did you just change your configuration.yaml?
There’s prerequisites to moving from one DB platform to another, you can’t just change a line in the config. Inatalling MySQL and creating the user and blank db are a big part of this; you will need to read that thread.
And I would suggest reading some of my findings at the end before moving forward; there are some things that you will want to do in the initial setup that are a lot more difficult to do AFTER you install and move to MySQL that will make a big difference in performance and storage space.
yes followed this link
My URL looks like this:
db_url: 'mysql://dbuser:password@localhost/hass?charset=utf8'
Keep in mind that the dbuser name/password is the user you set up in MySQL, not the credentials in HA (assuming you didn’t use the same credentials in MySQL as you did in HA). hass is the name of my database, btw - note the pathing.
Also I would suggest reading this post before you go any further. As I said, this makes a big difference and is easier to do when you are initially setting up than afterwards.
I’m going to be in an out today, but I’ll try to check in to see how you are doing. But keep in mind there may be some lag time between responses. If anyone else can help out Claudio, I’d appreciate it.
so far it works all ok.
I just do not know where the hass_db is stored to check its size.
It should be in
/var/lib/mysql
but I cn’t access the directory with WINSCP, it gives me permission error (although I am inside with my pi user password)
Running MySQL on an SD Card will wear out your ad card pretty fast, but then again so does generating a 30GB SQLite file.
can you tell me how to minimize the database?
I find my database size by going to terminal and typing
mysql -u hass -p
then my password (hass is my mysql username)
then this for size, (just copy, paste and hit enter)
SELECT table_schema "DB Name",
Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema;
I also installed phpmyadmin which make looking and searching mysql much easier.