Gossamer Forum
Home : Products : DBMan : Discussions :

defragmenting DB

Quote Reply
defragmenting DB
after including an autodelete function the .db file gets fairly fragmented in the 'ID' field.
Is there a way to add an automod-run which would reorganize the 'ID' field e.g. when the counter hits 10000 and update the .count file to the new value.

gaz. wally

Quote Reply
Re: defragmenting DB In reply to
You could probably add it to the autodelete mod and have it renumber your records every time you do a delete. It would undoubtedly be easier to do it that way than to check the .count file all the time and then do it separately.


JPD
http://www.jpdeni.com/dbman/
Quote Reply
Re: defragmenting DB In reply to
It makes sense to add the renumber run to the autodelete mod. But then again the .count file still also needs to be updated.
Do you know a renumber routine?

__wally_

Quote Reply
Re: defragmenting DB In reply to
I'd like to give a warning. Renumbering the DB will invalidate index pages / queries downloaded by another user, and may result in them fetching the wrong record. (e.g. If another user is trying to view the old #5, he'll get the new #5.) So, you probably want to renumber the DB as rarely as possible.

Considering that fragmentation of the index field does not make any difference from an efficiency standpoint, and since the number can be anything up to 2 billion in DBMan, is there really a reason to renumber the database?




Quote Reply
Re: defragmenting DB In reply to
Elb--
I don't think there's any reason to renumber the database, but this is something that has come up so often, I've given up trying to talk people out of it.

wallaby --

You'll need to change the part of the script that writes to the .db file. The changes are in red below:

Code:

$i = 1;
open (DB, ">$db_script_path/$db_file_name") or ("Can't open: $db_script_path/$db_file_name. Reason: $!");
if ($db_use_flock) { flock (DB, 2); }
foreach (@lines) {
next if /^#/;
next if /^\s*$/;
chomp;
@values = split (/\Q$db_delim\E/o, $_);
print "Comparing: '$today' vs '$values[$removeby_field]' ... \n";
if ($today > (&date_to_unix($values[$dateadded_field]) + (86400 * $values[$removeby_field]))) {
print "Record(s) Deleted\n";
next;
}
$values[$db_key_pos] = $i;
++$i;
$line = join "$db_delim",@values;
print DB $line, "\n";

}
close DB;
open (ID, ">$db_id_file_name") or ("unable to open id file: $db_id_file_name.\nReason: $!");
if ($db_use_flock) {
flock(ID, 2) or ("unable to get exclusive lock on $db_id_file_name.\nReason: $!");
}
print ID $i; # update counter.
close ID; # automatically removes file lock

Be sure you backup your database before you do this. It hasn't been tested, except for syntax errors.

JPD
http://www.jpdeni.com/dbman/
Quote Reply
Re: defragmenting DB In reply to
hey great!

so far it is working(?!).

The code is only eliminating the trailing empty delimeters. That way the whole DB gets mixed up. Is there a possible workaround for that?

h.a.n.d. _wally

Quote Reply
Re: defragmenting DB In reply to
The code I gave you doesn't renumber the records? If it doesn't, it's not working. Smile

JPD
http://www.jpdeni.com/dbman/
Quote Reply
Re: defragmenting DB In reply to
JPD,
the code is renumbering the db file!

The problem is that in each row the last empty delimeters are gone after the defrag run.

;-) sorry for mispronunciation _wally

Quote Reply
Re: defragmenting DB In reply to
Oh. I see. I didn't think about empty fields. Blush

You're going to need to be sure that you have the following subroutines from the db.cgi script:

sub split_decode
sub join_encode
sub array_to_hash

Change

@values = split (/\Q$db_delim\E/o, $_);

back to

@values = &split_decode($line);

Change

$line = join "$db_delim",@values;
print DB $line, "\n";


to

%rec = &array_to_hash(0,@values);
$line = &join_encode(%rec);
print DB $line;


See if that does any better for you.


JPD
http://www.jpdeni.com/dbman/
Quote Reply
Re: defragmenting DB In reply to
oops,

this alternation sets the .count file back to "0" and leaves the .db file empty.

_wally

Quote Reply
Re: defragmenting DB In reply to
Then I don't think I know how to answer your question. This is all I could think of.

JPD
http://www.jpdeni.com/dbman/
Quote Reply
Re: defragmenting DB In reply to
thank you very much for your effort. I recon I leave the idea to defrag the .db file for a while until the index climbs up to the roof.

;-) _wal