Running Hassio on a raspberry Pi4 with 4GB memory, which are the pros and cons using MariaDB database in place of the default one?
1 Like
Hey!
When I started using HA I ran it on a Raspberry Pi 3B+ and used the default database. It worked well enough for a while, but after I started doing more with it it seemed to bog down a bit, especially when trying to view history. I switched to MariaDB and it worked MUCH better! So much so I wish I had made the change much earlier. 
7 Likes
Ok, thanks for the suggestion…
And sometimes i think will it better to have dB on a USB Stick in the RPi.
When system crash you still have the dB and all your history…
haha
I switched to MariaDB running on Synology NAS and it is night and day difference performance wise! Well, I know NAS will be faster, even over network. Another advantage of such solution is moving DB away from SD card, which significantly reduces card wear and expands its lifespan.
Thanks, but one thing i still do not understand. Where will be the database? Always in the sd card or not?
And more, can you share the code about how to add the db to the configuration.yaml?
1 Like
If you use standard HA add-on then it will be installed on SD and will be running on the same RPi (including storage for DB itself). In this case performance difference, I guess, will be marginal. I’ve never used MariaDB on RPi, so I’m not sure if this can be configured to run on any other media directly atatched to RPi (while keeping engine on same machine).
What I was referring to, was redirection of sql service keeping HA’s recorder data to different machine running the DB server (MariaDB on NAS in my case).
That was not my experience. The performance difference was huge.
As mentione never tried to use MariaDB on the same RPi as HA, so no experience, but interesting to know! Perhaps it might mean, for people running HA as VM on decent server, that using MariaDB as standard add-on and without placing BD itself on different media might add some performance too (avoiding SD card wear is not an issue i nthis case, obviously).
Ok, can You share the code to use MariaDB?
I mean what to put in config.yaml? I see there are two options…
First, I run “HA Core” in a Python venv. I don’t use whatever hassio is called these days, add-ons, containers, VMs, etc. So I also had to install and configure MariaDB manually.
As far as what’s in configuration.yaml:
recorder:
db_url: !secret db_url
That’s it. And how is db_url defined (in secrets.yaml)? Well, that’s a secret! lol But it’s something like this:
db_url: mysql://root:password@localhost/homeassistant?charset=utf8
And FWIW, here are (the somewhat cryptic) notes I took when I originally set this all up:
https://www.home-assistant.io/components/recorder/
https://r00t4bl3.com/post/how-to-install-mysql-mariadb-server-on-raspberry-pi
https://mariadb.org/
sudo apt-get install mariadb-server
sudo apt-get install libmariadbclient-dev libssl-dev
sudo -u homeassistant -H -s
source /srv/homeassistant/bin/activate
pip3 install mysqlclient
deactivate
exit
sudo mysql_secure_installation
Enter current password for root (enter for none):
Set root password? [Y/n] Y
New password: <password>
Re-enter new password: <password>
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y
sudo mysql -u root
MariaDB [(none)]> use mysql;
MariaDB [mysql]> update user set plugin='' where User='root';
MariaDB [mysql]> flush privileges;
MariaDB [mysql]> \q
mysql -u root -p
Enter password: <password>
MariaDB [(none)]> CREATE DATABASE homeassistant;
MariaDB [(none)]> \q
sudo nano /etc/systemd/system/[email protected]
After=xxx mariadb.service
sudo systemctl daemon-reload
configuration.yaml
recorder:
db_url: !secret db_url
secrets.yaml
db_url: db_url: mysql://root:<password>@localhost/homeassistant?charset=utf8
And lastly, I moved off my RPi a while ago. I’m currently running on Linux on a slightly old laptop.
6 Likes
Thanks a lot…
1 Like
Other reason to use external DB is option to run SQL queries to the DB - by the sample - this is the way to track last status change of the device (otherwise after restart this data is lost and etc.)
I’m interested in doing this - running mariadb on my synology nas. Would you mind sharing the steps to accomplish this? Right now, I’m running the standard install on a rpi4. The db is on the sd card.
Ok so I’m an impatient person and already did it. SO Straightforward and found a perfect writeup right here:
You could not get better answer from me… this is exactly the same tutorial I used for my installation 
You save me! I really love you! =) hahaha Thanks man! Super easy super clear. Perfect!
Hi,
Here’s a revised write up after I just installed everything for the first time and the writeups steps have changed - I struggled a bit.
I really experience a speed up in performance. I have a somewhat large system on hassio.
- Stats of my system (why I thought this would help)
- Phoscon (Conbee II Stick) : 85 device, 14 entities (xiaomi, ikea, sensors, lights etc)
- Google Cast : 10 Devices, 14 entities (those are speakers & chromecast sticks)
- Ikea: 63 devices, 56 entitites (ok, this is weird, I’ll check later!)
- Tuya: 24 devices, 23 entities (lamps, switches)
- and various others, broadlink, Node-REd, spotify, Synology
- Under “States” of developer tool, the table lists 555 items.
- This is on HASSIO, RPI4
*Before anything else: Backup system
Go to Supervisor> Snaptshots > Give it a name, then full snapshot (it takes 3-4 minutes) and Download it to your computer.
1> Install the addons.
- Under “Supervisor”, then addon store
- Search for "MariaDB, then “install”
- Go back, search for phpMyAdmin, then “install”.
- Don’t click yet on anything else.
2> Configure in MariaDB
- Under “Supervisor” click on “MariaDB”
- On top, go to “Confguration”
- Here’s my config - you can change it as wished. I added “Potatoe” to all the field that are “my own choice” so you know it’s not some part of code some directory or else… I know, I’m weird.
- Yes, the password I chose is crazy long so chose yours but at least 14 characters with upper case, lowercase, numbers, weird characters. My password in this example is: Ok_I_Will_Change_This_Later_Since_It_is_1_Password
Under “Configuration”:
databases:
- Potatoe_HASSIO_MariaDB_DATABASE
logins:
- username: Potatoe_HASSIO_MariaDB_USER
password: Ok_I_Will_Change_This_Later_Since_It_is_1_Password
rights:
- username: Potatoe_HASSIO_MariaDB_USER
database: Potatoe_HASSIO_MariaDB_DATABASE
Under “Network” (replace the “disabled” grey text by)
3307
Save.
- It might prompt you to restart - do so.
- Otherwise go back to “Info” in MariaDB, then make sure it’s running (if you see “START” click it) (you know it’s running if the options in red are “STOP” and “RESTART” (don’t click those!))
3> Magic
I realized that everything got auto configured - you can view it in in PhPmyAdmin. It created the table, the user and assigned the proper rights.
You can check bu need no actions:
- Supervisor, phpMyAdmin - make sure it’s running, if not, click “Start”
- The botom right, in blue, open Web UI
- You should see on the right menu in the center, your table that you created in step 2. No need to do anything here.
4> You need to add a bit of code to configuration.yaml
We will do this the secure way so you will “hide” the sensiste info in another file.
We will use a “secret” refference called “URL_MariaDB_Thing” so it’s not exposed when the system connects. (You can create your own secret name if you want instead of URL_MariaDB_Thing)
In configuration.yaml
recorder:
purge_keep_days: 10
purge_interval: 1
db_url: !secret URL_MariaDB_Thing
In secrets.yaml (if you don’t have this, create it as a new file in the same directory as configuraiton.yaml)
You need to replace in the link below, the following parts with your own info
- Potatoe_HASSIO_MariaDB_USER with what you chose in step 2 as username
- Ok_I_Will_Change_This_Later_Since_It_is_1_Password with what you chose in step 2 as your password
- 192.168.86.249:3307 with the ip adress of your hassio system - but leave :3307 at the end
- Potatoe_HASSIO_MariaDB_DATABASE with the name of the database you chose in step 2
- There is no paragraph mark / line break in the code below. It’s all in one long line
URL_MariaDB_Thing: mysql://Potatoe_HASSIO_MariaDB_USER:Ok_I_Will_Change_This_Later_Since_It_is_1_Password@192.168.86.249:3307/Potatoe_HASSIO_MariaDB_DATABASE?charset=utf8
5> Restart.
Some notes:
- It seems that MariaDB now can create the DB out of the box in its configuration.
- Other writeup were asking to configure things in phpmyadmin which got me confused and fail.
- Also, the tcp story in the config: I found that nowhere - I was just lucky to type the port in the field and it worked.
2 Likes
… we are about 2 months later. I sustained a “bricked system” - meaning nothing would work anymore after a verison upgrade. Although this isn’t related to MariaDB, I coul never get the system to be smooth anymore. Maybe it’s the new SD card in my raspberry pi or who knows, but it would slow down (delays in turning on lights for example) until it crashed… everyday. I am not a developer so finding out what’s wrong is sometimes impossible for me.
So… I ditched MariaDB for an “in memory” virtual database.
What is that?
If I made a simple parallel… if you create a spreasheet in Excel and do stuff, calculate etc, it’s “in memory” until you save it. When you re-open it, it first loads the saved version and is now “in memory” until you re-save.
Standard Databse like MariaDB save every single time something happens. It’s like hitting the button save everytime you change or search your excel. This has a nasty effect on SD cards which are built “for the long run”, storing your 4GB cat video for example. But are prone to wear when constantly being written and changed.
In memory database will live, like it says, in the RAM of of device as long as it’s on. Drawback is you will lose everything on restart.
But what do you loose actually? The historical data. In other words, you won’t be able to see your sensor past data beyond the current startup of your device.
Want it too?
Remove (or comment out with # signs at the begining of each line) the MariaBD code from your configuration,yaml and add this:
recorder:
purge_keep_days: 2
db_url: 'sqlite:///:memory:'
That’s all there is!
I’ve experience an immediate and very impressive change in speed and reactivity.
2 Likes
Didn’t think of that, but good if you indeed don’t value history.
But if you are one who does value history, you can try to reduce the amount of entities/domains etc it records. And instead of using SD, you could go for SSD?
In my own setup, I have 2 sensors written to SQLite DB that HA uses. And a few other domains (lights, climate, alarm etc) to InfluxDB. In case I ever need to query old data, I got it available.
Got my RPi running off an SSD instead of SD. All major improvements and very stable.
Hi, I actually research that but I coudn’t find a write up at very very detailed level. I mean ssh for me is already a scary word - I was happy to plug in a usb stick or something but can’t find the step by step on hassio to achieve that.