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.
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:
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;
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!