SQLite: Add busy timeout preset to reduce locking errors when indexing

[lastzero]

Based on the advice in #392 (comment) by @lapo-luchini, we will add ?_busy_timeout=5000 to the default SQLite DSN.

Is there anything else that might be helpful? At https://pkg.go.dev/github.com/mattn/go-sqlite3#section-readme you will find an overview of the parameters.

I've also seen advice to set sqlite_unlock_notify at compile time, but I haven't figured out what it really does, whether we should use it or if it's a Gitea-specific build tag that otherwise has no effect?

We just got back from vacation, so I'm pretty busy and don't have much time to read through the documentation. Help is much appreciated! 👍

[jan666]

My database is almost always locked so I'd like to test it. Is there an easy way to add this to the DSN?

[lapo-luchini]

Is there anything else that might be helpful?

Mhh, I haven't tested it on my installation yet, but this should be a nice scenario for WAL usage, I quote from official documentation:

There are advantages and disadvantages to using WAL instead of a rollback journal. Advantages include:

1. WAL is significantly faster in most scenarios.
2. WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.
3. Disk I/O operations tends to be more sequential using WAL.
4. WAL uses many fewer fsync() operations and is thus less vulnerable to problems on systems where the fsync() system call is broken.

But there are also disadvantages:
…

6. WAL might be very slightly slower (perhaps 1% or 2% slower) than the traditional rollback-journal approach in applications that do mostly reads and seldom write.
7. There is an additional quasi-persistent "-wal" file and "-shm" shared memory file associated with each database, which can make SQLite less appealing for use as an application file-format.
8. There is the extra operation of checkpointing which, though automatic by default, is still something that application developers need to be mindful of.

[lapo-luchini]

My database is almost always locked so I'd like to test it. Is there an easy way to add this to the DSN?

I'm on FreeBSD so YMMV but I'm using this in my /etc/rc.conf:

photoprism_enable="YES"
photoprism_assetspath="/var/db/photoprism/assets"
photoprism_storagepath="/var/db/photoprism/storage"
photoprism_args="--site-url=https://(DOMAIN)/ --dsn=/var/db/photoprism/storage/index.db?_busy_timeout=5000"

This leads to a process running like this:

/usr/local/bin/photoprism
    --defaults-yaml=
    --assets-path=/var/db/photoprism/assets
    --storage-path=/var/db/photoprism/storage
    --originals-path=/var/db/photoprism/storage/originals
    --import-path=/var/db/photoprism/storage/import
    --site-url=https://(DOMAIN)/
    --dsn=/var/db/photoprism/storage/index.db?_busy_timeout=5000 start

[lapo-luchini]

I'm currently trying ?_timeout=5000&_journal=WAL and works fine.

Can't say I notice much difference with only using ?_timeout=5000 but it's definitely doing as instructed (index.db-shm and index.db-wal files were created) so I guess it's worth keeping the option given the above pro/cons.

[jan666]

I added it yesterday. Was great for the first hours, but now it's back:

