Tunable Parameters Tuning of hardware Tuning of schema Tuning of indices Tuning of materialized views Tuning of transactions Database System Concepts-6th Edition 24.7 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 24.7 ©Silberschatz, Korth and Sudarshan th Edition Tunable Parameters Tuning of hardware Tuning of schema Tuning of indices Tuning of materialized views Tuning of transactions
Tuning of Hardware Even well-tuned transactions typically require a few l/O operations Typical disk supports about 100 random l/O operations per second Suppose each transaction requires just 2 random I/O operations. Then to support n transactions per second,we need to stripe data across n/50 disks (ignoring skew) Number of I/O operations per transaction can be reduced by keeping more data in memory If all data is in memory,I/O needed only for writes Keeping frequently used data in memory reduces disk accesses, reducing number of disks required,but has a memory cost Database System Concepts-6th Edition 24.8 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 24.8 ©Silberschatz, Korth and Sudarshan th Edition Tuning of Hardware Even well-tuned transactions typically require a few I/O operations Typical disk supports about 100 random I/O operations per second Suppose each transaction requires just 2 random I/O operations. Then to support n transactions per second, we need to stripe data across n/50 disks (ignoring skew) Number of I/O operations per transaction can be reduced by keeping more data in memory If all data is in memory, I/O needed only for writes Keeping frequently used data in memory reduces disk accesses, reducing number of disks required, but has a memory cost
Hardware Tuning:Five-Minute Rule Question:which data to keep in memory: If a page is accessed n times per second,keeping it in memory saves n* price-per-disk-drive accesses-per-second-per-disk Cost of keeping page in memory price-per-MB-of-memory ages-per-MB-of-memory Break-even point:value of n for which above costs are equal If accesses are more then saving is greater than cost Solving above equation with current disk and memory prices leads to: 5-minute rule:if a page that is randomly accessed is used more frequently than once in 5 minutes it should be kept in memory (by buying sufficient memory!) Database System Concepts-6th Edition 24.9 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 24.9 ©Silberschatz, Korth and Sudarshan th Edition Hardware Tuning: Five-Minute Rule Question: which data to keep in memory: If a page is accessed n times per second, keeping it in memory saves n * price-per-disk-drive accesses-per-second-per-disk Cost of keeping page in memory price-per-MB-of-memory ages-per-MB-of-memory Break-even point: value of n for which above costs are equal If accesses are more then saving is greater than cost Solving above equation with current disk and memory prices leads to: 5-minute rule: if a page that is randomly accessed is used more frequently than once in 5 minutes it should be kept in memory (by buying sufficient memory!)
Hardware Tuning:One-Minute Rule For sequentially accessed data,more pages can be read per second. Assuming sequential reads of 1MB of data at a time: 1-minute rule:sequentially accessed data that is accessed once or more in a minute should be kept in memory Prices of disk and memory have changed greatly over the years,but the ratios have not changed much So rules remain as 5 minute and 1 minute rules,not 1 hour or 1 second rules！ Database System Concepts-6th Edition 24.10 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 24.10 ©Silberschatz, Korth and Sudarshan th Edition Hardware Tuning: One-Minute Rule For sequentially accessed data, more pages can be read per second. Assuming sequential reads of 1MB of data at a time: 1-minute rule: sequentially accessed data that is accessed once or more in a minute should be kept in memory Prices of disk and memory have changed greatly over the years, but the ratios have not changed much So rules remain as 5 minute and 1 minute rules, not 1 hour or 1 second rules!
Hardware Tuning:Choice of RAID Level To use RAID 1 or RAID 5? Depends on ratio of reads and writes RAID 5 requires 2 block reads and 2 block writes to write out one data block If an application requires r reads and w writes per second RAID 1 requires r+2w l/O operations per second RAID 5 requires:r+4w l/O operations per second For reasonably large r and w,this requires lots of disks to handle workload RAID 5 may require more disks than RAID 1 to handle load! Apparent saving of number of disks by RAID 5(by using parity,as opposed to the mirroring done by RAID 1)may be illusory! Thumb rule:RAID 5 is fine when writes are rare and data is very large, but RAID 1 is preferable otherwise If you need more disks to handle I/O load,just mirror them since disk capacities these days are enormous! Database System Concepts-6th Edition 24.11 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 24.11 ©Silberschatz, Korth and Sudarshan th Edition Hardware Tuning: Choice of RAID Level To use RAID 1 or RAID 5? Depends on ratio of reads and writes RAID 5 requires 2 block reads and 2 block writes to write out one data block If an application requires r reads and w writes per second RAID 1 requires r + 2w I/O operations per second RAID 5 requires: r + 4w I/O operations per second For reasonably large r and w, this requires lots of disks to handle workload RAID 5 may require more disks than RAID 1 to handle load! Apparent saving of number of disks by RAID 5 (by using parity, as opposed to the mirroring done by RAID 1) may be illusory! Thumb rule: RAID 5 is fine when writes are rare and data is very large, but RAID 1 is preferable otherwise If you need more disks to handle I/O load, just mirror them since disk capacities these days are enormous!