I've been having an issue with DBISAM with a new application I've written.

I have the following pseudo layout:

DBISAMTblOASHistoryHeader (one field called 'RecordTimestamp')

DBISAMTblOASHistory (several fields, including one called 'RecordTimestamp')

History is the detail table from HistoryHeader based on 'RecordTimestamp'.

For each entry in HistoryHeader, there may be a few thousand in History.

When closing my database, the application can sometimes hang for close to 30-45 seconds.

I've spoken with Tim and he suggested adding a DBISAMEngine and increasing all of the Max... properties to 2Mb.  Well, I increased by doubling all the way to 16Mb, but there is absolutely no difference at all.

When I tried this using another database engine, the problem resolves completely.  Immediate close of the database and the application shuts down (and the database for that engine is located on the same network drive).

Any ideas on what I can do to fix this?  This is a major problem as the data for these databases is going to increase by a few thousand records per day...

For your reference, here is what I set the DBISAMEngine properties to:

object DBISAMEngineScheduler: TDBISAMEngine
 Active = False
 EngineVersion = '4.32 Build 1'
 EngineType = etClient
 EngineSignature = 'DBISAM_SIG'
 Functions = <>
 LockFileName = 'dbisam.lck'
 MaxTableDataBufferSize = 67108864
 MaxTableDataBufferCount = 67108864
 MaxTableIndexBufferSize = 67108864
 MaxTableIndexBufferCount = 67108864
 MaxTableBlobBufferSize = 67108864
 MaxTableBlobBufferCount = 67108864
 TableDataExtension = '.dat'
 TableIndexExtension = '.idx'
 TableBlobExtension = '.blb'
 TableDataBackupExtension = '.dbk'
 TableIndexBackupExtension = '.ibk'
 TableBlobBackupExtension = '.bbk'
 TableDataUpgradeExtension = '.dup'
 TableIndexUpgradeExtension = '.iup'
 TableBlobUpgradeExtension = '.bup'
 TableDataTempExtension = '.dat'
 TableIndexTempExtension = '.idx'
 TableBlobTempExtension = '.blb'
 ServerName = 'DBSRVR'
 ServerDescription = 'DBISAM Database Server'
 ServerEncryptionPassword = 'elevatesoft'
 ServerConfigFileName = 'dbsrvr.scf'
 ServerConfigPassword = 'elevatesoft'
 Left = 180
 Top = 256
End

Please note that I set the engine to Active:=True before opening any tables and set it to Active:=False AFTER closing all tables.

I should also note that this occurs when the client machines are running either Windows XP or Windows 7.

My dev machine is the Win7 machine and most of my clients are WinXP, but it doesn't make any difference in speed which OS is used...

I should also note, I mistakenly said 16MB - I had tried all the way up to 64MB.

Robert


I'm trying to think what can cause this sort of effect and its either interaction with controls or flushing buffers. Without something to work with I have no other idea.

Can you post a sample project in the binaries or, if its very big, give an ftp location?

Roy Lambert [Team Elevate]

Well, that's the thing...  This seems to be a major issue on our Linux fileserver, but not on my Buffalo TeraStation (though, I'm the only person connecting to the TeraStation while we have 50+ users connecting to the Linux box).  So I'm not sure the problem would necessarily repeat itself to you or not.

What really blows my mind is by simply changing out DBISAM for another database (free trial of AbsoluteDB), there are no issues whatsoever.  I do NO other code changes except replacing the DBISAM Database and Table components.

If you still want an example project, though, I can give you access to something I can put together, but I need it to remain private as the databases contain proprietary company data.

While i can't add much to engine configuration i have usually found 2
tools that help me in such situations :

1. process Monitor from Sysinternals/Microsoft :
http://technet.microsoft.com/en-us/sysinternals/bb896645

You can set it to log all the activity for your executable and see if
that might indicate where the 30+seconds is spent.

note that it won't tell you what's wrong but you should be able to see
what is going on in terms of file open/close


