1 / 147

Bringing IDS Internals to the Surface Forum 2002 Presentation November 9, 2002

Bringing IDS Internals to the Surface Forum 2002 Presentation November 9, 2002. Using IDS Internals for Troubleshooting, Maintenance, and Performance Tuning. Mark Scranton Principal Consultant/Trainer IBM Denver mscranto@us.ibm.com mail@markscranton.com www.markscranton.com. Who Am I?.

dbain
Download Presentation

Bringing IDS Internals to the Surface Forum 2002 Presentation November 9, 2002

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. Bringing IDS Internals to the SurfaceForum 2002 PresentationNovember 9, 2002 Using IDS Internals for Troubleshooting, Maintenance, and Performance Tuning Mark ScrantonPrincipal Consultant/Trainer IBM Denver mscranto@us.ibm.com mail@markscranton.com www.markscranton.com

  2. Who Am I? • Started with Informix in July of 1995 • I do: • engines…only. • IDS, XPS, DB2 • focusing on internals, perf tuning, configuration. • teaching and consulting. • webcasts

  3. Contact Info • mscranto@us.ibm.com • mail@markscranton.com • www.markscranton.com - website full of scripts, tips, tricks, white papers, presentations, and of course, pics of my kids.

  4. Presentation Overview • this presentation will identify “internals” concepts that could be used daily for maintenance, support and troubleshooting of IDS. • a strong working knowledge of IDS is assumed. • these concepts are also great at the IDS Trivial Pursuit Parties…held all over the country I’m sure…

  5. Choices • Disk Overhead • Physical and Logical Pages • Maximum Number of Extents • Long Transactions • TOPIC: Checkpoint Realities (incl plog overflow) • IDS 9.4 - 2G Chunk Size • Physical Log Overflow • TOPIC: Partnums (and the Route to Data) • TOPIC: Forward Pointers • TOPIC: Fragmentation and Overhead • TOPIC: Extending the RSVD pages • 9.3 Features: Update Statistics Changes • TOPIC: Slot Re-use • Detached IDXs Proof: 7 to 9

  6. Detached IDXs Proof: 7 to 9

  7. Detached IDXs Proof: 7 to 9

  8. Detached IDXs Proof: 7 to 9

  9. Detached IDXs Proof: 7 to 9

  10. Detached IDXs Proof: 7 to 9

  11. Topics • Disk Overhead • General Overhead – row and page level • Fragmentation and Overhead – it all adds up • Performance Impactors • Forward pointers • Maximum number of extents for a table

  12. Topics • Show Stoppers • Physical Log Overflow • Long Transactions • Extending the Root Reserved Pages • Good To Know • Slot Re-use on a page • The Route To Data • Two 9.3 Features • update statistics • dynamic logical logs

  13. TOPIC: Logical vs. Physical Pages • Understanding the difference can help tremendously in a number of different scenarios: • interpretation of msg log assertion failures • interpretation of the extent list of a partition page • lock level identification with respect to rowid(s) • oncheck usage • a bunch of other stuff

  14. Logical vs. Physical Pages • Physical Pages • always with respect to a chunk • numbering starts with 0 • format: 0xCCCPPPPPccc - chunk numberppppp - page offset into the chunk

  15. Logical vs. Physical Pages 30023c 30023d 30023e 30023f Chunk 3 0xCCCPPPPPc - chunk # p - page offset 300240 300241 300242 300243 All addresses show physical pages 300244 300245 300246 300247

  16. Logical vs. Physical Pages • Logical Pages: • always with respect to a tablespace • numbering starts with 0 • format simply 0 through x

  17. physical page num logical page num Logical Pages 30023c 30023d 30023e 30023f extent 1 for sparky 300240 300241 300242 300243 0 1 2 3 300244 300245 300246 300247 create table sparky... 4 5 6 7

  18. chunk 0x4 page 0x33 Usage - extentlist extent list on a partition page (oncheck -pt skippy:sparky)

  19. Usage Example - bad page

  20. Usage Examples - lock level rowid format: 0xLLLLLLSS L - logical page in the table S - slot/row # on the page $ onstat -k oncheck -pp 0x100016 2

  21. TOPIC: Disk Level Overhead • Why the concern? • table sizes are growing • extent allocations, disk sizing areas of concern • Two types: • page level • the same regardless of page size (2K or 4K) • row level

  22. Why Do You Care? • Extents concatenate, right? • Extents double, right? • So WHY do I need to pre-allocate extent sizes? • Here’s a proof:

  23. load fails though due to long transaction start with 3 small logs, attempt to load 10M rows into a RAW table: : dynamic log is allocated why? Extent Allocation(s) Proof

  24. dbspace2 dbspace1 dbspace3 dbspace4 Extent Allocation(s) Proof 5,604 * 4 dspaces = 22,416 PTEXTENDS into the llogs

  25. Disk Level Overhead Page Level Overhead • 2048 (2K page) • 28 • ----------------- • 2020 bytes per page Page Header (24 bytes) • 4096 (4K page) • 28 • ----------------- • 4068 bytes per page Page Trailing Timestamp(4 bytes)

  26. Disk Level Overhead Row Level Overhead page header row1 • each row has 4 bytes overhead for it’s slot table entry • should be considered for large table sizing • most pages have this structure row 2 slot 1 slot 2

  27. Overhead Calculations • example: 1,000,000 rows, 208 bytes wide • simple estimation of disk required: • data pages only • nrows * row_width = 208,000,000 bytes • 101,563 2K pages

  28. Overhead Calculations • Other overhead not considered: • page overhead … 28 bytes per page • bitmap pages needed … can track 4032 pages per bitmap • slot table entry per row … 4 bytes • 255 rows per page max • no unnecessary splitting of rows • page size … 2K or 4K

  29. Fundamentals: What is a BITMAP Page? • A bitmap page keeps track of the fullness and type of other pages in a tablespace. • Each tablespace has at least one…the first one is always logical page 0. • Used a great deal when identifying pages and their types for a tablespace.

  30. Fundamentals: What is a BITMAP Page? • For 2K ports, a single bitmap can track 4032 pages. So large tables may have many bitmaps. • Used when inserting rows, allocating new pages, etc…

  31. Overhead Calculations $ oncheck –pt wisc_db:tenktup1 111,140 2K data pages used

  32. Overhead Calculations • let’s add some indexes • 1 indexes of INT – 4 bytes each • Fragmented table and indexes • quick estimate • 4 bytes * 1,000,000 rows = 4,000,000 bytes • 1,954 2K pages needed

  33. Overhead Calculations • Other index overhead not considered: • page overhead … 28 bytes per page • bitmap pages needed … can track 4032 pages per bitmap • slot table entry per row … 4 bytes • no unnecessary splitting of rows • page size … 2K or 4K • other B+ tree levels … leaves, branches, root

  34. Overhead Calculations 7,248 pages

  35. Summary

  36. TOPIC: IDS 9.4 - 2G Chunk Size • IDS 9.4 solves the 2G chunk/file size limit. • Has been an “oustanding” request for many years now. • There is a 2-step conversion for moving to large chunk support when going to 9.4. • after phase 1, you can still revert back. • after phase 2, you cannot.

  37. Old Chunk Size Limit Rightmost 20 bits (unsigned) of pg_addr field determined the number of pages in a chunk 0x 001 00008 Page offset Chunk number 1 nibble (half byte) = 4 bits f f f f f = 1,048,575 pages 1,048,575 pages x 2,048 bytes = 2,147,481,600 bytes

  38. Old Number of Chunks Limit Leftmost 12 bits (signed) of pg_addr field determined the number of chunks in an IDS instance 0x 001 00008 Page offset Chunk number 1 nibble (half byte) = 4 bits + 7 f f - = 2,047 chunks

  39. How’d We Do It? Modifying the page header structure while still retaining the 24 byte size has a number of advantages: • Minimizes conversion processing time • over 99% of the contents of the page untouched by system • allows “on-the-fly” conversion (discussed in the next module) • Minimizes risk to data • enables partial conversion for testing other 9.40 features • allows easy reversion from above partial conversion

  40. Page Address Expanded to 48 Bits • Pre-9.40 IDS: a 12-bit chunk number and a 20-bit page number in the chunk • IDS 9.40: a 16-bit chunk number and a 32-bit page offset within the chunk • the chunk number has a range of 1 to 32,767. • the offset has a range of 0 to 2,147,483,647 pages.

  41. IDS 9.40 Chunk Size Limit 32 bit (signed) pg_offset field determines the number of pages in a chunk. 0x 00000008 0001 Page offset 1 nibble (half byte) = 4 bits + 7 f f f f f f f - = 2,147,483,648 pages 2,147,483,648 pages x 2,048 bytes = 4,398,046,509,056 bytes

  42. IDS 9.40 Number of Chunks Limit A new 16 bit (signed) pg_chunk field determines the number of chunks in a 9.40 IDS instance. 0x 00000008 0001 Chunk number 1 nibble (half byte) = 4 bits + 7 f f f - = 32,767 chunks pages

  43. 9.3 engine: sysmaster:systables 9.4 engine: sysmaster:systables Before and After

  44. File Sizes of 18 Trillion MB Supported • All relevant 32-bit Unix platforms support compiler flags to enable large files (18 quintillion bytes) • All relevant Unix vendors support 64-bit hardware and offer 64-bit versions of their Unix operating systems • Where the offset of the beginning of a chunk was a 32-bit number, it has been increased to a 64-bit offset. This effectively lets IDS make full use of any device.

  45. TOPIC: Max Number of Extents • What’s the maximum number of extents for a tablespace? • Do you really want to know? • Odds of running out? Slim. • Will running out happen to you? • It did at CA Water….here’s an real example….

  46. CA Water

  47. Errors Reported

  48. maybe… 15166e10 8 8 0 1000000 1 PO- /dev/ius3102 1581d018 20 11 0 1000000 4 PO- /dev/ius5004 Disk/Dbspace Full?

  49. Out of Extents

  50. Wow… Tblspaces n address flgs ucnt tblnum physaddr npages nused npdata nrows nextns resident454 15caf420 0 3 20013a 231a8b 5184 5184 0 0 167 0 635 166cd488 0 1 2001fa 2329da 138512 138512 0 0 152 0 636 15cafda0 0 1 2001fb 2329db 4172 4169 0 0 169 0 639 166cdde0 0 2 2001fe 2329de 4992 4982 0 0 87 0 674 166e9588 0 1 200224 232a04 3200 3089 0 0 87 0 1142 15c9cb98 0 2 2004a4 2463ba 1240 1240 1235 24598 53 0 1248 167d0488 2 2 200512 2467b5 98304 96504 75817 75817 136 0 1255 1664ac38 0 1 200526 247458 4384 4384 0 0 97 0 1265 16730a20 0 1 200536 247468 3264 3264 0 0 90 0 1285 166e9018 0 1 20054d 24747f 3264 3180 0 0 90 0 1498 170694f8 0 1 200663 24866f 22640 22619 22612 247191 79 0 2764 16495200 400 2 200c41 23f2f0 1720 1675 1674 14110 51 0 2766 16495d58 0 1 200c43 23f2f2 588 581 0 0 41 0 3581 16398590 0 1 20148f c210ab 1000 976 905 13546 46 0 3615 176de778 0 1 2014b4 c210d0 36100 36072 0 0 53 0 3616 16791bc8 0 1 2014b5 c210d1 31248 31247 0 0 30 0 3795 16791448 602 3 201569 c2757c 566112 565646 557000 10953950 166 03798 167209f8 0 3 20156c c2757f 102468 102266 0 0 166 0 3921 16dd8888 0 2 800002 900005 6320 6311 6309 123530 104 0 5096 15ca9cf0 0 2 900002 a00005 6288 6285 6283 122875 77 0 5102 160e62f0 0 4 a00002 b00005 6304 6302 6300 123103 74 0 7666 17285c10 2 1 a00b0b 7cbf39 27504 27333 27326 914741 119 0 7877 16691da0 0 1 b00051 d05c9c 2536 2536 0 0 80 0 7881 1686a018 0 1 b00055 d05ca0 2044 2028 0 0 79 0 7882 166e9a90 2 1 b00056 d05ca1 398159 398159 303251 303251 169 0 7883 16748630 0 1 b00057 d05ca2 9000 8838 0 0 103 0 7884 1669ed28 0 1 b00058 d05ca3 2800 2800 0 0 84 0 7885 167296c0 0 1 b00059 d05ca4 2064 2064 0 0 79 0 7886 16749960 0 1 b0005a d05ca5 2144 2144 0 0 80 0 7890 1686a7a8 0 1 b0005f d05caa 2004 2004 0 0 66 0 7968 1686a578 0 1 b000b1 d19932 3676 3676 0 0 51 0 7974 1674b3e0 0 1 b000b8 d19939 1792 1792 0 0 49 0 7975 1674b610 0 1 b000b9 d1993a 1648 1639 0 0 52 0 8002 1661bbf8 0 1 b000e5 d19c2c 1262565 1262565 1262251 1769171 1968741 174447e0 0 1 b004e1 176b890 6584 6544 0 0 91 0 8882 1621d9c0 0 2 b00575 17acfa0 10864 10830 10827 119189 76 0 117 active, 9433 total num of extents

More Related