Database Reference
In-Depth Information
•
Prefetching of data pages
•
Locking patterns
•
Logging overhead
•
Memory allocation
•
Disk space allocation
•
Storage device read/write mechanisms
Goals and Solution Options
Three broad goals prompt the tuning effort in a database environment:
•
Improve overall system throughput
•
Reduce response times for transactions and queries
•
Speed up applications
Bottlenecks
Performance is generally affected by constrictions or bottlenecks in
one or a few components. Removing bottlenecks produces the greatest benefit. A
database environment is like a complex queuing system. Bottlenecks in queuing
systems can slow down and even halt movement. In the same way, bottlenecks in a
database system can slow down the execution of queries and transactions. Before
undertaking a formalized tuning effort, first try to discover bottlenecks and elimi-
nate them.
Figure 17-7 presents a database environment as a queuing system and demon-
strates how bottlenecks can make transactions and queries sluggish.
Tuning Options
Performance tuning is an elaborate topic. Getting into too
detailed a discussion is beyond our scope here. We will consider three areas for per-
Buffer
Manager
Disk
Manager
Transaction
Transaction
Scheduler
Transaction
Manager
CPU
Concurrency
Control
Figure 17-7
Potential bottlenecks in the database environment.
Search WWH ::
Custom Search