1 / 47

Trademarks (Omissions are unintentional)

Evolving DB2 CPU and Response Metrics Ned Diehl The Information Systems Manager, Inc. ned.diehl@perfman.com www.perfman.com 610-865-0300 Philadelphia CMG 17 November 2007. ISM PerfMan IBM Parallel Sysplex RMF z/OS zIIP. MVS DB2 CICS IMS VTAM SAP.

julianna
Download Presentation

Trademarks (Omissions are unintentional)

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. Evolving DB2 CPU and Response MetricsNed DiehlThe Information Systems Manager, Inc.ned.diehl@perfman.comwww.perfman.com610-865-0300Philadelphia CMG17 November 2007

  2. ISM PerfMan IBM Parallel Sysplex RMF z/OS zIIP MVS DB2 CICS IMS VTAM SAP Trademarks (Omissions are unintentional)

  3. Objectives • Discuss sources of DB2 CPU and response metrics with focus on DB2 SMF records • Traditional MVS performance analyst and capacity planner is target audience • Present graphical examples

  4. Disclaimers • While all examples shown use real data, they are sometimes selected to make a point. Thus they do not necessarily represent optimally running systems. • “Good” performance can be very subjective and depends on what the objectives are. • Specific field names are listed, but it is best to validate spelling and definition prior to use. Field names can be release dependent.

  5. Contents • zIIP Overview • DB2 Structure • Data Sources • Key Performance Metrics • Recommendations • Summary • Record Layouts • Glossary • References

  6. zIIP Overview • zIIP is the most recent addition to the family of specialty processors • Others include zAAP (Java), IFL (Linux), ICF (Coupling Facility), and SAP (System Assist Processor) • Initially available on System z9 • Cost effective offloading of eligible workloads from standard central processors (GP CP-s) • Each zIIP engine costs significantly less than a GP CP • No IBM software costs • There can be as many zIIP-s as GP CP-s • There can also be other specialty processors • Enclave SRB-s are potential units of work • These are preemptible SRB-s (pre-SRB) • Exploitation started in DB2 V8 with PTF-s • Other vendors and IBM functions have added support • Managed by WLM

  7. zIIP OverviewDB2 Support • DB2 V8 enabled portions of the following for zIIP: • DDF SQL that uses DRDA and TCP/IP • Approximately 50% is eligible • LOAD, REORG, and REBUILD INDEX utility work • Star schema parallel query processing • DB2 V9 added portions of the following: • Children of long running parallel queries • Over 100ms of CP time • Native SQL stored procedures • Stay in pre-SRB mode rather than switching to TCB • Most other stored procedure time is not zIIP eligible • SAP exploited DDF starting with DB2 V8 • Previously it used RRSAF

  8. DB2 Structure

  9. DB2 StructureSystem Address Spaces • MSTR or SSAS - Overall Control • DBM1 or DBAS - Database Services • Buffer Pools • EDM Pool • IRLM - Internal Resource Lock Manager • DIST or DDF - Distributed Data Facility • SPAS - Stored Procedures (Programs) • Primarily Client/Server

  10. DB2 StructureGeneral • Evolving architecture and data sources • While many key metrics are not old, new functions often accompanied with related measurements • Exploits multiple engines & SYSPLEX • Requestors can have multiple connections (e.g. CICS) • Minimal performance value to multiple production DB2s on an image • Uses Sub System Interface (SSI) • Cross memory services for local • Coupling facility for data sharing • TCP/IP and VTAM for distributed • Preemptible (Enclave & Client) SRBs for DDF and parallel processing

  11. Data SourcesOverview • RMF - Good starting point • SMF 30 - Job accounting • SMF 42-6 - Dataset performance by job • SMF 100 - DB2 System Functions • SMF 101 - DB2 Accounting • SMF 102 - DB2 Statistics (“GTF” for DB2) • Requestor Measurements - CICS 110 & IMS log

  12. Data SourcesRMF • Most application CPU time reported with requestor • DIST AS is requestor for server work • Separate 72 records for each DDF enclave service class include most server application CPU time • CPU usage reported in service units • R723CCPU: TCB plus SUP & SUC • R723CSRB: SRB (on GP CP) • R723CSUP: Pre-SRB on zIIP • R723CSUC: zIIP eligible on GP CP • R723NFFS: zIIP normalization factor • Workload resource granularity a problem • Especially CICS & IMS

  13. Data SourcesSMF 30 • Good for batch and TSO if totals desired • Poor granularity for CICS and IMS • Separate records for each DB2 system AS • Totals for system functions • Records for DIST AS include system and DDF application CPU, but net system values can be calculated • The following CPU (GP CP) time fields predate zIIP : • SMF30CPT: traditional TCB (includes ASR, ENC, & DET) • SMF30SRB: traditional SRB • SMF30ASR: preemptible SRB (client, not enclave) • SMF30ENC: independent enclaves • SMF30DET: dependent enclaves

  14. Data SourcesSMF 30 • zIIP support added the following CPU fields: • SMF30_TIME_ON_zIIP: Pre-SRB on zIIP • SMF30_ENCLAVE_TIME_ON_zIIP: Independent enclave on zIIP • SMF30_DEPENC_TIME_ON_zIIP: Dependent enclave on zIIP • SMF30_TIME_zIIP_ON_CP: zIIP eligible on CP • SMF30_ENCLAVE_TIME_zIIP_ON_CP: Independent enclave zIIP eligible on CP • SMF30_DEPENC_TIME_zIIP_ON_CP: Dependent enclave zIIP eligible on CP • SMF30_ENCLAVE_TIME_zIIP_QUAL: Normalized independent enclave on zIIP • SMF30_DEPENC_TIME_zIIP_QUAL: Normalized dependent enclave on zIIP • SMF30SNF: zIIP time normalization factor • Note that several fields are included in others

  15. Data SourcesSMF 100 – DB2 SYSTEM • System level interval reporting • System components • Buffer pools • DDF locations • Most metrics (including CPU times) not deltas but are accumulations from start of monitoring • Difficult to synchronize prior to DB2 V7 • Consider setting STATISTICS SYNC to 59 with the DB2 DSNTIPN installation panel. • Most application CPU time not included • Some server CPU included as DIST SRB! • SPAS not reported • There are multiple logical subtypes • IFCID 0001 QWSA segments contain CPU statistics by AS

  16. Data SourcesSMF 100 – DB2 SYSTEM • QWSA fields of interest: • QWSAPROC: Identifier of MSTR, DBM1, IRLM, or DIST • QWSAEJST: TCB time • QWSASRBT: SRB time (includes QWSAPSRB) • QWSAPSRB: Pre-SRB time on GP CP • QWSAPSRB_zIIP: Pre-SRB time on zIIP • QWSA notes: • QWSAPSRB & QWSAPSRB_zIIP have only been observed non-zero for DIST • QWSAPSRB is application CPU • System DIST SRB = QWSASRBT – QWSAPSRB • Normally very small

  17. Data SourcesSMF 101 – DB2 Accounting • Generally one record per ended transaction • IFCID 0003 contains standard accounting data • Multiples for parallel • Optional rollup record for parallel children • Optional aggregate rollup (V8) reporting for DDF & RRSAF • Some interactive work might not appear to be • TSO • SAP without commit boundary accounting (V8) • No interval reporting • Best source of workload reporting • Summarization tools probably required • QWAC segment contains application (Class 1) and “In-DB2” (Class 2) measurements • Numerous identification fields • Correlation header (QWHC) • Accounting fields (QMDA)

  18. Data SourcesSMF 101 – DB2 Accounting • Packages/DBRM (Class 7) in QPAC segment (QPAC) • One segment for each package used • Additional identifier of package name (QPACPKNM) • Separate subtype (IFCID 0239) for overflow, V8, or rollup • Must associate with IFCID 0003 to know if rollup • Correlation headers are identical • Subset of thread level metrics • Will not necessarily cross-foot • Often a better source of what is being executed • Stored procedures must be packages • If roll-up no reliable transaction count • Cannot assume all rolled up transactions used all packages

  19. Data SourcesRequestor Measurements • CICS 110 & other CICS monitors • Improving DB2 resource consumption data • CICS 2.2 added DB2 CPU time to transaction detail • USRCPUT (CMF 008) & USRDISPT (CMF 007) include DB2 • L8CPUT (CMF 259) is typically DB2 CPU • Each connection (thread) is separate TCB • With recent levels, probably best source for CICS DB2 workload counts, response times, and CPU • IMS Log • Log 07 (Accounting) record includes DB2 CPU in DLRTIME • Parallel child CPU not included • Difficult to use for accounting or performance analysis

  20. Data SourcesComments • Most DB2 CPU time is application TCB & preemptible SRB • Included with requestor totals • DIST is requestor for server work • SMF 30 & RMF 72 include all 101 transaction CPU time • SMF 100 SRB includes some server application CPU • Granular DB2 workload reporting requires SMF 101 • Relative use of stored procedures varies significantly among installations

  21. Key Performance MetricsCPU Usage - System • Easily reported • Normally small in prime time • Many functions use SRBs (non-preemptible) • DBM1 SRB typically largest • Primarily asynchronous database I/O • DIST levels could be high – most will normally be application • SMF 100 SRB includes server application home CPU (not stored procedure) • SMF 30 includes all server application CPU in one record, though enclave totals are also in separate fields • RMF produces separate record for each service class • Most application CPU should be in enclave records • If significant change, work with DB2 specialist • Direct relationship between TCB or SRB in an AS, and a set of DB2 functions • RMF report classes can be easiest source

  22. DB2 CPU UsageSystem Address Spaces

  23. Key Performance MetricsCPU Usage -Application • SMF 101 CPU time notes: • Within a class all fields are separate. There are no totals. • Class 2 values are subsets of class 1 or common fields. • Total (Class 1) • QWACEJST – QWACBJST (End - Start): Home GP CP • If rollup record do not subtract QWACBJST • QWACSPCP: Stored Procedure GP CP • QWACTRTT: Trigger not enclave GP CP • QWACTRTE: Trigger enclave GP CP • QWACUDCP: User defined function GP CP • QWACCLS1_zIIP: Home zIIP • QWACTRTT_zIIP: Trigger zIIP • QWACSPNF_CP: Native Stored Proc GP CP • QWACSPNF_zIIP: Native Stored Proc zIIP

  24. Key Performance MetricsCPU Usage -Application • In-DB2 (Class 2) • QWACAJST: Home GP CP • QWACSPTT: Stored procedure SQL GP CP • QWACTRTT: Trigger not enclave GP CP • QWACTRTE: Trigger enclave GP CP • QWACUDTT: User defined function GP CP • QWACCLS2_zIIP: Home zIIP • QWACTRTT_zIIP: Trigger zIIP • QWACSPNF_CP: Native Stored Proc GP CP • QWACSPNF_zIIP: Native Stored Proc zIIP • Package (Class 7) • QPACTJST: GP CP • QPACCLS7_zIIP: zIIP • zIIP Eligible on GP CP • QWACZIIP_ELIGIBLE: No indication of related fields

  25. DB2 CPU UsageSystem Plus Application

  26. DB2 CPU UsageApplication by Type

  27. Negligible zIIP CPU DIST TCB largest value MSTR also significant DB2 System CPU UsageServer Environment

  28. Server (DDF) is dominate application Over 50% of server could be zIIP zIIP on GP CP also shown on zIIP to show total potential DB2 Application CPUServer Environment

  29. Most CPU time is In-DB2 Minimal stored procedure use zIIP on GP CP not shown separately due to duplication DB2 Application CPUServer Environment

  30. The complex contains 1 zIIP and 8 GP CP Two significant images share the box SYSA WLM weights of 100% zIIP and 45% GP CP DB2 Application CPUServer Environment

  31. Estimate of uncaptured CPU included PDB2 includes all DB2 system functions and monitors Only DDF applications in separate service class DB2 CPU UsageServer Environment

  32. Significant queuing at only 50% utilization There was also significant GP CP delay (remember 45% weight) DB2 CPU UsageServer Environment

  33. Key Performance MetricsTransaction Rates • Many different choices • SMF 101 • RMF • Requestor data • SMF 101 not necessarily one-to-one with user request • Multiple SMF 101 for parallel • Optional rollup records for children • RRSAF (e.g. WebSphere) might use commit level reporting • DDF & RRSAF might be in rollup records • QWACPCNT is aggregate count for rollup records • QWACRINV = 1, 2, or 3 for DDF & RRSAF • Commit count is good for most interactive work • QWACCOMM • Choose based on requirement • Coordinate with other reporting

  34. Key Performance MetricsTransaction Rates

  35. Key Performance MetricsResponse Times • Several choices • SMF 101 • RMF • Requestor data • SMF 101 • Application - might include wait for work (e.g. CICS & RRSAF) • In-DB2 - typically best for DB2 analysis • For rollup records, must divide by commit (QWACCOMM) or aggregate (QWACPCNT) count • Match choice with requirement • Should have response objectives

  36. Key Performance MetricsResponse Times • Application (Class 1) • QWACESC – QWACBSC • If rollup record do not subtract QWACBSC • In-DB2 (Class 2) • QWACASC: Home AS • QWACSPEB: Stored procedure SQL • QWACTRET: Trigger not enclave • QWACTREE: Trigger enclave • QWACUDEB: User defined function • QWACSPNF_ELAP: Native stored procedure • Package (Class 7) • QPACSCT

  37. Key Performance MetricsResponse Components • SMF 101 provides much detail • CPU Time • DASD Wait • DDF Wait • Exception Delays • Waits & exception delays optionally reported • Application delay rather than event time • In-DB2 (Class 3) in QWAC & QWAX segments • Package / DBRM (Class 8) in QPAC segment • Use to direct efforts when response problems occur

  38. Package and application metrics have good resolution Essentially all server work used packages OTHER probably CPU delay (zIIP & GP CP) CPU is sum of all In-DB2 times. Different rates because some transactions used multiple packages. OTHER WAIT is sum of service and exception delays Key Performance MetricsResponse Components

  39. Package SERVICE is higher because it includes items that are separate application metrics, but not shown. Key Performance MetricsResponse Components

  40. Recommendations • Teamwork with subsystem specialists • Track key performance metrics • System • Workload • Use experts if unexpected values or significant deviations • Vary reporting periods and intervals with needs • Keep history • Incorporate with management reporting • For CPU analysis, beware of double counting and missed data

  41. RecommendationsMetric Sources • RMF for system components • System CPU usage • Memory • Paging • SMF 101 for workloads • CPU • Transaction rates • Response times • Response components • Depending on objectives, requestor measurements might be better for workloads.

  42. Summary • Traditional data is available • Many basic metrics are old, but function exploitation often requires corresponding changes • Detail DB2 skills not needed • Much published information • Numerous tools available • Performance analysis, capacity planning, and accounting should be changed for specialty processors • “Excess” zIIP capacity can be cost effective

  43. DB2 Record Layouts • SMF record layouts are produced by assembling macros in DB2xxx.SDSNMACS, where xxx is release level. • SMF 100 IFCID 0001 : DSNDQWST SUBTYPE=0 • System services • SMF 100 IFCID 0002 : DSNDQWST SUBTYPE=1 • Database services & buffer pools • SMF 101 IFCID 0003 & 0239 : DSNDQWAS SUBTYPE=ALL • Accounting • Additional field description information in: • DSNxxx.SDSNIVPD(DSNWMSGS) starting with V8 • DSNxxx.SDSNSAMP(DSNWMSGS) for older releases • All referenced time fields are in 8-byte TOD-clock format. • Recording options controlled with DB2 DSNTIPN installation panel.

  44. DB2 Terminology • Allied Address Space - AS connected to DB2 that can make DB2 requests. • Application Plan - Effectively a transaction definition. The control structure that is produced during the bind process. • DRDA – Distributed Relational Database Architecture • EDM Pool - Environmental descriptor management pool. A pool in memory used for database descriptors, application plans, and packages. • Enclave - One or more units of work running in the same or multiple address spaces. Effectively a virtual AS. • In-DB2 - Application processing within DB2. • Package - A set of statically bound SQL statements that is available for processing.

  45. DB2 Terminology • Preemptible SRB - TCB like but less expensive to create. • Requestor - Source of “transaction” for accounting purposes. • RRSAF - Recoverable Resource Manager Services attachment facility. • Server - Target of requests from a remote system. • Stored procedure - User-written application program that can be invoked through use of the SQL CALL statement. • Thread - Connection between DB2 and requesting AS. • Trigger - A set of SQL statements that is stored in a DB2 database and executed when a certain event occurs in a DB2 table. • User-defined function (UDF) -A function that is defined to DB2 and can be referenced in SQL statements.

  46. References • Ned Diehl, "DB2 CPU and Response Metrics", Proceedings, CMG05, p668 and http://www.perfman.com • Ned Diehl, “Measurement and Modeling of DB2 zIIP Workloads”, Proceedings, CMG 2006 • Joel Goldstein, “DB2 Version 3 & 4 Performance Metrics”, CMG96 Proceedings, p668 and http://responsivesystems.com/white.htm • Peter Enrico, “Focus Enclaves”, Cheryl Watson’s Tuning Letter 1999, N0.2 • John Arwe, “Preemptible SRBs”, CMG95 Proceedings, p646 • Chuck Hoover, “Tuning DB2 From The Ground Up”, Share Feb 98 Proceedings, Session 1340 • Namik Hrle & Johannes Schuetzner, “Finding Out Who Did It”, IDUG Solutions Journal Volume 11, Number 2 (2004) • Barry Merrill “Captured DB2 CPU Time”, Cheryl Watson’s Tuning Letter 2005, N0.1 & MXG Listserv December 2004 • William Favero, “zIPPing Along”, ZJOURNAL August/September 2006 • http://search.ittoolbox.com/?r=zIIP&Submit1=Go

  47. References • System Management Facilities, IBM • Resource Measurement Facility Report Analysis, IBM • IBM Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS Report Reference, IBM • DB2 Administration Guide, IBM • DB2 Installation Guide, IBM • PerfMan for DB2, ISM • PerfMan for z/OS, ISM • CMG Proceedings • Share Proceedings • IDUG Proceedings & Solutions Journal • Google

More Related