[digiKam-users] Just over 100k photos - mysql internal or sqlite

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

[digiKam-users] Just over 100k photos - mysql internal or sqlite

Dougie Nisbet

Since I'm just starting out with digikam I thought this was the time to experiment with migrating to mysql. On the configuration screen digiKam can find the binary for mysql_install_db but not mysqld. mysqld is installed and when I use 'find' to tell it that the binary is in /usr/sbin/mysqld it looks promising.

However, trying to migrate the database gives the pop-up error window:

An error occurred during the internal server start.

Details: Could not start database initialiser.

Executable: mysql_install_db

Arguments: --datadir=/store1/images/.mysql.digikam/db_data, --defaults-file=/tmp/.mount_digikaJbT7bS/usr/share/digikam/database/mysql-global.conf

Process error: Unknown error

and on the command line I see:

Digikam::DatabaseServer::processErrorLog: Arguments: "--datadir=/store1/images/.mysql.digikam/db_data, --defaults-file=/tmp/.mount_digikaJbT7bS/usr/share/digikam/database/mysql-global.conf" Digikam::DatabaseServer::processErrorLog: Stdout: "Neither host 'office' nor 'localhost' could be looked up with\n'/usr/sbin/resolveip'\nPlease configure the 'hostname' command to return a correct\nhostname.\nIf you want to solve this at a later stage, restart this script\nwith the --force option\n\nThe latest information about mysql_install_db is available at\nhttps://mariadb.com/kb/en/installing-system-tables-mysql_install_db\n"

which is a bit off. resolveip is a command I didn't know existed but running it returns a local IP address for my host.

Performance using sqlite seems ok, and at just over 100k images I'm probably a borderline case so I thought I would try the mysql database option out of curiosity.

Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

Gilles Caulier-4
Hi,

At home, my collection is over 250.000 files with sqlite. Database and files are stored on SSD and it's enough fast.

GillesC aulier

Le mer. 29 mai 2019 à 12:24, Dougie Nisbet <[hidden email]> a écrit :

Since I'm just starting out with digikam I thought this was the time to experiment with migrating to mysql. On the configuration screen digiKam can find the binary for mysql_install_db but not mysqld. mysqld is installed and when I use 'find' to tell it that the binary is in /usr/sbin/mysqld it looks promising.

However, trying to migrate the database gives the pop-up error window:

An error occurred during the internal server start.

Details: Could not start database initialiser.

Executable: mysql_install_db

Arguments: --datadir=/store1/images/.mysql.digikam/db_data, --defaults-file=/tmp/.mount_digikaJbT7bS/usr/share/digikam/database/mysql-global.conf

Process error: Unknown error

and on the command line I see:

Digikam::DatabaseServer::processErrorLog: Arguments: "--datadir=/store1/images/.mysql.digikam/db_data, --defaults-file=/tmp/.mount_digikaJbT7bS/usr/share/digikam/database/mysql-global.conf" Digikam::DatabaseServer::processErrorLog: Stdout: "Neither host 'office' nor 'localhost' could be looked up with\n'/usr/sbin/resolveip'\nPlease configure the 'hostname' command to return a correct\nhostname.\nIf you want to solve this at a later stage, restart this script\nwith the --force option\n\nThe latest information about mysql_install_db is available at\nhttps://mariadb.com/kb/en/installing-system-tables-mysql_install_db\n"

which is a bit off. resolveip is a command I didn't know existed but running it returns a local IP address for my host.

Performance using sqlite seems ok, and at just over 100k images I'm probably a borderline case so I thought I would try the mysql database option out of curiosity.

Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

Webreaper
I found both sqlite and mysql were equally slow and painful with a collection of 400k images which are stored across the network. ;)

On Wed, 29 May 2019 at 13:02, Gilles Caulier <[hidden email]> wrote:
Hi,

At home, my collection is over 250.000 files with sqlite. Database and files are stored on SSD and it's enough fast.

GillesC aulier

Le mer. 29 mai 2019 à 12:24, Dougie Nisbet <[hidden email]> a écrit :

Since I'm just starting out with digikam I thought this was the time to experiment with migrating to mysql. On the configuration screen digiKam can find the binary for mysql_install_db but not mysqld. mysqld is installed and when I use 'find' to tell it that the binary is in /usr/sbin/mysqld it looks promising.

