Speedups for the Thumbs DB (esp. on LAN/Multi-User)

[m.Th.]

XnView MP can work on a LAN with multiple users simultaneously accessing the same data store. However due of its architecture, this number is relatively small, and one of the main reasons (if not THE main reason) is that SQLite locks the entire DB when it writes. This isn't such a problem for the small db (xnview.db) but for the big one, the one with the thumbnails inside (thumbs.db).

However, there is a simple solution for this:

In Settings | Integration | Paths add bellow to "Folder for Database (.db)" an Edit Folder Field saying "Folder for thumbs (leave empty to store in the same folder with the DB)". Something like this:

Thumbs-Path.png

This will enhance the speed of the program not only in the case of an multi-user environment (each user can have its non-shared thumbs.db stored locally), but also on LAN (1Gb Lan is a bottleneck when it comes to transfer large amounts of data like many thumbnails) and also locally: the head of the HDD will spend the time seeking sectors because it will write to (at least) two different files simultaneously: xnview.db and thumbs.db - one can setup the program to put the thumbs.db on a 2nd HDD, SSD or even on a USB thumb drive.

The speed improvements will be significant.

[oops66]

+1 it's seem to be a good idea .

[CameronD]

Perhaps just clarify that the default setup (at least for Win7 and I presume also for Linux-based systems) is to have the database location allocated on a per-PC, per-user basis.
So in a default setup the problem does not arise.

The situation you describe can happen if the user deliberately chooses to assign the xnview.db to a network location for the specific purpose of sharing. In that case, forcing the thumbs db to a local drive is a good suggestion.

It also raises the question about other potential pitfalls in having a shared xnview.db. The most obvious one to me is the folder pathname.
Windows allows referencing either by mapped drive-letter or UNC path (\\server\folder\etc).
The XnView db will use whichever pathname you have used each time, so it would be easy to get multiple DB entries to the same image file unless all users were being careful about how the files were being accessed.
I am not sure if it is even possible for windows and Linux/OSX devices to use the same pathname format.
That is another reason I insist that the metadata within my image files is the master copy, if at all possible.

[m.Th.]

Perhaps just clarify that the default setup (at least for Win7 and I presume also for Linux-based systems) is to have the database location allocated on a per-PC, per-user basis.
So in a default setup the problem does not arise.

Isn't quite like that. Because we have at least two files (ok, three if we take in account the SQLite's journaling) the write/read speed is much lower because the program accesses these files simultaneously and the HDD's heads must change continuously their position, hence the most "read/write" time is spent to seek the correct sector/cluster and not to read the actual data.

Of course, if we'll take in account the SQLite's caching (Tools | Settings | Database | Memory usage for database engine) and HDD's caching + TCQ/NCQ capabilities, the things get better but nothing magical will happen. The real solution IMHO is the one implemented years ago by many DB systems (Oracle for example has thins thing as a standard technique): split the data on two (or more) disks/storage units in order to have more throughput. Oracle puts the data on one disk and indexes on another. Nowadays, instead of buying a 2nd HDD one can safely buy an USB3 flash-drive or (better) a small SSD, which is more than enough for such tasks.

The situation you describe can happen if the user deliberately chooses to assign the xnview.db to a network location for the specific purpose of sharing. In that case, forcing the thumbs db to a local drive is a good suggestion.

It also raises the question about other potential pitfalls in having a shared xnview.db. The most obvious one to me is the folder pathname.
Windows allows referencing either by mapped drive-letter or UNC path (\\server\folder\etc).
The XnView db will use whichever pathname you have used each time, so it would be easy to get multiple DB entries to the same image file unless all users were being careful about how the files were being accessed.

Mapped drive-letter is a feature which is very discouraged for many reasons (including this one). On any Windows LAN which I saw (and I humbly dare to say that I saw some), everyone works with UNC paths, except, of course, if there is a very old program which requires drive letters. (as a funny thing, there are programs which check if the drive letter is a mapped drive and reject it).

In networked DAMs there is already a standard in place: having a central server with a share, something like \\MyServer\Archive for the photos (usually a RAID 60 with a bunch of HDDs) and, in the case of a file-based db, another share, say \\MyServer\DB for the database with RCK (Rating/Color/Keywords) - usually a RAID1 with two SSDs.

The problem with the above setup is, as I stated in my OP, is that the thumbs db is also there, clogging the system without a real need to do it. I have a very practical (and painful) experience with a 60 GB thumbs DB in SQLite first and now on MS SQL Server on the LAN.

I am not sure if it is even possible for windows and Linux/OSX devices to use the same pathname format.

