Performance Tuning Adjusting various parameters and design choices to improve system performance for a specific application. Tuning is best done by 1.identifying bottlenecks,and 2.eliminating them. Can tune a database system at 3 levels: Hardware--e.g.,add disks to speed up l/O,add memory to increase buffer hits,move to a faster processor. Database system parameters--e.g.,set buffer size to avoid paging of buffer,set checkpointing intervals to limit log size. System may have automatic tuning. Higher level database design,such as the schema,indices and transactions(more later) Database System Concepts,5th Ed. 23.2 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5 23.2 ©Silberschatz, Korth and Sudarshan th Ed. 2 Performance Tuning Adjusting various parameters and design choices to improve system performance for a specific application. Tuning is best done by 1. identifying bottlenecks, and 2. eliminating them. Can tune a database system at 3 levels: Hardware -- e.g., add disks to speed up I/O, add memory to increase buffer hits, move to a faster processor. Database system parameters -- e.g., set buffer size to avoid paging of buffer, set checkpointing intervals to limit log size. System may have automatic tuning. Higher level database design, such as the schema, indices and transactions (more later)
Bottlenecks Performance of most systems(at least before they are tuned)usually limited by performance of one or a few components:these are called bottlenecks E.g.80%of the code may take up 20%of time and 20%of code takes up 80%of time Worth spending most time on 20%of code that take 80%of time Bottlenecks may be in hardware(e.g.disks are very busy,CPU is idle),or in software Removing one bottleneck often exposes another De-bottlenecking consists of repeatedly finding bottlenecks,and removing them This is a heuristic 3 Database System Concepts,5th Ed. 23.3 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5 23.3 ©Silberschatz, Korth and Sudarshan th Ed. 3 Bottlenecks Performance of most systems (at least before they are tuned) usually limited by performance of one or a few components: these are called bottlenecks E.g. 80% of the code may take up 20% of time and 20% of code takes up 80% of time Worth spending most time on 20% of code that take 80% of time Bottlenecks may be in hardware (e.g. disks are very busy, CPU is idle), or in software Removing one bottleneck often exposes another De-bottlenecking consists of repeatedly finding bottlenecks, and removing them This is a heuristic
ldentifying Bottlenecks Transactions request a sequence of services e.g.CPU,Disk I/O,locks With concurrent transactions,transactions may have to wait for a requested service while other transactions are being served Can model database as a queueing system with a queue for each service transactions repeatedly do the following request a service,wait in queue for the service,and get serviced Bottlenecks in a database system typically show up as very high utilizations (and correspondingly,very long queues)of a particular service E.g.disk vs CPU utilization 100%utilization leads to very long waiting time: Rule of thumb:design system for about 70%utilization at peak load utilization over 90%should be avoided Database System Concepts,5th Ed. 23.4 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5 23.4 ©Silberschatz, Korth and Sudarshan th Ed. 4 Identifying Bottlenecks Transactions request a sequence of services e.g. CPU, Disk I/O, locks With concurrent transactions, transactions may have to wait for a requested service while other transactions are being served Can model database as a queueing system with a queue for each service transactions repeatedly do the following request a service, wait in queue for the service, and get serviced Bottlenecks in a database system typically show up as very high utilizations (and correspondingly, very long queues) of a particular service E.g. disk vs CPU utilization 100% utilization leads to very long waiting time: Rule of thumb: design system for about 70% utilization at peak load utilization over 90% should be avoided
Queues In A Database System concurrency control manager .… lock lock request grant CPU manager transaction transaction source manager transaction page monitor page reply request page disk manager buffer request 州 manager page reply Database System Concepts,5th Ed. 23.5 ©Silberschat乜，Korth and Sudarshan
Database System Concepts, 5 23.5 ©Silberschatz, Korth and Sudarshan th Ed. 5 Queues In A Database System
Tunable Parameters Tuning of hardware Tuning of schema Tuning of indices Tuning of materialized views Tuning of transactions 6 Database System Concepts,5th Ed. 23.6 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5 23.6 ©Silberschatz, Korth and Sudarshan th Ed. 6 Tunable Parameters Tuning of hardware Tuning of schema Tuning of indices Tuning of materialized views Tuning of transactions