Database Systems: Efficiency Issues and RAID Techniques, Slides of Introduction to Database Management Systems

Download Database Systems: Efficiency Issues and RAID Techniques and more Slides Introduction to Database Management Systems in PDF only on Docsity! 1 Efficiency Issues G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS Physical Design • Design so far • E/R modelling helps find the requirements of a database • Normalisation helps to refine a design by removing data redundancy • Physical design • Concerned with storing and accessing the data • How to deal with media failures • How to access information efficiently Efficiency Issues G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS RAID Arrays • RAID - redundant array of independent (inexpensive) disks • Storing information across more than one physical disk • Speed - can access more than one disk • Robustness - if one disk fails it is OK • RAID techniques • Mirroring - multiple copies of a file are stored on separate disks • Striping - parts of a file are stored on each disk • Different levels (RAID 0, RAID 1…) Efficiency Issues G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS RAID Level 0 • Files are split across several disks • For a system with n disks, each file is split into n parts, one part stored on each disk • Improves speed, but no redundancy Disk 1 Disk 2 Disk 3 Data Data1 Data2 Data3 Efficiency Issues G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS RAID Level 1 • As RAID 0 but with redundancy • Files are split over multiple disks • Each disk is mirrored • For n disks, split files into n/2 parts, each stored on 2 disks • Improves speed, has redundancy, but needs lots of disks Disk 1 Disk 2 Disk 3 Data Data1 Data2 Disk 4 Efficiency Issues G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS Parity Checking • We can use parity checking to reduce the number of disks • Parity - for a set of data in binary form we count the number of 1s for each bit across the data • If this is even the parity is 0, if odd then it is 1 1 0 1 1 0 0 1 1 0 0 1 1 0 0 1 1 1 0 1 0 1 0 0 1 0 1 1 0 1 1 1 0 0 1 0 0 0 1 1 1 Efficiency Issues G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS Recovery With Parity • If one of our pieces of data is lost we can recover it • Just compute it as the parity of the remaining data and our original parity information 1 0 1 1 0 0 1 1 0 0 1 1 0 0 1 1 0 1 1 0 1 1 1 0 0 1 0 0 0 1 1 1 2 Efficiency Issues G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS RAID Level 3 • Data is striped over disks, and a parity disk for redundancy • For n disks, we split the data in n-1 parts • Each part is stored on a disk • The final disk stores parity information Disk 1 Disk 2 Disk 3 Data Data1 Data2 Disk 4 Data3 Parity Efficiency Issues G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS Other RAID Issues • Other RAID levels consider • How to split data between disks • Whether to store parity information on one disk, or spread across several • How to deal with multiple disk failures • Considerations with RAID systems • Cost of disks • Do you need speed or redundancy? • How reliable are the individual disks? • ‘Hot swapping’ • Is the disk the weak point anyway? Efficiency Issues G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS Indexes • Indexes are to do with ordering data • The relational model says that order doesn’t matter • From a practical point of view it is very important • Types of indexes • Primary or clustered indexes affect the order that the data is stored in a file • Secondary indexes give a look-up table into the file • Only one primary index, but many secondary ones Efficiency Issues G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS Index Example • A telephone book • You store people’s addresses and phone numbers • Usually you have a name and want the number • Sometimes you have a number and want the name • Indexes • A clustered index can be made on name • A secondary index can be made on number Efficiency Issues G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS Index Example Name Number John 925 1229 Mary 925 8923 Jane 925 8501 Mark 875 1209 As a Table As a File Secondary Index 8751209 9251229 9258501 9258923 Jane, 9258501 John, 9251229 Mark, 8751209 Mary, 9258923 Order does not really concern us here Most of the time we look up numbers by name, so we sort the file by name Sometimes we look up names by number, so we index name Efficiency Issues G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS Choosing Indexes • You can only have one primary index • The most frequently looked-up value is often the best choice • Some DBMSs assume the primary key is the primary index, as it is usually used to refer to rows • Don’t create too many indexes • They can speed up queries, but they slow down inserts, updates and deletes • Whenever the data is changed, the index may need to change