Transcript Bringing Internals to the Surface Internals Concepts for Highly Effective Engines Mark Scranton Principal Consultant/Trainer Informix Denver www.markscranton.com.

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:
0xCCCPPPPP
ccc - chunk number
ppppp - page offset into the chunk
Informix
user.conference
7
Physical Pages
Chunk 3
30023c 30023d
30023e 30023f
300240 300241 300242 300243
0xCCCPPPPP
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
30023e 30023f
extent 1 for
sparky
300240 300241 300242 300243
create table
sparky...
30023c 30023d
physical
page num
logical
page num
0
1
2
300244 300245 300246 300247
3
4
5
Usage Examples - Extent List
extent list on a partition page
(oncheck -pt skippy:sparky)
chunk 0x4
page 0x33
Informix
user.conference
11
Usage Examples - Bad Page
Informix
user.conference
12
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
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!
1st 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 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 tablespace
• Typically one page per tablespace
• It “describes” the tablespace
Informix
user.conference
17
partnum
Logical page num
within the tablespace
tablespace (starting at 0)
dbspace number
(starting at 1)
0x D D D L L 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
Informix
user.conference
18
rootdbs
database tablespace
name
owner
created
sparky
informix 11-11-1999
flags
partnum
00200003
SELECT *sparky;
FROM mud;
DATABASE
dbspace4
tablespace tablespace
page 3c
CREATE
mud; sparky IN dbspace4;
CREATETABLE
DATABASE
systables
database
sparky
name
partnum
mud 0020003c
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
• 4K vs. 2K 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 TechNotes for a full white paper
on this topic
• Send me email - [email protected]
• 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