Checking photos repeatedly after index completed

[iwinstar]

PhotoPrism Version: 211007-8f55d6f8-Linux-x86_64

......
INFO[2021-10-09T08:14:07Z] indexing completed in 1378 s
INFO[2021-10-09T08:28:47Z] photo pqtivkj34ertw2oe: keeping meta title “Miami Downtown”
INFO[2021-10-09T08:28:51Z] photo pqtivl67m2fjbyqk: keeping meta title “Giant's Causeway”
INFO[2021-10-09T08:28:53Z] photo pqtivlqwnlsd1gcg: keeping meta title “Rising from Orange”
......
......
......
INFO[2021-10-12T08:51:11Z] photo pqtjsgzlrhvcdkwp: keeping meta title “Country Horizon”
INFO[2021-10-12T08:51:18Z] metadata: optimized 110 photos
INFO[2021-10-12T08:51:41Z] index: updating counts
INFO[2021-10-12T08:51:46Z] index: updating covers
INFO[2021-10-12T08:58:28Z] photo pqtivkj34ertw2oe: keeping meta title “Miami Downtown”
INFO[2021-10-12T08:58:30Z] photo pqtivl67m2fjbyqk: keeping meta title “Giant's Causeway”
INFO[2021-10-12T08:58:32Z] photo pqtivlqwnlsd1gcg: keeping meta title “Rising from Orange”
......

I don't add or modifiy any photo, seem it's checking all photos again.

[graciousgrey]

That's expected PhotoPrism regularly runs an optimize command in the background

[iwinstar]

That's expected PhotoPrism regularly runs an optimize command in the background

I checked https://docs.photoprism.org/getting-started/config-options/, couldn't find any option related to this task. How could I adjust it? I have more than 263k photos, it'll need 3 days to run once each time.

[lastzero]

Are you sure it needs 3 days to go through "just" 263k database rows? It's not indexing, just updating based on existing index data.

You can increase the interval of the background worker via PHOTOPRISM_WAKEUP_INTERVAL:

https://docs.photoprism.org/getting-started/config-options/

For example, PhotoPrism generates titles based on metadata or recognized faces. Since not all photos can be instantly updated when you change the name of a person, this happens in the background.

[iwinstar]

MariaDB [photoprism]> select date_format(checked_at, '%m-%d %H'), count(1) from photos where deleted_at is null group by date_format(checked_at, '%m-%d %H');
+-------------------------------------+----------+
| date_format(checked_at, '%m-%d %H') | count(1) |
+-------------------------------------+----------+
| 10-09 11                            |       50 |
| 10-09 12                            |      750 |
| 10-09 13                            |     7076 |
| 10-09 14                            |     8974 |
| 10-09 15                            |     9822 |
| 10-09 16                            |     9884 |
| 10-09 17                            |     6211 |
| 10-09 18                            |     3823 |
| 10-09 19                            |     6822 |
| 10-09 20                            |     9038 |
| 10-09 21                            |     8740 |
| 10-09 22                            |     7809 |
| 10-09 23                            |     6664 |
| 10-10 00                            |     3219 |
| 10-10 01                            |     3483 |
| 10-10 02                            |     2001 |
| 10-10 03                            |     1868 |
| 10-10 04                            |     1739 |
| 10-10 05                            |     1851 |
| 10-10 06                            |     1774 |
| 10-10 07                            |     1780 |
| 10-10 08                            |     1623 |
| 10-10 09                            |     1497 |
| 10-10 10                            |     1439 |
| 10-10 11                            |     1298 |
| 10-10 12                            |     1654 |
| 10-10 13                            |     1785 |
| 10-10 14                            |     1839 |
| 10-10 15                            |     1529 |
| 10-10 16                            |     1554 |
| 10-10 17                            |     1830 |
| 10-10 18                            |     1828 |
| 10-10 19                            |     1769 |
| 10-10 20                            |     1670 |
| 10-10 21                            |     1557 |
| 10-10 22                            |     1645 |
| 10-10 23                            |     1883 |
| 10-11 00                            |     1877 |
| 10-11 01                            |     1860 |
| 10-11 02                            |     1841 |
| 10-11 03                            |     1849 |
| 10-11 04                            |     1803 |
| 10-11 05                            |     1854 |
| 10-11 06                            |     1795 |
| 10-11 07                            |     1615 |
| 10-11 08                            |     1417 |
| 10-11 09                            |     1288 |
| 10-11 10                            |     1606 |
| 10-11 11                            |     1402 |
| 10-11 12                            |     1541 |
| 10-11 13                            |     1414 |
| 10-11 14                            |     1959 |
| 10-11 15                            |     3399 |
| 10-11 16                            |     3305 |
| 10-11 17                            |     3946 |
| 10-11 18                            |     4754 |
| 10-11 19                            |     4861 |
| 10-11 20                            |     4345 |
| 10-11 21                            |     3969 |
| 10-11 22                            |     3720 |
| 10-11 23                            |     3576 |
| 10-12 00                            |     3323 |
| 10-12 01                            |     2953 |
| 10-12 02                            |     3590 |
| 10-12 03                            |     3447 |
| 10-12 04                            |     3758 |
| 10-12 05                            |     3843 |
| 10-12 06                            |     3288 |
| 10-12 07                            |     1856 |
| 10-12 08                            |     2527 |
| 10-12 09                            |    10430 |
| 10-12 10                            |    10184 |
| 10-12 11                            |     9924 |
| 10-12 12                            |     9602 |
| 10-12 13                            |     4224 |
+-------------------------------------+----------+
75 rows in set (1.115 sec)

