1 / 50

What’s New in the OpenEdge Database

What’s New in the OpenEdge Database. Norwegian PUG Event. Richard Banville, Fellow, OpenEdge Development April 8 , 2014. Introduction. Parallel development between OpenEdge 10.2B and OpenEdge 11 Including rollout of some enhancements in the older releases

tod
Download Presentation

What’s New in the OpenEdge Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. What’s New in theOpenEdge Database Norwegian PUG Event Richard Banville, Fellow, OpenEdge Development April 8, 2014

  2. Introduction • Parallel development between OpenEdge 10.2B and OpenEdge 11 • Including rollout of some enhancements in the older releases • Some OE 10.2B releases occurred after OE 11.0 shipped • This confuses where some features exist • Release information provided with each topic • Indicates there and above • Not a deep dive into each feature • Just going to scratch the surface

  3. Agenda • Major Features • Not So Major Features • Relatively Unknown or Forgotten Features

  4. Major Features

  5. Transparent Data Encryption – TDE (OE 10.2B) Very low overhead (0 – 4%) • Application transparent data encryption • Full index query support • No need to move data Transparent • Encrypt individual objects or areas • Can change encryption policies online • Storage engine encrypts blocks on disk Flexible • Provides secure encryption key storage • Support industry standard ciphers • Limits access to physical data Secure

  6. OpenEdge Transparent Data Encryption (TDE) How Does It Work? Database Storage Engine Write I/O Keys Encrypted Data Encrypt Shared Memory Buffer Pool (plain text block) & plain text Decrypt PolicyArea KeyStore Database Read I/O Policies Product Install • Key store • Database Master Key (DMK) • Admin/User Passphrase • Manual/Automatic • Encryption Policy Area • Encryption Policies • What to encrypt • How to encrypt (cipher)

  7. Other Encryption Features • Encrypted backup/restore • Optionally encrypt binary data dump • Migration • Encrypt data over time or immediately • No application changes • Full index capabilities on encrypted data • Supporting utilities • Enable/disable • Epolicy manage <area | table> rekey | update | encrypt • Epolicy scan • Table/index move w/possible re-encryption • Can change encryption policies online

  8. Multi-tenant Tables (OE 11.0) Simplifies Development of Multi-tenant Applications Multi-tenancy Car Rental Application • Ease maintenance & app. upgrades • Basic multi-tenant paradigm • Multi-tenant tables built into the database • Tenants share same schema definition • Data physically partitioned by tenant identity • Minimal application changes • Just establish tenant identity (per database) Customer 1 Ben Affleck 2 Mark Wahlberg 3 Steve Carrell Tenant 1 (Hertz-Boston) 4 Robin Williams 7 Harrison Ford 8 Berrnie Mac Tenant 2 (Hertz-Chicago) 2 Dennis Rodman 7 Nick Nolte 9 Lindsay Lohan 10 Charlie Sheen Tenant 3 (Rent-a-wreck) *Fictitious example

  9. Multi-tenant Tables (OE 11.0) Simplifies Development of Multi-tenant Applications Multi-tenancy Car Rental Application • Index keys unique per tenant partition • Shared & tenant sequences/tables • Query is tenant specific • Authenticate as tenant • _User • Client Principal • SQL only _users • Assert tenant identity • Useful for database consolidation • Same application & schema Customer Tenant 1: Hertz-Boston 1 Ben Affleck 2 Mark Wahlberg 3 Steve Carrell FIND CUSTOMER WHERE cust-num=2. 4 Robin Williams 7 Harrison Ford 8 Berrnie Mac Tenant 2 (Hertz-Chicago) 2 Dennis Rodman 7 Nick Nolte 9 Lindsay Lohan 10 Charlie Sheen Tenant 3 (Rent-a-wreck) *Fictitious example

  10. Multi-tenant Tables (OE 11.0) Simplifies Development of Multi-tenant Applications Multi-tenancy Car Rental Application • Keys unique per tenant partition • Shared & tenant sequences/tables • Query is tenant specific • Authenticate as tenant • _User • Client Principal • SQL only _users • Assert tenant identity • Useful for database consolidation • Same application & schema Customer Tenant 3: Rent-a-wreck Tenant 1: Hertz-Boston 1 Ben Affleck 2 Mark Wahlberg 3 Steve Carrell FIND CUSTOMER WHERE cust-num=2. FIND CUSTOMER WHERE cust-num=2. 4 Robin Williams 7 Harrison Ford 8 Berrnie Mac 2 Dennis Rodman 7 Nick Nolte 9 Lindsay Lohan 10 Charlie Sheen *Fictitious example

  11. Multi-Tenancy Enhancements • Resource Governors (OE 11.1) • -LGovernor - % of lock table any one tenant (user group) is allowed to occupy • -nGovernor – Number of database connections any one tenant is allowed to consume • Tenant Index Rebuild online (OE 11.3) • proutil <db> -C enablemtidxbld (online & offline) • proutil <db> -C mtidxbuild table <name> index <name> tenant | group <name> • Acquires EXCLUSIVE table partition lock • Tenant data for associated table is restricted to no-lock • Index being rebuilt is offline • Can run multiple concurrently • Data access to table available for all other tenants and other tables of this tenant proutil<db> -C enablemtidxbld proutil<db> -C mtidxbuildtable <db> index <name>tenant | group <name>

  12. Multi-Tenancy Enhancements • Tenant Data Recovery (OE 11.3) • Basically a restore, roll forward, binary transfer between databases • Proutil <db> -C datamove source <sourcedb> [ tenant <name> [ table <name> ] | [ sequence < name> ] [ recs <n> ] • Schema validation performed automatically proutil<db> -C datamovesource <db>tenant <name> table <name> sequence <name> [recs <n>] Progress OpenEdge Tenant/Group Data Move Source Database: <source_db> Destination Database: <dest_db> 1. Move Table Data by Tenant 2. Move Sequence Data by Tenant 3. Move Table Data by Group 4. Set Records per Transaction ( Current: 100 ) 5. Quit Enter your selection: Table Instance Control Txn Size

  13. Not So Major Features

  14. Utility Enhancements • Roll forward enhancements (OE 11.3) • rfutil <db> -C roll forward –ailist<ailist- name> • Can now specify a list of files to roll forward rather than individual ones • Avoids redo on each restart • Also per file completion status information provided • Dbrestrict (OE 11.3) • Restricts access to the database for processes other than the specified operation • Unlike “oplock”, allows access by certain read-only activities and prostrct add • For “datamove” allows DB Broker, promon & proshut • Prorest can enable –dbrestrict too! rfutil <db> -C rollforward –ailist<ailist-name> 10:01:10: 50% of aimage file processed (100 notes processed)... proutil<db> -C dbrestrict[ datamove | rollforward ] [ enable | disable | status]

  15. Utility Performance • Online Backup Pause (OE 11.3) • Requires new backup header version for bi cluster information • Applies to online only • Not backward compatible • On by default • Use –bibackup all option for backward compatibility • Backup active clusters only • Skips unused clusters regardless of where they are in the cluster ring or bi file • Restore will reorder cluster to compact state probkup online <db> [ incremental ] <device-name>[ bibackup {active | all } ] [enableai ] [ enableaiarchiver – aiarchdir <dir-list > ]

  16. Index Rebuild Performance (OE 10.2B06, OE 11.2) sort block size (8K – 64K, note new limit) -TB 64 # threads for data scan phase -datascanthreads 1.5 X #CPUs merge block size ( default -TB) -TMB 64 -TF merge pool fraction of system memory (in %) 80% -mergethreads # threads per concurrent sort group merging X -threadnum = 1.5 X #CPUs -threadnum # concurrent sort group merging 2 or 4 -TM # merge buffers to merge each merge pass 32 -rusage report system usage statistics -rusage -silent a bit quieter than before -silent

  17. Index Rebuild Performance (OE 10.2B06, OE 11.2) sort block size (8K – 64K, note new limit) -TB 64 # threads for data scan phase -datascanthreads 1.5 X #CPUs merge block size ( default -TB) -TMB 64 12 ½ hours  2 ½ hours 5X improvement! -TF merge pool fraction of system memory (in %) 80% -mergethreads # threads per concurrent sort group merging X -threadnum = 1.5 X #CPUs -threadnum # concurrent sort group merging 2 or 4 -TM # merge buffers to merge each merge pass 32 -rusage report system usage statistics -rusage -silent a bit quieter than before -silent

  18. Networking Control Philosophy: Throughput by keeping server busy without remote client waits! • Process-based control • -Ma, -Mn, -Mi • Controls the order users are assigned to servers • Resource-based control • -Mm <n> • Maximum size of network message • Client & server startup • New tuning knobs – resource-based control • Alleviate excessive system CPU usage by network layer • Control record data stuffed in a network message • Applicable for “prefetch” queries

  19. Networking – Prefetch Query • No-lock query with guaranteed forward motion or scrolling • Multiple records stuffed into single network message • Browsed static and preselected queries scrolling by default DO PRESELECT EACH customer NO-LOCK: ….end. FOR EACH customer NO-LOCK: ….end. define query cust-q for customer SCROLLING. open query cust-q FOR EACH customer NO-LOCK. repeat: get next cust-q. end.

  20. -NmsgWait (OE 10.2B06 & OE 11.1) TCP I/O FOR EACH customer NO-LOCK: ….end. Remote Client Server -Mm 8192 • Determines how long a server waits for remote request • Benefit: Could reduce polling thereby reducing system CPU • Detriment: Could make remote client wait longer for a lock • Tuning suggestions: • There are better ways to reduce polling. • Leave it alone • Set it to 1 (default is 2)

  21. -prefetchPriority (OE 10.2B06 & OE 11.1) TCP I/O FOR EACH customer NO-LOCK: ….end. Remote Client Server -Mm 8192 • Defers check (poll) for requests from other remote clients while network buffer is populated with multiple records • Reduces System CPU costs • Tuning suggestions: • Set to 100 (worked best in my tests) • Some field testing suggests 50 is best • Watch out for slower response to non-prefetch queries

  22. -prefetchDelay (OE 10.2B06 & OE 11.1) TCP I/O FOR EACH customer NO-LOCK: ….end. Remote Client Server -Mm 8192 • Avg record size = 200 bytes • ~40 records per msgmaximum (-Mm 8192) • Puts multiple records in first message when set. • If not set, first response message only contains 1 record • Benefits: • Can improve browse widget window population • Can reduce TCP I/O for a single query by 1 • Tuning suggestions: • In theory, enabling this should improve performance • I have seen no difference in my testing

  23. -prefetchNumRecs and –prefetchFactor (OE 10.2B06 & OE 11.1) TCP I/O FOR EACH customer NO-LOCK: ….end. Remote Client Server -Mm 8192 • Record size and –Mm matter! • Avgrecord size = 200 bytes • ~40 records per msg maximum • Too small –Mm negates affect • Determines how many records are “stuffed” in a message • Number of records vs percentage full • Reduces network traffic and improves TCP/IP I/O efficiency • Tuning suggestions: • -prefetchNumRecs100 records • Reduce if remote clients complain of “choppy” behavior • Default is 16 records

  24. Enhancements to Database Buffering System Memory • Memory access much faster than disk access • Reduce read and write I/O via caching • Memory is finite • Working set concept • Managing the buffer pool • Primary buffer pool • Alternate buffer pool • Private Buffers • Replacement mechanisms Process Shared Memory DatabaseBuffer Pool BiBuffers AiBuffers File system cache I/O Multi level caches Multi level caches .d .d .d .d .d .d .d .d .b .a

  25. 1024 144 256 128 96 64 The Database Buffer Pool LRU MRU LRU buffer eviction policy Primary Buffer Pool -B buffers Database Buffer Lookup Buffer pool hash table • Database block lookup via single hash table • Replacement policy via LRU mechanism • LRU vs MRU end of replacement chain • Manages “working set” well • Easily “fouled” • High housekeeping cost

  26. 1024 144 256 128 96 64 Reducing “Housekeeping” Costs (OE 10.2B06 and OE 11.1) MRU LRU LRU buffer eviction policy Primary Buffer Pool -B buffers Database Buffer Lookup Buffer pool hash table • -lruskips <n> (-lru2skips for alternate buffer pool) (OE 10.2B06 and OE 11.1) • Skip LRU maintenance for <n> accesses • LRU and LFU (least frequently used) combined • Small numbers make a BIG difference (100 works well) Promon, R&D 4. Administrative Functions ... 4. Adjust Latch Options 4. Adjust LRU force skips: 100 FIND _Startup. ASSIGN _Startup-LRU-Skips = 100.

  27. 48 1024 132 144 256 128 64 128 96 164 64 92 Alternate Buffer Pool (OE 10.2B) MRU LRU LRU buffer eviction policy Primary Buffer Pool -B buffers • 1 buffer pool cache • 1 Hash table • Multiple LRUreplacement chains Buffer pool hash table Database Buffer Lookup Alternate Buffer Pool -B2 buffers LRU2 buffer eviction policy(only when needed) MRU LRU • Object specific data managed separately • Reduces “housekeeping” cost • Data can become resident in memory • Data Dictionary (by table/index/LOB) • proutil enableB2 (by storage area) • proutil viewB2 option

  28. 48 1024 200 160 132 144 48 256 128 128 64 80 128 164 96 40 40 92 64 104 96 Private (per user) Read-only Buffers (little known/used category) MRU MRU LRU LRU MRU LRU LRU buffer eviction policy • Selfless act for table scan users • Will not improve performance of user • Helps others since it avoids fouling of LRU mechanism • Use it for operations like dbanalys or table scan queries • Intended to improve performance overall Primary Buffer Pool -B buffers Database Buffer Lookup Buffer pool hash table -Bp (per user) eviction policies Alternate Buffer Pool -B2 buffers LRU2 buffer eviction policy(only when needed) MRU LRU

  29. 48 1024 200 160 144 132 48 128 256 128 64 128 80 164 96 40 40 92 64 104 96 Private (per user) Read-only Buffers (little known/used category) MRU MRU LRU LRU MRU LRU LRU buffer eviction policy Primary Buffer Pool -B buffers Database Buffer Lookup Buffer pool hash table -Bp (per user) eviction policies Alternate Buffer Pool -B2 buffers • Values • Default: 0; Max 64 per client (cumulative total limited to 25% of –B) • Change per client maximum with -Bpmax at db startup • Set # buffers via -Bp at client startup • Can be changed at runtime via _MyConnection._Myconn-NumSeqBuffers LRU2 buffer eviction policy(only when needed) MRU LRU

  30. Some Tuning Suggestions • -lruskips (OE 10.2B06 & OE 11.1) • 100 seems to work well • Only helps if LRU latch contention • -B2 (OE 10.2B) • Set large enough to hold very active “small” tables • Include indexes as well as the table • -Bp (V 9.0A) • Set to # tables in query + depth of each index used • 2 way join query: 8 to 10 will work well • Use for table scan type queries • Application as well as utilities (dbanalys) • -B • Tune to acceptable read I/O rates, not buffer pool hit ratio

  31. Auditing (OE 10.1A) • Policy based (flexible) • Change capture internal to database • Audit logs highly protected • Create, update and delete change capture • (no read capture provided) • Application events • Archive for reporting • Reclaim Audit Space (OE 11.2) • proutil <db> -C auditreconfig [ tablearea <name> ] [ indexarea <name> ] • Re-creates audit tables and indexes in new area • Drops old audit tables and indexes • Existing data is truncated • Carefully use prostrct truncate/remove to reclaim area’s disk space proutil<db> -C auditreconfig[ tablearea <name> ] [ indexarea <name>

  32. OpenEdge Replication

  33. Maintain a “hot” standby with OpenEdge Replication (V9.?)

  34. OpenEdge Replication Enhancements • Increase max –pica (OE 11.2) • Database Service Communication Area Size (-pica) • Maximum value of increased from 512 to 1,000,000 • Avoid database “slowdown” when target not keeping up • JTA & Replication (OE 11.2) • Allow JTA distributed transactions and OpenEdge Replication

  35. OpenEdge Replication Enhancements • Table move/index move (OE 11.3) • Now propagated to target • -asc <n>: asynchronous level (relatively unknown category) • Default is 10, Max is 500 • Disabled with setting of 501 • Server tells agent # blocks to be sent before ACK • Increasing can greatly improve performance • Set in pmmgr.properties file replserv option [replserv]   arguments=-logging 2 –asc 501

  36. Relatively Unknown orForgotten Features

  37. Misc Parameters • -keeptargetlfe (OE 10.2B04) • Keep target large file enabled (lfe) • Restores a backup without enabling large files • By default: Target = Source on restore prorest <target-db> <source-bak> [ -keeptargetlfe ]

  38. Client Database-Request Statement Caching (OE 10.1C) • What is it? • Client execution stack trace dump • Includes: Line #, procedure name, file name • When should you use it? • Diagnosing deployed application problems • Local and remote access • What’s in a name? • Don’t confuse with other “statement caches” • Documentation • OpenEdge Data Management: Database Administration

  39. Enablement – Multi-level activation promon  R&D  1. Status Displays ... 18. Client Database-Request Statement Cache ... 1. Activate For Selected Users 2. Activate For All Users 3. Activate For All Future Users 4. Deactivate For Selected Users 5. Deactivate For All Users 6. Deactivate For All Future Users 7. View Database-Request Statement Cache 8. Specify Directory for Statement Cache Files Activate View Location

  40. Procedure Call Stack It’s just a LIFO… • Top is last procedure executed • Bottom is first procedure executed • Top down, newest to oldest • One time full stack • Continuous full stack • Continuous current location #Procedure NameFile Name 19 : reallyLongNamedInternalProcedure3 proctestb.r 12 : reallyLongNamedInternalProcedure2 proctestb.r 5 : reallyLongNamedInternalProcedure1 proctesta.r445 : reallyLongNamedInternalProcedure0 proctesta.r 1 : /usr1/stmtest/p72340_Untitled1.ped Top Newest Oldest Bottom

  41. Manual Stack Generation • Stack also available via “prostack” or “progetstack” • Useful for “non-responsive” connections • Available with 10.1c • Must have access to client’s machine • kill –SIGUSR1 <pid> (Don’t forget the dash) • protrace.<pid> • Startup parameters • Execution stack • ** ABL Stack Trace ** (OE 10.2B) • ** Persistent procedures/Classes ** • ** PROPATH ** • ** Databases (logical/type/physical) **

  42. Logical Table A Table B Index A-1 Index B-1 Name Idx Cust# Idx SRep Idx Index D-1 Index D-2 Cust Tbl Table D Schema Object Mapping Cache - OM Latch: OM Lookup and LRU Languages Mapping Get _StorageObject record

  43. Logical Table A Table B Index A-1 Index B-1 Name Idx Cust# Idx SRep Idx Index D-1 Index D-2 Cust Tbl Table D Schema Object Mapping Cache - OM Latch: OM Lookup and LRU Languages Mapping -omsize Primary Cache No latching, no paging Get _StorageObject record Zero contention! Hash Table OM cache loaded at startup

  44. Logical Table A Table B Index A-1 Index B-1 Name Idx Cust# Idx SRep Idx Index D-1 Index D-2 Cust Tbl Table D Schema Object Mapping Cache - OM Latch: OM Lookup and LRU Languages Mapping OM -omsize OM latch needed forpaging maintenance Secondary Cache Primary Cache No latching, no paging Get _StorageObject record Zero contention! Little contention Hash Table Hash Table OM cache loaded at startup 2nd cache for over flow & new objects

  45. How to manage Object Mapping Cache • Do I have a problem? • Check latch statistics define variable prev-latches as integer. repeat: find _latch where _latch-name = "MTL_OM". display _Latch-Name _Latch-Lock /* # times latch acquired */ _Latch-Wait /* # time conflict occurred */ _Latch-Lock - prev-latches label "latch/sec". prev-latches = _Latch-Lock. pause 1. end. Activity Contention

  46. How to manage Object Mapping Cache • Do I have a problem? • Check latch statistics • Identify proper value • Set it properly on startup to avoid eviction & latch contention • (V9.0a !) • Memory used: ~192 bytes per entry • Default 1024 = ~384K • This is shared memory • 1 primary and 1 secondary cache per database select count(*) from _storageObject proserv <db> -omsize <n> …

  47. Altering Performance Related Parameters Online

  48. Increase Resource Startup Parameters Online (OE 10.1C) • Parameter list • -aibuf, -bibufs • -B, -B2 • -L • -omsize • -mtpmsize(OE 11.0) • -Mxs • Increase, not decrease • New segment restrictions • Kernel restrictions apply • Security restrictions • Servers: automatically attach quickly • Self serve: attach w/db action over time • Minimum segment size created: 128 MB proutil<dbname> -C increaseto <- param> <value>

  49. OpenEdge Database Enhancement Summary • Major Enhancements • Transparent Data Encryption • Multi-tenancy • Other Enhancements • Runtime tuning • Utility Performance • Application insights • Auditing • Little known or forgotten • Private buffers • Om cache (-omsize) • Changing parameters online

More Related