It is. Just few days ago we had a support request from someone here. He had a laptop with Mint and WinXP and wanted from both systems to access the same DB. In fact, if you look to the folders (Settings | Database) you'll see that even on Windows the paths are stored by Qt (which is cross-platform) in a linux format (ie with /) and not in Windows (IOW with \). Of course, the things (paths) must be set up accordingly (Samba etc.) - nothing difficult, but one must take care of them. Also, XnViewMP supports the 'Root Path' feature which takes care of different relative roots between the systems.

That is another reason I insist that the metadata within my image files is the master copy, if at all possible.

Oh, yes. Sure. Nobody disputes that. First-class exporting/importing in files metadata (for the formats which support it) and, of course, XMP is crucial. However, this isn't searchable and manageable (batch editing of metadata etc.). And here starts the pain.

[CameronD]

Perhaps just clarify that the default setup (at least for Win7 and I presume also for Linux-based systems) is to have the database location allocated on a per-PC, per-user basis.
So in a default setup the problem does not arise.

Isn't quite like that. Because we have at least two files

OK, to expand on my comment - there were two aspects to your reasoning:

1. SQLite's write-locking, and
2. spreading activity across multiple drives.

I'd certainly agree that many processes will improve by spreading activity across drives, and I accept you analysis of that aspect.

My comment was simply that slowdowns waiting for lock release do not occur in the default configuration.

Mapped drive-letter is a feature which is very discouraged for many reasons (including this one)

I can give you just as many examples of problems using UNC. Give me the Unix style any day.

I worked for a large organisation that would routinely reallocate departments to a different server. Groups would get moved from one department to another and their files would get moved to another server. Mapped drives were the only way to get any stability there.
I think the important thing is to stick to one or the other.

But of course, there are some MS programs that know better than I do how I need my data referenced.

.. you'll see that even on Windows the paths are stored by Qt (which is cross-platform) in a linux format (ie with /) and not in Windows (IOW with \). Of course, the things (paths) must be set up accordingly (Samba etc.) - nothing difficult, but one must take care of them.

Even as far back as in the days of DOS 5, the MS C libraries would allow use of '/' as a path separator.
It's the server part that I see as problematical - I don't know a way that any precompiled Linux program can open a file given a UNC path. Or is this something special to the QT library?

Or, do we need to use the root path feature? :

Also, XnViewMP supports the 'Root Path' feature which takes care of different relative roots between the systems.

Yes, I noticed that - it is probably the right solution - but I then forgot about it because I could not understand what it would do when I have images both on local hdd and a server.

[m.Th.]

I can give you just as many examples of problems using UNC. Give me the Unix style any day.

(Emphasis mine)

Yes. For example, you can do it via DFS (Distributed File System).

I worked for a large organisation that would routinely reallocate departments to a different server. Groups would get moved from one department to another and their files would get moved to another server. Mapped drives were the only way to get any stability there.
I think the important thing is to stick to one or the other.

The solution for these scenarios - (un)fortunately, I have also some experience in the matter - is a feature called 'Relocate folder on disk' (IDImager), 'Map to the Correct Physical Folder' (Photo Supreme), 'Update Folder Location' (Lightroom) aso.

It is a very simple and effective feature. Here is the screenshot from IDImager:

IDI-Relocation.jpg

I think that's self-explanatory. It replaces in DB the old path with the new path. But for this we need to have a front-end for our catalog. See here for discussion: http://newsgroup.xnview.com/viewtopic.p ... 67#p113504


An interesting approach is AfterShotPro's Movable Catalog Folders which aims squarely exactly to this: 'mapping' the on-disk folders with different paths on different computers (OSes) to the same folder record in the Catalog.
We can do the same or even better (more details upon request).

It's the server part that I see as problematical - I don't know a way that any precompiled Linux program can open a file given a UNC path. Or is this something special to the QT library?

Samba's Linux does it. For the programs is transparent. See here:

http://serverfault.com/questions/526509 ... s-explorer

Also you can use symlinks:
http://www.seriss.com/rush-current/rush ... TDFAQ-UNC2

[CameronD]

Firstly, I completely misunderstood the background to your post - for some crazy reason I got the idea that it was a suggestion for users, based on existing functioning within XnViewMP.
Now that I understand it is a request for a new feature I say definitely "yes please".

In my experiments I found perhaps a few more reasons to separate the db locations:

1. Users can choose different thumbnail options and these will obviously not be adhered to if different users access the same Thumb.db file. (not fatal, just inconvenience)
2. Database maintenance gets confused - My windows version of XnViewMP added some local folders and I tried "rebuild thumbnails" on the shared Linux version. All thumbnails were removed, and the program looped doing nothing (the abort button still worked). The Thumb.db file did not get any smaller, but XnViewMP showed 0 bytes allocated to thumbs and the DB explorer showed zero entries, so I presume sqlite was just leaving the file contents as unclaimed space rather than shrinking the file. Windows does something similar, but only zeroes thumbnails for folders it does not recognize - but that might be related to the order in which each system processes the list.
3. By far the worst possibility, if one user should decide to ''optimize'' the database and choose a option like checking for orphans then many entries can be wiped out.

