Transcription
Db2 12 for z/OS: Performance UpdateExploiting memory for performance MDUG – Michigan Db2 User Group Mark Rader, Db2 for z/OS IBM Washington Systems Center September 18, 2019
Db2 for z/OS memory exploitation to improve performance— Db2 12 memory exploitation— Migration expectation2 Copyright IBM Corporation 2019
Please note:—IBM’s statements regarding its plans, directions, and intent are subject to change or withdrawal without notice at IBM’ssole discretion.—Information regarding potential future products is intended to outline our general product direction and it should not berelied on in making a purchasing decision.—The information mentioned regarding potential future products is not a commitment, promise, or legal obligation todeliver any material, code or functionality. Information about potential future products may not be incorporated into anycontract.—The development, release, and timing of any future features or functionality described for our products remains at oursole discretion.Performance is based on measurements and projections using standard IBM benchmarks in acontrolled environment. The actual throughput or performance that any user will experience will varydepending upon many factors, including considerations such as the amount of multiprogramming in theuser’s job stream, the I/O configuration, the storage configuration, and the workload processed.Therefore, no assurance can be given that an individual user will achieve results similar to those statedhere.3 Copyright IBM Corporation 2019
Z Systems memoryMaximum memory on Z Systems 4Z13Z14 Copyright IBM Corporation 2019
Impact of paging on Db2 transactionsComparison with and without paging on Db2 OLTP workloads1400Transaction average with and without system paging1200Time in milli sec1000800No-paging (30GB)Paging (26GB)6004002000Resp timeCPU timeWait time5Not account Copyright IBM Corporation 2019
Db2 12 memory exploitation— Db2 12 memory exploitation Utilize more memory to optimize Db2 data process Improve scalability by utilizing memory— Migration expectation Memory usage CPU reduction6 Copyright IBM Corporation 2019
Dynamic PlanStability Runstats profile Query CPUimprovement inUnion All, OuterJoin Sort improvement Adaptive Index UDF Caching7 IDAAenhancements Native RestAPI Paginations Piece meal wisedelete XML, JSONimprovement Complex Triggers Temporalimprovement Enhanced Merge SQLPLimprovementDBA and 24*7Analytics, Cloud and Mobile App Exploit in memoryoptimization ContiguousBPOOL Insert Algorithm Better CommitLRSN More zIIP support Scalabilityimprovement DRDA zLOAD RLF for StaticSQL PerformanceSystem PerformanceDb2 12 for z/OS key features Easy MigrationOnline SchemaPBR RPNSecurityAsync LockDuplexingProfileimprovementTemporal RTSUtilityImprovementLOB zEDCcompression Copyright IBM Corporation 2019
System PerformanceDb2 12 key features – system performance Exploit Large Real Memory Index in memory Fast Traverse Block Contiguous buffer pool, PGESTEAL(NONE) Buffer Pool simulations More in memory optimizations Scalability improvement Insert Algorithm 2 Latch contention reduction DRDA zLOAD EDM pool management improvement More zIIP Usage Better lock avoidance with long running UR8 Copyright IBM Corporation 2019
Db2 12 key features – system performanceIndex In Memory Optimization9 Copyright IBM Corporation 2019
Index in memory optimizationIndex IX1 with 5 levelsRN2In MemoryStructure (FastTraverse Block)N3N4Table Fast Traverse Block (FTB) contains non-leaf pages Limitation : Unique index with key size of 64 bytes or less Up to 10,000 indexes per member10 Copyright IBM Corporation 2019
How does FTB work?— Db2 system parameter INDEX MEMORY CONTROL AUTO, DISABLE or user defined size (10MB -200GB) AUTO will use MAX (20% of sum of allocated buffer pools, 10MB) Storage for FTB is in addition to buffer pools— Db2 selects the candidates Main metric is traverse counter zIIP eligible system task— Users can influence via SYSIBM.SYSINDEXCONTROL table11 Copyright IBM Corporation 2019
SYSIBM.SYSINDEXCONTROL— SYSIBM.SYSINDEXCONTROL (table space DSNDB06.SYSTSICO) New since V12R1M100 Similar to SYSIBM.SYSINDEXCLEANUP (pseudo deleted index entries cleanup)— 3 possible ACTIONs: D Disable FTB creation A Automatic FTB creation F Force FTB creation (PI78958)— Criteria are Subsystem name: SSID Index name: IXCREATOR, IXNAME, PARTITION Date / time: E.g. FROM TIME until TO TIME12 Copyright IBM Corporation 2019
Note: index eligibility for FTB— Unique index— Key length 64 bytes— Index does not have versions— Catalog indexes excluded— No TIMESTAMP WITH TIME ZONE column in index key13 Copyright IBM Corporation 2019
High level flow: how Db2 picks the candidatesStep1 Identifies the candidate indexes (unique, 64 byte key , 10,000 index)Step 2 Tracks the traverse count per index / partition using moving averageStep 3 Adjusts the traverse count based on other activities (e.g. page splits)Step 4 If the traverse count meets the internal threshold, set as a FTB candidateStep 5 Builds FTB if there is no system paging, and enough memoryStep 6 Continue to monitor the threshold14 Copyright IBM Corporation 2019
Monitoring FTBs— DSNI070I message indicates : # of FTB created, Storage usage (MB) Every 2 mins - only when there are any updates (PI72330)— IFCID 389 FTB object level FTB status (stats class 8)— IFCID 477 FTB creation/free (performance class 4)— IFCID 2FTB status similar info as DSNI070I (PI72330)— Db2 command to display object level information - DIS STATS(IMU) LIMIT(*)DBID PSID DBNAME---- ---- -------0286 0005 DB1******* DISPLAY OFCREATORINDEXNAMELEVEL PART SIZE(KB)-------------- -------------- ----- ----- -------SYSADMI10004 00001 00000923STATS TERMINATED *********************************15 Copyright IBM Corporation 2019
Batch using random SELECTImprovement (%) from simple lookup with Db2 12 vs. Db2 1123%16%11%6%23Index Levels1645 Copyright IBM Corporation 2019
Using FTB – 2-way data sharing OLTPClassic IRWW 2-way: Db2 CPU/Transaction(Class2 MSTR DBM1 IRLM)Classic IRWW 2-way: Getpages/Commit67090650CPU/txn 90570105500V11 NFMV12BNFA, FTBV12R1M100FTBdisabledDisabledV12BNFA, FTBV12R1M100Enabled(AUTO)FTB enabled(AUTO)V12ANFA, FTBV12R1M500Enabled(AUTO)FTB enabledV11 NFM(AUTO)V12 BNFA, FTBV12R1M100DisabledFTB disabledV12 BNFA, FTB EnabledV12R1M100(AUTO)FTB enabled (AUTO)V12 ANFA, FTBV12R1M500Enabled (AUTO)FTB enabled(AUTO) Key Observations About 3.5% average Db2 CPU/transaction reduction without FTBFTB usage adds additional saving of 3%45% getpage reductionReal storage increase total was 300MB per member17 Copyright IBM Corporation 2019
Db2 12 key features – system performanceIndex In Memory OptimizationContiguous Buffer Pools18 Copyright IBM Corporation 2019
PGSTEAL(NONE) – in-memory contiguous buffer pools— Page steal algorithm : PGSTEAL LRU, FIFO, NONE— PGSTEAL(NONE) in Db2 10 and 11 Pre-loaded into buffer pools at the first access Db2 still maintains hash and LRU chains— PGSTEAL(NONE) in Db2 12: In-memory contiguous buffer pool Pre-loaded into buffer pool at the first access No more chain maintenance Up to 6400 buffers are allocated to handle overflow Eliminate impact of processor cache miss with large buffer poolsoWith 70% getpages in PGSTEAL(NONE), 8% CPU reduction was observed19 Copyright IBM Corporation 2019
Contiguous buffer poolsRDb2 11PGSTEAL(NONE),PGSTEAL(LRU)RSSRRSSSSRSROldest sequential bufferSRROldest bufferT1T2Db2 12PGSTEAL(NONE)P1P2P3P4P5P1P1P2P2P3P3P4P5P6P8P9R SR SS RT3Overflow area20 Copyright IBM Corporation 2019
Contiguous buffer pools and overflow area— No more chain maintenance for in-memory objects— Best fit for the objects frequently accessed with stable size— VPSIZE includes overflow area OVERFLOW ALLOC is 10% of VPSIZE within range of 50-6400 buffersoSUM(Number of pages of objects in this BP) 6400 VPSIZE DSNB604I – New Message is used when pages are read into overflow area Statistics trace now contains the overflow usageRSRSSOverflow area21 Copyright IBM Corporation 2019
Implementing contiguous buffer pools— Define a new or use existing PGSTEAL(NONE) buffer pools The object needs to fit the buffer pool (VPSIZE includes overflow area)— Objects which are stable in the size and frequently accessed Real Time Statistics in Db2 12 includes GETPAGE counters Real Time Statistics in Db2 12 support temporal to keep the histories— The procedure to select the candidate objects is published in the Db2 12 performance Redbook22 Copyright IBM Corporation 2019
Db2 12 key features – system performanceIndex In Memory OptimizationContiguous Buffer PoolsBuffer Pool Simulation Support23 Copyright IBM Corporation 2019
Buffer pool simulation (Db2 11 PI22091)— Benefit of expanding buffer pools varies with data re-reference and the ratio betweenobject size and buffer pool size— Simulation provides accurate benefit of increasing buffer pool size in production environmentSPSIZE 32GBSimulation Pool (SP)VPSIZE 8GBVPSIZE 8GBALTER BPOOL(BP1) SPSIZE (8000K)24 Copyright IBM Corporation 2019
Buffer pool simulation – how it worksALTER BPOOL(BP1) SPSIZE (8000K)VPSIZE 8GBSPSIZE 32GBcontrol blocks forbuffer poolsR SR SS RS SR Rcontrol blocks forsimulated poolsR SR SR SR SR SRR SR SS SS RS RS RR SS SS SS SS RR RR RR RR25 Copyright IBM Corporation 2019
Interpreting BP simulation outputAVOIDABLE READ I/O Avoidable synchronous DB I/Os (R/S)SYNC READ I/O (R) 25463982SYNC READ I/O (S) 81181Avoidable prefetched pagesASYNC READ I/O 15470503SYNC GBP READS (R) 11172099SYNC GBP READS (S) 4601Avoidable GBP requestsASYNC GBP READS 1181076PAGES MOVED INTO SIMULATED BUFFER POOL 53668641Numbers of pagesTOTAL AVOIDABLE SYNC I/O DELAY 35321543 MILLISECONDSstolen from VPOOLand moved toSPOOLavoidable sync I/O per second Sync I/Os / interval-secAvoidable Sync I/Oelapsed time in millisec(25463982 81181)/360 70958 I/O per sec26 Copyright IBM Corporation 2019
Db2 12 key features – system performanceIndex In Memory OptimizationContiguous Buffer PoolsBuffer Pool SimulationsOther Db2 optimization using In-Memory27 Copyright IBM Corporation 2019
Other examples utilizing memoryRLFDGTTUDF RLF tables are cachedin memory Avoids catalogaccesses during SQLprocessing Applies to bothdynamic and static RLF Db2 catalog objectsrequired to declareDGTTs are cached inmemory Avoids accessingcatalog and directoriesduring declare DETERMINISTIC, NOEXTERNAL ACTIONand not MODIFIES SQLDATA Avoids invoking UDFagain for the sameinput28 Copyright IBM Corporation 2019
Db2 12 key features – system performanceIndex In Memory OptimizationContiguous Buffer PoolsBuffer Pool SimulationsOther Db2 optimization using In-MemoryInsert Improvement utilizing In-Memory Structure29 Copyright IBM Corporation 2019
INSERT and bottlenecksCFDb2NetworkBuffer PoolsIndexPRRCDb2 LogDb2 LogTable Space30 Copyright IBM Corporation 2019
New insert algorithm with MEMBER CLUSTERIn memory Structure (InsertPipe)Page AABCDPage AThread-1Additional 32 KBper partition forvariable lengthrows and 16 KBfor fixed lengthrowsPage BThread-2Thread-1Thread-3Page CThread-2Page DThread-331 Copyright IBM Corporation 2019
Insert algorithm 2— Applicable with UTS with Member Cluster (with and without APPEND)— SYSTEM level : DSNZPARM: DEFAULT INSERT ALGORITHM 0 – disable insert algorithm 2 (no object level override) 1 – use basic insert algorithm (algorithm 1) 2 – use new insert algorithm (algorithm 2) - Default for Db2 12 Function Level V12R1M500— Object level : CREATE TABLESPACE or ALTER TABLESPACE INSERT ALGORITHM clause0 – use subsystem defaulto 1 – use basic insert algorithm (Db2 11 behavior)o 2 – use advanced insert algorithmo32 Copyright IBM Corporation 2019
Insert algorithm applied to Db2 11 PMR recreationPBG/Member Cluster with 800 concurrent 0000945327081460Throughput (insert/sec)V11TOTAL CPU per commit (us)V12PBG with Member Cluster, RLL, with 400 bytes per row, one index,800 concurrent threads, 10 insert per commit33 Copyright IBM Corporation 2019
Db2 12 key features – system performanceIndex In Memory OptimizationContiguous Buffer PoolsBuffer Pool SimulationsOther Db2 optimization using In-MemoryInsert Improvement utilizing In-Memory StructureEDM Scalability Improvement with a new memory management3434 Copyright IBM Corporation 2019
Db2 EDM pool management— Challenges : Inherited performance overhead micro-managing EDM poolsEDMSTMTC : EDM statement cache poolso EDMDBDC : EDM DBD poolso EDM SKELETON POOL : EDM skeleton poolso Scalability inhibitor for having larger EDM statement cache and Skeleton pools— EDM pool management Db2 12 simplifies the storage management and removes the scalability inhibitors Three EDM zparms continue to be used to indicate the upper value of the pool but will no longer fail if the storageneeded exceeds the limit. Once the pools reach the max values in zparms, Db2 frees unused storage in LRU order35 Copyright IBM Corporation 2019
EDM statement pool36 Copyright IBM Corporation 2019
EDM skeleton pool37 Copyright IBM Corporation 2019
Db2 12 key features – system performanceIndex In Memory OptimizationContiguous Buffer PoolsBuffer Pool SimulationsOther Db2 optimization using In-MemoryInsert Improvement utilizing In-Memory StructureEDM Scalability Improvement with a new memory management3838 Copyright IBM Corporation 2019
Db2 12 memory exploitation— Db2 12 memory exploitation Utilize more memory to optimize Db2 data process Improve scalability by utilizing memory— Migration expectation Memory usage CPU reduction39 Copyright IBM Corporation 2019
Db2 12 memory usage— Base Db2 12 features Larger RID size (5- 7bytes)Continuous Delivery support (control structure updates for future expansion)EDM pools (up to 20% increase)RLF : The size of RLST tables— zPARM default increase EDM SKELETON POOL 10MB- 50MB— Optional features but turned on by default Index in memory (FTB) : up to 20% of allocated buffer pool size Insert Algorithm 2 : approx. 16-32KB bytes per data sets for pipe, possible increase in LBP/GBP— Other in-memory feature In memory contiguous buffer pools PGSTEAL(NONE) additional overflow buffers (up to 6400)40 Copyright IBM Corporation 2019
Db2 12 real storage usage expectation— Estimate up to 30% increase (excluding Db2 buffer pools) Without using FTB— Estimate up to 20% increase overall Including FTB usage, depending on how you size yours41 Copyright IBM Corporation 2019
ECSA usage in Db2 12— Samples from IBM workloads and a few ESP customers data Most of data shows equivalent usage in ECSA between V11 and V12 when the numbers of threads areequivalent Distributed IRWW shows 1.2KB increase per thread42 Copyright IBM Corporation 2019
High level expectation for Db2 12 performance improvements (YMMV)43 Copyright IBM Corporation 2019
Resources— Benefits of Configuring More Memory in the IBM z/OS Software Stack 8.pdf— Opportunities of using large real memory with Db2 10 and 11 to reduce CPU resource consumption by JohnCampbell using-large-real-memory-with-db2-10-and-11to— Performance Report on Exploiting Large Memory for Db2 Buffer Pools with SAP f/WebIndex/WP102461— Insight 2015 : Ten Reasons Why You Want to Invest in Memory— IBM Redbook : Db2 12 for z/OS Performance Topics www.redbooks.ibm.com/redbooks/pdfs/sg248404.pdf44 Copyright IBM Corporation 2019
Questions—? ? ?— If you think of questions later, look for me later this week or send me an e-mail mrader@us.ibm.com45 Copyright IBM Corporation 2019
Summary – Db2 12 performance— Significant system level performance benefits based on exploiting in-memory techniques Index Fast Traverse Block (FTB)oAvailable V12R1M100 Contiguous buffer poolsoAvailable V12R1M100 Insert algorithm 2oAvailable V12R1M500 Plus the others we covered today— Many other performance enhancements, not covered today, including: Query performance and optimizer enhancementsBest improvements seen with new access pathso Rebind packages without APREUSEo Available V12R1M100o Dynamic Plan Stability Variety of SQL and SQLPL features that can reduce CPU consumption of your workload Data sharing performance improvements46 Copyright IBM Corporation 2019
Exploiting memory for performance MDUG -Michigan Db2 User Group Mark Rader, Db2 for z/OS IBM Washington Systems Center . Monitoring FTBs —DSNI070I message indicates : # of FTB created, Storage usage (MB) Every 2 mins - only when there are any updates (PI72330)
