Reducing disk IO

I’m using a Raspberry Pi 4 with a USB3 based SSD to host this website and other services, and as a side effect the lounge looks like a disco due to all the flashing activity LEDs.

The machine should be fairly idle so I went looking for where the disk activity was coming from.

Running iotop -a and looking at the writes shows that mysql (aka MariaDB) is by far the heaviest writer.

Looking at the metrics recorded by the Prometheus mysqld_exporter:

delta(mysql_global_status_commands_total[1h])

gives:

which is dominated by inserts.

Dropping to the mysql command line and running:

SELECT table_name, table_rows from
INFORMATION_SCHEMA.TABLES ORDER BY table_rows DESC LIMIT 20;

gives:

The events and states tables are part of my HomeAssistant installation. Reading the docs shows that the recorder commits every second and changing this to 5m using:

recorder:
  db_url: mysql+pymysql://hass:....@db/hass?charset=utf8
  commit_interval: 300

fixes the problem. This reduces the write rate from 680 KiB/s to ~95 KiB/s:

and cuts the I/O seconds per second:

and makes the lounge much less blinky, yay!