The database must store an employee table consisting of 500,000 records each of

Question

The database must store an employee table consisting of 500,000 records each of which is 400 bytes long. A SSD has storage capacity of 1 TB, read bandwidth of 1 GB/sec., and a block size of 4096 bytes. Answer these questions:

a) What is the total capacity of RAID 0 with 6 drives? (0.5 marks)

b) What is the total capacity of RAID 5 with 10 drives? (0.5 marks)

c) How many records can be stored per block (blocking factor) using spanned and unspanned configurations? (2 marks)

d) What is the utilization for the unspanned configuration? (1 mark)

e) Assume the data is stored sequentially. How long does it take to read all records in the unspanned configuration? (1 mark)

f) Assume the file is physically ordered on key K, what is the average time to retrieve a record using key K in the spanned configuration? (2 marks)

g) The database designer wants to add a large description field of 1,000 bytes to each employee record. However, this field will only have data in it 20% of the time and is only accessed in 25% of the queries. The goal is to find the best record layout. (3 marks) For this question calculate:

The average time to read the whole table when using spanned, fixed records and no splitting.

The average time to read the whole table when using spanned, variable records.

The average time to read the whole table when using spanned records and splitting.

Assume a variable length record requires 20 bytes of overhead to store record information in addition to record data and a split record also requires 20 bytes of overhead. Also, the only layout whose performance will change depending on the queries is the split configuration as in that case the new field will be only accessed 25% of the time. For the other two configurations, the entire record is read for all queries.

Explanation / Answer

Ans a:

RAID0
Raw Storage: 6.0 TB / 6000.0 GB
Usable Storage: 5.5 TB / 5587.9 GB

Ans b:

RAID5
Raw Storage: 5.0 TB / 5000.0 GB
Usable Storage: 3.6 TB / 3725.3 GB

Ans c:

The database has 500000 million records.
The mean record size is 400 bytes.
Add the directory entry overhead (20 bytes) :
Mean record size + overhead = actual storage size
400 + 20 = 420
Divide that number into your database block size to determine the optimal records per block, as shown:

Spanned
all blocks are completely full except the last one
# of blocks required = 5,00,000 * 420 / 4096 = 51269 blocks(utilization is almost 100%)
Database block size / actual storage size = optimal records per block

4096 / 420 = 9.7532(approximatly 10)

Unspanned
put one record per block implies 5,00,000 blocks
each block is only 420/4096 * 100 = 10% full (utilization = 90%)

Divide that number into your database block size to determine the optimal records per block, as shown:
Database block size / actual storage size = optimal records per block
3676/ 420 = 8.7532(approximatly 9)

Ans d:

Utilization for spanned configaration 100%

Utilization for unspanned configaration 90%

Ans e:

It take 16.67 minutes to retrive all records

Ans f:

it takes 0.001 seconds is avaerage time to retrive one record

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.