Bug 397110

Summary:	Time to render filters is too long with MYSQL
Product:	[Applications] digikam	Reporter:	Dav <davm6101>
Component:	Albums-Filters	Assignee:	Digikam Developers <digikam-bugs-null>
Status:	RESOLVED FIXED
Severity:	normal	CC:	caulier.gilles, metzpinguin
Priority:	NOR
Version:	5.9.0
Target Milestone:	---
Platform:	Other
OS:	Linux
Latest Commit:		Version Fixed In:	8.1.0
Sentry Crash Report:

Description Dav 2018-08-02 22:21:53 UTC

Hi there,

I'm on Ubuntu 18.04 and use the Digikam 5.9 appimage.

After encountering some integrity issues with my tags tree, I migrated to mysql to benefit of the InnoDB (5.7.23-0ubuntu0.18.04.1, locally listening)

My database is composed of two collections, one locally stored, another one stored on a CIFS network share.
It is composed of 25k pictures, 66% as raw files, the remaining files are shared between JPG, and movies files.

I'm a bit surprised of the time taken by applying filters: it will takes more than 5 minutes to display 480 pictures: During this period, CPU usage of MySQL reach 100% and digikam CPU usage is aroun 40%.

My MYSQL configuration don't use sockets, and is quite the default one except the max_allowed_packet which has been improved for the digikam usage to 128M.

I had a look on the queries made to view if there are some improvements I could do to improve the digikam usage and I saw that most of the time is spent when doing the following statement (1358841 queries):

SELECT object 
FROM ImageRelations 
INNER JOIN Images ON ImageRelations.object=Images.id 
WHERE subject=? AND type=2 AND status!=3

But I have only 4 records in the ImageRelations table.

Perhaps there is some technical improvments which could be done on this part.

Kind regards

Comment 1 Maik Qualmann 2018-08-03 06:12:21 UTC

The problem can not be reproduced here. Can you install MariaDB to test? I think the AppImage uses MariaDB's client libraries internally. Right, Gilles? Both projects have moved relatively far apart. The thing with the queries, I look at me yet.

Maik

Comment 2 caulier.gilles 2018-08-03 09:45:31 UTC

Maik, 

yes it is. Mariadb is used everywhere to build bundles.

Gilles

Comment 3 Dav 2018-08-04 23:04:15 UTC

(In reply to Maik Qualmann from comment #1)
> The problem can not be reproduced here. Can you install MariaDB to test? I
> think the AppImage uses MariaDB's client libraries internally. Right,
> Gilles? Both projects have moved relatively far apart. The thing with the
> queries, I look at me yet.
> 
> Maik

Hi Maik,

I should detailed the kind of filters I applied.

Part of my tags tree looks like:

Country _ Australia _ Western Australia _ Perth _ Botanic Garden
Country _ Australia _ South Australia _ Adelaide 
Country _ France _ Paris _ Eiffel Tower

When I noticed all these queries it was when I selected Australia + and the 5 tags just bellow.

Hope it can help.

Regards

Comment 4 Maik Qualmann 2018-08-05 16:08:00 UTC

After a long analysis and debugging, I found the cause for repeatedly executing SQL queries. It is used to determine if there are grouped items. Normally, this information is then also in the ImageInfo cache. But in the ImageFilterModelPreparer the function ImageInfoList::loadGroupImageIds() is called and in this function it is not checked if the information is already in the cache and it is read again from the DB. This with every change of sorting direction and filtering. Therefore, this problem is also relevant to bug 396086. This makes by me about 2 seconds with 20000 images in the view. So do not explain the long time in the bug reports. I will fix it though.

Maik

Comment 5 Maik Qualmann 2018-08-05 16:35:38 UTC

Git commit a3104006d9047ccc338b7eb3d40a975f7059c0a9 by Maik Qualmann.
Committed on 05/08/2018 at 16:34.
Pushed by mqualmann into branch 'master'.

check if group info is already in the cache
Related: bug 396086

M  +20   -3    core/libs/database/item/imageinfo.cpp

https://commits.kde.org/digikam/a3104006d9047ccc338b7eb3d40a975f7059c0a9

Comment 6 Maik Qualmann 2018-08-05 16:50:39 UTC

Git commit 845a33a522e044949852589bf0c35cb577ae90da by Maik Qualmann.
Committed on 05/08/2018 at 16:48.
Pushed by mqualmann into branch 'master'.

check if tags info is already in the cache
Related: bug 396086

M  +18   -3    core/libs/database/item/imageinfo.cpp

https://commits.kde.org/digikam/845a33a522e044949852589bf0c35cb577ae90da

Comment 7 caulier.gilles 2020-08-01 16:17:29 UTC

digiKam 7.0.0 stable release is now published and now available as FlatPak:

https://www.digikam.org/news/2020-07-19-7.0.0_release_announcement/

We need a fresh feedback on this file using this version.

Thanks in advance

Gilles Caulier

Comment 8 caulier.gilles 2022-01-10 07:50:49 UTC

Hi Dav and happy new year,

Can you check if problem remain with digiKam 7.5.0 pre-release bundle available
here :

https://files.kde.org/digikam/

Thanks in advance

Gilles Caulier

Comment 9 caulier.gilles 2023-04-29 07:10:59 UTC

@Dav,

digiKam 8.0.0 is out. This entry still valid with this release ?

Best regards

Gilles Caulier

Comment 10 Dav 2023-05-09 01:25:46 UTC

Seems ok now, @Gilles, with DK 8 / Ubuntu 22.04
Thanks