Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 29 total
Thread Blob file re-use
Fri, Jan 29 2010 11:35 AMPermanent Link

(Matthew Jones)
I have an app which runs 24/7, and which stores large data files in blobs. The data
lasts for perhaps 15 minutes at most, and then is deleted by a maintenance thread.
What appears to be happening is that some blobs are just not being re-used for the
next files, and thus the file is ballooning. I've had a report of a file of 120Gb
today. Typical live contents are perhaps 40 or so files of 10Mb max.

What might be delaying the re-use of the blob space, and is there any way I can
improve on this?

The app is heavily multi-threaded, and scans the table for new activity constantly.
Code for the deletion is below. This is done in a "find first to delete, delete,
then find again" manner because deleting causes the other threads to pause, and if
there is a lot to delete then they pause so long that they lose their internet
connections sometimes.

/Matthew Jones/

procedure TMaintenanceThread.PurgeQueueDatabase;
var
   nDeletedCount : Integer;
   nStoreReference : Integer;
   szPurgeDate : string;
   nMinsToKeep : Integer;
   szSQL : string;
   tableDeleter : TDBISAMTable;
begin
   nMinsToKeep := g_xLatestConfig.DBKeepMins;   //
m_xOptionsDB.ReadInteger('Maintenance', 'HoursToKeep', 36);
   szPurgeDate := Engine.QuotedSQLStr(Engine.DateToAnsiStr(IncMinute(Date,
-nMinsToKeep)));
   m_xUpdateQuery.OnQueryProgress := CheckQueryProgress;

   tableDeleter := TDBISAMTable.Create(nil);
   try
      tableDeleter.SessionName := g_xThreadSession.SessionName;
      tableDeleter.DatabaseName := g_xStartupConfig.DatabasePath;

      szSQL := '';
      szSQL := szSQL + ' SELECT fsReferenceInt ';
      szSQL := szSQL + ' FROM FileStore ';
      szSQL := szSQL + ' WHERE fsStoreDate < ' + szPurgeDate + ' ';

      try
         Report('Purging file store database of items >' + IntToStr(nMinsToKeep) + ' mins
old');
         m_xUpdateQuery.SQL.Text := szSQL;
         m_xUpdateQuery.Open;
         m_xUpdateQuery.First;
         nDeletedCount := 0;
         tableDeleter.TableName := 'FileStore';
         tableDeleter.Open;
         while not m_xUpdateQuery.Eof do
         begin
            nStoreReference := m_xUpdateQuery.FieldByName('fsReferenceInt').AsInteger;
            if tableDeleter.Locate('fsReferenceInt', nStoreReference, []) then
            begin
               SetStatus('Deleting filestore ' + IntToStr(nStoreReference));
               tableDeleter.Delete;
               Inc(nDeletedCount);
            end
            else
            begin
               Report('Record to delete not found ' + IntToStr(nStoreReference));
            end;
            m_xUpdateQuery.Next;
            if Terminated then
               break;
         end;      // while
         Report('Purged ' + IntToStr(nDeletedCount) + ' items from file store database');
      finally
         m_xUpdateQuery.Close;
         tableDeleter.Close;
      end;


      szSQL := '';
      szSQL := szSQL + ' DELETE ';
      szSQL := szSQL + ' FROM ChangeQueue ';
      szSQL := szSQL + ' WHERE cqTimeStamp < ' + szPurgeDate + ' ';

   //   Report('PurgeQueueDatabase Queue SQL=' + szSQL);
      try
         SetStatus('Purging queue database');
         m_xUpdateQuery.SQL.Text := szSQL;
         m_xUpdateQuery.ExecSQL;
         Report('Purged ' + IntToStr(m_xUpdateQuery.RowsAffected) + ' items from queue
database');
      finally
         m_xUpdateQuery.Close;
      end;

      szSQL := '';
      szSQL := szSQL + ' DELETE ';
      szSQL := szSQL + ' FROM ClientState ';
      szSQL := szSQL + ' WHERE csLastActivity < ' + szPurgeDate + ' ';

   //   Report('PurgeQueueDatabase ClientState SQL=' + szSQL);
      try
         SetStatus('Purging client database');
         m_xUpdateQuery.SQL.Text := szSQL;
         m_xUpdateQuery.ExecSQL;
         Report('Purged ' + IntToStr(m_xUpdateQuery.RowsAffected) + ' items from client
state database');
      finally
         m_xUpdateQuery.Close;
      end;
   finally
      FreeAndNil(tableDeleter);
   end;
end;
Fri, Jan 29 2010 11:49 AMPermanent Link

(Matthew Jones)
Technical info: DBISAM 4.29 build 1.
Blob block size is 10240 bytes.

Also, at the bottom of the page
http://www.elevatesoft.com/customer?action=download&downaction=downnotes&producttype
=dbisamvclstdsrc&version=4 there is a link to incident 1694 which fails to produce
any output.

/Matthew Jones/
Fri, Jan 29 2010 12:03 PMPermanent Link

"Robert"

"Matthew Jones" <matthew@matthewdelme-jones.delme.com> wrote in message
news:memo.20100129163518.5896L@nothanks.nothanks.co.uk...
>I have an app which runs 24/7, and which stores large data files in blobs.
>The data
> lasts for perhaps 15 minutes at most, and then is deleted by a maintenance
> thread.
> What appears to be happening is that some blobs are just not being re-used
> for the
> next files, and thus the file is ballooning. I've had a report of a file
> of 120Gb
> today. Typical live contents are perhaps 40 or so files of 10Mb max.
>
> What might be delaying the re-use of the blob space, and is there any way
> I can
> improve on this?

Well the first thing to do IMO is to determine if the problem is records not
being deleted or space not being re-used. What's the record count?

Robert


Fri, Jan 29 2010 1:19 PMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Matthew


I don't know if it will help you. It will depend on the DBISAM version you're running. In TMaN my mail & news app which also has lots of blobs (also with FTI) I found that setting the memo fields to null made things run quite a bit faster. It was fixed later but I can't remember which version.

Roy Lambert
Fri, Jan 29 2010 5:37 PMPermanent Link

"Walter Matte"
One thing to realize, once the physical file changes (grows) it never gets
smaller.

Reuse, does not mean optimizing the data storage space.

The reuse, of blob space is hard, due to fragmentation.

When you do a backup, you should optimize this table as part of the
procedure.  Optimize will shrink the physical space.

http://www.elevatesoft.com/manual?action=mantopic&id=dbisam4&product=d&version=7&category=1&topic=22


Walter






"Matthew Jones" <matthew@matthewdelme-jones.delme.com> wrote in message
news:memo.20100129163518.5896L@nothanks.nothanks.co.uk...
>I have an app which runs 24/7, and which stores large data files in blobs.
>The data
> lasts for perhaps 15 minutes at most, and then is deleted by a maintenance
> thread.
> What appears to be happening is that some blobs are just not being re-used
> for the
> next files, and thus the file is ballooning. I've had a report of a file
> of 120Gb
> today. Typical live contents are perhaps 40 or so files of 10Mb max.
>
> What might be delaying the re-use of the blob space, and is there any way
> I can
> improve on this?
>
> The app is heavily multi-threaded, and scans the table for new activity
> constantly.
> Code for the deletion is below. This is done in a "find first to delete,
> delete,
> then find again" manner because deleting causes the other threads to
> pause, and if
> there is a lot to delete then they pause so long that they lose their
> internet
> connections sometimes.
>
> /Matthew Jones/
>
> procedure TMaintenanceThread.PurgeQueueDatabase;
> var
> nDeletedCount : Integer;
> nStoreReference : Integer;
> szPurgeDate : string;
> nMinsToKeep : Integer;
> szSQL : string;
> tableDeleter : TDBISAMTable;
> begin
> nMinsToKeep := g_xLatestConfig.DBKeepMins; //
> m_xOptionsDB.ReadInteger('Maintenance', 'HoursToKeep', 36);
> szPurgeDate := Engine.QuotedSQLStr(Engine.DateToAnsiStr(IncMinute(Date,
> -nMinsToKeep)));
> m_xUpdateQuery.OnQueryProgress := CheckQueryProgress;
>
> tableDeleter := TDBISAMTable.Create(nil);
> try
> tableDeleter.SessionName := g_xThreadSession.SessionName;
> tableDeleter.DatabaseName := g_xStartupConfig.DatabasePath;
>
> szSQL := '';
> szSQL := szSQL + ' SELECT fsReferenceInt ';
> szSQL := szSQL + ' FROM FileStore ';
> szSQL := szSQL + ' WHERE fsStoreDate < ' + szPurgeDate + ' ';
>
> try
> Report('Purging file store database of items >' + IntToStr(nMinsToKeep) +
> ' mins
> old');
> m_xUpdateQuery.SQL.Text := szSQL;
> m_xUpdateQuery.Open;
> m_xUpdateQuery.First;
> nDeletedCount := 0;
> tableDeleter.TableName := 'FileStore';
> tableDeleter.Open;
> while not m_xUpdateQuery.Eof do
> begin
> nStoreReference := m_xUpdateQuery.FieldByName('fsReferenceInt').AsInteger;
> if tableDeleter.Locate('fsReferenceInt', nStoreReference, []) then
> begin
> SetStatus('Deleting filestore ' + IntToStr(nStoreReference));
> tableDeleter.Delete;
> Inc(nDeletedCount);
> end
> else
> begin
> Report('Record to delete not found ' + IntToStr(nStoreReference));
> end;
> m_xUpdateQuery.Next;
> if Terminated then
> break;
> end; // while
> Report('Purged ' + IntToStr(nDeletedCount) + ' items from file store
> database');
> finally
> m_xUpdateQuery.Close;
> tableDeleter.Close;
> end;
>
>
> szSQL := '';
> szSQL := szSQL + ' DELETE ';
> szSQL := szSQL + ' FROM ChangeQueue ';
> szSQL := szSQL + ' WHERE cqTimeStamp < ' + szPurgeDate + ' ';
>
> // Report('PurgeQueueDatabase Queue SQL=' + szSQL);
> try
> SetStatus('Purging queue database');
> m_xUpdateQuery.SQL.Text := szSQL;
> m_xUpdateQuery.ExecSQL;
> Report('Purged ' + IntToStr(m_xUpdateQuery.RowsAffected) + ' items from
> queue
> database');
> finally
> m_xUpdateQuery.Close;
> end;
>
> szSQL := '';
> szSQL := szSQL + ' DELETE ';
> szSQL := szSQL + ' FROM ClientState ';
> szSQL := szSQL + ' WHERE csLastActivity < ' + szPurgeDate + ' ';
>
> // Report('PurgeQueueDatabase ClientState SQL=' + szSQL);
> try
> SetStatus('Purging client database');
> m_xUpdateQuery.SQL.Text := szSQL;
> m_xUpdateQuery.ExecSQL;
> Report('Purged ' + IntToStr(m_xUpdateQuery.RowsAffected) + ' items from
> client
> state database');
> finally
> m_xUpdateQuery.Close;
> end;
> finally
> FreeAndNil(tableDeleter);
> end;
> end;
>

Sat, Jan 30 2010 10:15 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Matthew,

<< Also, at the bottom of the page
http://www.elevatesoft.com/customer?action=download&downaction=downnotes&producttype =dbisamvclstdsrc&version=4 there is a link to incident 1694 which fails toproduce any output. >>Got it, thanks.--Tim YoungElevate Softwarewww.elevatesoft.com
Sat, Jan 30 2010 10:21 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Matthew,

<< I have an app which runs 24/7, and which stores large data files in
blobs. The data lasts for perhaps 15 minutes at most, and then is deleted by
a maintenance thread. What appears to be happening is that some blobs are
just not being re-used for the next files, and thus the file is ballooning.
I've had a report of a file of 120Gb today. Typical live contents are
perhaps 40 or so files of 10Mb max. >>

What is the largest file size that is being added ?  Is there a large
disparity in the smallest file size to the largest file size, of the files
that are being added ?  The max you should be seeing is the largest file
size * the highest number of rows in the table, provided that all rows
always have BLOBs allocated for them.

<< What might be delaying the re-use of the blob space,  >>

Nothing in DBISAM, specifically.  Re-use of space is done immediately for
each row.  As of 4.28 and higher, the allocated BLOB blocks for a given row
stay with that row, even through deletion.  So, when the row is re-used, the
BLOB blocks are re-used also.  This avoids all sorts of concurrency and
fragmentation issues with different sessions trying to re-use deleted BLOB
blocks from the same BLOB.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sat, Jan 30 2010 10:53 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim

>Nothing in DBISAM, specifically. Re-use of space is done immediately for
>each row. As of 4.28 and higher, the allocated BLOB blocks for a given row
>stay with that row, even through deletion. So, when the row is re-used, the
>BLOB blocks are re-used also. This avoids all sorts of concurrency and
>fragmentation issues with different sessions trying to re-use deleted BLOB
>blocks from the same BLOB.

Couldn't that cause what Matthew is seeing? What happens to BLOB blocks from rows that have been deleted when the row is overwritten but the new blob is smaller than the previous one?

Roy Lambert
Mon, Feb 1 2010 4:40 AMPermanent Link

(Matthew Jones)
Good question, and I've asked the support people to grab be a failed one, but they
are keen to get it going again, so they delete the databases (which is safe!). I've
asked harder...

/Matthew Jones/
Mon, Feb 1 2010 4:40 AMPermanent Link

(Matthew Jones)
I guess I'm not expecting it to get smaller, but not to grow beyond a sensible peak.
Unfortunately the 24/7 nature means that optimising is hard to schedule.

/Matthew Jones/
Page 1 of 3Next Page »
Jump to Page:  1 2 3
Image