Slideshow:
How to measure performance of database operations
Performance of an order index: example
Information:
1000 data records in a data file
Index file contains
1000 search keys (+ pointers) (to the
1000 data records)
Each index block contains
10 search keys (+ pointers)
There are 100 (index) blocks) in the (ordered) index file
Performance of an order index: example
Question:
Given a search key for a data record...
How many index blocks do you need to access (= read) (in the worst case) to find the database address of that data record (in the index file)
Performance of an order index: example
Answer:
Use binary search in the index file to find the index block that contains the given search key
Review: multi-level index files
Example multi-level index
Consider the original index file:
Example multi-level index
We can add an (primary) index file on the (ordered) index file:
Example multi-level index
Since the 2nd level index is still large, we can add an (primary) index file on the 2nd index file:
Performance of a multi-level index file
Answer: 3 disk IO operations
We can use the top level index to find the index block in the 2nd level index that will (guaranteed) contain the search key:
Answer: 3 disk IO operations
Then, we use 2nd level index to find the index block in the final level index that will (guaranteed) contain the search key:
Answer: 3 disk IO operations
Finally, we access the (orignal) index file block and find the database address for the record:
How many level of indexes do you need ???
- Recall:
- Access times to
read
one sector on disk
is in the order of
milli seconds
(see:
click here)
Access times to read a disk block (consisting of many sectors) is in the order of 0.01 sec
- Access times to
read
one sector on disk
is in the order of
milli seconds
(see:
click here)
- The
time to process the
data
in a disk block (8 - 64 Kbytes)
is usually
< 0.01 sec !!!
- Therefore:
- When computing the
processing delay of
a database operation, we:
- count the number disk acesses needed to execute the database operation
- When computing the
processing delay of
a database operation, we:
- Consider the following
example:
- Ordered index on
a data file containing
100 records:
Each index disk block stores 10 indexes
The index file contains of 100 disk blocks
Question:
- How many disk block accesses do you need to find the database address of the record using a search key ?
Answer:
- We can use
binary search in the
index file to
find the index record that
contains the search key:
- Since there are 100 disk blocks,
in the worst case, we
will access:
- 2 log( 100 ) ~= 7 index blocks
- Ordered index on
a data file containing
100 records:
- A 2-level index
uses an
primary index file
on a
secondary index file:
- We can generalize this
by adding
more layers of
index files
- Multi-level indexes can reduce the number of disk accesses in the look up operations
- Consider the
original
ordered index file:
- We can add
a primary index on the
index file
(just like the above example):
Because the number of index blocks on the second level index is still large, we can add a 3rd level index file on the 2nd level index:
Question:
- How many disk block accesses do you need to find the database address of the record using a search key ?
Answer:
- We can use
top level index
to find the index block
in the 2nd level index that
will (guaranteed) contain the
search key:
- Then, we use
2nd level index
to find the index block
in the final level index that
will (guaranteed) contain the
search key:
- Finally, we
access the
(orignal) index file block and
find the
database address for
the record:
- # disk IO operations need to
find the database address for the
record:
3 disk accesses !!!
- So the number of disk IO is significantly reduced using a multi-level index file !
- Question:
- How many levels of index files do you need ?
Answer:
- Depends on the
# search keys in the
(ground level) index file
- The more keys you have in the index file, the more levels of indexes you may need.
- Best solution:
- Dynamically adjust the
number of levels of
indexes depending
on the
# search keys
in the (ground level) index file !!!
- This is the B+-tree indexing technique that we will discussed next...
- Dynamically adjust the
number of levels of
indexes depending
on the
# search keys
in the (ground level) index file !!!