Full backup runs at 4:00 am but recorder/logbook/history does not resume when backup is finished

[dbrunt (Daniel)]

Is/has anyone else experienc[ing/ed] this issue with MariaDB?
Full backup runs at 4:00 am via Google Drive Backup (same issue when triggered by Samba Backup) but recorder does not resume when backup is finished? The Logbook will display new entries as they occur while it as active but it cannot retrieve any historical events since 4:00 am. I can see the DB being locked and released when backup starts/finishes, but logbook/history are unable to retrieve events since it was locked. It’s as if they are still being queued after the DB is unlocked…
Do I need to re & re MariaDB???

[02:34:03] INFO: Ensuring internal database upgrades are performed
[02:34:04] INFO: Ensure databases exists
[02:34:04] INFO: Create database homeassistant
[02:34:04] INFO: Ensure users exists and are updated
[02:34:05] INFO: Update user homeassistant
[02:34:05] INFO: Init/Update rights
[02:34:06] INFO: Granting all privileges to homeassistant on homeassistant
[02:34:07] INFO: Successfully send service information to Home Assistant.
s6-rc: info: service legacy-services successfully started
[04:00:12] INFO: Lock tables using mariadb client...
[04:00:12] INFO: MariaDB tables locked.
[04:59:11] INFO: MariaDB tables unlocked.

[fuatakgun]

Recorder has an internal queue that keeps state changes and events in the memory until db is unlocked again.

Throughout 59 minutes, all these states would be accumulated and if they go above a certain threshold, recorder will give up and throw an error.

My suspicious is around long locking time, try to decrease db size, there are many posts out there on this matter.

[dbrunt (Daniel)]

Thanks but ALL states since sometime in the 4:00am-4:59am backup window are missing/queued, not just the ones during the backup period. I’ve also noticed HA grow more and more sluggish as time progresses much like VMware when a snapshop fails to be removed…
So perhaps since I am seeing the 4:28 and 4:30am events being retrieved, something is wrong with the unlocking/flushing queue to DB, likely some sort of error/overflow resulting in all events after the error being lost as well. My full backup is about 3.5GB…

[dbrunt (Daniel)]

It may be an issue with exceeding 30,000 recordings during the backup window.
I have reviewed/fine-tuned my recorder settings including adding…

recorder:
  exclude:
    event_types:
      - call_service

which may thwart the problem.
If not, next step is to call service recorder.disable pre backup and recorder.enable post backup…

[dbrunt (Daniel)]

I just found another possible reason!

auto_purge boolean (optional, default: true)
Automatically purge the database every night at 04:12 local time

I changed the scheduled time of the backup in Backups (Google Drive).
It triggered an immediate backup which is still in progress (55min) but this error has occured:

I am adding:

recorder:
  exclude:
    event_types:
      - service_removed
      - service_executed
      - platform_discovered
      - homeassistant_start
      - homeassistant_stop      
      - feedreader
      - service_registered
      - call_service
      - component_loaded
      - logbook_entry
      - system_log_event
      - automation_triggered
      - script_started
      - timer_out_of_sync

Error still occured.
Solution: Exclude MariaDB from backup…

[fuatakgun]

Excluding mariadb from backup means, you will not get any historical data, if you restore to previous day’s data. If it is really not important for you, just set recorder retention duration to couple of days.

[dbrunt (Daniel)]

I know I will lose history if I restore from a backup without the database.
I am looking at MariaDB to figure out why it’s 9.6 GB…

Progress…

…and still decreasing!

[dbrunt (Daniel)]

Mission accomplished!

After repeatedly purging entities/entity_globs, I also had to optimize tables in MariaDB via phpMyAdmin…
Before:

After optimizing states:

and then events, event_data and state_attributes:

My new recorder settings…

recorder:
  db_url: !secret mariadb_url
  db_retry_wait: 5
  purge_keep_days: 10
  include:
    domains:
      - automation
      - binary_sensor
      - climate
      - cover
      - device_tracker
      - input_boolean
      - input_datetime
      - input_number
      - input_select
      - input_text
      - light
      - lock
      - media_player
      - person
      - sensor
      - switch
  exclude:
    event_types:
      - automation_triggered
      - call_service
      - component_loaded
      - feedreader
      - homeassistant_start
      - homeassistant_stop      
      - logbook_entry
      - platform_discovered
      - script_started
      - service_executed
      - service_registered
      - service_removed
      - system_log_event
      - timer_out_of_sync
    entities:
      - sensor.insteon_groups
      - sensor.home_assistant_v2_db
      - sensor.last_active
      - sensor.last_boot # Comes from 'systemmonitor' sensor platform
      - sensor.mariadb_database_size
      - sensor.processor_use
    entity_globs:
    # Emporiavue
      - sensor.sensor.circuit_*
      - sensor.car_charger*
      - sensor.dishwasher*
      - sensor.dryer*
      - sensor.fridge*
      - sensor.furnace*
      - sensor.microwave*
      - sensor.range*
      - sensor.emporia_*
     # Others
      - sensor.*battery*
      - sensor.*node_status*
      - sensor.*voltage*
      - sensor.*power*
      - sensor.*rx*
      - sensor.*tx*
      - sensor.browser_mod*
      - sensor.clock*
      - sensor.date*
      - sensor.glances*
      - sensor.hass_agent*
      - sensor.internet*
      - sensor.load_*
      - sensor.memory_*
      - sensor.processor_*
      - sensor.sun*
      - sensor.time*
      - sensor.*uptime*
      - sensor.weather*