My BLE tracking project (“Ziggy”) has been running faithfully for months, logging Bluetooth Low Energy traffic around the house. It started small, using a simple SQLite file (ziggy_data.db) for storage. It was the perfect “Keep It Simple” solution.
But “simple” has limits.
This week, I hit that limit. The database had grown to 7.8 million rows. Grafana dashboards were lagging, querying the history took several seconds, and I was starting to worry about file corruption with multiple scripts trying to write to the database simultaneously.
It was time to graduate from a flat file to a dedicated SQL Server.
The Architecture Change
I decided to move to a centralized architecture. Instead of the data living inside a file in the application folder, it now lives in a Dockerized MariaDB container.
- Old Way: App → Local File (
.db) - New Way: App → LAN Network (
10.0.1.2) → MariaDB Container → SSD Storage
By exposing the database on the LAN IP, I’ve future-proofed the setup. Any new Raspberry Pi I add to the cluster can log data to this central core without complex Docker networking or file shares.
The Migration (The Scary Part)
Moving nearly 8 million records on a Raspberry Pi sounds like a recipe for a system crash. I wrote a Python script using batch processing (chunking data 25,000 rows at a time) to keep RAM usage low.
The performance was startling. The Pi + SSD combination managed to write 44,000 rows per second. The entire migration of months of history took less than 3 minutes.
The Result: “Jesus Buggery” Fast
The difference in Grafana is night and day.
- Before: Calculating traffic density over 30 days involved churning through a massive text file.
- After: MariaDB uses proper indexing. Queries that used to hang now return in milliseconds.
We are now running a proper tiered architecture: Python for logic, MariaDB for storage, and Grafana for visualization. The homelab just got a major upgrade.
