Hi all
Sitting with a curious thing. I use to be able to run weekly backups. stored locally.
And recently… I keep on getting space warnings.
I got my Mariadb as HA db store set to keep data for 7 days, everything else gets deleted.
Backups… well I want to do a supervisor update, but have a space warning, I currently have a single backup on local disks/800MB.
somewhere the space is being chewed up, any change anyone can point me to what to go look.
Nothing…
I’m going to have to delete this local backup I have to give it space to work, to fix this… and then make a new full backup.
This is a 32GB SD crd filled.
G
executed a docker image prune -a -f
recovered 2GB…
It’s something, but still allot of space “missing”
settings/system/repair/ => systen information
This is somehow with 7 days retention/configured in configuration.yaml. Also now configured a automation… something is somehow suddenly dumping allot of records into the db.
If you want a stable system, I would shoot for 1/3 live data, 2/3 space on your drive minimum. If you have 16gb OS data, the backup tarball will also be about that size, so leave yourself enough space for the system to make that tarball.
ye… agree on the want space…
but first i need to make it… figure out where all the space is going, been running the system for 5 yrs, been stable, and suddenly the last couple of months it’s gotten space hungry.
can’t recall exactly when it happened… but the newest addition was a ecowitt weather station.
best i can think of is to tell recorder to exclude storing those values, but then i actually want those values for the last 7 days.
i need a way to figure out, what is eating up the same in that 16GB app data, whats the hungry consumer.
G
You’ll need to add an exclude: key to your recorder config and one or more of the sub-options. If the entities have a unique to Ecowitt naming pattern then the entity_globs: sub-option would be the way to go.
Another thought, the dbStats App from Jehy’s Home Assistant add-on (app) repository can be a good way to see what’s consuming your db, it’s how I nailed down multi-target mmWave sensors as my culprit. I had totally forgotten about it.
I find the easiest way to see a list of entities for an integration is using Tools > Template editor with this template (I’m assuming Ecowitt is actually named ecowitt):
That gives you a nice vertical list of entities to compare.
I don’t know what ‘tinman’ is but lounge definitely looks like an area name prepend, avoid hardcoding those in case you ever change the room it’s in and appropriately update the entitiy id’s. Try *.*_ws3800c_wifi3d1c*
running this exclude, also reduced the retention days to 3 to see if i can get a meaningful data drop on app…
if not then I dont know whats eating space in app, my thinking then it’s not the mariadb store…
G
both indexes on states and states_attributes are corrupt, this resulted in the normal purge job failing.
figured this out when trying to export the homeassistant db so as to migrate it to my SAN this has 50TB+ space… where I provisioned a new homeassistant db
the export failed, resulting in me getting into the current homeassistant db and ye…
so it seems i dont need a new home, just need to fix the current, without preferably loosing all state information.
G
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 58
Server version: 11.4.10-MariaDB Alpine Linux
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [homeassistant]> REPAIR TABLE state_attributes;
+--------------------------------+--------+----------+---------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------------------------+--------+----------+---------------------------------------------------------+
| homeassistant.state_attributes | repair | note | The storage engine for the table doesn't support repair |
+--------------------------------+--------+----------+---------------------------------------------------------+
1 row in set (0,891 sec)
MariaDB [homeassistant]>
MariaDB [homeassistant]> OPTIMIZE TABLE state_attributes;
+--------------------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------------------------+----------+----------+-------------------------------------------------------------------+
| homeassistant.state_attributes | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| homeassistant.state_attributes | optimize | error | Got error 106 "Socket is connected" from storage engine InnoDB |
| homeassistant.state_attributes | optimize | status | Operation failed |
+--------------------------------+----------+----------+-------------------------------------------------------------------+
3 rows in set, 1 warning (2 min 19,029 sec)
MariaDB [homeassistant]> ALTER TABLE state_attributes ENGINE=InnoDB;
ERROR 1030 (HY000): Got error 106 "Socket is connected" from storage engine InnoDB
MariaDB [homeassistant]> SELECT
-> table_name AS `Table`,
-> ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size (MB)`
-> FROM information_schema.TABLES
-> WHERE table_schema = 'homeassistant'
-> ORDER BY (data_length + index_length) DESC;
+-----------------------+-----------+
| Table | Size (MB) |
+-----------------------+-----------+
| states | 15263.45 |
| state_attributes | 1372.05 |
| statistics_short_term | 452.22 |
| statistics | 321.05 |
| events | 13.08 |
| event_data | 6.31 |
| statistics_runs | 2.02 |
| states_meta | 0.14 |
| statistics_meta | 0.06 |
| recorder_runs | 0.03 |
| event_types | 0.03 |
| schema_changes | 0.02 |
| migration_changes | 0.02 |
+-----------------------+-----------+
13 rows in set (0,044 sec)
MariaDB [homeassistant]> SELECT
-> FROM_UNIXTIME(MIN(last_updated_ts)) AS oldest_record,
-> FROM_UNIXTIME(MAX(last_updated_ts)) AS newest_record,
-> DATEDIFF(FROM_UNIXTIME(MAX(last_updated_ts)), FROM_UNIXTIME(MIN(last_updated_ts))) AS total_days_stored
-> FROM states;
+----------------------------+----------------------------+-------------------+
| oldest_record | newest_record | total_days_stored |
+----------------------------+----------------------------+-------------------+
| 2026-06-16 13:31:22.829454 | 2026-09-20 09:01:30.463865 | 96 |
+----------------------------+----------------------------+-------------------+
1 row in set (0,044 sec)
MariaDB [homeassistant]> SELECT COUNT(*)
-> FROM states
-> WHERE last_updated_ts < UNIX_TIMESTAMP(NOW() - INTERVAL 14 DAY);
ERROR 1034 (HY000): Index for table 'states' is corrupt; try to repair it
MariaDB [homeassistant]> DELETE FROM states
-> WHERE last_updated_ts < UNIX_TIMESTAMP(NOW() - INTERVAL 90 DAY)
-> LIMIT 50000;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_1` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))
MariaDB [homeassistant]> ALTER TABLE states ENGINE=InnoDB;
and it seems to be stuck on this ALTER for now, going to leave it… see if it is doing something, before i make it worse and control C on it…
wondering… guess the worse possible fix might be deleting and recreating the states table and loose all records… but also then dont want to touch states_attributes as it seems to be a mapping table and dont want to impact my configuration/build.
it seems all space was the states table… and it was due to the purge job failing due to corrupt index, busy seeing whats “save-able” otherwise will clean states out via a create new states table as copy structure of old… rename old to corrupt, rename new to states… / clean states_attributes, re-enable indexes…