$ grep -i lock /var/log/messages
Oct 17 13:30:27 photoprism photoprism[2625]: time="2022-10-17T13:30:27+02:00" level=error msg="faces: database is locked (update covers)"
Oct 17 13:31:34 photoprism photoprism[2625]: time="2022-10-17T13:31:34+02:00" level=error msg="faces: database is locked (update marker)"
Oct 17 13:41:07 photoprism photoprism[2625]: time="2022-10-17T13:41:07+02:00" level=warning msg="faces: database is locked while updating marker mrje2881dycac6pf match timestamp"
Oct 17 13:41:12 photoprism photoprism[2625]: time="2022-10-17T13:41:12+02:00" level=warning msg="faces: database is locked while updating marker mrje2891127cu2px match timestamp"
Oct 17 13:41:17 photoprism photoprism[2625]: time="2022-10-17T13:41:17+02:00" level=warning msg="faces: database is locked while updating marker mrje28glv432izri match timestamp"
Oct 17 13:41:22 photoprism photoprism[2625]: time="2022-10-17T13:41:22+02:00" level=warning msg="faces: database is locked while updating marker mrje2ax2n10qncfy match timestamp"
Oct 17 13:41:28 photoprism photoprism[2625]: time="2022-10-17T13:41:28+02:00" level=warning msg="faces: database is locked while updating marker mrje2b72c0sj0zpg match timestamp"
Oct 17 13:41:33 photoprism photoprism[2625]: time="2022-10-17T13:41:33+02:00" level=warning msg="faces: database is locked while updating marker mrje2bf273gg3cbk match timestamp"
Oct 17 13:41:38 photoprism photoprism[2625]: time="2022-10-17T13:41:38+02:00" level=warning msg="faces: database is locked while updating marker mrje2bg2gaxdhfc4 match timestamp"
Oct 17 13:41:43 photoprism photoprism[2625]: time="2022-10-17T13:41:43+02:00" level=warning msg="faces: database is locked while updating marker mrje2bo5uotthm47 match timestamp"
Oct 17 13:41:48 photoprism photoprism[2625]: time="2022-10-17T13:41:48+02:00" level=warning msg="faces: database is locked while updating marker mrjckkf1go9np917 match timestamp"
Oct 17 13:41:53 photoprism photoprism[2625]: time="2022-10-17T13:41:53+02:00" level=warning msg="faces: database is locked (clear marker face)"
Oct 17 13:41:58 photoprism photoprism[2625]: time="2022-10-17T13:41:58+02:00" level=warning msg="faces: database is locked (clear marker face)"
Oct 17 13:42:04 photoprism photoprism[2625]: time="2022-10-17T13:42:04+02:00" level=warning msg="faces: database is locked while updating marker mrje2c5f6uhj07st match timestamp"
Oct 17 13:42:09 photoprism photoprism[2625]: time="2022-10-17T13:42:09+02:00" level=warning msg="faces: database is locked (clear marker face)"
Oct 17 13:42:14 photoprism photoprism[2625]: time="2022-10-17T13:42:14+02:00" level=warning msg="faces: database is locked (clear marker face)"
Oct 17 13:42:19 photoprism photoprism[2625]: time="2022-10-17T13:42:19+02:00" level=warning msg="faces: database is locked (clear marker face)"
Oct 17 13:42:24 photoprism photoprism[2625]: time="2022-10-17T13:42:24+02:00" level=warning msg="faces: database is locked (clear marker face)"
Oct 17 13:42:29 photoprism photoprism[2625]: time="2022-10-17T13:42:29+02:00" level=warning msg="faces: database is locked (clear marker face)"
Oct 17 13:42:35 photoprism photoprism[2625]: time="2022-10-17T13:42:35+02:00" level=warning msg="faces: database is locked (clear marker face)"
Oct 17 13:42:40 photoprism photoprism[2625]: time="2022-10-17T13:42:40+02:00" level=warning msg="faces: database is locked (clear marker face)"
Oct 17 13:42:45 photoprism photoprism[2625]: time="2022-10-17T13:42:45+02:00" level=warning msg="faces: database is locked (clear marker face)"
Oct 17 13:42:50 photoprism photoprism[2625]: time="2022-10-17T13:42:50+02:00" level=warning msg="faces: database is locked (clear marker face)"
Oct 17 13:42:55 photoprism photoprism[2625]: time="2022-10-17T13:42:55+02:00" level=warning msg="faces: database is locked (clear marker face)"
Oct 17 13:43:00 photoprism photoprism[2625]: time="2022-10-17T13:43:00+02:00" level=warning msg="faces: database is locked (clear marker face)"
Oct 17 13:43:44 photoprism photoprism[2625]: time="2022-10-17T13:43:44+02:00" level=warning msg="faces: database is locked while updating marker mrjclex3klgwnq1s match timestamp"
Oct 17 13:44:04 photoprism photoprism[2625]: time="2022-10-17T13:44:04+02:00" level=warning msg="faces: database is locked while updating marker mrjccea21s1j9mcs match timestamp"
Oct 17 13:44:14 photoprism photoprism[2625]: time="2022-10-17T13:44:14+02:00" level=warning msg="faces: database is locked (clear marker face)"
Oct 17 13:44:26 photoprism photoprism[2625]: time="2022-10-17T13:44:26+02:00" level=warning msg="faces: database is locked while updating marker mrjclex3klgwnq1s match timestamp"
Oct 17 13:44:31 photoprism photoprism[2625]: time="2022-10-17T13:44:31+02:00" level=warning msg="faces: database is locked while updating marker mrjccea21s1j9mcs match timestamp"

I think it's better than before but not super great

[lastzero]

You can reduce the number of workers if tuning Sqlite for concurrency doesn't work. What kind of storage do you use?