User forums now online! Please see the latest post in the Announcements forum for more information.

Author Topic: Save My database! (making it smaller)  (Read 1143 times)

0 Members and 1 Guest are viewing this topic.

Greatly Perturbed

  • Member
  • **
  • Posts: 8
    • View Profile
Save My database! (making it smaller)
« on: 2014-03-04 18:31:30 »
My database is hitting the jet 2GB limit. What are my options?

1. Is there a way to convert the database to Sqlite3 format?
2. How to get the thumbnails out of the database and into separate files?

I found the solitary python script in the undernourished Help files, but of course no instructions in how to actually use it. I mean, what's the point of having that search box on the mainscreen when the help files are so meagre? Grrr!  

My thumbnail options are currently 96 by 96, 24 bit color, no compression. Please don't suggest changing the format to jpg or using a different color depth. I've tried for several days doing that on copies of the database and it always ends up bigger!

My final goal is to have my thumbnails at 120 by 120 size. I need about 5% wiggle room as this database won't grow very quickly.

I rally hope someone out there can help me. Thanks for reading.

Daan van Rooijen

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 841
    • View Profile
Save My database! (making it smaller)
« Reply #1 on: 2014-03-04 21:19:04 »
> My database is hitting the jet 2GB limit. What are my options?

I'd start by removing any/all orphans and compressing the database.

Orphans: Thumbnail | Remove | Orphans | All Local Disks
Compress: Database | Advanced | Compact & Repair

I'm sorry for raising the taboo subject anyway, but I can't believe that uncompressed thumbnails would take up less space than JPG-compressed thumbnails. How many thumbnails do you have? (see Database | Statistics). With my current thumbnail settings (160x160 pixel, JPG Q88) I can store over 200,000 thumbnails before the database hits 2GB. At only 96x96 pixels, yours should contain some 500,000 thumbnails at that point if you used the same JPG compression (but uncompressed, those 500K thumbnails would consume 96x96x3 x 500,000 + some overhead = ~15 GB harddisk space.. hence my disbelief).
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

Greatly Perturbed

  • Member
  • **
  • Posts: 8
    • View Profile
Save My database! (making it smaller)
« Reply #2 on: 2014-03-04 22:28:43 »
Thanks for the reply I did as you suggested and reduced the file size by 1.2% so that it now stands at 2,071,532 KB. Other statistics are as follows:

Disk volumes defined: 9
Folder paths defined: 2,637
Thumbnail Records: 316,627
keywords defined: 68,130
keyword references: 1,804,477
User field defined: 3
User field references 47

Every time I've tried to use compression or remake the thumbnails after changing the preferences there has been an error and the size of the database ends up bigger than when I started.

This is another topic but how did you make thumbnails that big? Every time I've experimented with thumbnails over 120x120 pixels they start to become cropped. The bigger the thumbnail the more the more cropping.

Anything else I can do to save my database?

Daan van Rooijen

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 841
    • View Profile
Save My database! (making it smaller)
« Reply #3 on: 2014-03-05 12:00:38 »
Okay, I see you really have a whole lot of thumbnails.. I was hoping that a large part of your database would be occupied by old, erased thumbnails that hadn't been fysically purged from the database file yet.

Your questions about converting to SQLite3, or externalizing the thumbnails were certainly good, but I don't have the answers to them.

Have you tried Database | Jpeg Compress? (of course, keeping a backup of your current database). For now, that would seem like the best solution to me. It should greatly reduce the database size.

Also, are you sure that simply starting a new database is no option? I see that you have a lot of keywords and if those were painstakenly entered by hand, I can see how you don't want to lose them. But if they were auto-generated maybe you can just start a new database (in SQLite 3 format if you wish).

> This is another topic but how did you make thumbnails that big? Every time I've experimented with thumbnails over 120x120 pixels they start to become cropped.

Changing the thumbnail size doesn't affect your existing thumbnails - those will still be 96x96 until you re-make them. So, that's why they may look cropped. Just select a few and press F3 to see what your thumbnails will look like once you rebuild them. Of course, thumbnails for new files will also be built at the new, larger size that you set.
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

Greatly Perturbed

  • Member
  • **
  • Posts: 8
    • View Profile
