پاورپوینت کامل Chapter 23: Advanced Application Development 51 اسلاید در PowerPoint

توجه : این فایل به صورت فایل power point (پاور پوینت) ارائه میگردد

 پاورپوینت کامل Chapter 23: Advanced Application Development 51 اسلاید در PowerPoint دارای ۵۱ اسلاید می باشد و دارای تنظیمات کامل در PowerPoint می باشد و آماده ارائه یا چاپ است

شما با استفاده ازاین پاورپوینت میتوانید یک ارائه بسیارعالی و با شکوهی داشته باشید و همه حاضرین با اشتیاق به مطالب شما گوش خواهند داد.

لطفا نگران مطالب داخل پاورپوینت نباشید، مطالب داخل اسلاید ها بسیار ساده و قابل درک برای شما می باشد، ما عالی بودن این فایل رو تضمین می کنیم.

توجه : در صورت  مشاهده  بهم ریختگی احتمالی در متون زیر ،دلیل ان کپی کردن این مطالب از داخل فایل می باشد و در فایل اصلی پاورپوینت کامل Chapter 23: Advanced Application Development 51 اسلاید در PowerPoint،به هیچ وجه بهم ریختگی وجود ندارد

بخشی از مطالب داخلی اسلاید ها

پاورپوینت کامل Chapter 23: Advanced Application Development 51 اسلاید در PowerPoint

اسلاید ۴: ۴Identifying BottlenecksTransactions 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 followingrequest a service, wait in queue for the service, and get servicedBottlenecks in a database system typically show up as very high utilizations (and correspondingly, very long queues) of a particular serviceE.g. disk vs CPU utilization100% utilization leads to very long waiting time: Rule of thumb: design system for about 70% utilization at peak loadutilization over 90% should be avoided

اسلاید ۵: ۵Queues In A Database System

اسلاید ۶: ۶Tunable ParametersTuning of hardwareTuning of schemaTuning of indicesTuning of materialized viewsTuning of transactions

اسلاید ۷: ۷Tuning of HardwareEven well-tuned transactions typically require a few I/O operationsTypical disk supports about 100 random I/O operations per secondSuppose 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 memoryIf all data is in memory, I/O needed only for writesKeeping frequently used data in memory reduces disk accesses, reducing number of disks required, but has a memory cost

اسلاید ۸: ۸Hardware Tuning: Five-Minute RuleQuestion: 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-diskCost of keeping page in memory price-per-MB-of-memory ages-per-MB-of-memoryBreak-even point: value of n for which above costs are equalIf 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 RuleFor 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 memoryPrices of disk and memory have changed greatly over the years, but the ratios have not changed muchso rules remain as 5 minute and 1 minute rules, not 1 hour or 1 second rules!

اسلاید ۱۰: ۱۰Hardware Tuning: Choice of RAID LevelTo use RAID 1 or RAID 5 Depends on ratio of reads and writesRAID 5 requires 2 block reads and 2 block writes to write out one data blockIf an application requires r reads and w writes per secondRAID 1 requires r + 2w I/O operations per secondRAID 5 requires: r + 4w I/O operations per secondFor reasonably large r and w, this requires lots of disks to handle workloadRAID 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 otherwiseIf you need more disks to handle I/O load, just mirror them since disk capacities these days are enormous!

اسلاید ۱۱: ۱۱Tuning the Database DesignSchema tuningVertically partition relations to isolate the data that is accessed most often — only fetch needed information.E.g., split account into two, (account-number, branch-name) and (account-number, balance). Branch-name need not be fetched unless requiredImprove performance by storing a denormalized relation E.g., store join of account and depositor; branch-name and balance information is repeated for each holder of an account, but join need not be computed repeatedly.Price paid: more space and more work for programmer to keep relation consistent on updatesbetter to use materialized views (more on this later..)Cluster together on the same disk page records that would match in a frequently required join, compute join very efficiently when required.

اسلاید ۱۲: ۱۲Tuning the Database Design (Cont.)Index tuningCreate appropriate indices to speed up slow queries/updatesSpeed up slow updates by removing excess indices (tradeoff between queries and updates)Choose type of index (B-tree/hash) appropriate for most frequent types of queries.Choose which index to make clusteredIndex tuning wizards look at past history of queries and updates (the workload) and recommend which indices would be best for the workload

اسلاید ۱۳: ۱۳Tuning the Database Design (Cont.)Materialized ViewsMaterialized views can help speed up certain queriesParticularly aggregate queriesOverheadsSpaceTime for view maintenanceImmediate view maintenance:done as part of update txn time overhead paid by update transactionDeferred view maintenance: done only when requiredupdate transaction is not affected, but system time is spent on view maintenanceuntil updated, the view may be out-of-datePreferable to denormalized schema since view maintenance is systems responsibility, not programmersAvoids inconsistencies caused by errors in update programs

اسلاید ۱۴: ۱۴Tuning the Database Design (Cont.)How to choose set of materialized viewsHelping one transaction type by introducing a materialized view may hurt othersChoice of materialized views depends on costsUsers often have no idea of actual cost of operationsOverall, manual selection of materialized views is tediousSome database systems provide tools to help DBA choose views to materialize“Materialized view selection wizards”

اسلاید ۱۵: ۱۵Tuning of TransactionsBasic approaches to tuning of transactionsImprove set orientationReduce lock contentionRewriting of queries to improve performance was important in the past, but smart optimizers have made this less importantCommunication overhead and query handling overheads significant part of cost of each callCombine multiple embedded SQL/ODBC/JDBC queries into a single set-oriented querySet orientation -> fewer calls to databaseE.g. tune program that computes total salary for each department using a separate SQL query by instead using a single query that computes total salaries for all department at once (using group by) Use stored procedures: avoids re-parsing and re-optimization of query

اسلاید ۱۶: ۱۶Tuning of Transactions (Cont.)Reducing lock contentionLong transactions (typically read-only) that examine large parts of a relation result in lock contention with update transactionsE.g. large query to compute bank statistics and regular bank transactionsTo reduce contentionUse multi-version concurrency controlE.g. Oracle “snapshots” which support multi-version 2PLUse degree-two consistency (cursor-stability) for long transactionsDrawback: result may be approximate

اسلاید ۱۷: ۱۷Tuning of Transactions (Cont.)Long update transactions cause several problemsExhaust lock spaceExhaust log space and also greatly increase recovery time after a crash, and may even exhaust log space during recovery if recovery algorithm is badly designed!Use mini-batch transactions to limit number of updates that a single transaction can carry out. E.g., if a single large transaction updates every record of a very large relation, log may grow too big.* Split large transaction into batch of “mini-transactions, each performing part of the updates Hold locks across transactions in a mini-batch to ensure serializabilityIf lock table size is a problem can release locks, but at the cost of serializability* In case of failure during a mini-batch, must complete its remaining portion on recovery, to ensure atomicity.

اسلاید ۱۸: ۱۸Performance SimulationPerformance simulation using queu