I can’t tell I’m afraid. Things were already fast with no noticeable delays (apart from very slow dB migrations on mariadb during certain monthly upgrades).
1 Like
I haven’t noticed any performance changes since migrating back from MariaDB to SQlite. I also kept an eye out on the memory/cpu usage in proxmox, but no significant changes there.
1 Like
I don’t think it makes a differenc. Have not noticed anything on my RPi4. So if there is a measureable one, then it is not noticeable real-life.
But it is one less addon that needs supporting.
And for some reason, the SQLite version is almost 1 GB smaller than the MariaDB database was.
Are you comparing mariadb vs sqllite both running on devices; or a remote mariadb ?
Both running inside the docker image on the RPi4
I was able to do this:
Using Terminal & SSH addon, connect and install Python, Pip and MariaDB-to-SQLite3
apk add python3
apk add py3-pip
pip install mysql-to-sqlite3
But, I don’t know what to do next:
Stop Home Assistant
Migrate the database:
mysql2sqlite -f /config/home-assistant_v2.db -d <database_name> -u <mariadb_user> -p -V --use-buffered-cursors
Replace <database_name> and <mariadb_user> by the database name and user used by Home Assistant
Comment the db_url in your YAML configuration
Restart Home Assistant core.
How do I “Stop Home Assistant”?
From the “Terminal & SSH” add-on, I can execute:
[core-ssh ~]$ whoami
root
[core-ssh ~]$ ha core stop
But then the whole Home Assistant would stop, including my session.
Therefore, I don’t know where to run the “mysql2sqlite” command.
Please if anyone can write down more detailed instructions.
Home Assistant is installed as Proxmox virtual machine.
Thank you.
Use putty or similar to ssh to the HA (Port 222). The session will not end then.
ssh to HA port 222, connection refused.
Also tried 2222, 22222.
I checked the add-on, changed it to port 22, but still connection refused (add-on is started).
Did you use the right username and password? Did you define them?
P.S.: I am using “Advanced SSH & Web Terminal” addon.
I found that my SSH add-on was different. So, I uninstalled it and installed this:
Same as yours @AleXSR700
Then I watched this video:
Enable SSH In Home Assistant - TUTORIAL 2022 - YouTube
Then I ssh from my Windows PC.
I had to again install python3, py3-pip, and mysql-to-sqlite3.
And the rest worked nicely!
I haven’t uninstalled MariaDB though.
1 Like
What was wrong on the first run, where you har to restart, i’m also failing with the connection the the DB.
I forgot to enable the MariaDB port 3306 to connect, you need to enable that on the MariaDB add-on.
Check this:
thanks 
that fixed it
Please let us know if you see any improvements and if anything was changed or lost in the migration. I haven’t done this yet, but would like to get on to the preferred db path.
No, I don’t see any kind of improvement in terms of speed or performance. This is because my database is still small (about 200MB) and my HA running on Proxmox VM using Intel n5105 mini-PC, which is more than enough.
I want to migrate back to SQLite3 because I believe “the less the better”. If I can cut down or reduce any kind of (unnecessary) component, I will do it. Especially after reading all the horror stories during HA updates.
I started HA just a few months ago, and I went straight to use MariaDB after reading and watching lots of videos telling that “You better use MariaDB for performance reason because SQLite3 is bad and slow”. But now, after the HA developers announced that they made big improvements on SQLite3, which were confirmed by many people, I wanted to move back as well.
About the data after the migration, nothing changed. All data are intact! Short term and long term statistics, whatever, they are not affected. As of now, HA is running the same as before
Hope this helps.
1 Like
So, considering that I only wanted to preserve my long term statistics for keeping the energy dashboard coherent, and that this energy dashboard is so buggy and messes the data so much whenever a change is made (such as adding a new monitored device where it will insert insanely high negative values): those stats were of poor use to start.
So all I did was comment the mariadb recorder configuration in configuration.yaml and restarted. It re-created the sqlite db and I reconfigured everything manually.
It’s been 3 days now, and all I can say is that HA with sqlite running locally is much slower than when using a remotely hosted mariaDB on a powerful linux box. When I first load the history graph, it takes several seconds for the graph to show ; until then it shows “Loading state history”.
When using the remotely hosted mariadb it’s almost always instantaneous.
only thing i had to do, to add the 3306 port online inside the MariaDB addon.
i went from 1.7gig db to 1.39gb db with now MySQL.
thanks for your post.
1 Like
this looks very simple indeed, but as always the devil is in the details…
is there no need for the db password? (you only mention <database_name> and <mariadb_user> )
my case if my db_url is
mysql://mariusthvdb:greatpassword@core-mariadb/homeassistant?charset=utf8mb4
what would that command_line become:
mysql2sqlite -f /config/home-assistant_v2.db -d homeassistant -u mariusthvdb -p -V --use-buffered-cursors
?
or, should I also replace the password greatpassword in that line (after the -p ?)
just to be sure: we can still restore the MariaDB when backed up regularly, or do we need special precausions for that?
The tool will prompt you for the password.
I am not sure to understand what you mean.
After the migration the MariaDB database won’t be used anymore so even if you back it up it will be outdated.
If you want to use MariaDB again just uncomment the MariaDB URL.
If you want to have updated data you can use SQLite3 to MySQL before starting HA again. I have not tested.
thanks! that’s what I needed to know.
will go ahead and try 
that is, if I understand this better:
the place to install the converter, when using HAOS, couldnt that be inside the docker? so.
ssh [email protected] -p 22222
docker exec -it homeassistant /bin/bash
this is my regular routine to run the py-spy’s or other commands that require shell access to the HA container (not the OS).
it would have the advantage the install is temporary, and gets cleaned up at next container update)
might I suggest you write up your post as a community guide? it seems it would benefit the whole community to have this as a top level post!