Ideas for caching a table?

[Cringer]

OE 11.7.17 (or whatever the latest patch is).
Redhat

We have a table in the database that is the system control table. Pretty much every program has an include file in it to read this record. Sometimes this even happens multiple times due to nested includes. It's a mess.

We are reading this table over 4000 x per second. Which is insane. It is mostly static data. If it does change then it is rarely. So it's a perfect candidate for caching.

Yes, I suppose it's persistently in the -B but it offends me that the churn is so high!!

This is a classic webspeed application. Requests are usually only to a single .p. So standard caching only removes the situations where the record is read multiple times in one program.

Does anyone have any ideas how I might go about caching this record for a predetermined period of time before re-reading it from the database?

Considerations should include that we will be moving to PASOE in the not too distant future.

 

[peterjudge]

Put it in -B2 ? And let it churn away.

You can cache the data in static OOABL members (properties/whatever). Add a "last loaded" timestamp too, and maybe a "max age" value.

In the activate procedure - and if that doesn't run for Classic WebSpeed, there's a procedure named something like init-request - check the last-loaded + max-age values and if they're exceeded, reload the data.

 

[Cringer]

-B2 may be the path of least resistance. Staring me right in the face!!

 

[Rob Fitzpatrick]

Yes, I suppose it's persistently in the -B but it offends me that the churn is so high!!

It should! Accessing data in the buffer pool is of course faster than incurring database server disk I/O to page it into the buffer pool first, but it is far from being free just because it is memory-resident.

I doubt that using -B2 will help in this case.

Currently, that control record's record block is in the buffer pool. Assigning the table to the ABP doesn't change that. As this is a WebSpeed application, with presumably all agents hammering this same record, I suspect there is high contention for the BHT chain for this buffer, or for the buffer latch. There is only one buffer hash table, so using ABP won't change the problem. And with this kind of BHT contention (assuming it exists), -hashlatchfactor won't help either. You can see buffer access and wait statistics in ProTop.

A big part of the gain with ABP when it was introduced in 10.2B was in reducing LRU latch activity and contention in the primary buffer pool, as the ABP has its own LRU chain and latch. But -lruskips is a much more effective means of doing that, without any configuration or tuning needed.

I'm sure @TomBascom will weigh in on this, especially if this control record is accessed with a find statement, and even more so if the agents are remote. In that case, the big cost you're paying is the three network messages for the find, which you will still have with ABP.

So as @peterjudge suggested, I'd test caching the data client-side, with a static class or a temp-table.

 

[TomBascom]

Caching it as a temp-table will run circles around having it in the either of the buffer pools. Especially if you have client server connections. TT access can be 5x to 20x faster than db access - even when the db data is in the buffer pool.

If your application just happens to have an include file that everyone includes then this is actually very easy to do... let us assume that that every program includes "common.i". In common.i you want:

define new global shared temp-table tableName like tableName.

You might want to modify that to:

define new global shared temp-table tableName like tableName
  index idxName is primary unique uniqueFieldName
.

(Maintaining lots of indexes is kind of expensive and, for small tables, TT indexes don't improve performance very much (if at all). So you might see additional improvements by limiting the TT to a single unique index if you can.)

Then you just need to add something in your startup / activation procedure to initialize it. Something like this:

for each dbname.tableName no-lock:
  create tableName.
  buffer-copy dbname.tableName to tableName.
end.

(This is a lot faster with just one unique index than it is with lots of indexes being built...)

Presto! Naked references to tableName will now read the temp-table. If you have _some_ code that really, really, needs to access the db table you can still do so with dbname.tableName style references.

Of course, this approach assumes that the data is read-only. If the data might change once in a while then you might also include some logic to track a timer and every N minutes (or whatever) refresh the cache.

The cost / benefit analysis depends greatly on how many records the cached table has vs the number of accesses. If you have a small table with lots of access this can be a very big win. We have seen cases where TWO THIRDS of the database workload (BILLIONS of reads per day) has been eliminated in this way resulting in major improvements in throughput and scalability.

 

[peterjudge]

Caching it as a temp-table will run circles around having it in the either of the buffer pools. Especially if you have client server connections. TT access can be 5x to 20x faster than db access - even when the db data is in the buffer pool.

If your application just happens to have an include file that everyone includes then this is actually very easy to do... let us assume that that every program includes "common.i". In common.i you want:

define new global shared temp-table tableName like tableName.

You might want to modify that to:

define new global shared temp-table tableName like tableName
  index idxName is primary unique uniqueFieldName
.

(Maintaining lots of indexes is kind of expensive and, for small tables, TT indexes don't improve performance very much (if at all). So you might see additional improvements by limiting the TT to a single unique index if you can.)

Then you just need to add something in your startup / activation procedure to initialize it. Something like this:

for each dbname.tableName no-lock:
  create tableName.
  buffer-copy dbname.tableName to tableName.
end.

(This is a lot faster with just one unique index than it is with lots of indexes being built...)

Presto! Naked references to tableName will now read the temp-table. If you have _some_ code that really, really, needs to access the db table you can still do so with dbname.tableName style references.

Of course, this approach assumes that the data is read-only. If the data might change once in a while then you might also include some logic to track a timer and every N minutes (or whatever) refresh the cache.

The cost / benefit analysis depends greatly on how many records the cached table has vs the number of accesses. If you have a small table with lots of access this can be a very big win. We have seen cases where TWO THIRDS of the database workload (BILLIONS of reads per day) has been eliminated in this way resulting in major improvements in throughput and scalability.

As much as the thought of using global shared temp-tables makes me throw up in my mouth a little bit, having such a beast have the same name as the db table is a neat trick, and should let you implement "common.i" incrementally throughout the application.

 

[Cringer]

So will a temp-table cache persist across sessions if it's global shared? I'll have to have a play around with that today.

 

[peterjudge]

So will a temp-table cache persist across sessions if it's global shared? I'll have to have a play around with that today.

It will persist in a single AVM session (so Classic agent/client session/etc, or a single session in a PASOE multisession agent process). The "global" nature of shared things means that it's not limted to the current call stack, but the whole AVM session can access that thing (variable, temp-table, <shudder> frame, ...).