Home-assistant.db 30Gbyte in one day, what to do?

Configuration
20

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.

https://www.phpmyadmin.net/

1 Like
21

1283 MB is the size, after what 1 hour …

22

you are building size really fast, do you use time as a sensor update entry?

23

I am not sure what is that.

Like what for example?

24

trying to install this now. I hope with that I can see what’s inside, and also delete the DB

25

When people create sensors they sometime have difficulty on how it updates. As a solution, they set this to time so it updates the sensor every second. This adds a lot to the database.

entity_id (Optional): Add a list of entity IDs so the sensor only reacts to state changes of these entities. This will reduce the number of times the sensor will try to update it’s state.

it is done here

26

I do this for quick delete and create a new HA database.

mysql> drop database DATABASENAME;
mysql> create database DATABASENAME;

1 Like
27

I do not think I have this.

But something I must have

28

I downloaded on my windows 10 machine. Have no idea how to start the program.

Damned I just want this **** database to not populate, and that’s it

30

phpmyadmin is probably not for you if you just want to reduce your database. I thought it might be helpful to analyse the database entries to see which one is taking the most space. I would suggest you use the include option in the recorder and add only what you really want to log. (or track down which ones are causing the problem) You can then delete the database and recreate and see if the size builds as fast.

if you want phpmyadmin, put it in the www directory of HA or another webserver on your mysql machine.

I point to mine with this on builtin webserver on a mac.

xxxxxxxx.duckdns.org/~username/phpMyAdmin/phpinfo.php

31

I just want to fix the problem, but each steps it seems I add problems

I followed this tutorial to install phpmyadmin on the same pi3 of HASS

and … it doesn’t work, pointing to
http://192.168.1.238/phpmyadmin
gives

404: Not Found

on my web browser

32

try

http://192.168.1.238/phpMyAdmin/phpinfo.php

33

same
404 not found

34

Ha static webserver might need this

http://192.168.1.238:8123/local/phpMyAdmin/phpinfo.php

35

@anon35356645 If you have another always on computer on your network or preferably a NAS, you may be able to set up mysql or one of the other supported databases there that won’t put the strain on your HASS machine. 30 GB sounds like a lot of database in a single day. Are you sure you aren’t misreading the size? At the command line try
ls -lh

36

I don’t understand much of all this.

I followed these instructions

I think it installed apache webserver or something?

EDIT was missing an installation command
sudo apt-get install python-mysqldb
sudo /etc/init.d/apache2 restart

37

I know very little of a lot of things.

But I can read B, KB, MB and GB

Now its 350MB, not long ago I deleted and recreated the DB

38

Now I am able to access phpmyadmin

where shall I look at?

39

I have a NAS, but I really don’t care much about DB, maybe just sensor and some light, but it is not so important

40

Most is in EVENTS, but I have no clue what I should look at, there are many of everything