However, trying to migrate the database gives the pop-up error window:

An error occurred during the internal server start.

Details: Could not start database initialiser.

Executable: mysql_install_db

Arguments: --datadir=/store1/images/.mysql.digikam/db_data, --defaults-file=/tmp/.mount_digikaJbT7bS/usr/share/digikam/database/mysql-global.conf

Process error: Unknown error

and on the command line I see:

Digikam::DatabaseServer::processErrorLog: Arguments: "--datadir=/store1/images/.mysql.digikam/db_data, --defaults-file=/tmp/.mount_digikaJbT7bS/usr/share/digikam/database/mysql-global.conf" Digikam::DatabaseServer::processErrorLog: Stdout: "Neither host 'office' nor 'localhost' could be looked up with\n'/usr/sbin/resolveip'\nPlease configure the 'hostname' command to return a correct\nhostname.\nIf you want to solve this at a later stage, restart this script\nwith the --force option\n\nThe latest information about mysql_install_db is available at\nhttps://mariadb.com/kb/en/installing-system-tables-mysql_install_db\n"

which is a bit off. resolveip is a command I didn't know existed but running it returns a local IP address for my host.

Performance using sqlite seems ok, and at just over 100k images I'm probably a borderline case so I thought I would try the mysql database option out of curiosity.

Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

Gilles Caulier-4
Across the network, sqlite is not the problem, as you CANNOT store the database on a network file system. It must be in local. Only the collection can be on a remote storage.

So the network bandwith usable to consult the collections is the real problem.

With a remote Mysql, you use more bandwith with the database.

Gilles Caulier

Le mer. 29 mai 2019 à 14:27, Mark Otway <[hidden email]> a écrit :
I found both sqlite and mysql were equally slow and painful with a collection of 400k images which are stored across the network. ;)

On Wed, 29 May 2019 at 13:02, Gilles Caulier <[hidden email]> wrote:
Hi,

At home, my collection is over 250.000 files with sqlite. Database and files are stored on SSD and it's enough fast.

GillesC aulier

Le mer. 29 mai 2019 à 12:24, Dougie Nisbet <[hidden email]> a écrit :

Since I'm just starting out with digikam I thought this was the time to experiment with migrating to mysql. On the configuration screen digiKam can find the binary for mysql_install_db but not mysqld. mysqld is installed and when I use 'find' to tell it that the binary is in /usr/sbin/mysqld it looks promising.

However, trying to migrate the database gives the pop-up error window:

An error occurred during the internal server start.

Details: Could not start database initialiser.

Executable: mysql_install_db

Arguments: --datadir=/store1/images/.mysql.digikam/db_data, --defaults-file=/tmp/.mount_digikaJbT7bS/usr/share/digikam/database/mysql-global.conf

Process error: Unknown error

and on the command line I see:

Digikam::DatabaseServer::processErrorLog: Arguments: "--datadir=/store1/images/.mysql.digikam/db_data, --defaults-file=/tmp/.mount_digikaJbT7bS/usr/share/digikam/database/mysql-global.conf" Digikam::DatabaseServer::processErrorLog: Stdout: "Neither host 'office' nor 'localhost' could be looked up with\n'/usr/sbin/resolveip'\nPlease configure the 'hostname' command to return a correct\nhostname.\nIf you want to solve this at a later stage, restart this script\nwith the --force option\n\nThe latest information about mysql_install_db is available at\nhttps://mariadb.com/kb/en/installing-system-tables-mysql_install_db\n"

which is a bit off. resolveip is a command I didn't know existed but running it returns a local IP address for my host.

Performance using sqlite seems ok, and at just over 100k images I'm probably a borderline case so I thought I would try the mysql database option out of curiosity.

Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

Webreaper
I've tried both. The photos are on the network share, but I've tried it with a local Sqlite, a local mysql and a remote mysql. The performance is unusable on all of them - 10+ minutes startup before I can start browsing images, etc. And that's with auto-import at startup turned off. 

On Wed, 29 May 2019 at 13:31, Gilles Caulier <[hidden email]> wrote:
Across the network, sqlite is not the problem, as you CANNOT store the database on a network file system. It must be in local. Only the collection can be on a remote storage.

So the network bandwith usable to consult the collections is the real problem.

With a remote Mysql, you use more bandwith with the database.