2. if the process monitor does not work then sometimes network trace
helps to see the SMB messages - using Wireshark for example
(http://www.wireshark.org/) again would let you see what is oing on
communication wise to the share.


As a more general comment any chance you can switch to CS model instead
of file-share?  50+ file share users is really pushing it IMHO.

Raul



On 4/10/2012 11:07 AM, Robert D. Smith wrote:
> I've been having an issue with DBISAM with a new application I've written.
>
> I have the following pseudo layout:
>
> DBISAMTblOASHistoryHeader (one field called 'RecordTimestamp')
>
> DBISAMTblOASHistory (several fields, including one called 'RecordTimestamp')
>
> History is the detail table from HistoryHeader based on 'RecordTimestamp'.
>
> For each entry in HistoryHeader, there may be a few thousand in History.
>
> When closing my database, the application can sometimes hang for close to 30-45 seconds.
>
> I've spoken with Tim and he suggested adding a DBISAMEngine and increasing all of the Max... properties to 2Mb.  Well, I increased by doubling all the way to 16Mb, but there is absolutely no difference at all.
>
> When I tried this using another database engine, the problem resolves completely.  Immediate close of the database and the application shuts down (and the database for that engine is located on the same network drive).
>
> Any ideas on what I can do to fix this?  This is a major problem as the data for these databases is going to increase by a few thousand records per day...
>
> For your reference, here is what I set the DBISAMEngine properties to:
>
> object DBISAMEngineScheduler: TDBISAMEngine
>    Active = False
>    EngineVersion = '4.32 Build 1'
>    EngineType = etClient
>    EngineSignature = 'DBISAM_SIG'
>    Functions =<>
>    LockFileName = 'dbisam.lck'
>    MaxTableDataBufferSize = 67108864
>    MaxTableDataBufferCount = 67108864
>    MaxTableIndexBufferSize = 67108864
>    MaxTableIndexBufferCount = 67108864
>    MaxTableBlobBufferSize = 67108864
>    MaxTableBlobBufferCount = 67108864
>    TableDataExtension = '.dat'
>    TableIndexExtension = '.idx'
>    TableBlobExtension = '.blb'
>    TableDataBackupExtension = '.dbk'
>    TableIndexBackupExtension = '.ibk'
>    TableBlobBackupExtension = '.bbk'
>    TableDataUpgradeExtension = '.dup'
>    TableIndexUpgradeExtension = '.iup'
>    TableBlobUpgradeExtension = '.bup'
>    TableDataTempExtension = '.dat'
>    TableIndexTempExtension = '.idx'
>    TableBlobTempExtension = '.blb'
>    ServerName = 'DBSRVR'
>    ServerDescription = 'DBISAM Database Server'
>    ServerEncryptionPassword = 'elevatesoft'
>    ServerConfigFileName = 'dbsrvr.scf'
>    ServerConfigPassword = 'elevatesoft'
>    Left = 180
>    Top = 256
> End
>
> Please note that I set the engine to Active:=True before opening any tables and set it to Active:=False AFTER closing all tables.
>

Please see my responses below each of your comments...

>> Raul wrote:


>> While i can't add much to engine configuration i have usually found 2
>> tools that help me in such situations :

>> 1. process Monitor from Sysinternals/Microsoft :
>> http://technet.microsoft.com/en-us/sysinternals/bb896645

>> You can set it to log all the activity for your executable and see if
>> that might indicate where the 30+seconds is spent.

>> note that it won't tell you what's wrong but you should be able to see
>> what is going on in terms of file open/close

I'll take a look at that!


>> 2. if the process monitor does not work then sometimes network trace
>> helps to see the SMB messages - using Wireshark for example
>> (http://www.wireshark.org/) again would let you see what is oing on
>> communication wise to the share.

I'll take a look at that too, thanks!


>> As a more general comment any chance you can switch to CS model instead
>> of file-share?  50+ file share users is really pushing it IMHO.

50+ users are not in my app simultaneously - just connected to the fileserver in general.  I maybe have 5 users max connected at any one time.

Raul

Ok, ProcMon shows me something similar to the following:

2:31:21.9917324 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 0, Length: 512, Priority: Normal
2:31:21.9926007 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 41,216, Length: 416, Priority: Normal
2:31:21.9934413 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 41,632, Length: 416, Priority: Normal
2:31:21.9942844 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 42,048, Length: 416, Priority: Normal
2:31:21.9948083 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 42,464, Length: 416, Priority: Normal
2:31:21.9953640 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 42,880, Length: 416, Priority: Normal
2:31:21.9958945 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 43,296, Length: 416, Priority: Normal
2:31:21.9967085 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 43,712, Length: 416, Priority: Normal
2:31:21.9972317 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 44,128, Length: 416, Priority: Normal
.....
2:32:02.8339353 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 21,747,264, Length: 416, Priority: Normal
2:32:02.8349638 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 21,747,680, Length: 416, Priority: Normal
2:32:02.8359017 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 21,748,096, Length: 416, Priority: Normal
2:32:02.8369817 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 21,748,512, Length: 416, Priority: Normal
2:32:02.8379364 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 21,748,928, Length: 416, Priority: Normal
2:32:02.8385611 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 21,749,344, Length: 416, Priority: Normal
2:32:02.8392029 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 21,749,760, Length: 416, Priority: Normal
2:32:02.8399462 PM   OASScheduler.exe   12088   UnlockFileSingle   \\!enterprise\public\AS400Spool\DB\dbisam.lck   SUCCESS   Offset: 4,291,124,924, Length: 1
2:32:02.8410665 PM   OASScheduler.exe   12088   LockFile   \\!enterprise\public\AS400Spool\DB\dbisam.lck   SUCCESS   Exclusive: True, Offset: 4,291,143,332, Length: 1, Fail Immediately: True
2:32:02.8418248 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 0, Length: 512, Priority: Normal
2:32:02.8428726 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 20,130,688, Length: 416, Priority: Normal
2:32:02.8435932 PM   OASScheduler.exe   12088   UnlockFileSingle   \\!enterprise\public\AS400Spool\DB\dbisam.lck   SUCCESS   Offset: 4,291,143,332, Length: 1

As you can see, it's just doing a bunch of ReadFiles and as you can also see, it takes about 30-40 seconds for it to finish...

Does this tell you anything?

Robert,

Looking at your previous description it appears it is in fact busy
reading the history detail table.

Without knowing too much about the app and what it does i can only make
some wild guesses:

1. Any chance your history header (master table) selection changes when
during shutdown? This would result in history (detail table) to reload data.

I don't know if you can test something as simple as killing the
master-detail link at the start of shutdown (setting mastersource to
nil) - for example in menu action for app close rather than waiting app
close message to arrive (form close to be called etc).

2. I think you did mention that default engine buffers made no
difference but i wonder if 64MB buffers you have affect this. I have
done very little on dbisam file share side so i don't know if dbisam
tries to do read-ahead to fill buffer or just uses the buffer to avoid
going to disk (cache model) - otherwise filling 64MB might take a while.

Do you have any logging and do you see from it where the 30 second gap
is ? For example does it happen before you get form close or after you
start closing datasets and such.

Raul



On 4/10/2012 2:33 PM, Robert D. Smith wrote:
> Ok, ProcMon shows me something similar to the following:
>
> 2:31:21.9917324 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 0, Length: 512, Priority: Normal
> 2:31:21.9926007 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 41,216, Length: 416, Priority: Normal
> 2:31:21.9934413 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 41,632, Length: 416, Priority: Normal
> 2:31:21.9942844 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 42,048, Length: 416, Priority: Normal
> 2:31:21.9948083 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 42,464, Length: 416, Priority: Normal
> 2:31:21.9953640 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 42,880, Length: 416, Priority: Normal
> 2:31:21.9958945 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 43,296, Length: 416, Priority: Normal
> 2:31:21.9967085 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 43,712, Length: 416, Priority: Normal
> 2:31:21.9972317 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 44,128, Length: 416, Priority: Normal
> ....
> 2:32:02.8339353 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 21,747,264, Length: 416, Priority: Normal
> 2:32:02.8349638 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 21,747,680, Length: 416, Priority: Normal
> 2:32:02.8359017 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 21,748,096, Length: 416, Priority: Normal
> 2:32:02.8369817 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 21,748,512, Length: 416, Priority: Normal
> 2:32:02.8379364 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 21,748,928, Length: 416, Priority: Normal
> 2:32:02.8385611 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 21,749,344, Length: 416, Priority: Normal
> 2:32:02.8392029 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 21,749,760, Length: 416, Priority: Normal
> 2:32:02.8399462 PM   OASScheduler.exe   12088   UnlockFileSingle   \\!enterprise\public\AS400Spool\DB\dbisam.lck   SUCCESS   Offset: 4,291,124,924, Length: 1
> 2:32:02.8410665 PM   OASScheduler.exe   12088   LockFile   \\!enterprise\public\AS400Spool\DB\dbisam.lck   SUCCESS   Exclusive: True, Offset: 4,291,143,332, Length: 1, Fail Immediately: True
> 2:32:02.8418248 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 0, Length: 512, Priority: Normal
> 2:32:02.8428726 PM   OASScheduler.exe   12088   ReadFile   \\!enterprise\public\AS400Spool\DB\OASHistory.dat   SUCCESS   Offset: 20,130,688, Length: 416, Priority: Normal
> 2:32:02.8435932 PM   OASScheduler.exe   12088   UnlockFileSingle   \\!enterprise\public\AS400Spool\DB\dbisam.lck   SUCCESS   Offset: 4,291,143,332, Length: 1
>
> As you can see, it's just doing a bunch of ReadFiles and as you can also see, it takes about 30-40 seconds for it to finish...
>
> Does this tell you anything?
>

Here is my CloseDB function that gets executed on FormClose:

procedure TFrmMain.CloseDB;
begin
 ActnCollapse.Execute;
 UpdateStatBar('Closing Database...');
 DBISAMTblOASMachNoOrders.Close;
 DBISAMTblOASHistoryHeader.Close;
 DBISAMTblOASHistory.Close;
 DBISAMDBOASScheduler.Close;
 UpdateStatBar('');
end;

It hits the UpdateStatBar('Closing Database...') immediately.

Perhaps reverse the closing of HistoryHeader and History?