I used this method, and it worked great! I only migrated the tabled for the (long term) statistics:
- statistics
- statistics_meta
- statistics_runs
- statistics_short_term
Therefor I only needed to execute steps 1-7 & 11-17
Great work, thanks!
Have you noticed any performance chances since migrating back to sqllite?
Having migrated back recently, average processor use appears to have increased by about 5 percent with more spikes (I am recording two months worth of data, not just long term statistics), but the logbook view seems snappier.
2 Likes
I’ll need to get back on this when the states and events of the 10 days are recorded. It is just 2 days ago now so not much recorded yet
1 Like
I made a change to my energy monitoring last week which resulted in losing the historical data. I didn’t realise until several days had passed.
I took it as an oppurtunity to do a cold migration back to SQL lite (disabling the MariaDB add on, updating configuration.yaml to remove the integration in the recorder: and restarting.
After a restart the sqlite db was recreated and everything carried on as normal.
The performance improvements are noticeable. Every view on the front end loads instantly; navigation is so much more snappy now.
I still have a very small DB and obviously as the days go on it’ll grow, but I’m happy with the switch so far.
There is also a command line tool to export a MariaDB/MySQL database to a new SQLite3 database.
I’ve successfully used it today to migrate back to SQLite3, all data were migrated so short and long term statistics.
I use Home Assistant Core installation.
Steps should be as follow for Home Assistant OS:
- 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
- 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_urlin your YAML configuration - Restart Home Assistant core.
Really check what I wrote before executing I.E. for path as I had not the opportunity to test with HA OS but main ideas are here.
All tables are migrated using this command so if you use the same database for Home Assistant statistics and something else, these other data will be migrated.
See the tool documentation to see how to make a more precise migration if needed.
14 Likes
All data will be migrated or only the long term statistics?
All data will be migrated.
I’ve just edited my previous post to precise all data will be migrated.
1 Like
This worked great for me on Home Assistant OS (on a home assistant blue). I ran it from my ubuntu laptop, and it took just a few minutes to convert a multi-gigabyte MariaDB database.
For ease of cleanup, I installed it in a python virtual environment:
python3 -m venv venv
source venv/bin/activate
pip install mysql-to-sqlite3
I could then run it from my Ubuntu laptop against my HA host as follows (after enabling port 3306 in the MariaDB add on settings as shown in the screenshot by @AleXSR700 above):
mysql2sqlite -f home-assistant_v2.db -h <home assistant host> -P 3306 -d homeassistant -u homeassistant -p -V --use-buffered-cursors
It took 7 minutes to create a 3GB database in home-assistant_v2.db, which I then copied into the /config directory over SMB. I then commented out the db_url line that specified the MariaDB details in the recorder section of my configuration.yaml. I restarted Home Assistant and it picked up the new Sqlite3 database, with all historical data intact.
Thanks for the great info!
8 Likes
just curious, what speed, after the migration, are HA running faster now ?
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