Gilles Caulier

Le mer. 29 mai 2019 à 14:27, Mark Otway <[hidden email]> a écrit :
I found both sqlite and mysql were equally slow and painful with a collection of 400k images which are stored across the network. ;)

On Wed, 29 May 2019 at 13:02, Gilles Caulier <[hidden email]> wrote:
Hi,

At home, my collection is over 250.000 files with sqlite. Database and files are stored on SSD and it's enough fast.

GillesC aulier

Le mer. 29 mai 2019 à 12:24, Dougie Nisbet <[hidden email]> a écrit :

Since I'm just starting out with digikam I thought this was the time to experiment with migrating to mysql. On the configuration screen digiKam can find the binary for mysql_install_db but not mysqld. mysqld is installed and when I use 'find' to tell it that the binary is in /usr/sbin/mysqld it looks promising.

However, trying to migrate the database gives the pop-up error window:

An error occurred during the internal server start.

Details: Could not start database initialiser.

Executable: mysql_install_db

Arguments: --datadir=/store1/images/.mysql.digikam/db_data, --defaults-file=/tmp/.mount_digikaJbT7bS/usr/share/digikam/database/mysql-global.conf

Process error: Unknown error

and on the command line I see:

Digikam::DatabaseServer::processErrorLog: Arguments: "--datadir=/store1/images/.mysql.digikam/db_data, --defaults-file=/tmp/.mount_digikaJbT7bS/usr/share/digikam/database/mysql-global.conf" Digikam::DatabaseServer::processErrorLog: Stdout: "Neither host 'office' nor 'localhost' could be looked up with\n'/usr/sbin/resolveip'\nPlease configure the 'hostname' command to return a correct\nhostname.\nIf you want to solve this at a later stage, restart this script\nwith the --force option\n\nThe latest information about mysql_install_db is available at\nhttps://mariadb.com/kb/en/installing-system-tables-mysql_install_db\n"

which is a bit off. resolveip is a command I didn't know existed but running it returns a local IP address for my host.

Performance using sqlite seems ok, and at just over 100k images I'm probably a borderline case so I thought I would try the mysql database option out of curiosity.

Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

leoutation
In reply to this post by Gilles Caulier-4
On 5/29/19 2:01 PM, Gilles Caulier wrote:
> Hi,
>
> At home, my collection is over 250.000 files with sqlite. Database and
> files are stored on SSD and it's enough fast.
>

I guess you have not big files, like png, tiff or xcf > 100Mo...
I you don't use mysql but sqlite, good luck with these files, they
substantially slow down operations...

--
Maderios
Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

leoutation
On 5/29/19 4:31 PM, [hidden email] wrote:

> On 5/29/19 2:01 PM, Gilles Caulier wrote:
>> Hi,
>>
>> At home, my collection is over 250.000 files with sqlite. Database and
>> files are stored on SSD and it's enough fast.
>>
>
> I guess you have not big files, like png, tiff or xcf > 100Mo...
> I you don't use mysql but sqlite, good luck with these files, they
> substantially slow down operations...
>
Precision, I use hard drive, not ssd. In this last case, it may be
different, i don't know...

--
Maderios
Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

Iñigo
El 29/05/2019 a las 18:47, [hidden email] escribió:

> On 5/29/19 4:31 PM, [hidden email] wrote:
>> On 5/29/19 2:01 PM, Gilles Caulier wrote:
>>> Hi,
>>>
>>> At home, my collection is over 250.000 files with sqlite. Database and
>>> files are stored on SSD and it's enough fast.
>>>
>>
>> I guess you have not big files, like png, tiff or xcf > 100Mo...
>> I you don't use mysql but sqlite, good luck with these files, they
>> substantially slow down operations...
>>

But the digiKams Mysql support is experimental, isn't it?

Or is the internal Mysql server trustworthy?

