Photolab 9 database limits

[eluohm]

My current PL9 database is 17.6 Gb. It takes a few minutes to open and display my photo library. My PC has the requirements for Photolab 9 (well over the minimum). the PL9 database is maintained on my SSD system 1 TB hard drive. My main photo folders are in an external USB3 hard drive. Once the photos are displayed, their processing speed is ok.

My question : is it possible to use multiple databases instead of a unique one, like one database per year ? This would maybe accelerate their opening in Photolab.

Or is there another way to improve the opening of my current database ?

[platypus (Canon & Mac)]

@eluohm

is it possible to use multiple databases instead of a unique one, like one database per year ?

PhotoLab was not built to select from different databases like other apps. I can imagine a work around though - but it would go further than most users would want to go and it would require a very strict mode of operation from your side.

Concept:

• make a backup and set it aside
• trash the PL DB
• repeat
• • index a year
• • give the DB a distinct name or location
• until all years have an individual DB

Now, you’d have to convince PL to open with the DB for the year you want. Problem is, that this is complicated and all but relaxed way to work. And as soon as you select an “outside” folder, the DB will contain outside of the year images…

The simple way: Trash the DB before you start PL. This has some drawbacks, but many posters here work PL this way.

[andras.csore]

@eluohm

My current PL9 database is 17.6 Gb

Size more-or-less only related about PL editing ‘history entries’.

Example of my database - not so much photo in there: 10296.
Database size (overall): 400 MB. Some (not all) data table size:

