These are the default DBSYS.INI parameters.
Max Read Lock Count=100
Max Table Data Buffer Count=8192
Max Table Data Buffer Size=32768
Max Table Index Buffer Count=8192
Max Table Index Buffer Size=65536
Max Table Blob Buffer Count=8192
Max Table Blob Buffer Size=32768


I would like to take advantage of my high performance hardware to cut down
in database repair/alter time. I tried to tune the above parameters by
setting them to higher values. I tested 999999 for each parameter then I
repaired a table with 350,000 row.
I got the following results:
repair time using default settings= a little over 10 min
repair time using custom values = less than 3 minutes.

what do you think of my tests?
is there a way or documented approach to teach me how to tune DBISAM
parameters? I'm sure those settings can be tuned based on the CPU/ harddrive
size/free space, etc...
Thank you,
-Halim

Halim Boumedjirek wrote:
> These are the default DBSYS.INI parameters.
> Max Read Lock Count=100
> Max Table Data Buffer Count=8192
> Max Table Data Buffer Size=32768
> Max Table Index Buffer Count=8192
> Max Table Index Buffer Size=65536
> Max Table Blob Buffer Count=8192
> Max Table Blob Buffer Size=32768
>
>
> I would like to take advantage of my high performance hardware to cut down
> in database repair/alter time. I tried to tune the above parameters by
> setting them to higher values. I tested 999999 for each parameter then I
> repaired a table with 350,000 row.
> I got the following results:
> repair time using default settings= a little over 10 min
> repair time using custom values = less than 3 minutes.
>
> what do you think of my tests?

Did you remember to reboot before you repeated the test?
In my test case, I bumped up the data buffer and index buffer to
something like 16MB, and ran extensive repeatable Ranges on a large
million row table, and it made absolutely no difference whatsoever. It
always clocked at 149 ranges/second no matter what. I'll need to do some
more testing to find out what the problem is.

Dave

I have done my testing using DBSYS in single multi user mode, so there is no
need to reboot there.
I think you should be able to see a big difference in speed. I was always
wondering why servers with big memory and high cpu power would spend the
same amount of time to repair a table as a simple PC. I assume that it had
to do with the local DBISAM setting rather than PC power.
-Halim

"Dave Harrison" <daveh_18824@spammore.com> wrote in message
news:C0E93798-8707-4FAA-8FF1-9251828B0BD0@news.elevatesoft.com...
> Halim Boumedjirek wrote:
>> These are the default DBSYS.INI parameters.
>> Max Read Lock Count=100
>> Max Table Data Buffer Count=8192
>> Max Table Data Buffer Size=32768
>> Max Table Index Buffer Count=8192
>> Max Table Index Buffer Size=65536
>> Max Table Blob Buffer Count=8192
>> Max Table Blob Buffer Size=32768
>>
>>
>> I would like to take advantage of my high performance hardware to cut
>> down in database repair/alter time. I tried to tune the above parameters
>> by setting them to higher values. I tested 999999 for each parameter then
>> I repaired a table with 350,000 row.
>> I got the following results:
>> repair time using default settings= a little over 10 min
>> repair time using custom values = less than 3 minutes.
>>
>> what do you think of my tests?
>
> Did you remember to reboot before you repeated the test?
> In my test case, I bumped up the data buffer and index buffer to something
> like 16MB, and ran extensive repeatable Ranges on a large million row
> table, and it made absolutely no difference whatsoever. It always clocked
> at 149 ranges/second no matter what. I'll need to do some more testing to
> find out what the problem is.
>
> Dave

Halim


There's always a need to reboot to get rid (as best as possible) any effects of Windows caching.

Roy Lambert

Halim Boumedjirek wrote:

> I have done my testing using DBSYS in single multi user mode, so there is no
> need to reboot there.

Windows is really good at caching DBISAM files, so the speed up may be
due to caching. You really need to reboot between tests if you want
accurate results.

Dave

Halim,

<< is there a way or documented approach to teach me how to tune DBISAM
parameters? I'm sure those settings can be tuned based on the CPU/ harddrive
size/free space, etc... >>

Just don't make the settings larger than 16-32 megs.  Anything over that
will probably not buy you much improvement, and probably will just reduce
the performance.  The caching in DBISAM (and EDB) is not designed to use
extremely large amounts of memory, but rather to serve as an immediate
buffer for multi-block operations that is faster than the OS caching.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<Just don't make the settings larger than 16-32 megs.  Anything over that
will probably not buy you much improvement, and probably will just reduce
the performance.  The caching in DBISAM (and EDB) is not designed to use
extremely large amounts of memory, but rather to serve as an immediate
buffer for multi-block operations that is faster than the OS caching.>>

EDB comes with default numbers for all these settings which should work for all types of systems (old ones and new ones).  But what numbers do
you recommend to get the best of EDB under top of the modal systems (a lot of memory with fast drives and processors)?

Abdulaziz,

<< EDB comes with default numbers for all these settings which should work
for all types of systems (old ones and new ones).  But what numbers do you
recommend to get the best of EDB under top of the modal systems (a lot of
memory with fast drives and processors)? >>

The defaults are the best in most cases.  The buffering in EDB is just like
DBISAM in that it is not used as a replacement for the caching done by the
OS, but as a supplement to it.  We let the OS do the large memory caching
and use the local EDB buffering for speeding up local operations as
necessary.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
> Abdulaziz,
>
> << EDB comes with default numbers for all these settings which should work
> for all types of systems (old ones and new ones).  But what numbers do you
> recommend to get the best of EDB under top of the modal systems (a lot of
> memory with fast drives and processors)? >>
>
> The defaults are the best in most cases.  The buffering in EDB is just like
> DBISAM in that it is not used as a replacement for the caching done by the
> OS, but as a supplement to it.  We let the OS do the large memory caching
> and use the local EDB buffering for speeding up local operations as
> necessary.
>

Tim,
    I had set the EDB table buffer to several MB (I tried several
settings) and I didn't notice any difference at all. Have you or anyone
else actually noticed any improvement if you allocate more buffers to a
large table? I'm doing Ranges and it never gets faster than 150
ranges/second regardless of the buffer size. I'm going to have to look
into this further but thought I'd ask if there are any improvements to
using more buffers.

Dave

Dave,

<< I had set the EDB table buffer to several MB (I tried several settings)
and I didn't notice any difference at all. Have you or anyone else actually
noticed any improvement if you allocate more buffers to a large table? I'm
doing Ranges and it never gets faster than 150 ranges/second regardless of
the buffer size. I'm going to have to look into this further but thought I'd
ask if there are any improvements to using more buffers. >>

There are so many variables involved with such a test that is next to
impossible for me to make any comment that would cover everything.  The
bottom line is that the default buffering should be adequate for a range
test, and any increase in the buffers probably won't help improve the
performance because the amount of buffering required is beyond what EDB can
realistically use without getting into other issues with buffer management,
etc.

--
Tim Young
Elevate Software
www.elevatesoft.com