Save My database! (making it smaller)
« Reply #4 on: 2014-03-05 15:21:56 »
> Have you tried Database | Jpeg Compress? (of course, keeping a backup of your current database). For now, that would seem like the best solution to me. It should greatly reduce the database size.

Thanks, but Jpeg Compress is useless. Compressing to 50% saves around 1 MB:

The database compact and repair process succeeded.
  Old size: 2,147,483,648 bytes
  New size: 2,146,553,856 bytes
  Compressed by: 0.0 %


> Also, are you sure that simply starting a new database is no option? I see that you have a lot of keywords and if those were painstakenly entered by hand, I can see how you don't want to lose them. But if they were auto-generated maybe you can just start a new database (in SQLite 3 format if you wish).

Most of the keywords were auto generated but I don't want to lose my user-generated fields, keywords, galleries and searches. I did try starting to create a new database in SQLite 3 format but gave up because of the slow time to create thumbnails.

Regarding Thumbnail size regardless of whether it is a new database or I rebuild thumbnails any size over 120 is cropped. I've no idea how you can make good large thumbnails.

Daan van Rooijen

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 841
    • View Profile
Save My database! (making it smaller)
« Reply #5 on: 2014-03-05 19:21:38 »
> Thanks, but Jpeg Compress is useless. Compressing to 50% saves around 1 MB:

That really doesn't make any sense.. I think either that function is broken in v9 or it can't handle a maxed out 2GB database (or, could it be that your Temp folder has no room left for a 2 GB file?).

Maybe, as an experiment, you could make a new database with 120x120 JPG Q88 thumbnails and let TP9 run overnight to thumbnail all those 300K+ files. Then see how big the database has become. I think it will be just a 1/4th or so of what you have now.
   
> Regarding Thumbnail size regardless of whether it is a new database or I rebuild thumbnails any size over 120 is cropped. I've no idea how you can make good large thumbnails.

The only times where I've seen that is when the images themselves are very small (icon files for instance) or when TP can't create a thumbnail, and instead assigns a generic thumbnail obtained from the program that is assigned to their filetype.

Oh wait! I think I know what the problem is. I think you're only referring to their display size, which is expressed as a percentage and which you can access from the bar above the thumbnail listing. But that has no bearing on how large the actual thumbnails in your database are. In Options | Preferences | Thumbnails you can define the width and height at which your (new or remade) thumbnails will actually be generated and stored in the database.
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

Greatly Perturbed

  • Member
  • **
  • Posts: 8
    • View Profile
Save My database! (making it smaller)
« Reply #6 on: 2014-03-05 20:32:47 »
Thanks for your continuing help. I guess the compression option is broken.

> Maybe, as an experiment, you could make a new database with 120x120 JPG Q88 thumbnails and let TP9 run overnight to thumbnail all those 300K+ files. Then see how big the database has become. I think it will be just a 1/4th or so of what you have now.
I'm pretty sure I tried that and it maxed out long before completing the tree. I'm beginning to think that the number of keywords automatically generated is also greatly impacting on the size.
 
> Oh wait! I think I know what the problem is. I think you're only referring to their display size, which is expressed as a percentage and which you can access from the bar above the thumbnail listing. But that has no bearing on how large the actual thumbnails in your database are. In Options | Preferences | Thumbnails you can define the width and height at which your (new or remade) thumbnails will actually be generated and stored in the database.

Yes, that's the setting I have used and reused and anything over 120 gets cropped. I've tried different display settings as well but that hasn't made a difference regarding whether the image shows up cropped or not.

Greatly Perturbed

  • Member
  • **
  • Posts: 8
    • View Profile
Save My database! (making it smaller)
« Reply #7 on: 2014-03-06 07:49:24 »
> Maybe, as an experiment, you could make a new database with 120x120 JPG Q88 thumbnails and let TP9 run overnight to thumbnail all those 300K+ files. Then see how big the database has become. I think it will be just a 1/4th or so of what you have now.
>    

I've recreated the database with 120x120 jpeg Q80. The size is smaller but still 1.95 GB. I'd like to use it but is there any way to transfer my saved searches, galleries and user fields?

Daan van Rooijen

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 841
    • View Profile
Save My database! (making it smaller)
« Reply #8 on: 2014-03-06 08:55:56 »
> I've recreated the database with 120x120 jpeg Q80. The size is smaller but still 1.95 GB.

I'd expected the compression to make a bigger difference.. too bad.

