Block Party Techniques for tuning DB 2 LUW

Block Party! Techniques for tuning DB 2 LUW Buffer Pools Martin Hubel, MHC Inc. Session Code: C 6 Tuesday, November 17 10: 15 – 11: 15 Platform: DB 2 for LUW

Agenda 1. 2. 3. 4. 5. Goals in BP tuning BP Sizing Separate sequential I/O from random I/O Other tuning ideas Mistakes people make Copyright© 2015 Martin Hubel Consulting Inc. 2

Buffer Pools and I/O DB 2 Buffer Pools DB 2 Data Base Physical I/O Logical I/O Rows Read Page Dataset Application Rows Selected Rows Fetched OS PAGING If system busy/ Buffers too big Copyright© 2015 Martin Hubel Consulting Inc. 3 3

Key BP Characteristics in DB 2 for LUW • Can name pools during CREATE • Choose meaningful names like HOT_INDEX_BP • Add, alter, or drop buffer pool without stopping DB • IMMEDIATE will make changes while DB 2 is running • DEFERRED makes changes at next database activation • Can alter BP size and TS allocation to buffer pools • Use a block-based area in each buffer pool • Can only be implemented DEFERRED • Area for asynchronous I/O Copyright© 2015 Martin Hubel Consulting Inc. 4

Buffer Pool I/O • Concepts of logical and physical I/O • Logical request for data is an application’s request for data • Rows selected, rows fetched • If data is not in BP, physical I/O involves retrieval from disk • Causes delays & consumes CPU • Logical I/O satisfied from the buffer pool requires no physical I/O Copyright© 2015 Martin Hubel Consulting Inc. 5

Types of Physical I/O • Random • Uses available index • Prefetch • “Read-ahead” capability for data and indexes • Reduces waits caused by reading data from disk • Called “asynchronous” I/O • Pure sequential • List • Smart data/index (dynamic & readahead (10. 1)) • Dynamic (sequential detection) for highly sequential patterns • Readahead when data is badly clustered • BIND or execution time decision • Prefetch quantity is adjustable in DB 2 for LUW • AUTOMATIC, numeric • Prefetch size = (number of containers) * (number of physical disks per container) * (extent size) Copyright© 2015 Martin Hubel Consulting Inc. 6

7 Methods Considered for BP Tuning • Increase size of BP • Arguably the most obvious and effective way to reduce I/O • In LUW, memory is cheap and often sized larger than z/OS • Memory can be deployed such that its benefit is greatly reduced 1. Separate by type of I/O • High asynchronous I/O can force high-use pages from BP • Need high use pages to remain resident – presumably accessed via index 2. Separate tables from indexes • Indexes are expected to be smaller than tables 3. Separate by volume • High-use objects are isolated from other objects in the hope of increasing page residency • Of the 3 ways to separate objects, which is the most effective? Copyright© 2015 Martin Hubel Consulting Inc. 7

Or, Set it to music Song Lyrics You've got to accentuate the positive Eliminate the negative Latch on to the affirmative Don't mess with Mister In-Between Johnny Mercer / Harold Arlen Artist: Bing Crosby and many others Copyright© 2015 Martin Hubel Consulting Inc. 8 8

“Accentuate the positive” • Random I/O is positive • Means indexes are used • Keep high use pages in BP • Make the BP large enough to avoid physical I/O Copyright© 2015 Martin Hubel Consulting Inc. 9 9

10 Buffer pool activity illustration • Individual or groups of pages can be read from disk • For random I/O, pages will fill buffer • Least recently used (LRU) algorithm used to write over oldest unlocked pages • A bigger BP means more pages in buffer • Some highly used pages may remain resident for long periods Copyright© 2015 Martin Hubel Consulting Inc. 10

BP Size Example • Single large BP, batch workload, DB 2 V 7. 2, 32 -bit • More memory improved throughput • Bigger buffer pools are generally better – go large Copyright© 2015 Martin Hubel Consulting Inc. 11

“Eliminate the negative” • We want maximum residency for high use pages • Let’s remove I/O or objects that negatively impact page residency • Two primary methods: 1. Separate random from sequential I/O 2. Separate objects that negatively impact page residency Copyright© 2015 Martin Hubel Consulting Inc. 12 12

Eliminate Sequential From Random I/O • Sequential I/O involves reading a large number of pages to find data that qualifies • Can push many high-use random pages out of the buffer pool • In the past, it was prudent to use separate buffer pools for randomly and sequentially accessed objects • Use block based buffer pools • Can effectively separate within a buffer pool using the block based area Copyright© 2015 Martin Hubel Consulting Inc. 13

