Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 29 total |
Blob file re-use |
Fri, Jan 29 2010 11:35 AM | Permanent 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 AM | Permanent 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 PM | Permanent 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 PM | Permanent Link |
Roy Lambert NLH Associates 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 PM | Permanent 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent 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 AM | Permanent 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 3 | Next Page » | |
Jump to Page: 1 2 3 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |