reference file cacheing (faq7 ) -

  • Powerforce is so data driven that it struggles under load in big systems
    Revelations indexing and our bad design (design hasn't changed over last 12 years) has lead to the need to refactor data access improvements in what ever way is possible.

 

Refactor here means to re-write our application in place, whilst not changing any current code logic.


Why refactor?


We have the following versions of Powerforce:

  • BASELINE
  • RAWS
  • EDMEN
  • MCORP
  • DEVEL10_SQL ( Baseline version )
  • DEVEL10
  • GEO (SQL)
  • SNP


Other than the SQL versions, indexing is the bane of the applications existance - every page refresh in the 'guts' of our application is a 'select' against a table with a 5-part key (possibly not the smartest design, however we have neither time nor money to refactor that component).

Once the SELECT is complete, there are any number of reference record reads to get simple things like 'descriptions' for items etc. The majority using the OI basic "XLATE" function - which we are not fond off ( exists as a TODO item of the cacheing)

Substantial speed throughput has been achieved with the use of more current versions of Windows NT, post 2008, most preferable 2012, where the system itself supports data cacheing.

The greatest improvement has come through the use of SSD disks - they are superb, inexpensive means to gain a 10 fold performance boost. However, not every client can be pursuaded to move on either of these two fronts.


The foundation of cacheing (RTP65) has been known to us for a few years, however, it was always too hard, and not well understood to code.

Fortunately we've recently taken the plunge with impressive results - a task which under current circumstances take just on 5.00 hours has been pared back to 40 - 45 minutes.


reference file cacheing (sample )

 

All coding starts with a "test function" through which we manage code refactoring. In this example we are refactoring the [TABLE] sys_users.

Because we're refactoring existing code (over 2000 programs), it's important to make the conversion as simple as possible, therefore all our new functions are coded as closely as possible to the manner in which OI writes its code. As an example in Basic we would write:

READ buffer FROM file_unit, id THEN
... process record

END ELSE
...process NO record

END

This translates into a void = nvc_table(directive, buffer, filler, id) OR void = nvc_table("R",buffer, "", id).

There is no need for the fileunit - becuase each function has the file unit acccessible to only itself in it's own private common space.

Test Program ( )

 

Always write a test program to exercise new functionality. The program will then serve as a re-factoring mechanism when base code changes are made. This following piece of code is our generic test program (one per table).

We use "gettickcount" to collect timing of how fast the cacheing is coming back.

compile function test_nvc_sysusers( void )
/////////////////////////////// /////////////////////////////// ///////////////////////////////
/////////////////////////////// /////////////////////////////// ///////////////////////////////
//                                                                                              
// This program is proprietary and is not to be used by or disclosed to others, nor is it to    
// be copied without written permission from Envizion Systems Pty Ltd.
//      
// Name       :                                                     
//                                                                                              
// Description: test routine for nvc_sysusers - cacheing
//
// * @package   PowerForce
// * @author    Martin Drenovac (for Envizion Systems Pty Ltd)
// * @copyright Copyright (c) 2007 - 2013, Envizion Systems Pty Ltd
// * @licence   http://powerforcesoftware.com/user/license.html
// * @link      http://powerforcesoftware.com
// * @since     Version 1.0
// * @filesource
//                                                                                                      
// History (Date, Initials, Notes)                                                              
// 05/04/13 mpd Original programmer
// 08/04/13 mpd more testing
// 11/04/13 mpd include timing here to compare no-cache vs cache
//                                                                                              
// * Date: 2013-04-05 17:34:21 +1000 (Fri, 05 Apr 2013)
/////////////////////////// //////////////////////////// //////////////////////////////////////
//////////////////////////// //////////////////////////// /////////////////////////////////////
#PRAGMA OUTPUT SYSLISTS mpd_test_nvc_sysusers

$INSERT nv_copyright
$INSERT nv_sysprocmarker
$INSERT nv_userprefs

declare function    nv_error,   nvc_sysusers,   gettickcount
declare subroutine  check_assignments

check_assignments( void )

nv_version$$ = "[ver: d101]"
IF void[1,5] _eqc "ver$$" THEN
   
RETURN nv_version$$
END

//
// read my pwd record
//
filler  =
""
buffer  =
""
mpd0    = nvc_sysusers(
"R",buffer,filler, "PWD*MARTINXXX")
mpd1    = nvc_sysusers(
"T")

OPEN "SYS_USERS" TO f$f ELSE
   
RETURN
END

now_start   = gettickcount()
id_list =
""
FOR j = 1 TO 2000
   
SELECT f$f
   eof =
0
   
LOOP
       
READNEXT id ELSE eof = 1
   
WHILE NOT(eof) DO
       id_list<-
1> = id
       mpd2    = nvc_sysusers(
"R",buffer,filler, id)
   
REPEAT
NEXT j
now_finish  = gettickcount()
now_elapsed = now_finish - now_start

mpd3    = nvc_sysusers("T",buffer,filler, "PWD*MARTIN")

mpd4    = nvc_sysusers("Z",buffer,filler, "")    ; // this will return the final count, having zapped everything
//
// don't use cache - time
//
mpd5    = nvc_sysusers(
"N",buffer,filler, "")   
nocache_start   = gettickcount()
id_list =
""
FOR j = 1 TO 1000
   
SELECT f$f
   eof =
0
   
LOOP
       
READNEXT id ELSE eof = 1
   
WHILE NOT(eof) DO
       id_list<-
1> = id
       mpd6    = nvc_sysusers(
"R",buffer,filler, id)
   
REPEAT
   
   
NEXT j
nocache_finish  = gettickcount()
nocache_elapsed = nocache_finish - nocache_start

mpd8    = nvc_sysusers("Z",buffer,filler, "")



nvc_sysusers ( nvc_[table_name])

 

This is the code logic of the table processing, as per my earlier comments - there is no need for fileunit, because this function is one per table, and this function does all that is / wants to be done to that one table. However, to maintain the 'write-ability' of refactoring from our OI coding of "read buffer from file_unit, id) - we code the function the same, so the progamming changes are minimal.

This is the one piece of code that you need to replicate for each table that you wish to cache.

Because we premise the design on the use of //named common// - all that needs to be done it to define the "table_name" and immediately you have all the same cacheing logic for this table, as the rest of the system.

The "$INSERT nvc_logic" - effectively defines the code of this particular tables cacheing.

 

compile function nvc_sysusers( action, buffer, fileunit, id )
/////////////////////////////// /////////////////////////////// ///////////////////////////////
////////////////////////////// ////////////////////////////// /////////////////////////////////                                                                                             
// This program is proprietary and is not to be used by or disclosed to others, nor is it to    
// be copied without written permission from Envizion Systems Pty Ltd.
//      
// Name       :                                                     
//                                                                                              
// Description: Cache routine for table [SYS_USERS]
//
// * @package   PowerForce
// * @author    Martin Drenovac (for Envizion Systems Pty Ltd)
// * @copyright Copyright (c) 2007 - 2013, Envizion Systems Pty Ltd
// * @licence   http://powerforcesoftware.com/user/license.html
// * @link      http://powerforcesoftware.com
// * @since     Version 1.0
// * @filesource
//                                                                                                      
// History (Date, Initials, Notes)                                                              
// 04/04/13 mpd Original programmer
// 08/04/13 mpd null null_size_records@. nvc_EQUATES$
// 10/04/13 mpd change LOCATE to post failed read from cache
// 11/04/13 mpd dont_cache@
// 12/04/13 mpd $INSERT nvc_logic
//                                                                                              
// * Date: 2013-04-04 17:34:21 +1000 (Thu, 04 Apr 2013)
///////////////////////////// ///////////////////////////// //////////////////////
///////////////////////////// ////////////////////////////// /////////////////////
#PRAGMA OUTPUT SYSLISTS mpd_nvc_sysusers

$INSERT nv_copyright
$INSERT nv_sysprocmarker
$INSERT nv_userprefs

declare function    nv_error,   nv_file
declare subroutine  check_assignments,  rtp65

check_assignments( action, buffer, fileunit, id )

nv_version$$ = "[ver: d101]"
IF action[1,5] _eqc "ver$$" THEN
   
RETURN nv_version$$
END
retval  =
""

table_name  = "SYS_USERS"                           ; // Set this for the //common//

$INSERT nvc_logic                                   ; // all code is the same

nvc_logic ( this is the cacheing logic )

 

This is the 'code' for the cacheing - we do lots of silly things - like keep stats (for future functionality), we have the ability to zero out all cache, and other things that we like to experiment with. (TODO section below outlines current working mindset).


COMPILE INSERT nvc_logic
//////////////////////// ////////////////////////////// ///////////////////////////////////////
//////////////////////////// //////////////////////////////////////// /////////////////////////
//                                                                                              
// This program is proprietary and is not to be used by or disclosed to others, nor is it to    
// be copied without written permission from Envizion Systems Pty Ltd.
//      
// Name       :                                                     
//                                                                                              
// Description: this is the nvc_ caching business logic. All the same, dynamically make the support code
//
// * @package   PowerForce
// * @author    Martin Drenovac (for Envizion Systems Pty Ltd)
// * @copyright Copyright (c) 2007 - 2013, Envizion Systems Pty Ltd
// * @licence   http://powerforcesoftware.com/user/license.html
// * @link      http://powerforcesoftware.com
// * @since     Version 1.0
// * @filesource
//                                                                                                      
// History (Date, Initials, Notes)                                                              
// 12/04/13 mpd Original programmer. Split business logic out into this $INSERT
// 17/04/13 mpd nvc_started@ - set on init()
//                                                                                              
// * Date: 2013-04-12 17:34:21 +1000 (Fri, 12 Apr 2013)
///////////////////// /////////////////////////////////////// /////////////////////////////////
// TODO how to handle DICT
// TODO how to handle XLATE
////////////////////////////////// //////////////////////////////// ///////////////////////////
//
// our caller sets "table_name", which sets our common name
//
$INSERT nvc_common_equates
$INSERT RTP65_EQUATES

if Assigned(init@) Else init@ = 0

If init@ else
   
GOSUB z_init:                                  ; // each function has to manage its own
end

EQU CACHENAME$ TO com                             ; // cache is same as COMMON

BEGIN CASE
   
CASE action[1,1] _eqc nvc$create    ; GOSUB do_create:
   
CASE action[1,1] _eqc nvc$read      ; GOSUB do_read:
   
CASE action[1,1] _eqc nvc$delete    ; GOSUB do_delete:
   
CASE action[1,1] _eqc nvc$dict      ; GOSUB do_dict:    
   
CASE action[1,1] _eqc nvc$update    ; GOSUB do_update:
   
CASE action[1,1] _eqc nvc$zap       ; GOSUB z_zap:
   
CASE action[1,1] _eqc nvc$tos       ; GOSUB do_tos:
   
CASE action[1,1] _eqc nvc$select    ; GOSUB do_select:
   
CASE action[1,1] _eqc nvc$xlate     ; GOSUB do_xlate:
   
CASE action[1,1] _EQC nvc$nocache   ; GOSUB z_nocache:  
   
CASE 1  ; void  = nv_error("XA00",action)
END CASE
RETURN retval

//////////////////////////////////////////// ////////////////////////////////////
* clear:
*   if assigned(ropcFh@) then
*       rtp65 (rtp65_CLEAR$, ropcFh@, "", "", "", status)
*   end  
* RETURN

create:
   rtp65 (rtp65_CREATE$,
"",  CACHENAME$, "", "", status)   
   rtp65 (rtp65_OPEN$, ropcFh@, CACHENAME$,
"", "", status)
   rtp65 (rtp65_CLEAR$, ropcFh@,
"", "", "", status)
   x = ropcFh@
   retval = ( status =
0 )
RETURN

do_create:
   
GOSUB do_update:
RETURN

do_delete:
   status  =
""
   rtp65(rtp65_READ$, ropcFh@, id, buffer,
"", status)
   retval = ( status =
0 )                             ; // Return true for success
   
DELETE f$f, id ELSE
       
NULL                                            ; // TODO some better logic - really
   
END
RETURN

do_dict:
   
RETURN

do_read:
   buffer  =
""
   trip_me_up =
0        ; // just slow logic to check for NULL records - should be deleted
   
   
IF NOT(dont_cache@) THEN
       status  =
""
       fh      = ropcFh@
       rtp65(rtp65_READ$, ropcFh@, id, buffer,
"", status)
       
       
//
       
// If this fails - go to DISK
       
//
       
       
IF LEN(buffer) THEN
           read_from_cache@    +=
1
           
RETURN
       
END ELSE
           missed_from_cache@  +=
1
           trip_me_up =
1
       
END
   
END
   
   
READ buffer from f$f, id THEN
       
LOCATE id IN id_list@ BY "AR" USING @FM SETTING pos THEN
           
       
END ELSE
           id_list@    = INSERT(id_list@,pos,
0,0,id)           ; // testing, just to verify
       
END
       
       
IF LEN(buffer) = 0 AND trip_me_up = 1 THEN
           trip_me_up -=
1
           missed_from_cache@  -=
1
           null_size_records@  +=
1
       
END ELSE
           read_from_disk@     +=
1
           
GOSUB z_write:                                    ; // store it in RAM for later
       
END
   
END ELSE
       buffer =
""
       missed_from_disk@   +=
1
   
END
RETURN

do_remove_cache:
   cache_status    =
0
   status          =
0
   
//  if name # "" then
   rtp65(rtp65_DELETE$, ropcFh@, id,
"", "", status)
   
//  end
   retval = ( status =
0 )                                    ; // Return true for success
RETURN

do_select:
   
RETURN

/*
* TO_STRING
*/

do_tos:
   retval =
""
   retval<-
1> = "init@ :":init@
   retval<-
1> = "f$f   :":f$f
   retval<-
1> = "table_name@ :":table_name@
   retval<-
1> = "id_list@   #:":DCOUNT(id_list@, @FM)
   retval<-
1> = "ropcFh@     :":ropcFh@
   retval<-
1> = "read_from_cache@   :":read_from_cache@
   retval<-
1> = "read_from_disk@    :":read_from_disk@
   retval<-
1> = "missed_from_disk@  :":missed_from_disk@
   retval<-
1> = "missed_from_cache@ :":missed_from_cache@
   retval<-
1> = "null_size_records@ :":null_size_records@
   retval<-
1> = "i@, j@, k@, l@(10), m@, n@, o@"
RETURN

/*
* On Write, we have to write to DISK as well as into RAM. Into RAM, so that we can read it immediately
*/

do_update:
   rtp65(rtp65_WRITE$, ropcFh@, id, buffer,
"", status)       
   status() = status
   
GOSUB z_write:
RETURN

do_xlate:
   
RETURN

/*
* RPT65 Private Methods
*/


open_file:
   status =
""
   rtp65 (rtp65_OPEN$, ropcFh@, CACHENAME$,
"", "", status)
   
if status then
       
GoSub Create
   
end
   
   retval = ( status =
0 )
return

/*
* Init the environment for this one table
* Actual file for the Write
*/

z_init:
   isOK        = (nv_file(
"O", table_name, fileunit) = 0)
   
IF isOK THEN
       
TRANSFER fileunit TO f$f
       init@       =
1
       table_name@ = table_name
       cache_started@  = date():time()                      ;
// need tickcount
   
END ELSE
       
// 12/04/13 HOW to flag fileNot open so as not to abort into dbg
       
RETURN                                               ; // nothing to cache is there?
   
END
   
   status  =
""
   rtp65 (rtp65_CREATE$,
"",  CACHENAME$, "", "", status)   
   rtp65 (rtp65_OPEN$, ropcFh@, CACHENAME$,
"", "", status)
   rtp65 (rtp65_CLEAR$, ropcFh@,
"", "", "", status)
   x = ropcFh@
RETURN

z_nocache:
   dont_cache@ =
1
RETURN

z_reset:
   status  =
""
   rtp65(rtp65_CLOSE$ , ropcFh@,       
"", "", "", status)
   rtp65(rtp65_DELETE$, ropcFh@, CACHENAME$,
"", "", status)
return

/*
* CLEARCOMMON for this table
*/

z_zap:
   
GOSUB do_tos                                                  ; // do something useful
   
TRANSFER retval TO x
   
GOSUB z_reset
   
TRANSFER x TO retval
   init@               =
""
   f$f                 =
""
   table_name@         =
""
   id_list@            =
""
   ropcFh@             =
""
   read_from_cache@    =
""
   read_from_disk@     =
""
   missed_from_cache@  =
""
   missed_from_disk@   =
""
   null_size_records@  =
""
   
freecommon  comm_Name@
RETURN

z_write:
   status =
""
   
IF LEN(id) THEN
       
//
       
// This is the hard write to DISK
       
//
       
WRITE buffer TO f$f, id ELSE
           
NULL
           
RETURN                            ; // can't pretend it's in RAM, if not ON DISK
       
END
       
       
IF assigned(ropcFh@) ELSE
           
GOSUB Open_File
           
// check that's it's actually open then
       
END
       rtp65(rtp65_WRITE$, ropcFh@, id, buffer,
"", status)       
       status() = status
   
END       
RETURN

Param definitions ( $INSERT nvc_common_equates )

 

Thankfully OI allows an $INSERT within an $INSERTed file, therefore we include this definition of the calling parameters (for readability sake).

You'll notice that our //common// definition has some random (hitherto unused variables) - that's so that when we dream up something new, that we don't need to recompile 100 programs to cater for changes in common definition.


compile INSERT nvc_common_equates
////////////////////////////////// ////////////////////////////////// /////////////////////////
////////////////////////////////// ////////////////////////////////// /////////////////////////
//                                                                                              
// This program is proprietary and is not to be used by or disclosed to others, nor is it to    
// be copied without written permission from Envizion Systems Pty Ltd.
//      
// Name       :                                                     
//                                                                                              
// Description: define per file common area Using ins guarantees consistency across all data code
//
// * @package   PowerForce
// * @author    Martin Drenovac (for Envizion Systems Pty Ltd)
// * @copyright Copyright (c) 2007 - 2013, Envizion Systems Pty Ltd
// * @licence   http://powerforcesoftware.com/user/license.html
// * @link      http://powerforcesoftware.com
// * @since     Version 1.0
// * @filesource
//                                                                                                      
// History (Date, Initials, Notes)                                                              
// 04/04/13 mpd Original programmer
// 08/04/13 mpd nvc_ EQUATES
// 11/04/13 mpd nvc_dont_cache$ - do not cache this table
// 17/04/13 mpd cache_started@  - what time was the cache kicked off. Allows aging
//                                                                                              
// * Date: 2013-04-04 17:34:21 +1000 (Thu, 04 Apr 2013)
//////////////////////////////// //////////////////////////////// /////////////////////////////
// set file_id to the TABLE being managed
// init@        flag whether initialised
// f$f          the fileUnit to CRUD
// table_name@  'TABLE_NAME'
// id_list@, ropcFh@, read_from_cache@, |
//      read_from_disk@, missed_from_cache@, missed_from_disk@, null_size_records@, |
//      dont_cache@, j@, k@, l@(10), m@, n@, o@
///////////////////////////////// ///////////////////////////////// ///////////////////////////

equ COMMON_PREFIX$  to "%NVC_"
comm_Name@ = COMMON_PREFIX$: table_name
common //comm_Name@// init@, f$f, table_name@, id_list@, ropcFh@, read_from_cache@, |
       read_from_disk@, missed_from_cache@, missed_from_disk@, null_size_records@, |
       dont_cache@, cache_started@, k@, l@(
10), m@, n@, o@
//

*   if Assigned(init@) Else init@ = 0
*   
*   If init@ else
*       GOSUB zinit:                              ; // each function has to manage its own
*   end

EQU nvc$create  TO  "C"
EQU nvc$delete  TO  "D"
EQU nvc$dict    TO  "I"
EQU nvc$nocache TO  "N"                            ; // DICT - all other letters taken
EQU nvc$read    TO  "R"
EQU nvc$select  TO  "S"
EQU nvc$tos     TO  "T"
EQU nvc$update  TO  "U"
EQU nvc$xlate   TO  "X"
EQU nvc$zap     TO  "Z"

/*
*       Compile Insert Cache_Mfs_Common
*       
*       EQU CREATE_CACHE$  TO 0
*       EQU OPEN_CACHE$    TO 1
*       EQU CLOSE_CACHE$   TO 2
*       EQU CLEAR_CACHE$   TO 3
*       EQU READ_CACHE$    TO 4
*       EQU WRITE_CACHE$   TO 5
*       EQU DELETE_CACHE$  TO 6
*       EQU MAX_CACHE_CNT$ to 100
*       
*       EQU CACHE_HANDLE_DELIM$ to \F7\
*       Equ CACHE_HANDLE_POS$ To 6
*       
*       declare subroutine rtp65
*       
*       common /cache_mfs_Common/init@, handle_cnt@, names@, file_handles@(max_cache_cnt$), cache_handles@, full_cache_flags@
*/


Getting started & things to look out for


..This code represents our mindset and reflects how our application is coded.

Download the code as indicated below in total

Change the name of a table in "nvc_sysusers" to reflect your table, and then run the test_nvc_sys_users to see the output & familiarise yourself to the code logic.


..

Code Downloads

Use the following links to download the code:


TODO ( current ideas awaiting coding time )

  • How do you manage DICT.[table_name] under this framework
  • Code for XLATE - re-code nv_xlate to utilise nvc_[table_name]
  • How do you know when to 'zap' cache, so it starts again - as when an update is made to a reference record. Investigating using memcache for this as the mechanism of application / enterprise wide synchronisation
  • How will nvc_* work under web?