14 Effect of sequential I/O • DB 2 uses prefetch to avoid waits for I/O • Each prefetch operation can overlay 32+ pages in BP • Hit ratio for randomly accessed pages can be drastically reduced • Must be reread from disk • Sequentially read pages are rarely used again • Scans simply replace oldest pages Copyright© 2015 Martin Hubel Consulting Inc. 14

Block-based buffer pools • A block-based buffer pool consists of • Page area - non-sequential (random) workloads • Block area - a specified number of contiguous pages • If block based buffer pool is available • Significantly improves prefetch performance • CREATE and ALTER BUFFERPOOL • NUMBLOCKPAGES <number-of-pages> • Number of block pages must not > 98 % of BP pages • Specifying value 0 disables block I/O • NUMBLOCKPAGES should be multiple of BLOCKSIZE • BLOCKSIZE <number-of-pages> • 2 <= Block size <=256. Default value is 32 • Number of pages read from disk in a block I/O Copyright© 2015 Martin Hubel Consulting Inc. 15

16 Effect of sequential I/O with block-based BP • Prefetch occurs in blockbased area • Sequentially read pages are used and written over • Hit ratio for randomly accessed pages is perserved • Block area does not have to be large to give good results • 1 percent of a large pool is enough Copyright© 2015 Martin Hubel Consulting Inc. 16

Rethinking conventional wisdom • Traditional advice (especially for z/OS): • Separate randomly and sequentially accessed objects • “RAMOS” and “SAMOS” • Measure I/O characteristics carefully and place objects in correct BP • New thinking: • Block area separates random and sequential I/O automatically • Determined by DB 2 where each I/O should be done: page or block area • Even for a single object, the block area is used • Separate BPs for type of access is not needed in DB 2 LUW provided you define a block area • A single block-based buffer pool may provide reasonable performance for many applications Copyright© 2015 Martin Hubel Consulting Inc. 17 17

Block-based BP: final thoughts (for now) • Virtually every DB 2 object has some form of sequential access • Every BP should have a block area defined • Only 1 – 3 % of pool size: a little is enough • Even if no sequential access, a small block area avoids a problem • No further need to separate synchronous / asynchronous I/O into separate pools • Measurements from BP snapshot show effectiveness: • Block I/O – 90%+ show be in block area • Vector I/O – sequential I/O in page area. Should be zero or <10% • If Block I/O is zero with block area – the definition is not correct • Match block size to extent size • High sequential access may indicate indexes are needed Copyright© 2015 Martin Hubel Consulting Inc. 18 18

Move large objects away from others • Examples: • 40 million page fact table in data warehouse • Very large tables in ERP systems • Access is likely single row • Must use index access • Any scans are a performance disaster – likely already solved • Often, any pages currently in the buffer pool will not be used again prior to them being replaced • Every access to the table requires a physical I/O • Buffer pool performance would be improved for other objects if the large table is isolated from other tables Copyright© 2015 Martin Hubel Consulting Inc. 19 19

“Latch on to the affirmative” • Some DBAs manage 100+ databases • May not have the time to go this far • But may have time for critical applications • Other ideas to improve BP residency • Pin high use tables in a BP • Separate tables from indexes • One table per table space Copyright© 2015 Martin Hubel Consulting Inc. 20 20

Pin high use tables in BP • Also termed “page fixing” • Take small read-only tables and separate them into a small pool • Almost no physical I/O • Ensures top performance for these high use tables • Size of BP could approach the sum of NPAGES and/or NLEAF for the objects in the pool Copyright© 2015 Martin Hubel Consulting Inc. 21 21

Separate tables from indexes • Another technique started on the mainframe • Rationale: • Tables are bigger than indexes • Indexes small and used more often to find the rows of interest within tables • Indexes remain resident in BP if separated from tables • In DB 2 for LUW, the table definition specifies the table space to be used • Can use a second table space for indexes • All indexes for the table are stored together in that TS • 10. 5. 0. 4 - Shadow tables benefit from a separate BP in one benchmark Copyright© 2015 Martin Hubel Consulting Inc. 22 22

One table per table space • Further to separating tables and indexes • Another idea from DB 2 for z/OS • Each table would likely use a separate table space for table and one for its indexes • Provides the greatest granularity of measurement data • Each table and its indexes would have its own TS statistics available • Other reasons for one table / TS: • Backup / recovery at the table level • Be careful of RI with point in time recovery Copyright© 2015 Martin Hubel Consulting Inc. 23 23

