Database: sqlite vs mysql

[mcwieger]

In the settings I see that sqlite is the preferred database type. However, in other applications (e.g. Digikam) I experience that a separate mysql database improves performance. Why is sqlite preferred? And if I want to migrate to test performance, I probably have to rebuild the database, right?

[bpatrik]

I use the app with SQLite just the driver I use also offers built in MySQL support, so it's realively free to support that too. But generally I optimize for SQLite.

The app ment for family usage, with small number of connection. Never tested but I expect that inprocess SQLite runs faster on a raspberry the an external dedicated process.

Yes you would need to reindex the db (and users again) if you switch DB

-- Sorry for being brief, sent from my phone.

On Mon, 19 Dec 2022, 16:30 mcwieger, @.***> wrote:

In the settings I see that sqlite is the preferred database type. However, in other applications (e.g. Digikam) I experience that a separate mysql database improves performance. Why is sqlite preferred? And if I want to migrate to test performance, I probably have to rebuild the database, right?

— Reply to this email directly, view it on GitHub https://github.com/bpatrik/pigallery2/issues/573, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABZKA5VJAZQFBLZADK7TMMDWOB5TFANCNFSM6AAAAAATDPS7KY . You are receiving this because you are subscribed to this thread.Message ID: @.***>

[rhatguy]

Remember also that using inbuilt SQLite inside the PiGallery2 docker container makes the container standalone (not dependent on any external services). There is some value in that also outside of the performance conversation.

[unlucio]

While there is undoubtedly value in been able to run the container standalone, there is also value in having a choice. Lots of connections/users is not the only thing that might drive the choice towards a DB server: SQLite is a very convenient little data store, but it doesn't play well over the network.

Consider the following possible scenario: The user has a NAS where they keep all their data and share it over the network (either nfs or samba)

The user might be simply mounting their NAS shares, where most likely their picture are as well, on their PI so all the containers data are neatly stored on the NAS as well. This makes it so that the SQLite file would find its self working over the network, and it doesn't really like to work in that scenario.

Docker Volumes can also be defined as network share mounts: https://docs.docker.com/storage/volumes/#choose-the--v-or---mount-flag so the volume passed to the container might not be fiscally on the machine where the container runs, and thus the SQLite file is on the network again.

Another likely scenario is that the user has more than 1 PI, and they might leverage and the convenience and ease of use of docker swarm and let it choose where to put the container among their PIs. Again this would put the SQLite file to be shared over the network.

IMHO a person with the sensibility to choose a private hosted solution for their pictures, over the ones provided by the tech-giants, might easily have a setup where SQLite falls short not for load but simply for convenience.