Fixing Home Assistant DataBase Problems With MariaDB Addon
In this video I replace my overloaded Home Assistant Internal Database (SQLite) with MariaDB. MariaDB is an implementation of MySQL that can handle a lot more data a lot more efficiently than SQLite.
If you are having unknown slowdowns with your Home Assistant Instance, or if you are having the display of sensors disappear, this may well be the fix for you.
A good way to tell is for you to look at your ‘home-assistant_v2. db’ file. If it is over 800mb, you are a candidate to replace the internal database with MariaDB Addon or some other external database.
If you have mariadb or an outside the standard database in use for recorder, there is a breaking change regarding the access string. ‘@’ needs to be replaced with ‘%40’.
See the recorder breaking change here:
https://www.home-assistant.io/blog/2021/07/07/release-20217/#breaking-changes
Episode: 26
Related website for this video:
https://www.whatarewefixing.today/?p=240
Video related links:
UUID Generator website to generate a password:
https://www.uuidgenerator.net/
Home Assistant Recorder Integration Page:
https://www.home-assistant.io/integrations/recorder/
If you are interested in saving/exporting the SqLite data, here’s a Tutorial on how to do it.
https://www.srm.im/2017/06/11/home-assistant-migration-from-sqlite3-to-mariadb-mysql/
MariaDB Addon Configuration: (Note, Look at the attached website for copy-able formatted text.)
databases:
– homeassistant
logins:
– username: loginHere
host: homeassistant
password: yourPasswordHere # I suggest using UUID number here for password
rights:
– username: borusa
host: homeassistant
database: homeassistant
grant: ALL PRIVILEGES ON
Home Assistant Recorder: Integration Configuration: (Note, Look at the attached website for copy-able formatted text.)
recorder:
db_url: mysql://loginHere:yourPasswordHere%40core-mariadb/homeassistant?charset=utf8
# The below is the old format that was allowed, but is now depticated. See above…
# db_url: mysql://loginHere:yourPasswordHere@core-mariadb/homeassistant?charset=utf8
purge_keep_days: 2
purge_interval: 1
# Everything is included by default.
# Ensure you are keeping only what you need to keep DB small
include: # Include everything you graph and will want to see later
domains:
– sensor
– binary_sensor
– switch
– light
exclude: # Eliminate anything that you never graph or refer back to
domains:
– automation
– updater
entities:
– sun.sun
– sensor.time
– sensor.date_time_iso # Don’t record date and time
– sensor.yr_symbol # Ignore this as well
– group.weather # Don’t save weather
Contact Links:
What are we Fixing Today Homepage / Website:
https://www.WhatAreWeFixing.Today/
Channel Link URL: (WhatAreWeFixingToday)
https://bit.ly/WhatAreWeFixingTodaysYT
What are we Fixing Today Facebook page (Sir GoodEnough):
https://bit.ly/WhatAreWeFixingTodayFB
What are we Fixing Today Twitter Account (Sir GoodEnough):
https://bit.ly/WhatAreWeFixingTodayTW
Discord Account: (Sir_Goodenough#9683)
https://discord.gg/Uhmhu3B
Please help support the channel:
Patreon: https://www.patreon.com/WhatAreWeFixingToday
Buy me Coffee: https://www.buymeacoffee.com/SirGoodenough
PayPal one-off donation link: https://www.paypal.me/SirGoodenough
Cash App $CASHTAG: https://cash.me/$SirGoodenough
Venmo cash link: https://venmo.com/SirGoodenough
If you would like to donate anything to this channel, Use this address:
C/O Sirius GoodEnough
322 Buena Vista Ave.
Department: DYT
Waukesha, Wisconsin, 53188-3602
Comments are closed.