Author Topic: Database compacting, is it working?  (Read 2224 times)

JohnZeman

  • Global Moderator
  • *****
  • Posts: 1451
  • I'm too damn old to act my age.
Database compacting, is it working?
« on: November 09, 2013, 01:28:19 AM »
As I recall a few months back Mario found a bug that prevented a database from being properly compacted and I think he fixed it.  Lately my database has been running around 7.3GB in size and I generally compact it daily.  Last night due to some self-inflicted problems I decided to start all over with a brand new database.  I loaded it with the exact same images, attributes, categories, everything, and the result is the new database is 1.3gb smaller in file size than the original.  Furthermore lately when I do a database optimize/compact operation I'm not seeing any database file size reduction at all.

I'm not calling this a bug because I'm not sure that it is, I'm interested in finding out if others are seeing the same thing I am.

Richard

  • Guest
Re: Database compacting, is it working?
« Reply #1 on: November 09, 2013, 03:03:35 AM »
Hi John,

Keep in mind how space is allocated in blocks for a database. That may account for some of what you are seeing. If the amount of space reserved for a database becomes excessive, when is the extra space removed?  I also question the true meaning of "compact". Writing files in a more logical order will not make the files smaller. What gets compacted? Wasted space? When I de-fragment a drive, efficiency is improved but only because the files are in a more logical order. Nothing gets much smaller.

jch2103

  • Oldtimer
  • ****
  • Posts: 2243
Re: Database compacting, is it working?
« Reply #2 on: November 09, 2013, 05:10:37 AM »
All true,  but deleting a large number of records from a database can leave empty space in the database file.  Compacting in this situation should reduce the size of the database file.
John

Mario

  • IMatch Developer
  • Administrator
  • *****
  • Posts: 29760
Re: Database compacting, is it working?
« Reply #3 on: November 09, 2013, 11:54:47 AM »
The compact which is part of the Diagnosis or the stand-alone Compact under Database > Tools > Compact...?

Richard

  • Guest
Re: Database compacting, is it working?
« Reply #4 on: November 09, 2013, 01:50:27 PM »
Quote
Compacting in this situation should reduce the size of the database file.

Let's say that I have a 10 GB allocation but only 9 GB is used. Now I remove files from the database. At what point should the amount of space allocated be reduced?

JohnZeman

  • Global Moderator
  • *****
  • Posts: 1451
  • I'm too damn old to act my age.
Re: Database compacting, is it working?
« Reply #5 on: November 09, 2013, 03:51:00 PM »
The compact which is part of the Diagnosis or the stand-alone Compact under Database > Tools > Compact...?

Mario I always the compact which is part of Diagnostics.

Mario

  • IMatch Developer
  • Administrator
  • *****
  • Posts: 29760
Re: Database compacting, is it working?
« Reply #6 on: November 09, 2013, 06:13:51 PM »
Try the Database > Tools > Compact.

DigPeter

  • Super Hero
  • ****
  • Posts: 1238
Re: Database compacting, is it working?
« Reply #7 on: November 09, 2013, 06:51:27 PM »
I see that the word is "Optimising" only for diagnostics, but "Compact & optimise" in Database tools.  When I do the latter I get a significant reduction in Db size, but not with diagnostics.

sinus

  • Global Moderator
  • *****
  • Posts: 4464
  • IMatch-User since 2001 (IMatch 3.6)
Re: Database compacting, is it working?
« Reply #8 on: November 11, 2013, 08:20:27 AM »
... Lately my database has been running around 7.3GB in size ...

OT: John, beside your thread, I really wonder, if it would be wiser to cut such a huge DB into two pieces. My DB with 3.6 is about the same size, but for example to click on a category, what has a lot of images, it takes time. When IM5 is out, I will have to decide this. But of course... to have all images in one DB has advantages. Sorry, I do not want to "hack" your thread, wanted only to say this, think, it is not worth to create a new thread.
Best wishes from Switzerland! :-)
Markus

Mario

  • IMatch Developer
  • Administrator
  • *****
  • Posts: 29760
Re: Database compacting, is it working?
« Reply #9 on: November 11, 2013, 11:21:22 AM »
The Optimize and Analysis step in the diagnosis no longer compacts the database.
This was superfluous and did not help in analyzing and checking the database.

I have documented this now  more explicitly. If you want to shrink your database, use the Database > Tools > Compact command.


@sinus:

Please define "it takes time" and let us know if the category you talk about has 5,000  or 50,000 files.
And a log file from a session where you did that will help.

Usually loading a file window is very fast, but I think I remember you are using custom file window layouts with variables and metadata, and this can take a long time to load. The standard data is usually cached in memory. But if you use variables and metadata in your file window layout, IMatch has to load the data from the database file on disk, and this can cause a 100% to 1000% slow-down.

Or when the category you want to look at is "dirty" and needs to be re-calculated before the file can load the files.

Many factors play a role here. I can load a 20,000 files file window in about 4 seconds for a 120,000 files database... how long does it take on your system?

JohnZeman

  • Global Moderator
  • *****
  • Posts: 1451
  • I'm too damn old to act my age.
Re: Database compacting, is it working?
« Reply #10 on: November 12, 2013, 01:07:48 AM »
OT: John, beside your thread, I really wonder, if it would be wiser to cut such a huge DB into two pieces. My DB with 3.6 is about the same size, but for example to click on a category, what has a lot of images, it takes time. When IM5 is out, I will have to decide this. But of course... to have all images in one DB has advantages. Sorry, I do not want to "hack" your thread, wanted only to say this, think, it is not worth to create a new thread.

Markus I really haven't had many speed problems with IM5, it seems quite a bit quicker than version 3 for my jpg photos.

JohnZeman

  • Global Moderator
  • *****
  • Posts: 1451
  • I'm too damn old to act my age.
Re: Database compacting, is it working?
« Reply #11 on: November 12, 2013, 01:29:05 AM »
The Optimize and Analysis step in the diagnosis no longer compacts the database.
This was superfluous and did not help in analyzing and checking the database.

I have documented this now  more explicitly. If you want to shrink your database, use the Database > Tools > Compact command.

Thanks Mario.  The Database Tools > Compact and Optimize seems to work as it should.  It was the compact and optimize operation that had been part of the diagnosis that didn't seem to do anything.