I think all you need to do is to add your cameras to the exclude list. You can find you camera entitiy names on the dev state page. I guessed at them below. (I didn’t see your mysql url so you need to change it)
recorder:
purge_days: 2
db_url: mysql://hass:[email protected]/HOMEBASE
exclude:
entities:
-camera.camera_41
-camera.camera_42
-camera.camera_43I will try that, but I started having problems this week. While I have those cameras since 6 months and more … not sure is that.
I am not 100% sure, but I think I started to have problems when I upgraded from 0.43.2 to 0.44.1 and it failed, so I reverted back to 0.43.2 and started to have problems, then upgraded again and problems persisted (but can’t be 100% sure).
Other things I can think of, is the use of Zanzito (the photo part?), but also does not explain so many GB per day
This was what I was talking about when I referred to too many things at once. I would start by commenting out anything new and non essential to operations just to cam things down a bit.
The problem is finding the large records in the db; I’m not a SQL expert and I’m having a bit of a problem finding a query that will show the SIZE of the largest records. I’m still looking but a db guy would be helpful here.
yes or the number amount of same entity in the DB, or something like that
In case you still have a backup of one of your huge sqlite-databases, you could use the following queries to get some more info about the content that’s consuming the space:
Getting the row with the most data in the event_data column:
SELECT event_type,event_data FROM events ORDER BY length(event_data) DESC LIMIT 1;
Getting the row with the most data in the attributes column:
SELECT entity_id, attributes FROM states ORDER BY length(attributes) DESC LIMIT 1;
Now that you’ve said you’re using the Zanzito app, I think that might actually be the issue. I have never used it, but from having a few glimpses at the thread I believe the camera-data is transferred via MQTT, which then could be binary data inside the JSON that is being saved as the event_data (the first query of the two I have provided). There’s not an encoded / efficiently compressed video in your database, but rather a LOT of JPEG snapshots or something like that.
So thinking about it, Zanzito would be the first thing that comes to my mind to cause the database bloating.
unfortunately I do not have a copy of the sqlite, I have changed to mysql because of suggestion in this thread.
Is there any similar command to check in mysql DB?
Yes I did some photos with Zanzito, but was in the past, maybe last week the last one … you think that once a photo is done, is continuously added to the DB? And to do GByte per day???
I’ve never used cameras in HASS. But using other MQTT platforms I know, that their state is saved to provide a compete history. If the MQTT camera is implemented in such a way, that the image-data is part of the entities state, then yes, it would probably save all the images to the database since the image would be part of the state. I have to correct my statement from above also, since the data wouldn’t be in the events tabel but rather in the states table.
I don’t know the query for MySQL. It will be pretty smiliar though. But maybe phpmyadmin has the ability to sort by size included? Haven’t used phpmyadmin for a while, so I don’t knoe that.
but I did maybe 3 or 4 pictures not more then that. It could be the issue if its saving the same picture over and over, like every second or so (like increasing the DB of 1MB per second, 60MB per minute or something
That’s what I’m not sure about. I don’t know how cameras in HASS work. Even without actively taking pictures, it could be that the stream of the camera is read permanently, and thus saving the state.
Hi, I developed Zanzito and modified the HA MQTT core component to manage binary payloads and wrote the MQTT camera component. I might be wrong, but basing on the code and my experience, I don’t think Zanzito is the problem.
When Ha receives a binary payload, it doesn’t log its content. Besides, the camera component acts like any other HA still image camera component: it doesn’t receive a “stream” of data, but just the last picture sent: it doesn’t record or save anything. And Zanzito doesn’t send images continuously unless told to do so…
The custom HA notifier component I wrote for Zanzito bypasses completely the HA Event bus architecture and publishes directly to the MQTT server, so neither sending a picture to Zanzito logs any binary data.
I myself have six Zanzito cameras in HA, two of which send pictures every 30 seconds, my recorder is configured as follows:
recorder:
db_url: mysql://hassuser:[email protected]/hass_db
purge_days: 10
and my db is less than one Gb in total size.
Unless there’s a sneaky bug somewhere, I’m afraid the culprit is somewhere else 
gl
Yeah, I didn’t hink Zanzito was the culprit but something is storing large binary data in his db. My vote would have been for ffmpeg motion sensing, but now I’m thinking that HA should just be saving the state data of the sensor.
What about cameras? Anything else in HA that may store large binary data?
I just have sensor, lights, switch … I mean all of this are bytes … before you do GB you need months, not 1 day
Yeah you would think photo, since is 1M and if there is a problem, like one store a second or few, that can bring GB in a day … nothing else I could think of.
Once I know how to deal with the mysql, I will put back recorder, and I can check
Those commands work in mysql, if you have phpmyadmin installed, go to the sql page here and type them in, after you select your database. Use the option to show full text to see what you got.
From the terminal, type
mysql -u username -p
use DATABASENAME;
SELECT entity_id, attributes FROM states ORDER BY length(attributes) DESC LIMIT 1;
SELECT event_type,event_data FROM events ORDER BY length(event_data) DESC LIMIT 1;
This won’t help the size issue but I did put some sql commands in a bash script to reduce the number of entries in the database. I setup HA to run the shell command to the bash script every 30 mins.
#!/bin/sh
mysql -uUSERNAME -pPASSWORD <<QUERY_INPUT
USE DATABASENAME;
DELETE FROM events WHERE event_type = 'service_executed';
DELETE FROM events WHERE event_type = 'call_service';
DELETE FROM events WHERE event_type = 'component_loaded';
QUERY_INPUT
It has reduced the rows by 1.5M or 90% and increase speed of loading logbook and history with no apparent ill affects. The size is about the same at 800MB.
This was what I was trying to post last night when my broadband went down - and up - and down - and…
As I’m typing this, I’m not even sure if it will still be up when I press Reply.
But what I missed was ORDER BY length(event_data) so it wouldn’t have helped anyway - thanks for posting this @RobDYI.
I sure hope my connection is more stable today. I couldn’t even use the phone to call for support.
Now my HASS is crashing every 2 hours or so (pi3 not), so something else must going on. How can I troubleshoot this?
And the DB file is 25Gbyte, even if recorder is off.
You know what, its time to reformat all
Did you also remove history?
I have a brand new installation. Now on 0.45
recorder is not present, history is, the DB its growing at 1MB every 3 seconds … too much
now I have back sqlite, how/where to issue those commands?
I have DB browser for sqlite and I am inside the DB
