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

[Stephen Leibowitz]

*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