• Folders table (where the folder structure stored: 176 row, size: 0.01 MB
• Items (photos / edited photos + virtual copies): 10296 / 71 MB
• Sources (the files itself): 10036 / 1.8MB
• HistoryEntries: 55342 / 322 MB → and 4340 photos has history entry (approx half of all photos) and only average 13 history entry per photo!

So, i think your 17GB database size due the ‘Advanced History’ - default like 50 for each edited photo (i leave it to default 50 in my database). Example: If no history, for 1GB database size, around 150 000 photo needed, for 17GB → around 2.5 million (may just 2 million)

is it possible to use multiple databases instead of a unique one, like one database per year

AFAIK in the file menu → Dxo Photolab database → backup ; restore. May you can do the trick with that.
However, i think it MAY has one small issue → preview cache. I guess, by default its not designed for multiple database. However, cache folder can be changed, so its can also works.

This would maybe accelerate their opening in Photolab.

I dont think its save too much if any. A few times i ‘debug’ what happen in DxO PL startup, folder opening, etc. and comment in the forum about that a few times. As far as i remember, of course its does database reads a lot during startup, but it was ‘not too much’ versus other things during PL startup (but i test with minimal history entries). For me currently like around 25-40 sec to start PL9 (all things in internal drives)

My main photo folders are in an external USB3 hard drive.

May that’s can be the thing behind startup performance.

Other: Data tabe size SQL query’s line by line (Windows database, i think in Mac the same, just folder names others (but similar)

Select count(*) from folders as itemcount_folders 
SELECT SUM("pgsize") FROM "dbstat" WHERE name='Folders' 
Select count(*) from Items as itemcount_items
SELECT SUM("pgsize") FROM "dbstat" WHERE name='Items'
Select count(*) from sources
SELECT SUM("pgsize") FROM "dbstat" WHERE name='Sources' 
Select count(*) from HistoryEntries as itemcount_history 
SELECT SUM("pgsize") FROM "dbstat" WHERE name='HistoryEntries'  
Select count(*) from (Select count(*) from HistoryEntries group by ItemId)  -- how many photos has History
Select avg(cnt1)  from (Select count(*) as cnt1 from HistoryEntries group by ItemId) -- average historyentry per photo

[platypus (Canon & Mac)]

@andras.csore

Data tabe size SQL query’s line by line (Windows database, i think in Mac the same, just folder names others (but similar)

I usually look at the DB with

[platypus (Canon & Mac)]

@eluohm

My current PL9 database is 17.6 Gb

Really?

I just checked my database that I indexed from scratch. The DB lists about 25’000 image files and has a size less than 60 MB for PL7 and PL8. For PL9, the size is below 40 MB. My Photo Archive has a total size of less than 400 GB.

BUT: I use PL like a plugin and therefore, there is not much to be stored for history. Entries are mostly about an initial preset - and the rest all happens elsewhere, in Lightroom mostly, the catalog of which takes about 400 MB.

All things considered, I’d check the state of the DB by moving it away from PhotoLab (for backup) and letting PL index the whole lot from a clean slate. The GIGA-byte size of the DB just doesn’t feel right.

[eluohm]

Here is what I see in my PL9 database folder:

I guess the xxxx-1.db files are just database backups ? If so, the real size of my PL9 database is 3.28 Gb, not 17 Gb. I do a backup of this database folder weekly, and these xxxx-1.db files are also included in my backup job.. I will change that. But this will not change the slow opening of my photo library in PL9.

I must say that I have a large collection of photos, a few hundred thousands, taken over 4 decades. I like the way PL manage the photo library, compared to Lightroom which uses the catalog concept instead, based on an import process of the files. But maybe It would be easier to manage a large collection of files (year based for example) with the catalog concept.

[platypus (Canon & Mac)]

@eluohm

I guess the xxxx-1.db files are just database backups ?

You can easily find out by directing the backups to some other folder. Nevertheless, a DB of 3GB is way bigger than anything I’ve seen so far in this context.

@eluohm

I must say that I have a large collection of photos, a few hundred thousands, taken over 4 decades. I like the way PL manage the photo library, compared to Lightroom which uses the catalog concept instead, based on an import process of the files.

Lots of photos indeed.
As for “the way PL manage the photo library, compared to Lightroom” let me say the following: Both PL and LrC

• read files and store metadata in their catalogs or databases
  → they simply use different words for the same thing.
  → Technically, metadata is stored in a catalog in a database.
• LrC has an explicit import feature while PL imports every image file it sees without requesting your attention.
  → LrC puts you in control of what files are to be catalogued
  → PL simply gobbles up everything that gets in its way
  → the result is the same: metadata in a database/catalog

Now, PL’s auto-ingest feature can lead to duplicate/multiple entries for the same thing(s), e.g. if folder or file names have been changed, or if the archive’s structure was modified, all of which is a common thing users do occasionally.

Based on my own experience, I can say that PL’s database gets off track easily and that is why some users delete the database regularly and rely fully and solely on sidecars.

You could do the following:

• close PL and rename the folder that contains the database(s)
• set PL to index your archive … making sure that the process can run without interruptions from sleep timers or power outages etc. Let it roll until done
• check the size of the database.

[eluohm]

Thank you for your advice. If I understand correctly, PL looks for a database each time it launches. If it doesn’t exist, it creates a new one. I thought only the pointers to the photos were stored in the database, along with the metadata, and not the raw or JPEG files themselves. Image edits are saved separately in the DOP files. The cache folder is used for displaying the photo previews. So, that’s not how PL works?

[platypus (Canon & Mac)]

PL’s database only contains metadata, data about the image files, shooting conditions, image size, database internal info etc. With several thousands of rows, the database can get heavy, but 3 GB still feels like a lot.

I suppose that the last few years of using computers have seen more than one computer and therefore more than one entry per file, at least there is a fair chance for that. To check, follow the directions I gave in my previous post. If unsure, just ask.

My photo archive contains roughly 25’000 image files. They occupy about 400 GB on my Mac’s internal drive. PL9’s database size on the drive is about 40 MB.

Can you be specific about the number of image files and the space they take?

• Number of files, file types?
• Space occupied on your drive(s)?

[andras.csore]

@eluohm

Image edits are saved separately in the DOP files

Other points your absolutely correct, except the DOP. DOP not mandatory. Edit in the database → Its synced to DOP. If DOP exist, but editing not exist in the database, database refresh from DOP.

Example: Edit part in the database:

The DOP:

The two is the “same”. Some header in the DOP, etc.
1 edit in the DB approx 11 000 character +/- (usually). The DOP is the ‘same’

[andras.csore]

@platypus

My photo archive contains roughly 25’000 image files.
…
My photo archive contains roughly 25’000 image files. They occupy about 400 GB on my Mac’s internal drive. PL9’s database size on the drive is about 40 MB.

I guess, its may without editing. If all has editing (and history was turned OFF for all), as i calculate, the size of the database around 300-400MB IF history was turned ON, and all photos edited once with some preset applied → approx +300MB → database size approx 700-800MB

I still think, the ‘History’ what can takes a lot.
Some history entry - of course - simple, like this one:

Some has a size of full dop size, example (just start part)

• HistoryEntries: 55342 / 322 MB → and 4340 photos has history entry (approx half of all photos) and only average 13 history entry per photo!

So, in my database 4340 photo has average 13 history entries → 55342 row in history data table → takes up 322 MB.
Example: If for one (1) photo preset applied 1 time → one time ‘dop size’, may approx 11kb, Apply another preset for the photo → +11kb → History now 2x11kb. Copy all settings form another photo → +11kb → now 3x11kb, and so on. If preset applied 50 times for one phot → History entry just for one photo in the history data table is 0.5MB.

Its delete all history entries (windows database)- data table name can differ in Mac, bit i guess is some similar.

DELETE from HistoryEntries

Statistic from delete

PL start just fine after that. Editing start to create now History entries.

[platypus (Canon & Mac)]

History makes a difference indeed but I still am a bit puzzled about it getting so heavy in the context given by the OP (decades, hundreds of thousands) …after all, these images need to be touched folder by folder.

How did you delete your history entries? With the command provided in the Protocol panel? A menu command? SQL in a database browser?

Other than that: If History makes the DB bloat, it’s best limited in PL’s settings. While older versions of PL allowed “unlimited” number of entries, PL9 limits history to 250 entries.

[platypus (Canon & Mac)]

To test history settings, I set the limit to 10 and applied presets until history seemed full. This got me 10 entries for presets.

Limiting history to its max of 250…will probably limit the number to 250 (collapsed) lines in the protocol panel…and make the database bloat.

Setting the limit to 10 in PL9 seems to prune excess entries, whether this covers all images or only the ones shown or selected, I can’t tell (yet).

CAUTION

• Deleting history entries might not bring database size down. Looks like DB growth is irreversible…which might indeed lead to GB sized databases…

[andras.csore]

How did you delete your history entries? With the command provided in the Protocol panel? A menu command? SQL in a database browser?

I show it in ‘DB browser for SQLite’

1.) List History entries (i have only 10 for demo purposes)
2.) Delete History entries from Database
3.) Compact database

Sample code:

/* PL9, Windows database - Under Mac, data table names may differ, but some similar */ 

/* List history . I set to 10 in PL, for demo. Note: see  field TransActionContext -> here 'merge" after 11 the previous in a way or similar */
/* ItemId is the photo id. NOT the File! if you has Virtual copy, than its also has a different ItemId */ 
select * from HistoryEntries he   

/* Delete all history entries */ /* Not forgot click on 'Write changes' */     
DELETE from HistoryEntries   

/* Comnpact the database. Note: As its duplicate the database temporary, free space need to be at least database size */
/* In 'DB Browser for SQLite the Tools -> Compact database menu point do the same (also execute VACUUM) */ 
VACUUM 

Note: I think may PL does VACUUM anyway?

I add a few point later (now i had to go)