Bringing Internals to the Surface Internals Concepts for
Bringing Internals to the Surface Internals Concepts for Highly Effective Engines Mark Scranton Principal Consultant/Trainer Informix Denver www. markscranton. com
Who Am I? • • Principal Consultant/Trainer, Informix Denver I do engines - XPS and IDS 5+ yrs with Informix website - www. markscranton. com • tips/tricks • sql/scripts • documents, presentations Informix user. conference 2
Presentation Overview • This presentation will identify “internals” concepts that appear to be trivia • But they could be useful daily for maintenance and support of IDS • A strong working knowledge of IDS is assumed • More info available in the “IDS Internal Architecture” class Informix user. conference 3
Topics • Physical pages vs. logical pages? • Discussion about the value of knowing the difference • The dog has swallowed it’s tail • . . . or the tale of physical log overflow • PARTNUMs - and the story of getting to data • We’ve all seen them…what does it really mean? Informix user. conference 4
Topics • The maximum number of extents for a table? • Is there REALLY a max? • Where did my row go? • The long tale of the forward pointer • The last topic Informix user. conference 5
Topic 1: Logical Pages vs. Physical Pages • Understanding the difference can help in: • • • 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 Informix user. conference 6
Physical Pages • Always with respect to a chunk • Numbering starts with 0 • Format: 0 x. CCCPPPPP ccc - chunk number ppppp - page offset into the chunk Informix user. conference 7
Physical Pages Chunk 3 30023 c 30023 d 30023 e 30023 f 300240 300241 300242 300243 0 x. CCCPPPPP c - chunk # p - page offset 300244 300245 300246 300247 Informix user. conference 8
Logical Pages • Always with respect to a tablespace • Numbering starts with 0 • Format simply 0 through x Informix user. conference 9
Logical Pages 30023 e 30023 f extent 1 for sparky 300240 300241 300242 300243 create table sparky. . . 30023 c 30023 d physical page num logical page num 0 1 2 300244 300245 300246 300247 3 10 4 5
Usage Examples - Extent List extent list on a partition page (oncheck -pt skippy: sparky) chunk 0 x 4 page 0 x 33 Informix user. conference 11
Usage Examples - Bad Page Informix user. conference 12
Usage Examples - Lock Level rowid format: 0 x. LLLLLLSS L - logical page in the table S - slot/row # on the page onstat -k oncheck -pp 0 x 100016 2 Informix user. conference 13
Topic 2: Physical Log Overflow Physical Log File what happens now? 75% full: checkpoint request is raised back to the beginning, and keep writing! 1 st before images of modified cache pages Informix user. conference 14
Physical Log Overflow Physical Log File 75% full: checkpoint request is raised • Two potential scenarios: • the chkpt completes, and we give the physical log a new logical beginning. In that case, we don’t care about the overwritten pages • if the engine falls over, you’ll most likely get stuck in fast recovery when trying to come back up Informix user. conference 15
Topic 3: Partnums • Three areas focused on here: • What is a partnum? • How do we use it to get to stuff • What is a tablespace? Informix user. conference 16
Definitions • Tablespace tablespace • Keeps track of tablespaces, or partitions, in a dbspace • Each dbspace has one • Typical allocation is 50 pages for each extent • Partition page • A page in a tablespace • Typically one page per tablespace • It “describes” the tablespace Informix user. conference 17
partnum Logical page num within the tablespace (starting at 0) dbspace number (starting at 1) 0 x D D D L L L 1 1/2 bytes (3 nibbles, or 3 hex digits) 2 1/2 bytes (5 nibbles, or 5 hex digits) 4 bytes 18 Informix user. conference
rootdbs database tablespace name owner created sparky informix 11 -11 -1999 flags partnum 00200003 SELECT *sparky; FROM mud; DATABASE dbspace 4 tablespace page 3 c CREATE mud; sparky IN dbspace 4; CREATE TABLE DATABASE systables database sparky name partnum mud 0020003 c 19 extents for mud Informix user. conference table mud
Topic 4: Max Number of Extents • What’s the maximum number of extents for a tablespace? • Consider the previous topic, and the use of the partition page for a tablespace Informix user. conference 20
The Partition Page Informix user. conference 21
The Partition Page oncheck -pt skippy: sparky Informix user. conference 22
Oh by the Way. . . • Do you really want to know the max? • Fragmentation changes the max for a logical table • 4 K vs. 2 K pages Informix user. conference 23
Topic 5: Forward Pointers • Forward pointer - a 4 -byte number that “points” to the remainder pieces of a row that spans pages • Used with rows longer than a page • Format is same as a rowid, although it’s NOT the rowid for the row • Used with varchars or really long rows (rows longer than a page typically) • We’ll focus on varchars. . . Informix user. conference 24
Forward Pointers w/ varchars • Problem - you can easily end up with a “chain” of forward pointer/remainder portions of rows • This can happen when you’ve updated a varchar column and increased it’s size • The new row size won’t fit into it’s old slot, and so we: • Move the row - all or some of it - to another page • Leave back a 4 -byte forward pointer on the home page Informix user. conference 25
Forward Pointers w/ varchars • This can happen as many times as it needs • Every move adds an additional 8 bytes to the row length • 4 bytes for the forward pointer in the home page • 4 bytes for the slot table entry on the new/remainder page Informix user. conference 26
Forward Pointers Informix user. conference 27
What Can I Do? • To rid yourself of the “chaining” effect: • Unload/reload the table • If the row will fit onto a single page, then there is no need for the forward pointer • If it’s grown to be larger than a page (actual row length), then you’ll have at least one. This can’t be avoided - the row is now longer than a page • Alter index to cluster • Same caveats as above Informix user. conference 28
Topic 7: How Do You Find Out More? • Attend the “IDS Internal Architecture” class • 5 days - very intense use of oncheck, and topics covered in this presentation • Taught in Denver, Chicago, Menlo Park most often • Watch Tech. Notes for a full white paper on this topic • Send me email - scranton@informix. com • Checkout the website - www. markscranton. com Informix user. conference 29
Thanks. . . • For listening. Hope you learned something • See you in class Informix user. conference 30
- Slides: 30