Are HA Backups useless? 🚼 (sqlite db always corrupted)

Configuration
,
21

Open source development isn’t interchangeable — people work on what they’re interested in. I get that you’re joking, but
 if people want this better, the best way to do it is to start actually making it better.

22

That’s easier said than done. The recorder is a very central part of HA and has inter-dependencies into several other parts. Changing it would be a very significant amount of work and requires indepth knowledge of the HA internals. Add to this a very ‘closed-inner-circle’ BDFL approach of the core developers, where it is extremely difficult to get anything approved into core that would change a fundamental part of HA as an outsider, regardless of how good it is technically. There are so many hoops they make you jump through, and there’s no guarantee they won’t just throw it out at the end and you wasted all your time.

Realistically, something like a completely new recorder component would be impossible to get approved into core unless one of the main devs does it.

3 Likes
23

If any of that was true we wouldn’t have:

  • Auth
  • Automation Choose
  • Automation Modes (Parallel, Restart, etc)
  • Revamped Template Engine
  • Scenes apply/recall
  • Lovelace

I could go on. Most of home assistant is built by volunteers. Many of the major features were built by people outside the 10 developers and outside the additional group of 50 developers that contribute a significant amount.

Being a Member on GitHub for home assistant grants you additional abilities on GitHub, that’s it. This whole inner circle crap was drummed up by conspiracy theorists who were upset that their PRs were closed when they didn’t follow the contribution rules. And as you can see by the examples above, you don’t even need to be a member in order to make a significant impact on Home Assistant.

2 Likes
24

Pretty much sums it up in a nutshell.

1 Like
25

Are you using the “official” ADD-ON , or an “external” MariaDB ?

26

Personally I recommend external on a naz.

1 Like
27

Thx for your answer, “external” was/is my intention eventually, but as my HA is quite new (about 4 month) , i have spend time learning, so i have no immediate needs for a “switch-over”, i first did a change to Influx, to get Grafana, after that i realized i had to “fiddle a little” with both retention/purge and recorder ( 1 of the reasons id like to go for MariaDB, as there are actually only few data-sets i find reason to keep for longer periods) , “Energy Dashboard” seems to “solve” 1 of them :slight_smile: , and with 2 mSATA on same “Board” as HA, i doubt that i will notice a different in performance

28

You might notice slight performance boosts depending on the speed of your naz. Originally for me, it was a huge boost because I was running on a PI3 and moved it to a naz.

1 Like
29

@AaronCake

I Don’t agree as “recorder component” should do what it’s build for Record!, and in my opinion Everything, and you have the options to choose whatever you want. Don’t involve recorder, in this topic, but cause it do what You want, You just have to configure cording to your preferences, which of-cause lead to your other “concern” History , true with 1-3 month storage of ALL records, it will be slow, specially if you want to see a period of 2weeks/1 month , You can easily, do that for 1 entities ( In the History View ).
Currently, if you want to see i.e 1 month for only 6 entities, there is no options “yet” for that, so that would be a “Feature-Request” ) 
 or You could “fix” that with a “mark-up/html” Card 

