did you ever figure this one out?
I’m having similar issues, but with a higher frequency.
Trying to create some sort of automation that reboots HA automatically when it happens, but not quite there yet.
I have the same problem. Did you solved it?
I’m not sure if it’s related but this happens every time I do a scheduled backup. I looked at the logs for MariaDB and saw that the table locks, unlocks then locks again. This happens in the same moment when the backup triggers. Another strange thing is that the time is un-syncronized in the first unlock.
Yet again this might be unrelated but I want to point it out
I did a scheduled backup with this add-on hassio-addons/samba-backup at master · thomasmauerer/hassio-addons · GitHub
[15:00:15] INFO: Lock tables using mariadb client...
[15:00:15] INFO: MariaDB tables locked.
[16:12:42] INFO: MariaDB tables unlocked.
[15:00:50] INFO: Lock tables using mariadb client...
[15:00:50] INFO: MariaDB tables locked.Seems most likely related, If the table locks(as it has to during backup), Then the recorder_queue start to grow faster, as it can’t commit “release” entries to the table during a lock. … if you at the same time have no idea of, or don’t control your “Recorder” then you could reach the “maximum-queue–size” during a “Backup-table-lock” or during any other type/cause of “table-lock”.
Many People don’t know or realize the consequences of an default/un-configured Recorder.
Default Recorder monitors,records ALL entities and therefore events related to same !
So beside configuring of the Recorder, people should also have control over/monitor, DB_size, DB_growth_cycle , commit_interval, purge_interval etc.
PS: it SHOULD BE mandatory recommended to read this Doc. In it’s full length
But why does it only happen when I do a scheduled backup and not a normal one?
I assume the scheduled backup is configured by you on the Maria-DB, and the normal backup is done by triggering HA-db-backup, or ? i really don’t know 
.
1 thing i know, if you use an external sqlite DB-Viewer in i.e Windows you should be very careful about what you do, if you for some reason “lock” a table for to long, ha will dump the db-file , and create a new.
And i don’t think this will happens if you use the “native” sqlite-web integration, as HA then still are in control and therefore performs(initiate) the db-lock / unlock process.
HA, can’t control whatever you configure your external DB-Server to do,
The recorder processes it’s “commits” to the external DB, and expects that the external DB is configured as intended, and look like the homeassistand_sqilite_db, but if some other software also have access to this external DB_ HA can’t control the lock/unlock, i.e if the lock is committed by an “external” process ( The sqlite_db_monitor_in_windows_hiccups_syndrome
Oh I see. The way I’m scheduling a backup is through an add-on that transfers the backup file to a Samba share. So I actually haven’t touched Maria-DB and only made the backup either trough the add-on or the normal way as in this: 
Sounds like it’s actually the add-on then, which “initiates” the lock/unlock.
Anyway i have no idea of how your system looks like, whether the DB is external physically and which “add-on” etc.
But anyway that works, works 
, Have you ever tried to perform a “Restore” with 1 of these Scheduled backup-files ?, i just wonder as i don’t know how ur setup looks like
Depending upon your physical setup, Maybe a way to increase the backup-process could be to initially perform the backup to the “internal drive, i.e /backup/temp/”(always faster) ( a local CP_RENAME command is as fast as can be), then transfer to the Samba_share ( which i assume is external, maybe over network to other physical Device ) or ?
The “transfer” process should in my opinion, not in any way, “obstruct” the ordinary function of HA
But there are other factors in the “backup” process to take into consideration, and that is to make sure that the SQLite DB-Software actually makes sure that it backs up a DB_File without “loose” ends , unfinished transactions/commits etc.
So if you want to be sure that it actually works, you have to perform a “restore” ( sure you will loose some events/data/entries, for the time it takes ) … but rather that than realize when you need ! , to restore then all the files you’ve been storing on the Samba-share is useless 
I managed to restore a backup file on a spare Pi. It booted properly but I noticed really strange behaviors. All the history was lost even if it should be backed up. All the dashboards and entities was there though. I will consider another way to do backups.
Yeah, that’s a problem, If a backup doesn’t backup the DB … but same problem many people here have with HA
Edit: Which HA-install do you run ?, homeassistant-os in VM ? , if so best way is to “regularly” copy the whole vm-image to another drive, while you have it shutdown for other purposes.
I use the homeassistant_db , and ofcause gets the “backup’s” during updates, they serves a purpose( to easy roll-back after these updates )
A manual “copy” of " /config " (files + subfolders), can be done at any time (after any changes, to views/themes/automations/scripts etc etc.) … only don’t copy the db-file there, while ha is running ( to avoid corruptions/lock which will create of new db-file ) … and therefore loss of history ( well while these data actually still are available in the “old” file) and in most cases extra-able
The folder .storage is also to be handled with caution while ha is running
I’m using home Assistant OS as it is. I don’t want to use VM’s since I want to have the whole Pi dedicated to HA. I have also found it a bit easier to use HA that way. I think that I will do a normal backup once per week with an automation and then move the backup manually for now.
1 Like
Ok, yes im sure i would also had run HAOS, on a Pi , and backup locally then either manually or external backup(or scripted) transfer/move the backups to other externaldrive
The recorder also stopped working for me at various intervals with the error message "the recorder queue reached the maximum size of 30000; Events are no longer being recorded…
Today I started an intensive search for the reasons.
I have found one - at least for me - and can report here.
First I tried to eliminate trivial errors
- Make sure that the recorder can write away events fast enough. (Check the network connection to the NAS, SD card, etc.).
- Can the system act that fast at all?
- RPI 3 is perhaps at the limit here…
But everything was fine with me.
I then took a look at the log file and - to make a long story short - unfortunately I did not find any further clues here either.
Then I took a look at the home-assistant_v2.db and - at least I hope so - found the culprit:
The recorder also stores call_services. In our case, it seems that a service call is repeated so often that the recorder cannot keep up with the writing.
The appeal has been launched over 1.4 million times.
The corresponding event can then be found in the table “Event-Data” under the corresponding ID.
In my case, the update of the UV values. It seems to fail and is repeated until it works - or not.
I have deactivated the integration for the time being and am now looking at the behaviour. But I think that’s it.
As a solution, I will exclude the call_services from recording.
I hope this approach will help you.
2 Likes
Great “research” , you could also just exclude the entity, if this uv_index is nothing you want/need to store
I don’t know how often this sensor ( or this integration ) poll/pull, but this could also be worth looking at
thanks for this insight of how to check the database, this allowed me to find clashing automations that were causing repeated attempts to cycle a mqtt switch, smashing the database.
Thank you