Matching folder names across operating systems:

The trick of using a top level folder on a Linux client to match the UNC server name works, but not quite as you suggested.

• The symlink trick does not work, because QT (I suppose) dereferences the symlink and stores the actual pathname in the DB.
• You must either mount the server folder exactly where it will match the UNC path, or
• if the remote filesystem is already mounted elsewhere, it can be done by using mount with a bind option, again so that the paths match.
• QT, or XnView, is even smart enough to spot that a folder is on a remote filesystem and automatically prefixes the path with the "//" (whether or not the top level directory represents a server name - no harm done if it isn't, apart from the windows machine occasionally grinding to a halt waiting to contact the non-existent server)
• the fileserver protocol is not important - I used samba for the windows machines and NFS for Linux.
• As far as I can see, DFS does not let you use unix-style pathnames in Windows, you still need the leading name

Whether OS-X has similar functions I don't know.

[m.Th.]

Now that I understand it is a request for a new feature I say definitely "yes please".

Glad to hear that I have your support! Greatly appreciated!

Thanks also for Matching the Folder Names analysis.

About this, I'm thinking about some ways to solve the Matching Folder Names:
Let's say that in DB I have:

\\myServer\Share\F1

A locally stored (per-user) text file with a table having two columns: find & replace for paths. For example, having in this file on Linux

Code: Select all

\\myServer\Share     \mnt\myWindozeMount\F1

...when I go in my Linux box to \mnt\myWindozeMount\F1 the program will correctly extract the thumbs and the RCK/metadata from the Database because it will replace on-the-fly the Linux path with the one from the DB. Is in fact like the 'hosts' files or DNS system, but for the folders. This has also the great advantage that if the sysadmin will want to relocate the shares (and the sysadmin WILL want to relocate the shares) we must only change few entries in this MobileFolders.txt and we are all set. Also a button will be provided to burn the changes in the DB, but it should be used with care.

What do you think?

[CameronD]

...A locally stored (per-user) text file with a table having two columns: find & replace for paths. For example, having in this file on Linux...
What do you think?

I think the first question is "how important is this?". and "will the added complexity give enough added value or would effort be better spent elsewhere?"

At a simple level, as a home user, I can arrange to have paths match as outlined in my previous post. So there are workarounds in the current situation, even if we need trivial sql code to replace mapped drive folders with UNC paths. I would imagine OS-X has a similar mechanism.

The difficulty with this simple solution would be in corporate systems where users have little or no control over server mount points.

However, I have no idea how important this factor would be to development directions in MP.

If multi-user, multisystem access to a common db is seen as desirable, then I think we should go a step beyond a simple text file.

I can see several advantages in putting the extra information into the XnView db itself, and going a bit beyond your suggestion and extending the current implementation of "Base Path"

1. a new table with user name, PC hostname, and some userRefID.
2. Split the folders table into two:
   • modify the folder table has:
     FolderID; int rootPathID; string sharedPath
   • a new rootPathTable approximately equivalent to your text file, but including
     int rootPathID; int userRefID; string rootPath

The full path is then a simple concatenation of 3 strings instead of two.
No need for search/replace.

The main reason that I think it is important to add this complexity is that each instance of XnView should be able to tell when there are folders and images in the DBs that are specific to other users but not the current one.
If sharability is an important feature then XnView needs more help to determine which images and folders are being shared.

The current database maintenance options are just too dangerous to use in a shared situation.

[m.Th.]

a new table with user name, PC hostname, and some userRefID.

Why this? Do you think that each user can have different profiles / drive mappings / mount points on different workstations?

The main reason that I think it is important to add this complexity is that each instance of XnView should be able to tell when there are folders and images in the DBs that are specific to other users but not the current one.

Why? Why you think that's important for my instance of XnView to know how do you see the folders?

The current database maintenance options are just too dangerous to use in a shared situation.

Sure.

[CameronD]

a new table with user name, PC hostname, and some userRefID.

Why this? Do you think that each user can have different profiles / drive mappings / mount points on different workstations?

Certainly - my local desktop partitions and contents are different from my laptop's.

The main reason that I think it is important to add this complexity is that each instance of XnView should be able to tell when there are folders and images in the DBs that are specific to other users but not the current one.

Why? Why you think that's important for my instance of XnView to know how do you see the folders?

It is vital - because of the next point.

The current database maintenance options are just too dangerous to use in a shared situation.

Sure.

There is a single database that includes all our images - both shared and local.
If you and I share a database and if I do any database maintenance that cleans up orphaned files and folders then XnView must be able to know which ones are nothing to do with me and ignore them, or else it will wipe out all the metadata for your files apart from the shared ones.