Compacting thumbnail database takes a long time

[G8DHE]

OK, well been trying various things over the holiday including the Sqlite command line tools to see if it would identify anything.
Whenever I ran "compact" on either database or both I get very variable results, but all taking an exceedingly long time overnight etc usually ending with a lockup with no processor time being used and no other activity.
However I resorted to using the Sqlite3 command line and this works seemingly every time here is the results for the Thumbs database this morning;

Code: Select all

thumbs-vac.txt
--------------
.open sphericalvisions.thumbs.db
.databases
.timer on
.progress 10000
pragma journal_mode;
pragma journal_mode = delete;
.stats
.selftest
.progress 100000
vacuum;
.stats
pragma journal_mode = wal;
.databases
-----------------------------------------------
C:\Users\Geoff\AppData\Local\IDimager Systems, Inc\Catalog>sqlite3
SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read thumbs-vac.txt
main: C:\Users\Geoff\AppData\Local\IDimager Systems, Inc\Catalog\sphericalvisions.thumbs.db
wal
Run Time: real 0.000 user 0.000000 sys 0.000000
delete
Run Time: real 0.003 user 0.000000 sys 0.000000
Memory Used:                         155232 (max 159016) bytes
Number of Outstanding Allocations:   228 (max 263)
Number of Pcache Overflow Bytes:     1032 (max 5136) bytes
Largest Allocation:                  120000 bytes
Largest Pcache Allocation:           4104 bytes
Lookaside Slots Used:                24 (max 69)
Successful lookaside attempts:       122
Lookaside failures due to size:      0
Lookaside failures due to OOM:       0
Pager Heap Usage:                    2956 bytes
Page cache hits:                     4
Page cache misses:                   2
Page cache writes:                   1
Page cache spills:                   0
Schema Heap Usage:                   1544 bytes
Statement Heap/Lookaside Usage:      0 bytes
Missing SELFTEST table - default checks only
Progress 1
Progress 2
.
.
Progress 1512
Progress 1513
0 errors out of 1 tests
Progress 1
Progress 2
.
.
Progress 106
Progress 107
Run Time: real 2714.313 user 352.734375 sys 1699.984375
Memory Used:                         2406744 (max 996656400) bytes
Number of Outstanding Allocations:   2018 (max 1941397)
Number of Pcache Overflow Bytes:     2160016 (max 4321368) bytes
Largest Allocation:                  9821242 bytes
Largest Pcache Allocation:           4104 bytes
Lookaside Slots Used:                26 (max 100)
Successful lookaside attempts:       726
Lookaside failures due to size:      17
Lookaside failures due to OOM:       15
Pager Heap Usage:                    2167108 bytes
Page cache hits:                     5433150
Page cache misses:                   237656328
Page cache writes:                   78569168
Page cache spills:                   78567512
Schema Heap Usage:                   0 bytes
Statement Heap/Lookaside Usage:      0 bytes
wal
Run Time: real 0.027 user 0.000000 sys 0.015625
sqlite> .databases
main: C:\Users\Geoff\AppData\Local\IDimager Systems, Inc\Catalog\sphericalvisions.thumbs.db
sqlite>

This completed in less than an hour and similarly for the cat.db. Still using version 2607 at present.
Not quite sure where this leaves me but at least I can compact/vacuum the database in a sensible time and without errors .....

[KeesdH]

Build 2619. Compacting both DB's. All well, no issues!

[Mikki]

Just tested version 5.3.0.2619.
Now compacting the databases takes 30 min. Excellent!

[G8DHE]

Looking more hopeful here as well just updated to 2619 and the cat.db has compacted just waiting now to see how the thumbs.db does ..... yea yes that's done both .db's in a sensible time.
Tks for finding the problem Hert!