Yes, It truly use 3 days.

[iwinstar]

SELECT * FROM `photos`  WHERE (place_id <> '' AND place_id <> 'zz' AND place_src <> '' AND place_src <> 'estimate') ORDER BY ABS(DATEDIFF(taken_at, '2017-02-20 15:29:32')) ASC,`photos`.`id` ASC LIMIT 1；

It usually used more than 1.5s to complete this query before I add the index.
It only use 0.1s after I add an index to place_src column.

[lastzero]

May I ask what hardware you have? Our test server here has 100k files, but uses an 8 years old Core i3 processor. It's not doing this for days. Note that the first run may be longer. Was this the first time?

[iwinstar]

I use a Synology DS918+ sever, run PhotoPrism in docker, all HDD drivers, just a weak J3455 processor.
I don't know whether it's the first time. I will check if it's faster this time.

[lastzero]

Surprised place_src helps as the selection is by taken_at... if the database filters by place_src first then yes, it may be slow. Note the cardinality of place_src should be between 1 and 4. Typically doesn't make sense to add an index in this case. You're welcome to contribute if you know how to optimize the query for the databases we currently support.

May I ask how long the initial indexing took?

Looking at the hardware specs of the DS918+, in particular the processor, I'm not surprised you have performance issues (like 70% slower than even a Core i3):

[lastzero]

With these hardware specs, feel free to go ahead and set PHOTOPRISM_WAKEUP_INTERVAL to a higher value and disable location estimates in Settings. This is probably using the most CPU power and you may not want it anyway. 👍

[iwinstar]

I understand your point, this is not the first time. it used 24 days to generate thumbs and complete index for the first time.

MariaDB [photoprism]> select date_format(created_at, '%m-%d'), count(1) from photos group by date_format(created_at, '%m-%d');
+----------------------------------+----------+
| date_format(created_at, '%m-%d') | count(1) |
+----------------------------------+----------+
| 05-22                            |     9215 |
| 05-23                            |    35012 |
| 05-24                            |    10156 |
| 05-25                            |     9364 |
| 05-26                            |    11411 |
| 05-27                            |    10138 |
| 05-28                            |     8152 |
| 05-29                            |    10640 |
| 05-30                            |    11437 |
| 05-31                            |    10041 |
| 06-01                            |     9060 |
| 06-02                            |     6694 |
| 06-03                            |     9975 |
| 06-04                            |     9369 |
| 06-05                            |     9008 |
| 06-06                            |     9775 |
| 06-07                            |     9803 |
| 06-08                            |     9437 |
| 06-09                            |     9526 |
| 06-10                            |     9275 |
| 06-11                            |     9024 |
| 06-12                            |     8461 |
| 06-13                            |     9158 |
| 06-14                            |     6803 |
| 07-04                            |     3212 |
| 09-25                            |       10 |
| 09-26                            |     8391 |
| 09-27                            |     1027 |
| 10-04                            |      167 |
+----------------------------------+----------+
29 rows in set (0.856 sec)

[lastzero]

So when it took 24 days to index, it's not surprising location estimates also take long. It's not a cheap query, but usually also no issue. I'd disable it in your case, which should also speed up the background check. Note the worker will also compare faces when you use facial recognition etc, so I wouldn't get rid of it completely unless you know what implications this has and are OK with it.

[iwinstar]

Most resources are used by MariaDB, PhotoPrism uses just a few for the last 7 days.

[lastzero]

Yes, this is because we use a lot of SQL. We know queries and tables can be optimized further, but don't consider it the most important issue right now. You're welcome to make specific suggestions and sponsor them to be included in a future release.

[iwinstar]

Sure, I will check SQLs at weekends, and I will set PHOTOPRISM_WAKEUP_INTERVAL to 3 months or even higher. This will solve my problem, Thanks.