The recorder queue reached the maximum size of 30000

Configuration
1

I’ve seen a few posts and comments about this error recently, but I’m not sure what people have really done to “solve” it. It happens to me every few days and it’s around the same time that the Google Drive Backup triggers and locks/ unlocks the MariaDB tables. I have my recorder set to keep 4 days. It appears I log ~1M rows per day:

image
image2426×540 174 KB

I just added some ignore items to my recorder config thinking that this might be an issue:

recorder:
  db_url: !secret recorder_url
  purge_keep_days: 4
  exclude:
    domains:
      - camera
      - select
      - remote
    entity_globs:
      - sensor.*_rx
      - sensor.*_tx
      - sensor.rx_*
      - sensor.tx_*
      - sensor.rx
      - sensor.tx
      - sensor.ticwatch_*
      - sensor.*uptime*
      - binary_sensor.ticwatch_*
      - switch.*_rain_delay
      - switch.camera*
      - number.camera*

All of this seems like a pretty big workaround though for a 4gb database. Is 1M rows a day a lot? Is there a way to increase that 30,000 number? I assume this is occurring when the database is locked and then HA queues up events before it can write to the recorder database?

1M/ day means I’m logging about 700 a minute. With a limit of 30,000, that means if the database is locked for 40’ish minutes, I hit this error? Am I understanding that correctly?

Thanks!

2

I mean another alternative is just don’t back up your database. The only data in there is history. If you start over with a brand new db everything will work the same except your history will be reset.

Backing up 4gb is huge. Massive increase in the time it takes to make, upload, download and restore the backup. And if you do hit a crisis and have to restore the best case scenario is you’re restoring a partial picture of the history anyway. You will have lost any data collected after the last backup.

Idk doesn’t seem worth it to me. The main reason I still use the mariadb addon is so that I can exclude it from my backups. All the recent performance improvements have made the performance benefits of using it over the native SQLite db obsolete, at least that’s my understanding. I just want to keep my emergency backups trim by excluding the db and you can’t do that with the SQLite db (since it lives in /config). I can always take a one off backup with the db in the event of a planned migration (like if I bought a new machine to run ha on).

3

Also I can’t speak for others but to me that sounds pretty big. I keep 7 days and mine is 2gb.

You aren’t doing all this on an sdcard right? If so you might want to look into moving off that right away. It sounds like you’re absolutely churning through it’s lifetime writes with all the writes to the db plus making a complete 4gb backup of it every day.

4

I mean another alternative is just don’t back up your database. The only data in there is history.

Good point. I guess I defaulted to backing everything up for the “easy button” if I need to restore. Though, I haven’t needed to do that in a long time. I’ll look into that. I do have InfluxDB running too, so the “full history” is already somewhere else.

You aren’t doing all this on an sdcard right? If so you might want to look into moving off that right away. It sounds like you’re absolutely churning through it’s lifetime writes with all the writes to the db plus making a complete 4gb backup of it every day.

No, it’s a NUC, running HA inside of a VM.

1 Like
5

@hoyt you still having issues with your recorder? I get this same issue every now and then and it drives me nuts! I have automatic backups turned completely off at the moment an it still occurred overnight…

6

I ended up excluding MariaDB from the drive backup.