Bringing Internals to the Surface

1
Bringing Internals to the Surface

• Internals Concepts for Highly Effective Engines

Mark ScrantonPrincipal Consultant/Trainer Informi
x Denver www.markscranton.com
2
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

3
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

4
Topics

• Physical pages vs. logical pages?
• Discussion about the value of knowing the
  difference
• The dog has swallowed its tail
• ...or the tale of physical log overflow
• PARTNUMs - and the story of getting to data
• Weve all seen themwhat does it really mean?

5
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

6
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

7
Physical Pages

• Always with respect to a chunk
• Numbering starts with 0
• Format 0xCCCPPPPPccc - chunk numberppppp -
  page offset into the chunk

8
Physical Pages
Chunk 3
30023c
30023d
30023f
30023e
300243
300242
300241
300240
0xCCCPPPPPc - chunk p - page offset
300247
300246
300245
300244
9
Logical Pages

• Always with respect to a tablespace
• Numbering starts with 0
• Format simply 0 through x

10
Logical Pages
30023c
30023d
30023f
30023e
create table sparky...
300243
300242
300241
300240
0
2
1
300247
300246
300245
300244
5
4
3
11
Usage Examples - Extent List
extent list on a partition page (oncheck -pt
skippysparky)
12
Usage Examples - Bad Page
13
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
14
Topic 2 Physical Log Overflow
Physical Log File
what happens now?
back to the beginning, and keep writing!
15
Physical Log Overflow
Physical Log File

• Two potential scenarios
• the chkpt completes, and we give the physical log
  a new logical beginning. In that case, we dont
  care about the overwritten pages
• if the engine falls over, youll most likely get
  stuck in fast recovery when trying to come back
  up

16
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 tablespace?

17
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 tablespace
• Typically one page per tablespace
• It describes the tablespace

18
partnum
0x
1 1/2 bytes (3 nibbles, or 3 hex digits)
2 1/2 bytes (5 nibbles, or 5 hex digits)
19
SELECT FROM mud
DATABASE sparky
page 3c
CREATE TABLE mud
CREATE DATABASE sparky IN dbspace4
20
Topic 4 Max Number of Extents

• Whats the maximum number of extents for a
  tablespace?
• Consider the previous topic, and the use of the
  partition page for a tablespace

21
The Partition Page
22
The Partition Page
oncheck -pt skippysparky
23
Oh by the Way...

• Do you really want to know the max?
• Fragmentation changes the max for a logical
  table
• 4K vs. 2K pages

24
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 its NOT the
  rowid for the row
• Used with varchars or really long rows (rows
  longer than a page typically)
• Well focus on varchars ...

25
Forward Pointers w/ varchars

• Problem - you can easily end up with a chain of
  forward pointer/remainder portions of rows
• This can happen when youve updated a varchar
  column and increased its size
• The new row size wont fit into its 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

26
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

27
Forward Pointers
28
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 its grown to be larger than a page (actual
  row length), then youll have at least one. This
  cant be avoided - the row is now longer than a
  page
• Alter index to cluster
• Same caveats as above

29
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 TechNotes for a full white paper on this
  topic
• Send me email - scranton_at_informix.com
• Checkout the website - www.markscranton.com

30
Thanks...

• For listening. Hope you learned something
• See you in class