Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 3 of 3 total
Thread Uses for Buffering settings in EDBSRVR
Sun, Mar 7 2021 5:41 AMPermanent Link

Adam Brett

Orixa Systems

This is more of a curiosity question than anything.

I have a good number of EDB Systems, but I don't use the Buffering capabilities of the Server in any of them.

I have quite large numbers of tables (50+) and complex queries (multiple joins, sub-queries etc.) in my systems but these all return read-only data. Users adding data do so in a very controlled "one record at a time" way, with each record update posted separately.

Is there anything useful I can do with Buffering to improve the responsiveness of my systems?

I cannot see much documentation about what Buffering is and how it works in the manuals. I can see BufferedFileIOSettings ... but the impact of changing settings, or reasons why you would use them are not really explained.
Tue, Mar 16 2021 4:50 PMPermanent Link

Mario Enríquez

Open Consult

+1

I'm on the fence on this too..

Following...
Wed, Mar 17 2021 9:32 AMPermanent Link

Raul

Team Elevate Team Elevate

On 3/7/2021 5:41 AM, Adam Brett wrote:
> This is more of a curiosity question than anything.
>
> Is there anything useful I can do with Buffering to improve the responsiveness of my systems?
>
> I cannot see much documentation about what Buffering is and how it works in the manuals. I can see BufferedFileIOSettings ... but the impact of changing settings, or reasons why you would use them are not really explained.
>

Tim can provide a more complete answer but here is my version.

General idea with buffering is to cache data, return it from memory and
generally try to reduce disk i/o.

How much it helps will really depend on your application and data usage
patterns so you would have to experiment with your app.

My guess is that the "single record edit" for users might not benefit
much but complex queries possibly yes - this is just a guess though
assuming multiple queries for example return sub-sets of same data and
not totally unique each time.

In general you have 3 levels of caching

1. OS caching which you do not have much control over other than drive
config and various flush settings. This might work pretty well actually
if same tables are frequently used and OS can cache those files.

in EDB

2. You can specify buffer sizes for tables using the "MAX ROW/INDEX/BLOB
BUFFER SIZE" when creating or altering the table.  This is always used
by EDB on session level but AFAIK generally the defaults work well and
EDB might do some adjusting itself here also.

This is limited since caching is per session and multiple users each
have own independent session cache.

You can use SessionStatistics and/or ServerSessionStatistics to see
buffering stats for local and server sessions.

See
https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=ServerSessionStatistics_Table
and notes


3. Finally there is BufferedFileIO which does require exclusive mode -
meaning you for example have to use single edbsrvr - but should provide
most caching since now EDB can maintain a proper shared cache and also
reduce need to perform constant disk writes.

With this EDB will try to cache as much as possible (as per configured
settings and buffer sizes) and reduce disk writes by also delaying flush
right away.

You do have to make sure server/pc running this is properly shutdown and
has UPS and such since data might not be written to disk yet at any
given point in time.

You enable it with BufferedFileIO engine property and then use
BufferedFileIOSettings to configure which tables to cache (or all).

And then can use FileIOStatistics table to see stats on cache performance.

See this section for general buffering overview

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Buffering_Caching

and starting engine "Buffered File I/O" section on how to add tables and
such

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2&product=rsdelphiwin32&version=10.4&topic=Configuring_Starting_Engine

and

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=FileIOStatistics_Table


Assuming you can use BufferedFileIO i would suggest to test this out if
you can - enable it, configure for your app tables and then check stats
after it's been running for a while.

if you have lots of ram available you can increase per table buffer size
to see if you can maximize data to be returned from ram buffer as much
as possible for your case .

Raul
Image