G
George Potemkin
Guest
Statement Cache is a very useful feature but it's also a rather dangerous thing. Some issues were discussed by Gus Bjorklund at Exchange 2011 OE1111: What's That User Doing? The Progress OpenEdge Client Request Statement Cache download.psdn.com/.../OE1111.wmv community.progress.com/.../2255.what-s-going-on-database-client-monitoring Here is a list of issues: 1. The scratch files (*.cst) are created by default in the user's working directory (or $HOME?). To read the _Connect records the other sessions should have permissions to read the files in the directory of a user with enabled statement cache. Obviously a security department demands the opposite. Defect OE00237502 / PSC00258643 knowledgebase.progress.com/.../000040544 The workaround is use promon/R&D/1/18/8. Specify Directory for Statement Cache Files 2. Enabling statement cache will dynamically allocate the space in shared memory: 288 bytes per session. Progress will use the -Mxs memory. But we can check the _Segment-ByteFree for last segment. 3. Enabling statement cache adds approximately 25% overhead for remote Clients. 4. Enabling statement cache creates an additional activity on the USR latch: every update of statement cache will generate 2 USR latch locks. And it's a main performance killer for the self-service connections. Example: Statement cache was enabled only for 14 most active users (of 1200 running users). 08/02/16 Activity: Latch Counts 11:20:50 08/02/16 11:20 to 08/02/16 11:20 (4 sec) ----- Locks ----- Naps Owner Total /Sec /Sec USR -- 397706 99426 3 14 sessions have created 99,426 USR latch locks per sec. The naps are really low but USR is a queued latch - the main waits are on semaphores: 08/02/16 Activity: Other 11:20:50 08/02/16 11:20 to 08/02/16 11:20 (4 sec) Total Per Sec Per Tx Commit 1092 273.00 1.00 Wait on semaphore 1060 265.00 0.97 Non-blocking waits 0 0.00 0.00 Semaphore latch waits 1043 260.75 0.96 08/02/16 Activity: Performance Indicators 11:20:50 08/02/16 11:20 to 08/02/16 11:20 (4 sec) Total Per Sec Per Tx Commits 1092 273.00 1.00 Total waits 17 4.25 0.02 Lock waits 0 0.00 0.00 Resource waits 17 4.25 0.02 Latch timeouts 314 78.50 0.29 (Wait on semaphore + Non-blocking waits + Semaphore latch waits) = (Total waits) on the Activity: Performance Indicators screen ??? Not in Progress V11.6.1 on AIX. So after enabling statement cache the most active sessions were waiting on semaphores. And it could be much worse if we would activate the caching for all users. 5. "Radioactive contamination" of the usrctl slots by USR latches. After disabling statement cache the reading of the corresponding _Connect record will always creates 2 USR latch locks - it does not matter if the old session still exists or if the slot in the usrctl table was already re-used by new session or if it's not currently in use. It's a minor issue provided there are no sessions that are constantly reading the _Connect records. We can easy use the statement cache to check what the sessions are doing /right now/ - a few short sampling intervals (1 sec or so) would be enough. But taking into account the above issues it would be a bad idea to activate the statement cache for a long period of time. What would be a best strategy to monitor the statement cache during /long/ period of time (for example, during 24 hours)? Let's say a session creates a high db access during last 5 min interval. Should we trigger the short interval monitoring to check what programs are running "right now" by enabling "One Time" (type 3) statement caching? Only once per 5 min interval or a many times using one sec intervals? How to gather the statistics about long running Progress sessions with the least possible impact on production environment? Best regards, George
Continue reading...
Continue reading...