[digiKam-users] Just over 100k photos - mysql internal or sqlite
Locked
[digiKam-users] Just over 100k photos - mysql internal or sqlite
 
|
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.
|
Re: Just over 100k photos - mysql internal or sqlite
|
|
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 :
|
|
|
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:
|
Re: Just over 100k photos - mysql internal or sqlite
|
|
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'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:
|
Re: Just over 100k photos - mysql internal or sqlite
|
|
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 |
Re: Just over 100k photos - mysql internal or sqlite
|
|
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... > different, i don't know... -- Maderios |
|
|
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. |
|
|
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 |
Re: Just over 100k photos - mysql internal or sqlite
|
|
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 |
Re: Just over 100k photos - mysql internal or sqlite
|
|
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 |
|
|
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 |
|
|
How fast is the network speed and how high is the ping?
-- Sent from: http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html |
Re: Just over 100k photos - mysql internal or sqlite
|
|
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 |
|
|
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: |
Re: Just over 100k photos - mysql internal or sqlite
|
|
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 |
|
|
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 |
|
|
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:
|
Re: Just over 100k photos - mysql internal or sqlite
|
|
This post was updated on .
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 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 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: I deleted a lot of data but the database file did not get any smaller. Is this a bug? Temporary Files Used By SQLite Path.GetTempPath Method 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 PRAGMAs. 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 estimate this beforehand. PRAGMA freelist_count; 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 and SQLiteStudio. -- Sent from: http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html |
| Free forum by Nabble | Edit this page |