“Don't mess with Mister In-Between” • Doing a good job on some things, but ignoring other factors, gives less benefit • Do tuning correctly • Understand how DB 2 buffer pools work • Set up BP size and block-based area • Place objects appropriately into pools • A half-tuned environment is money wasted. For example: • Not using block based areas • Ignoring some BPs • Placing objects incorrectly Copyright© 2015 Martin Hubel Consulting Inc. 24 24

Non-Buffered I/O Activity • Some I/O activity does not use the buffer pool: • • • Direct Reads Direct Writes Direct Read Requests Direct Write Requests Direct Read Time Direct Write Time Copyright© 2015 Martin Hubel Consulting Inc. 25

DB Parameters • NUM_IOSERVERS • Default 3, usually too small. • Set to number of physical devices supporting the database tablespaces • NUM_IOCLEANERS • Number of asynchronous cleaners to write changed pages to disk (Default value 1) • Increase num_iocleaners if no victim buffers available • CHNGPGS_THRESH • Controls when page cleaners become active (default 60) • Best to have high Async Write % • If num_iocleaners = # Devices and Async Write % not 90 -95%, then decrease chngpgs_thresh Copyright© 2015 Martin Hubel Consulting Inc. 26

DB 2 10. 5 Administrative Views • Snapshot and other administrative table functions provided by IBM can change with each version • Solution in V 10. 5+: Use administrative views • SYSIBMADM owner • Consistent names of columns between versions • In this presentation: • SNAPBP • SNAPTAB • SNAPTBSP • Future releases/versions could add columns Copyright© 2015 Martin Hubel Consulting Inc. 27 27

Excerpt from Bufferpool Snapshot Bufferpool name Database path Input database alias Snapshot timestamp 10: 18: 55. 275979 = IBMDEFAULTBP = ERP = /db 2/ERP/db 2 erp/NODE 0000/SQL 00001/ = ERP = 09/07/2011 Buffer pool data logical reads = Buffer pool data physical reads = Buffer pool temporary data logical reads = Buffer pool temporary data physical reads = Buffer pool data writes = Buffer pool index logical reads = Buffer pool index physical reads = Buffer pool temporary index logical reads = Buffer pool temporary index physical reads = Buffer pool xda logical reads = Buffer pool xda physical reads = Buffer pool temporary xda logical reads = Buffer pool temporary xda physical reads = Buffer pool xda writes = Total buffer pool read time (milliseconds) = Total buffer pool write time (milliseconds)= Asynchronous pool data page reads = Asynchronous pool data page writes = Buffer pool index writes = Asynchronous pool index page reads = Asynchronous pool index page writes = 1211647578 54216573 1189401912 1408611 3870243 4451803691 150401567 0 0 0 0 454933339 3505078 35959224 3864211 3023222 138536862 3019941 Asynchronous pool xda page reads Asynchronous pool xda page writes Total elapsed asynchronous read time Total elapsed asynchronous write time Asynchronous data read requests Asynchronous index read requests Asynchronous xda read requests No victim buffers available Direct reads Direct writes Direct read requests Direct write requests Direct reads elapsed time (ms) Direct write elapsed time (ms) Database files closed Unread prefetch pages Vectored IOs Pages from vectored IOs Block IOs Pages from block IOs Physical page maps = = = = = = 0 0 220862240 3440997 21194855 73976185 0 36079293 5142393056 22376070 86855746 1075363 77311504 3744437 0 2886823 0 0 93191971 171348157 0 Node number Tablespaces using bufferpool Alter bufferpool information: Pages left to remove Current size Post-alter size = 0 = 34 = 0 = 500000 28

Bufferpool Efficiency Calculations Overall Hit Ratio Data Hit Ratio Index Hit Ratio Database Files Closed Pages Read Per Minute Asynchronous Read milliseconds Asynchronous Write milliseconds Synchronous Read milliseconds Synchronous Write milliseconds Overall Read milliseconds Overall Write milliseconds Asynchronous Read Percentage Synchronous Read Percentage Asynchronous Write Percentage Synchronous Write Percentage Asynchronous Pages Read Per Request Pages per Block I/O Block to Vector I/O Ratio Block to Vector Pages Ratio • Most formulas are in Database Monitoring Guide and Reference • Newer administrative views available in DB 2 • Some need more work in my opinion Copyright© 2015 Martin Hubel Consulting Inc. 29

Martin Hubel Martin@mhubel. com Copyright© 2015 Martin Hubel Consulting Inc. 30

Martin Hubel MHC Inc. martin@mhubel. com Block Party! Techniques for tuning DB 2 LUW Buffer Pools Please fill out your session evaluation before leaving! Copyright© 2015 Martin Hubel Consulting Inc. 31