Data Caching

You answered your own question.  Yes, data is cached in -B.  To turn it off you could try setting -B to zero, but that wouldn't be a very good idea, even if it worked.  It is a good thing.  Properly tuned, your system should be satisfying 99+% of its queries from the cache.  If you are worried about stale data, don't be, since it is quite intelligent about making sure that you get the latest copy unless you use the -RO parameter, i.e., tell it you don't care.  If you are trying to do performance tests, you need to bounce the server to get back to the uncached state.

There is caching occurring at several levels in the system.

The -B database configuration startup parameter controls the size (as number of buffers) of the database buffer cache, which holds recently accessed data blocks once they have been read from disk. The purpose of this cache is to reduce the amount of disk I/O needed when accessing data in the database. You cannot turn this cache off completely.  The minimum size is 10 buffers and setting it that low is not advisable.

The operating system also has filesystem caches which serve a similar purpose for data blocks read from disk when files are accessed, including the database's data files. Most modern operating systems will allow the filesystem cache to use memory otherwise not needed by the system.  You can limit the size of the filesystem cache too.  Exactly how this is done depends on the operating system.

The 4GL runtime also does caching of records but normally in much smaller amounts than the database does.

-gus

Thanks. Yes I see in documentation that the mininum value is 10 blocks. I would like to turn database caching off temporarily for easier reading of performance testing between different clients. You mention that it is not advisable. But is this okay temporarily?

gus wrote:
There is caching occurring at several levels in the system.
The -B database configuration startup parameter controls the size (as number of buffers) of the database buffer cache, which holds recently accessed data blocks once they have been read from disk. The purpose of this cache is to reduce the amount of disk I/O needed when accessing data in the database. You cannot turn this cache off completely.  The minimum size is 10 buffers and setting it that low is not advisable.
The operating system also has filesystem caches which serve a similar purpose for data blocks read from disk when files are accessed, including the database's data files. Most modern operating systems will allow the filesystem cache to use memory otherwise not needed by the system.  You can limit the size of the filesystem cache too.  Exactly how this is done depends on the operating system.
The 4GL runtime also does caching of records but normally in much smaller amounts than the database does.
-gus

Even if you set the -B to 10, you haven't shut it off completely.  Moreover, you have set up a very abnormal environment which won't relate to real world activity.  E.g., in the real world, even if the *records* you are reading are not in the cache, some or all of the *index* is likely to be there.  So, turning off caching or throttling it creates a context you would never see in production.  Bounce the server; its the right thing to do.

Thanks. But I don't undertand by bouncing the server. You mean the database server right? So I keep the -B untouched and just restart the database? But if done, then the caching will just start over again. If I am doing client and query performance testing I will still get the first time being larger than the subsequent times.

Which is why you restart the database server for each test.

Regardless of how small you make the database's buffer cache, there will still be data in the filesystem cache.

The filesystem cache can be emptied by unmounting and remounting the disk(s) or by rebooting the machine.

But getting rid of all cached data is not going to give you an accurate or realistic estimate of how long your query will take.  It /will/ sort of give you an upper bound assuming you are running it in an isolated environment (nothing else running).

What is it that you are attempting to measure?

-gus

Yeah, I meant to mention that, given a properly tuned system with 99+% of the data coming from -B, the *normal* thing is to get data from the cache and for a small percentage of read operations to result in a disk read ... so, isn't that what one should be trying to measure in most cases?  I.e., run the test twice and throw out the *first* one.

 If I am doing client and query performance testing I will still get the first time being larger than the subsequent times.

What exactly are you trying to measure?

Client testing sounds like testing the system's performance. Query testing sounds like you're optimizing programs (code). I consider queries part of the program, not the system. For query performance testing (=program optimization), the actual run time is not that important as it varies from installation to installation. Measure the number of records read:

DEFINE VARIABLE iNumberOfReads AS INTEGER NO-UNDO.
FIND FIRST _ActSummary .
ASSIGN iNumberOfReads = _ActSummary._Summary-RecReads.
/* run your queries ... */
FIND FIRST _ActSummary .
MESSAGE "Queries required" _ActSummary._Summary-RecReads - iNumberOfReads "number of records".

That will give you the #1 performance indicator for queries (read operations). The less records required, the better... And caching has no impact on this number.

Thanks for the info. The front-end .NET client developers are doing data retrieval tests. But I concluded that any new caching on stale data where they can just omit the first test.

When I monitor the Activity, the Buffer Hits are usually always at or very near 100%. This is good right?

The right answer depends on context.  Yes, with a suitably high hit ratio, the general expectation is that one will get data from the -B cache ... but, that depends on the nature of what they are testing.  If the screen is one that is fetching historical data, i.e., something that no other function is likely to have accessed in a while, then you actually expect to have to get it from the disk.  If it is fetching something that someone else is likely to have looked at recently, then it is probably in the cache.  If it is going to be read and re-read, then it will be in the cache for the re-read.  For performance testing, you want to emulate the real world.  For just evaluating the quality of the queries, then you might want to go with Mike's idea of testing the total number of records returned relative to the number of records wanted.

Yes, that is good. What that is telling you is that nearly all of the database data block requests were satisfied by retrieving them from the database's block buffer cache in memory instead of reading them from the filesystem cache or from disk.

When you run your queries against a freshly started system, there is nothing in the caches and your first query is partly measuring how long it takes to retrieve the data from disk to load the caches /for that specific workload/. That is not representative of a real production system.

When you run the same query a second time, you are measuring how long it takes to retrieve the data partly from the caches and partly from disk.  If all the data fits in memory, then you are measuring how long it takes to retrieve the data just from cache. That is (probably) not representative of a real production system either, which will have all sorts of other activity going on at the same time.

You said the front-end developers are doing data retrieval tests. What are they attempting to determine? Whether or not their queries are properly written, or something else?

-gus

Thanks. The front-end has binding issues. But finding answers.

I am also wondering if we are using the correct AppServer operating mode. Currently state-free. In a .NET Windows client and Progress DB LAN environment. But initially our consultant had it configured as stateless.

We changed since we thought that state-free offered better performance, but

we never really did metrics.

The app will be intended for no more than about 50 users. The server resources is not a concern.

The queries, updates and business logic reside on the AppServer. The .NET front-end is only for UI and using the Proxy.

So, actually, the thing you are wanting to test has nothing to do with the DB or caching ... it is the performance of the AppServer connection.  So, write yourself a little procedure to supply some fixed data with no reference to the DB and test against that.

A cache hit ratio near 100% might be good.  Or it might not be.

It does mean that most of your db activity is being satisfied from the cache.  But if you are doing something ridiculous like reading the same records over and over and over and over and (get the point?) again you will have a very good cache hit ratio combined with poor performance.  This happens altogether too often in the real world.  As Mike pointed out you really need to be paying attention to how many record operations you are performing.  Ultimately that is what drives performance.  If you're reading a million records in order to display a small amount of data you're doing something very wrong.  Even if the cache hit ratio is 100%.