Thanks to the porting efforts of huo-ju (photoprism-freebsd-port) and psa (libtensorflow1-freebsd-port), it is quite easy to get PhotoPrism up and running on a FreeBSD system or jail.

The port uses SQLite 3 for the DB implementation by default, and while that runs fine for a few thousand pictures, things start slowing down a bit when reaching the tens of thousands.

This post will help you to migrate your PhotoPrism FreeBSD installation from SQLite to MariaDB.

We’ll assume you already have PhotoPrism running. If you don’t, just follow the port’s README file or the post-installation message. It’s that simple.

But why?

I have a library of ~25K pictures and videos. And while searching, browsing, etc, runs snappily on my instance, operations like adding a dozen pictures to an album or archiving them takes sometime between 5s to 10s to finish! During that time I can clearly see one CPU thread pegged at 100% usage.

While that’s bearable for now, specially since the UI doesn’t completely freeze, I’m afraid things could slow down to a crawl if we keep adding pictures to the system for the years to come, which is my intended use case.

So I started looking up on optimizing the DB, which I believed was the culprit, and found an official migration guide: https://docs.photoprism.app/getting-started/advanced/migrations/sqlite-to-mariadb/.

I used the guide as a reference and managed to migrate the DB. The operations that took 5-10s now completed almost instantly!

The official guide is Docker specific and I think people could benefit of a step-by-step tutorial on how to achieve the same results on FreeBSD.

Let’s do this

First, let’s install and harden MariaDB. I installed it on the same jail I use for PhotoPrism to avoid having it available on my LAN. And I recommend setting a password for the root user on MariaDB during the last command, the rest of this tutorial assumes you did that:

service photoprism stop

pkg install mariadb10110-server
service mysql-server enable
service mysql-server start
mysql_secure_installation

Next, let’s migrate the SQLite database to our newly installed MariaDB:

pkg install python3 py39-pip py39-sqlite3 
pip install sqlite3-to-mysql
sqlite3mysql -f /var/db/photoprism/storage/index.db -d photoprism -u root -p

Type in your MariaDB root password and wait for the process to complete!

Next, let’s configure the MariaDB user for PhotoPrism to use:

mysql -u root -p

Type your MariaDB root password once again and let’s do some SQL to create PhotoPrism’s user and give it the needed permissions:

SHOW DATABASES;
CREATE USER 'photoprism'@localhost IDENTIFIED BY 'p@$$w0rd';
SELECT User FROM mysql.user;
GRANT ALL PRIVILEGES ON photoprism.* TO 'photoprism'@localhost;
SHOW GRANTS FOR 'photoprism'@localhost;
FLUSH PRIVILEGES;

Create a defaults.yml configuration file for Photoprism (mine lives in /etc/photoprism/defaults.yml, as it is the default path and will be automatically used if you just run /usr/local/bin/photoprism), containing:

DatabaseDriver: "mysql"
DatabaseServer: "localhost:3306"
DatabasePassword: "PHOTOPRISM_MARIADB_PASSWORD"

For more configuration options, check the documentation:

Edit /etc/rc.conf to tell the port to use a configuration file:

sysrc photoprism_defaultsyaml="/path/to/defaults.yml"

And now restart PhotoPrism:

service photoprism start

Go check if everything is fine with your instance. Once you’re convinced it all works and MariaDB is being used, cleanup:

pip uninstall sqlite3-to-mysql
pkg remove python3 py39-pip py39-sqlite3
pkg autoremove
# Maybe move it somewhere else first, restart photoprism and
# double-triple check all is fine before deleting
rm /var/db/photoprism/storage/index.db

Profit

That’s all! I hope this tutorial can help more people to migrate and have a better performant PhotoPrism installation on FreeBSD!