(I'm on Windows)


Iñiog.

Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

woenx
In reply to this post by Webreaper
I only found startup times that big when I tried to access my picture gallery
through internet. I observed that low latencies are more important than
network speed. In my case, accessing a 100000 picture library via wifi,
startup times are not higher than two minutes. Maybe a bit more if many new
items are found. The database should be stored locally, of course.



--
Sent from: http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html
Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

leoutation
In reply to this post by Iñigo
On 5/29/19 7:00 PM, Iñigo wrote:

> But the digiKams Mysql support is experimental, isn't it?
>
> Or is the internal Mysql server trustworthy?
>
> (I'm on Windows)
>
I don't know for windows. On Linux, my case, it's not "experimental" for me.
I use it every day.


--
Maderios
Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

Rafael Linux
I reported exaggerate start up times on Digikam 5.9, working in a similar
configuration than yours:
- Digikam 5.9
- 300K photos on HD 6TB
- Database on SSD
- OpenSUSE 15.1

However, Digikam developers did a fantastic job on Digikam 6 and now it
takes about only 10 seconds to be operative. It's my experience.



--
Sent from: http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html
Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

Webreaper
In reply to this post by woenx
I tried it yesterday. Startup was over 25 minutes. This is on a MacBook pro. 

On Wed, 29 May 2019, 18:19 woenx, <[hidden email]> wrote:
I only found startup times that big when I tried to access my picture gallery
through internet. I observed that low latencies are more important than
network speed. In my case, accessing a 100000 picture library via wifi,
startup times are not higher than two minutes. Maybe a bit more if many new
items are found. The database should be stored locally, of course.



--
Sent from: http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html
Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

woenx
How fast is the network speed and how high is the ping?



--
Sent from: http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html
Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

Remco Viëtor
In reply to this post by Webreaper
On jeudi 30 mai 2019 13:59:52 CEST Mark Otway wrote:
> I tried it yesterday. Startup was over 25 minutes. This is on a MacBook
> pro.
>
The type of computer is probably irrelevant (if it's a reasonably recent
machine).

But what kind of network connection do you use from that MacBook?
If it's WIFI, especially when there are other connections on that frequency
(telephone, game consoles, other computers, ...), you're out of luck...

Remco


Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

Webreaper
It's wifi. But unless I'm importing new images from the LAN (and I have that disabled) I see zero reason why startup should take more than a few seconds with the DB located on the laptop, since everything should be local. 

On Thu, 30 May 2019, 13:30 Remco Viëtor, <[hidden email]> wrote:
On jeudi 30 mai 2019 13:59:52 CEST Mark Otway wrote:
> I tried it yesterday. Startup was over 25 minutes. This is on a MacBook
> pro.
>
The type of computer is probably irrelevant (if it's a reasonably recent
machine).

But what kind of network connection do you use from that MacBook?
If it's WIFI, especially when there are other connections on that frequency
(telephone, game consoles, other computers, ...), you're out of luck...

Remco


Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

Maik Qualmann
In reply to this post by Webreaper
Please try the current pre-release version of digiKam-6.2.0 and report if
digiKam now starts faster on a Mac:

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

Maik

Am Donnerstag, 30. Mai 2019, 13:59:52 CEST schrieb Mark Otway:

> I tried it yesterday. Startup was over 25 minutes. This is on a MacBook
> pro.
>
> On Wed, 29 May 2019, 18:19 woenx, <[hidden email]> wrote:
> > I only found startup times that big when I tried to access my picture
> > gallery
> > through internet. I observed that low latencies are more important than
> > network speed. In my case, accessing a 100000 picture library via wifi,
> > startup times are not higher than two minutes. Maybe a bit more if many
> > new
> > items are found. The database should be stored locally, of course.
> >
> >
> >
> > --
> > Sent from:
> > http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html




Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

Webreaper
Cool! Will give it a try tomorrow and see if it helps. 

On Thu, 30 May 2019, 13:59 Maik Qualmann, <[hidden email]> wrote:
Please try the current pre-release version of digiKam-6.2.0 and report if
digiKam now starts faster on a Mac:

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

Maik

Am Donnerstag, 30. Mai 2019, 13:59:52 CEST schrieb Mark Otway:
> I tried it yesterday. Startup was over 25 minutes. This is on a MacBook
> pro.
>
> On Wed, 29 May 2019, 18:19 woenx, <[hidden email]> wrote:
> > I only found startup times that big when I tried to access my picture
> > gallery
> > through internet. I observed that low latencies are more important than
> > network speed. In my case, accessing a 100000 picture library via wifi,
> > startup times are not higher than two minutes. Maybe a bit more if many
> > new
> > items are found. The database should be stored locally, of course.
> >
> >
> >
> > --
> > Sent from:
> > http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html




Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

Webreaper
Crikey. What a difference. Startup is now around 1 minute, maybe less - and for a chunk of that the splash screen is displayed, so you actually know it's doing something. Nice job!

On Thu, 30 May 2019 at 15:03, Mark Otway <[hidden email]> wrote:
Cool! Will give it a try tomorrow and see if it helps. 

On Thu, 30 May 2019, 13:59 Maik Qualmann, <[hidden email]> wrote:
Please try the current pre-release version of digiKam-6.2.0 and report if
digiKam now starts faster on a Mac:

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

Maik

Am Donnerstag, 30. Mai 2019, 13:59:52 CEST schrieb Mark Otway:
> I tried it yesterday. Startup was over 25 minutes. This is on a MacBook
> pro.
>
> On Wed, 29 May 2019, 18:19 woenx, <[hidden email]> wrote:
> > I only found startup times that big when I tried to access my picture
> > gallery
> > through internet. I observed that low latencies are more important than
> > network speed. In my case, accessing a 100000 picture library via wifi,
> > startup times are not higher than two minutes. Maybe a bit more if many
> > new
> > items are found. The database should be stored locally, of course.
> >
> >
> >
> > --
> > Sent from:
> > http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html




Reply | Threaded
Open this post in threaded view
|

Re: Just over 100k photos - mysql internal or sqlite

Stephen Leibowitz
In reply to this post by Gilles Caulier-4
*Tools* > *Maintenance* > *Perform database cleaning* is helpful in tuning
the databases. Here is a partial description, “The application is now able
to identify stale database objects (like deleted images, not used thumbnails
and face identities) and to remove them. This garbage collection is a stage
of the maintenance.”

The last item within *Perform database cleaning* is *Also shrink all
databases if possible* The tool tip is “This option leads to the vacuuming
(shrinking) of the databases. Vacuuming is supported both for SQLite and
MySQL.” I will restrict my comments about its usage to SQLite databases.

Selecting *Also shrink all databases if possible* will run the SQLite
VACUUM <https://sqlite.org/lang_vacuum.html>   command. It gives back
deleted space within the database to the file system.

VACUUM also defragments the database. Inserts, updates, and deletes can
cause the database file to become fragmented - where data for a single table
or index is scattered around the database file. Database fragmentation
reduces performance on hard disk drives. But there is considerable debate as
to whether this is so on SSDs. Here is a quote from a  forum post
<https://forum.palemoon.org/viewtopic.php?t=785>   that I find persuasive:
‘Also consider that “database fragmentation” is a whole different issue than
“file system fragmentation.” You can have a database in a contiguous file on
disk that is really slow because of internal fragmentation and not being
optimized.’

VACUUM can use up to twice as much temporary disk space as the original
file. The writing done by VACUUM is a reason to be cautious about using it
on SSDs, where repeated writes can wear out cells. Also, it can take a long
time for large databases.

These three links concern the temporary disk space that SQLite VACUUM uses:
https://sqlite.org/faq.html#q12
https://sqlite.org/tempfiles.html
https://docs.microsoft.com/en-us/dotnet/api/system.io.path.gettemppath?view=netframework-4.8

For the ambitious, it is possible to obtain statistics about a SQLite
database that may help decide if it should be vacuumed This is done with
three SQLite  PRAGMA <https://www.sqlite.org/pragma.html>  s.
*freelist_count* is the number of empty pages in the database. VACUUM will
give back these pages to the file system. Also, VACUUM defragments the
partially filled pages. This often yields additional empty pages that it
also gives back. Unfortunately, there are no PRAGMAs to measure this
beforehand.

PRAGMA  freelist_count
<https://www.sqlite.org/pragma.html#pragma_freelist_count>  ;
PRAGMA  page_count <https://www.sqlite.org/pragma.html#pragma_page_count>  ;
PRAGMA page_size;

The PRAGMAs can be run against the four digiKam databases when the databases
are stored in the SQLite format:
digikam4.db
recognition.db
similarity.db
thumbnails-digikam.db

digiKam should be closed when the PRAGMAs are run. I ran the PRAGMAs from
two standalone utility programs:  DB Browser for SQLite
<http://sqlitebrowser.org>   and  SQLiteStudio <https://sqlitestudio.pl>  .




--
Sent from: http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html