search

home-assistant / addons

:heavy_plus_sign: Docker add-ons for Home Assistant
https://home-assistant.io/hassio/
Apache License 2.0
1.57k stars 1.52k forks source link

MariaDB disk usage won’t decrease despite shrinking database size? #3504

Closed MikOsle closed 8 months ago

MikOsle commented 8 months ago

Describe the issue you are experiencing

Hi,

I had this thing happen where I accidentally flooded my MariaDB, and it blew up the disk storage usage. It is over 10 days ago and the database has been purged reducing its size from about 50GB to 5GB, However, the storage usage hasn’t really decreased at all.

Now, I’m no Linux or database whiz, but I did some poking around and found that the event_data.ibd is still 50Gb, even though a ROUND(SUM(data_length+index_length)/(102410241024), 3) query tells me the database is only 5Gb.

Any ideas on why this .ibd file is still big and how I can shrink it down?

d90157f7042300bbc9aa0b863a36e086ae679f65

What type of installation are you running?

Home Assistant OS

Which operating system are you running on?

Home Assistant Operating System

Which add-on are you reporting an issue with?

MariaDB

What is the version of the add-on?

2.6.1

Steps to reproduce the issue

Constantly an issue

System Health information

System Information

version core-2024.3.0
installation_type Home Assistant OS
dev false
hassio true
docker true
user root
virtualenv false
python_version 3.12.2
os_name Linux
os_version 6.6.16-haos
arch x86_64
timezone Europe/Copenhagen
config_dir /config
Home Assistant Community Store GitHub API | ok -- | -- GitHub Content | ok GitHub Web | ok GitHub API Calls Remaining | 4959 Installed Version | 1.34.0 Stage | running Available Repositories | 1408 Downloaded Repositories | 50
Home Assistant Cloud logged_in | false -- | -- can_reach_cert_server | ok can_reach_cloud_auth | ok can_reach_cloud | ok
Home Assistant Supervisor host_os | Home Assistant OS 12.0 -- | -- update_channel | stable supervisor_version | supervisor-2024.02.1 agent_version | 1.6.0 docker_version | 24.0.7 disk_total | 116.7 GB disk_used | 83.3 GB healthy | true supported | true board | generic-x86-64 supervisor_api | ok version_api | ok installed_addons | File editor (5.8.0), Advanced SSH & Web Terminal (17.2.0), Samba share (12.3.0), Grafana (9.1.3), InfluxDB (5.0.0), NGINX Home Assistant SSL proxy (3.8.0), Duck DNS (1.15.0), MariaDB (2.6.1), Z-Wave JS (0.4.5), eWeLink Smart Home (1.4.3), phpMyAdmin (0.9.1), Airthings (1.2.0), ESPHome (2024.2.2), Piper (1.5.0), Zigbee2MQTT Edge (edge), Mosquitto broker (6.4.0), Glances (0.21.0), Home Assistant Google Drive Backup (0.112.1), Firefox (1.1.0), MQTT Explorer (browser-1.0.3), motionEye (0.20.0), Tailscale (0.16.0), Cloudflared (5.1.5), go2rtc (1.8.5), Anylist (1.7), VLC (0.3.0), Studio Code Server (5.15.0)
Dashboards dashboards | 6 -- | -- resources | 29 views | 30 mode | storage
Recorder oldest_recorder_run | February 28, 2024 at 05:44 -- | -- current_recorder_run | March 8, 2024 at 12:05 estimated_db_size | 1896.31 MiB database_engine | mysql database_version | 10.6.12
Sonoff version | 3.6.0 (8dd8af9) -- | -- cloud_online | 0 / 3 local_online | 3 / 3
Spotify api_endpoint_reachable | ok -- | --

Anything in the Supervisor logs that might be useful for us?

No response

Anything in the add-on logs that might be useful for us?

No response

Additional information

No response

MikOsle commented 8 months ago

For what ever reason, after updating to 2024.3.0 the event_data.ibd shrank.

image image