> I'd like to use it but is there any way to transfer my saved searches, galleries and user fields?

No, I'm afraid not..

But I still don't understand why you couldn't jpg-compress the thumbnails in your existing database. It would be great if you could get that to work because that would preserve all your searches, user fields, etc.

There are two more things that I'd try. The first is this:

    [*]Make a copy of your original database and open that copy in TP9
    [*]Remove the thumbnails for a number of folders that are less important than others (for instance because the images in them have no keywords and aren't part of galleries, etc)
    [*]Run 'Compact and Repair' to purge them, making the database smaller
    [*]Then try the JPG Compress conversion again.
    [/list]

    If that doesn't work:


      [*]Open that same copy of the database again. Take note of its exact size in bytes.
      [*]Use Options | Preferences | Thumbnails to change the thumbnail settings to 120x120 JPG Q80.
      [*]Re-make the thumbnails for several large folders that already have thumbnails.
      [*]See if that has lowered the filesize of the database (even if just a little).
      [*]If not, run Compact & Repair and see if that helps decrease the size.
      [*]If it has, keep on remaking your current thumbnails until all have been remade. (you may have to run Compact & Repair intermittently but I don't think so)
      [/list]

      With some luck that should change all your thumbnails to 120x120 whilst preserving searches, user fields, etc and still staying just below the 2 GB limit.
      I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

      Greatly Perturbed

      • Member
      • **
      • Posts: 8
        • View Profile
      Save My database! (making it smaller)
      « Reply #9 on: 2014-03-06 10:20:36 »
      > But I still don't understand why you couldn't jpg-compress the thumbnails in your existing database. It would be great if you could get that to work because that would preserve all your searches, user fields, etc.

      I don't think it works. I removed around 6% of the thumbnails, ran compact and repair, did the compress and this was the result:

      The database compact and repair process succeeded.
        Old size: 1,973,600,256 bytes
        New size: 1,973,600,256 bytes
        Compressed by: 0.0 %


      Changing the thumbnail size as per your second suggestion just quickly expands the size to where it won't remake any new thumbnails. It seems pretty hopeless.

      Greatly Perturbed

      • Member
      • **
      • Posts: 8
        • View Profile
      Save My database! (made it smaller!)
      « Reply #10 on: 2014-03-08 20:38:44 »
      A happy ending! I tried what you suggested in your last post again. But this time I removed about 25% of the thumbnails before trying compression. I then remade the thumbnails I had removed. I'm happy to report that I now have a database with 120 by 120 pixels, 80 quality and size around 1.53 GB. Thanks Daan for all your help.

      Daan van Rooijen

      • Global Moderator
      • Sr. Member
      • *****
      • Posts: 841
        • View Profile
      Save My database! (made it smaller!)
      « Reply #11 on: 2014-03-09 11:24:30 »
      Very glad to hear that, it really had me puzzled why nothing seemed to work! I guess the compression feature just needed more room in the database to work with.
      I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

      aslaksen

      • Full Member
      • ***
      • Posts: 88
        • View Profile
      Save My database! (made it smaller!)
      « Reply #12 on: 2016-01-31 04:56:24 »
      I had a similar problem. I had it a 75%, then changed it to 50% and gained only 5MB. I then deleted about 50 000 thumbs, and tried compressing again to 50%, but then nothing happened. I had naively expected that the first time it had given up before it was finished.

      I'll try to delete some files, and maybe remove and rescan more files.

      Has anybody tried the python script? Where can I get it with instructions?

      Thanks!

      Daan van Rooijen

      • Global Moderator
      • Sr. Member
      • *****
      • Posts: 841
        • View Profile
      (off-topic comment)
      « Reply #13 on: 2016-01-31 07:24:16 »
      I can't help with the script that you mention, but have you read this thread about a way of making SQLite3 databases work much faster than before? Maybe that would make them a viable option for you now:

      http://forums.cerious.com/forum/index.php?id=4942
      I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

      aslaksen

      • Full Member
      • ***
      • Posts: 88
        • View Profile
      (off-topic comment)
      « Reply #14 on: 2016-01-31 07:36:30 »
      Dear Daan,

      Thanks for your suggestion.In the end, my database got corrupted, and right now I'm just rebuilding the database again. Hopefully that will solve my problems. If not I'll definitely try your suggestion.

      Thanks!