Hi, we've got a pool of appservers which expose our business logic to "clients". So far, so normal.
However, we store a huge amount of config in the database including:
In general, we just access this configuration whenever it is required for each API call. OpenEdge is nice and quick, especially when running shared-memory to get this sort of stuff - it's generally in the buffer pool.
Problem is, we're looking into using dataservers, and suddenly all these many "supporting" DB accesses become an issue. We're generating many SQL queries for data that we would ideally like to come from a cache. I know the SQL server will have some sort of caching itself, but I feel it's wrong that we should constantly be making network requests for the same data repeatedly. I suspect it's also bad from a performance perspective (typically we're seeing dataserver profiles 10x slower than "native" OpenEdge).
One approach, I'm considering, is to create temp-tables for this security/config/meta data - and use the temp-tables instead of the database. We could prime the temp-tables on appserver start, and with minimum coding changes we could make use of the "cached" data. Obviously there would be issues regarding the data going stale, but those issues could be dealt with in a variety of ways.
One drawback I can see of the temp-table approach, is we have lots of appservers and lots of data that we want to cache - this data will have to be stored somewhere, and AFAIK, OpenEdge doesn't have a way of letting multiple processes share (temp-table) data (which might be nice). So we'll have to ensure that we have enough resources (memory).
I was just wondering whether anyone had any other ideas? I'm presuming we're not alone in having this sort of issue, and was wondering how others have got around it.
And if you can think of any other drawbacks of the temp-table approach, can you let me know?
Any input would be appreciated.
Guy
We have a lazy generic temp-table cache for most master data. The temp-table has something like table / field / value as fields. Another temp-table contains meta-data and query handles.
This was created a long time ago (even pre-appserver) when number of database reads was our only solid performance metric.
The 15 second life limit was added while moving to appserver to reduce stale data issues.
Later on we also added temp-tables for generally static master data - these are filled / refreshed when needed based on a timestamp of last update in the database.
With lazy I mean that data is only fetched when first required.
Thanks for the reply Stefan.
I was thinking of doing a full copy of the temp-tables at appserver startup - to shared temp-tables (!!!)
So my thinking was that if we had some code e.g.
FIND config WHERE <criteria> NO-LOCK.
it becomes
{useCache.i}
FIND config WHERE <criteria> NO-LOCK.
where
/* useCache.i */
DEFINE SHARED TEMP-TABLE config for config /* using all indexes */.
I reckon all I have to do is insert the include file(s) into any procedure that reads (and doesn't update) the tables - the list of which I can get via xref or listings, et voila - I use temp-tables instead of DB tables. And using preprocessors, we could get this to take affect only for dataserver compilations...
So the issue is, if we allow "hot" config changes, (i.e. changes that should take affect without restarting the appservers), I need to refresh the data in the cache. An easy but slow way would be to check at every API call against a timestamp value in the DB, and then do a full refresh if that changes. We could have a global timestamp for all config, or a timestamp for each table - or maybe a bit of both - i.e. check the global timestamps and if it's changed, check the table timestamps.... However, by doing any full refresh, it could mean that the first API call on an appserver after a config change could take much longer whilst the temp-tables are rebuilt.
Alternatively, I was thinking of doing something like using "audit" data to only refresh those config records that have changed in the "cache".
I guess I'll probably start simple, and introduce a better caching model if required.
I like your idea of lazily getting the config data, however from what you've indicated, I'm presuming you don't do so "natively" (i.e. using FIND/FOR EACH/QUERY statements against the DB), and instead get it via a helper function or class? - which potentially returns a temp-table, but might get the data from DB? Unfortunately we've got far too many lines of code that accesses the database (hundreds of thousands), that I think it would be very difficult/costly to rewrite all our different statements (most static, some dynamic) to such a construct. But I would be happy to be shown otherwise :-)
For the master data we have NEW GLOBAL SHARED temp-tables - which are refreshed via a super procedure. Code that wants to take advantage of using tt vs db does need to be explicitly adjusted.
The adjustment is then:
a. call checkrefresh function in super to see if data in temp-tables is fresh enough
b. define buffer table for tt.
For config / other data (the keyed tt) - the finds were adjusted over time to use a function in the super to get the data instead of using a find / for each etc.
The only thing I can advise if going down that route (or for any other signature for that matter) is to make sure that you have sensible easy reading signatures on your functions / methods.