Hello,

I'm going thru the process of optimizing our app db usage and doing the
usual things like optimizing tables/indexes, checking frequently used
queries and such.

One area I have not spent much time in is the engine buffers and whether
those would provide any performance enhancements (MaxTableDataBufferSize
and Count, MaxTableIndexBufferSize and Count and  MaxTableBlobBufferSize
and Count).

Before i start testing this myself wanted to ask if anybody has already
done this and has any useful info to share.

I know windows OS does a pretty good job of disk caching already so just
wondering how much change does one see when increasing the buffers.

In my case the app is our server component so all data access is F/S
local file system.

I have no concerns over app consuming extra tens or (few) hundreds MB's
of RAM so question is specifically about what improvements have you seen
when changing from defaults.

TIA,
Raul

Raul wrote:

> One area I have not spent much time in is the engine buffers and
> whether those would provide any performance enhancements
> (MaxTableDataBufferSize and Count, MaxTableIndexBufferSize and Count
> and  MaxTableBlobBufferSize and Count).
>
> Before i start testing this myself wanted to ask if anybody has
> already done this and has any useful info to share.

If you search back (perhaps using Google) then you will see it has been
covered before, but perhaps a long time back. If I remember correctly,
the key here is that it depends on how you use your database. Big
buffers improve performance for mainly reads, as of course it has to
read less. But if you update the database, then those buffers are no
longer valid, and have to be re-read again, which takes longer, which
slows things down. This may be on a per-session basis, and therefore
not apply to some applications - but threads probably mean cache
changes.

I think the smart thing to do is to set up some sort of automated test,
where you simulate several clients, and then adjust and see how it
performs.

--

Matthew Jones

Thanks Matthew,

> If you search back (perhaps using Google) then you will see it has been
> covered before, but perhaps a long time back. If I remember correctly,
> the key here is that it depends on how you use your database.


I did go thru that and did find some suggestions either way (multiplier
to increase but not go too large as that might slow engine down). I was
just posting to see if anybody has any new consolidated info (i.e. has
run extensive tests that i start from

> I think the smart thing to do is to set up some sort of automated test,
> where you simulate several clients, and then adjust and see how it
> performs.

I'm planning to to do. The issue is that it's pretty fast already so i'm
really looking to squeeze little extra out of the server at this point
without changing data structures (for example take an operation that is
currently 350 msec and see if i could bring it down by 50msec or so -
most of that is db operations on getting data).

Raul

Raul,

<< I know windows OS does a pretty good job of disk caching already so just wondering how much change does one see when increasing the buffers. >>

Increasing the buffers can help if you're using direct, file-sharing access via a local session over the wire with a shared database.  It helps offset the additional load that comes from having to pull in the indexes and BLOBs, in addition to the records, from the file server.  This is especially true for indexes, which are constantly being hit for optimization of queries, etc.

Tim Young
Elevate Software
www.elevatesoft.com