mysqld at 100% CPU for weeks (400k library)

[vb0]

Granted, it's a huge library! But I finished indexing quite a while ago and things are otherwise running well (it was mostly fine during the indexing too). Many/most have GPS tags.

From PHOTOPRISM_DEBUG: "true" it's clear that it's actually doing something useful; it's doing about once a second "generating title from label" or "approximate position of uid is...". I assume it's enriching the database and things are kind of working as expected but maybe I'm missing some optimization, we can add some index to the db or something. I hope it's not the debug that makes it slow, one line per second. Or at least a way to gauge progress (I didn't capture the output, probably I could tell how many uids were processed).

[lastzero]

First of all, make sure to use our latest version as we optimized a few things. Then try disabling location estimates on https://demo.photoprism.org/settings/library - those usually cause most of the load.

Metadata updates now generally happen more often as photo titles get regenerated after people were recognized so that they contain the names. While I don't see such a high load with 100k images on our Core i3 test server, it's absolutely possible that you have a different experience with 400k images and/or other hardware, see #1618 (comment)

With that many files, it may also be worth increasing the InnoDB buffer size:
https://mariadb.com/kb/en/innodb-buffer-pool/#innodb_buffer_pool_size

PS: Please share your server specs (OS, CPU, disk type and memory) so that we can better reproduce your issue.

[vb0]

Thank you for your quick answer.

Disabling location estimates calmed it down completely. It did lots and lots of "generating title from label" quickly (but no more "approximate position of uid is..." of course) and then calmed down completely. Actually kind of too much, as it doesn't scan at all for faces. I did again a library scan and that went pretty quickly but no changes (as in face detection doesn't seem to start), I'm doing now a "complete rescan" just and see how that will end.

I'm using the latest docker version, Linux (Ubuntu 21.04), originals are on local disk, everything else on SATA3 SSD. The only relevant tweak I think I have is PHOTOPRISM_THUMB_SIZE: 720, because of the space and indexing effort needed with such a big library.

i7-5600U, 12GB RAM

[lastzero]

Note the comments in our Advanced Settings docs: 720px thumbs will significantly reduce the number and quality of image classification and face detection results as TensorFlow won't be able to properly "see" anymore.

[vb0]

Thanks, no worries I didn't really need it anyway, in a good way :-) . I was just curious about the face detection if it starts at all but I'm using Photoprism mainly for its map capabilities, I've been searching since 2006 for a program with even basic capabilities to show all your pictures on the map and it's almost impossible to find some that do it even in the most basic way (again, for a large collection). Lightroom kind of does it but it bogs down eventually even on many more and faster cores and 32GB RAM and SSD everything, Google Photos works beautifully but only in the mobile app (and it isn't an actual app you can run and storage costs since June). Photoprism is just what the doctor ordered 👍

[lastzero]

I'll close this then :) Going to have an eye on location estimates... maybe we can perform them less often or find a better way.