MariaDB yes or not?

Configuration
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?

32

Nothing, which is why I am surprised as you are. Iā€™ll try again.

33

FWIW: the current (default, recommended?) db recorder ends with charset=utf8mb4 instead of charset=utf8.

1 Like
34

I just realize tonight that the new config ends by mb4

Dont know what is changing?

I have to remove MariaDB Add-On after some months and reinstall again to make a new fresh database. Before my RPi4 CPU is between 20-40% use and after fresh DB between 5-15%ā€¦:slight_smile:

i will give a feedback if the mb4 change something for me :wink:

35

So did it make any difference? :wink:

36

Hello @Bepp

i dont see any change, i have the same problem since 6 month ago : maria dB use 10 to 25% of my RPI4 8Go CPU,

when i delete the add-on, restart core, it recreate a new database, and then Maria dB use 5% of CPU,

but when i restart or make an update of Core, Supervisor or an Add-on, CPU usage for Maria dB comes back to 10 to 25% againā€¦

I m really bored about that, and when i encode vidĆ©o with FFmpeg, my CPU goes up to 75%, sometimes 100%ā€¦so i have ordered a NUC8 I5 @3.6Ghz with 16Go Ram and i think it will be solved one time for allā€¦lol

I have about 95 wifi esp8266, esp32, rpi0w, rpi3b+ and rpi4 connect to HA and 25 zigbee sensors.

When Maria Db is fresh, my CPU idle is about 5-10%, what is nice, but cant encode video records of my cams in H264 (only 2 or 3 simultaneous instance), RPI4 is not enough powerfull from now, because i have too much stuffā€¦

37

Did you meanwhile figure out what is/was the problem with the CPU consumption of your MariaDB addon?

Do others see this as well? That would be the opposite of all the ā€žgreat performance improvement!ā€œ statements - or at least be a big trade-off (more performance by using more resources).

38

I have switched from the built in recorder to MariaDB last year. On my x64 Intel Celeron with 4GB RAM I actually do not see difference.

39

How to backup Mariadb database when have gone through the addon with phpadmin ?
I canā€™t find the database file anywhere.

40

Backup = Export from phpMyAdmin GUI.
Export ā†’ Quick ā†’ Format: SQL ā†’ Go

1 Like