( And tell me which DB( architecture ) wouldn’t be slow, if you want to “Pull” all records for 1-3 month in a view( thou that would be the browser’s concern ) , but in 1 “click” ?

But as you seems “comfortable” with squlite /db_in_common / JSON etc., i do believe you then also are “comfortable” with .JS 
 then instead of configure your own card( as above ) You could offer you expertise ti fix this Feature-Request ( there are several feature-request in regards to “history-view”, im sure you even could “combine” some of those, and by that please a-lot of people

EDIT2: As it sounds like you use “official” Homeassistant OS in VB/VM, have you tried to “copy” your DB-file, when HA is shutdown ? , i.e if in windows( I know it’s like pretty often) that microsoft what’s you to reboot system, and i know it’s also required sometimes in any linux-system, regardless, you can’t “bypass” this, and every-time you “apply” major Updates, it seems to me that you also have to “restart” HA, try to “Shutdown” HA, copy your DB, Start-up 
 im sure the Update wont get “corrupted” by that, and if you don’t have-to or want-to restart your HA, during time between major updates(or other, where a restart is required), you just wait until you find it appropriate or convenient, and follow above simple copy-db-file, when system is down ( PS: No treads or transactions running )

I don’t wanna put a burden on you, it’s just a tip, to make life more flawless, and comfortable

30

I use the official one.

1 Like
31

Absolutely and fully understood. Honestly I’d love to make a “pull request” with a new database structure and recorder that I have contributed but I really know nothing of Python and sadly just don’t have the time to contribute is this way which would involve learning another programming language.

Someone with this knowledge will have to do this in the future though because it is obvious from the constant database/recorder problems people experience that the current setup is completely unsustainable.

32

The issue is the way in which the recorder component uses the database. It is the most inefficient, space using and slowest way to save information to a database possible. Native data types are not used, large amounts of data are stored as strings in TEXT fields. There is simply no way to fix this other than redesign.

If one limits the amount of data stored either by filtering with the recorder component or simply setting purge_keep_days to a week or two, then it is somewhat manageable but severely limits functionality. And this is an option which must be explicitly set. Because if someone installs HA, then adds a sensor or two which post data every few seconds, in a few months HA is no longer running because the database has grown to 30GB and filled up all available drive space. When really, this data should be using 30MB.

I run HA Core in a venv on Ubuntu Server w/MySQL as the recorder DB. Honestly I don’t even bother trying to keep the database between (infrequent, maybe a few times a year) update. I just drop the database and let HA recreate it.

33

ok, i understand your point, sqlite was new to me when starting with HA, thou i had working experience with Oracle, and MySql in private, so yes i was a-bit “puzzled” of the “limitations” in this “HA-Component”, but after few days saw the “alternatives” MariaDB, or what-ever, quickly changed to Influx( but due to Grafana ) 
 and yes, the limitations do causes irritations, thou stated in documentation i got the impression that keeping the default Purge-settings , and with a “limited/minimum” installation/integrations/use, it would be sufficient as is( for most average user) ( raising default disk-size to 50, on existing install-media(images) and Bonk into people to READ THE DOCUMENTATION and Update-Notes, would(
could 
) ease the “emmidiate” pain for alot of people, but eventually the same people will end up in “point of no return” anyway, Disk Full 
 it has been a “problem” since before disk-storage was “invented” 


 it’s kind of like the same in many daily life situations ( that people have to learn to deal with ) , like you fill up your clothes, your fridge ( it does have it’s capacity-limit ) you can buy a new ( bigger ) or you can start consuming, but then you will see ( You have to fill it again ) no matter what ( and in good time, so you don’t run out of milk or beers ) and if you buy to much Groceries, it won’t fit in your fridge.

 People have learned to “live” with many routines through 1000 of years, now We just have to learn to live with that Phones do have a “storage” limit, computers the same, every “modern” and old storages likewise 
 and yet, like after 30 years of this “new” Add-On in peoples life, people still think that the Milk-Man comes daily, and place full-bottles on the door step, or that "someone throws out “outdated” milk and food.
And the “solutions” in HA are mentioned, everyone knows ( Or they can only blame them self ) , Many solve it various ways, like you mention your self, i dropped 1 or 2, before switching to Influx and fiddled with the “settings” in Influx/configuration.yaml, keeping the DB-size fairly stable, and recently increased the default-disk-size to 60GB ( I know some seems to have problems with this(But with WMware and the HA’s standard Images ability to automatically increase to the new available Size, no problems ) but are eventually installing a MariaDB, as im quite annoyed annoyed over the “poor” overview and options i can get in sqlite, like setting purge on individual tables, and easily adding “external” sources( in new tables to use in templates/html-Cards )

34

Also , i would llike to see your “calculation” you base you Statement upon , and your statement that ( Large amount of data stored as strings in TEXT fields ) , should be a cause.

I don’t know how many “entries” you have in your DB-File, but a quick view in sql-web, “events” and “states” , would fairly “Summaries” the amount of “Columns” needed , for each “Row” , and amount of entries of-cause also depends on amount of devices/entities/add-ons etc. etc.

But i am a-bit puzzled about how you got those “numbers” ( 30 GB should be 30MB ) 
 it simple doesn’t doesn’t seems to be concrete, reliable information you are trying to spread here

1 Like
35

It’s not really a calculation, but born from experience.

I created an energy sensor that takes readings every 2 seconds of about 12 items.

Published it to HA via MQTT.

Within 30 days, my database had increased beyond 10GB in size and HA was suffering. History was unusable, cameras didn’t how up due to all the DB thrashing happening in the background, and the energy data I was storing was pointless as I couldn’t do anything with it.

I rewrote the (Arduino) code on the sensor to instead post the values directly to a MySQL DB using native data types. Exactly the same data, after about 30 days, too about 200MB to store. I used SQL sensors to bring the data into HA creating queries to create a 10 second average in SQL.

Can read a little about it here:
https://community.home-assistant.io/t/how-to-save-sensor-data-to-mysql-database/163094/13

1 Like
36

ok, i interpret this as, the data you gained from that sensor during 30 days is about 200MB, as you say 
 so One could wonder what happened during these first 30 days, when you had it in homeassistant.db 
 i don’t know which “period” we are talking about, but i do know that within past 3 month, “Some” serious major “change-events” have occurred AND announced at several places, not alone in Update-notes ! 
 beside these “Changes”, there are the additional CORE updates ( Bunch of Feature-Requests from Users !" included, etc . etc. beside you did change default purge to more than 14 days, and You maybe added an extra “FEW” entities Your self :wink:

Where is this “event” stored ?

where is this “event” stored ?


 and where is this event stored ?

37

Most likely the recorder was designed with the intent of moving to a NOSQL database at some point, or to one of the growing number of SQL databases that support JSON as a native format.

This is not a reason for a complete recorder rewrite- it is wat allows the recorder to be flexible in what it stores. To badly translate a Dutch proverb: A relational SQL database is about as flexible as a lead door. This is why NOSQL databases are gaining so much traction.

38

Edit:

Geeesus, why you write this, now i just got another “new” Option :slight_smile: to look into

I don’t know the reasons, but it’s obvious that there are quite a few that seems to have “missed” that the Recorder Now Records ALL, so people have to make their choices, depending on their needs/installation etc. Flexibility a High level 
 Number 1 Choice i went for HomeAssistant

39

To be fair, they do use some native datatypes in the table. I think this was done maybe a year or two ago ? I remember at some point they had to update the db format and migrate the db (which resulted in some cryptic error on my setup and the db being corrupted, but that’s another story). Right now, the states table format looks like this:

image

Lots of varchars (some of which are unavoidable to some extent), a hideous TEXT (see below) and lots of redundant data being stored. Every single state change stores the full entity ID (as text), full domain name (as text too) and, worst of all, a full json dump of all the attributes:

image

A full json text dump for every single state change of every sensor.

The amount of redundancy in that db is insane. Not only will this bloat the db size, it also makes it slow to load. Not so much on the SQL query side, but all that redundant data needs to be parsed back into Python objects. And parsing is slow, and Python is slow. The problem here is, as you said, there’s no easy fix for this besides a full redesign of the recorder system from the ground up. State changes need to identify their sources over short index IDs or GUIDs that link back into another table for the entities and attributes to cut down on both parsing overhead and redundancy.

1 Like
40

I just did a simple check on a random state table entry on my db for a temperature sensor. A single state, as it is, uses 294 bytes in the db for a single temperature value. The exact size varies by sensor and even state, due to liberal use of variable length cells. If you remove most redundant overhead, keep the states and event IDs, a timestamp, an additional ID to link to the source entity and attribute table and finally, a IEEE float for the value, that data can be compressed into 24 bytes. That’s 8% of the original size. And technically this could be made even smaller.

1 Like