Increased traffic between HA and MySQL, everyday high IOWait

Installation
1

I am seeing large IOWait times on my server. Checking the Proxmox screen in Grafana I can see that there is a lot of I/O Read traffic between Home Assistant and my MySQL instance.

Read (constant since around 4:12):

image
image938×228 28.6 KB

Write (peaks around every 30-40 minutes):

image
image940×227 28.4 KB

Checking open connections on MySQL server I can see that they are all from Home Assistant (192.168.28.50).

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      186/mariadbd         off (0.00/0/0)
tcp        0      0 192.168.28.57:3306      192.168.28.50:39252     ESTABLISHED 186/mariadbd         keepalive (1812.06/0/0)
tcp        0      0 192.168.28.57:3306      192.168.28.50:44940     ESTABLISHED 186/mariadbd         keepalive (1803.92/0/0)
tcp        0      0 192.168.28.57:3306      192.168.28.50:35124     ESTABLISHED 186/mariadbd         keepalive (1811.17/0/0)
tcp        0      0 192.168.28.57:3306      192.168.28.50:40922     ESTABLISHED 186/mariadbd         keepalive (4539.33/0/0)
tcp        0      0 192.168.28.57:3306      192.168.28.50:44928     ESTABLISHED 186/mariadbd         keepalive (1811.59/0/0)

It starts around 4 in morning and ends around 8:30:

image
image944×230 28.7 KB

Anyone has simillar and figured it out why this is happening?

2

Database maintenance happens at 04:12 every night.

https://www.home-assistant.io/integrations/recorder/#auto_purge

Screenshot 2024-08-30 at 15-21-48 Recorder - Home Assistant
Screenshot 2024-08-30 at 15-21-48 Recorder - Home Assistant735×117 7.06 KB

3

That is is then. But it takes over 4 hours to finish? To store values I am using InfluxDB (Grafana).

My recorder settings:

db_url: !secret db_url
purge_keep_days: 10
exclude:
  domains:
    - cover
    - media_player
    - sun
    - light
    - updater

And my InfluxDB settings:

host: 192.168.28.53
port: 38086
database: homeassistant
username: homeassistant
password: !secret influxdb_password
max_retries: 3
default_measurement: state
exclude:
  domains:
    - automation
    - binary_sensor
    - device_tracker
    - group
include:
  domains:
    - sensor
    - switch
    - cover
    - binary_sensor
    - climate

Recorder is used only for history? I have changed purge_keep_days to 3 now. Will see if it does the maintenance faster.

4

Would you advise to use SQLite as database?

5

SQLite might be slightly more optimized, but not something you would be able to see on a graph like yours.
The time used here is depending on the size of the database and your hardware on which the database engine is running

1 Like
6

I have switched to local SQLite database. I can see increase of VM write since I did that, but it is better than too much network traffic.