MariaDB yes or not?

Configuration
11

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
12

Thanks a lot…

1 Like
13

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.)

14

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:

15

You could not get better answer from me… this is exactly the same tutorial I used for my installation :smiley: :+1:

16

You save me! I really love you! =) hahaha Thanks man! Super easy super clear. Perfect!

18

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
19

… 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
20

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.

21

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.

22

I followed your guide yesterday, it was easy, and the improvement have been quite outstanding. @lkeays, sad to hear about your troubles.

All this is actually pretty strange, and I am going to do a separate posting asking more broadly.

My setup is pretty much like lkeays in size and complexity. The event flow is quite intense often with many messages per second. This is really no problem for Home Assistant, and my RPi 4 usually had CPU around 10%. However, with the default recorder (MySQL I believe), the system was in general sluggish, often freezing for a few seconds, both in the admin screens and over Samba.
Changing to MariaDB has the CPU also around 10% (perhaps a nudge higher), but the sluggishness is gone. How can that be? I am going to speculate and ask about that in another posting.

History graphs are major thing in my tablet gui, so a history db is not for me.

23

Hello,

I would like to contact you with a few of my questions about MariaDB.
I’m still a beginner in HA, so I apologize if these are stupid questions.
My HA is running on RPi 4B.
It bothered me that the history of stored data is basically very short. Especially the history of temperature sensors.
So I installed MariaDB and everything seems to be running fine.
But nowhere did I find the settings for what data I want to store and for how long.
So MariaDB basically stores everything and infinitely far into history?
Is it possible to somehow set that I want to store the temperature history indefinitely, for example, but for example the history of switching lights only 24 hours?
Thanks

24

I use MariaDB with 2 days retention. And InfluxDB to store everything from the past.
Works great!

25

How

Can you share

2 Likes
26

That sounds great. I want that too, I guess.

But I can imagine that two database-engines running at the same time takes some performance and space. Isn’t that a problem?

Secondly: Did you install both databases on your HA via Supervisor -> Add-on-Store? And how do you setup that distinction between ‘available’ (MariaDB) data and ‘archive’ (InfluxDB) data?

Lookin’ forward to see some clarification on this.

27

Installed InfluxDB and MariaDB in Add-on inside HA
The path, username, password are in secret file

MariaDB Purge keep is 3 days, it will auto clean
To prevent lot of write actions I use commit_interval to 60 seconds. Every 60 seconds its write everything into MariaDB

Beside that it will also put everything in InfluxDB. Reason you can’t use Influx itself, history part in HA only work with MariaDB (What I know)

###################################################
# INFLUXDB
# https://www.home-assistant.io/integrations/influxdb/
###################################################
---
username: !secret influxdb_user
password: !secret influxdb_password
database: !secret influxdb_database
max_retries: 5
default_measurement: state

include:
  domains:
    - switch
    - light
    - sensor
    - binary_sensor
    - device_tracker

exclude:
  entities:
    - sensor.icloud3_event_log

###########################################################################################
# RECORDER
# https://www.home-assistant.io/integrations/recorder/
###########################################################################################
---
db_url: !secret mariadb
purge_keep_days: 3
auto_purge: true
commit_interval: 60
include:
  domains:
    - sensor
    - binary_sensor
    - switch
    - automation
    - light
    - media_player
    - updater
2 Likes
28
29

Thanks for the link. I studied it.
So I understand correctly that in the basic setup, Recorder stores everything in the database with an unchanged history?

30

I use Alexa TTS and after upgrading to Maria DB on my Intel NUC everything ran much quicker, however the Alexa TTS was completely messed up, re-authentication was required every few hours. I returned to the previous snapshot and all was ok again.

31

Alexa TTS is 100% separate from Maria DB. They are not related in the least bit. What else did you change?