Edit: Currently the best way to implement these changes is to follow this post: Improve Your Home Assistant Performance with MariaDB Tuning - #21 by jmrplens
Turbocharge Your Home Assistant with MariaDB Tuning
Over the past few months, I noticed that my Home Assistant instance was becoming slower over time. A few hours to days after restarting Home Assistant, I’d see delays in response times, especially when switching lights. I decided to investigate further and began monitoring the ping response times on my Shelly devices (flashed with ESPHome). To my surprise, these devices became increasingly unreliable over time, even though CPU usage and power draw on my host device remained stable, as you can see in the screenshot below:
Upgrading my RAM from 8GB to 32GB to reduce swap usage didn’t help, so I dove deeper and eventually found the culprit: MariaDB settings.
If you’re using the MariaDB add-on for your recorder, maybe you’ve encountered similar frustrations. In this guide, I’ll explain how to tune MariaDB to make the most of your hardware—and why it’s not as straightforward as you’d think.
A Word of Warning
I’m not a database expert—everything I’ve learned has been through trial and error with Home Assistant. Before you start, back up your database! While these changes shouldn’t corrupt your data, I’d rather be safe than sorry.
This guide assumes you’re using the MariaDB add-on and have at least an entry-level understanding of databases.
I also learned a lot from some topics on the forums/github, which I want to give a shout-out to:
- How can I tweak HASS.io MariaDB addon settings?
- Addon mariadb: expose more mariadb config parameters in addon config
- Please expose more of the my.cnf options in the GUI
- Add config parameters from mariadb-server.cnf to Addon config in HA · Issue #602 · MariaDB/mariadb-docker · GitHub
Default MariaDB Settings: Tuned for Low-End Devices
The default MariaDB settings are designed for devices like the Raspberry Pi. Here’s an excerpt from the default mariadb-server.cnf:
port=3306
log_error=mariadb.err
# Persistent storage location
datadir=/data/databases
# Use a proper collation set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
# Do not resolve DNS names
skip-name-resolve
# Tune for low-end devices (Like a Raspberry Pi)
key_buffer_size = 16M
max_connections = 64
myisam_recover_options = FORCE
myisam_sort_buffer_size = 8M
net_buffer_length = 16K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
sort_buffer_size = 512K
join_buffer_size = 128K
table_open_cache = 64
thread_cache_size = 8
thread_stack = 192K
tmp_table_size = 16M
# Disable query cache
query_cache_limit = 1M
query_cache_size = 0M
query_cache_type = 0
# InnoDB Tweaks
innodb_buffer_pool_size = 128M
innodb_log_buffer_size = 8M
innodb_log_file_size = 48M
max_binlog_size = 96M
These settings are not ideal for a more powerful setup, like my Intel NUC8 with an i5 CPU and 32GB of RAM, which can easily handle larger databases in memory.
Why Tuning is Tricky
While you can modify mariadb-server.cnf on disk, the MariaDB add-on reverts to its default settings on restart. Here’s why:
-
Supervisor-Controlled Add-on:
- The MariaDB add-on is managed by Home Assistant Supervisor. It’s built from a preconfigured Docker image, and changes to configuration files inside the container don’t persist across restarts.
-
No Rebuild Option:
- The
ha addons rebuildcommand doesn’t work for image-based add-ons like MariaDB.
- The
-
Temporary Changes:
- You can manually apply changes at runtime, but they will be lost on restart unless you use specific techniques (e.g., SQL commands).
How to Tune MariaDB Settings
Since persistent changes via mariadb-server.cnf aren’t feasible, we’ll focus on runtime changes using SQL commands. These changes are immediate but likely will need to be reapplied after each restart.
edit: I found that after a Home Assistant update, changes set through the phpMyAdmin variable editor do persist.
edit2: Changes to the MariaDB container only persist as long as the container itself is not restarted. So a Home Assistant restart will not remove the changes, but a complete host restart will. I recommend to use the mysql command custom integration suggested by @jmrplens to re-apply these changes on reboot.
Step 1: Accessing MariaDB
- Install and configure phpMyAdmin (available via the community add-ons store).
- Open the phpMyAdmin web interface.
- Log in with the credentials you set for the MariaDB add-on.
Alternatively, connect via a MySQL client if you prefer the command line.
Step 2: Modify Runtime Variables
Use the following SQL commands to apply optimized settings:
Alternatively, you can go to the variables tab in the phpMyAdmin home page and edit these variables manually.
-- Set InnoDB Buffer Pool Size to 12 GB (12884901888 bytes)
SET GLOBAL innodb_buffer_pool_size = 12 * 1024 * 1024 * 1024;
-- Set InnoDB Log Buffer Size to 8 MB (8388608 bytes)
SET GLOBAL innodb_log_buffer_size = 8 * 1024 * 1024;
-- Set Max Connections to 128
SET GLOBAL max_connections = 128;
-- Set Thread Cache Size to 32
SET GLOBAL thread_cache_size = 32;
-- Set Temporary Table Size to 256 MB (268435456 bytes)
SET GLOBAL tmp_table_size = 256 * 1024 * 1024;
-- Set Query Cache Size to 64 MB (67108864 bytes)
SET GLOBAL query_cache_size = 64 * 1024 * 1024;
-- Enable Query Cache
SET GLOBAL query_cache_type = 1;
Step 3: Verify Changes
To confirm your changes, run:
SHOW VARIABLES WHERE Variable_name IN (
'innodb_buffer_pool_size',
'innodb_log_buffer_size',
'max_connections',
'thread_cache_size',
'tmp_table_size',
'query_cache_size',
'query_cache_type'
);
The Results
After applying these changes, I observed:
- Improved Performance:
- Dashboards with multiple ApexCharts cards became significantly faster.
- Snappy Light Responses:
- Light switching now happens almost instantly.
- Increased Memory Usage:
- Host memory usage increased steadily, indicating that MariaDB was caching more data in memory.
Here’s a screenshot of my memory usage.
Final Thoughts
I hope this guide helps you optimize your Home Assistant setup. For those using the MariaDB add-on, tuning these settings can make a world of difference.
Suggestions for the Developers
It would be great if the MariaDB add-on provided predefined configuration profiles (e.g., Raspberry Pi, NUC, server) or exposed more settings in the UI for advanced users.
If you found this guide helpful or have suggestions to improve it, especially with advice on which additional settings to change or better suggestions of values, feel free to comment below!
