Database Reference
In-Depth Information
the number of concurrent users accessing the system within a certain time window,
each user will enjoy faster response time at the expense of fewer overall transactions
completing in that duration.
Typically, on-line transaction processing (OLTP) systems (also called operational
databases) want low response time or high throughput, in terms of transactions per
second, depending on the application needs. A decision support system (DSS) also
wants low response time. However, a DSS might also want high throughput in terms
of data blocks read or written per unit time. This type of throughput is not necessarily
counterproductive to high concurrency and low response times. A batch (production)
system typically wants lower wait times. For example, everyone likes for the payroll
application to complete on time!
It is important to always consider the following two central tuning goals:
•
Maximize your return on investment (ROI). Invest your time and
effort wisely by working on the problems most likely to yield the
optimum improvement.
•
Minimize contention. Bottlenecks are characterized by delays and
waits; eliminate or reduce these whenever possible.
Finally, the following general-purpose database tuning goals should be considered:
•
Minimize the number of data blocks that need to be accessed;
review and rewrite database access code as necessary.
•
Use caching, buffering, and queuing whenever possible to
compensate for the electro-mechanical disadvantage (memory is
faster than disk).
•
Minimize the data transfer rates (the time it takes to read or write
data); fast disks, RAID, and parallel operations help do this.
•
Schedule concurrent programs that complement instead of
compete with each other.
21.5.2 Tuning Methodology
It is best to approach tuning with a structured methodology. After ensuring that the
operating system is performing at its peak and that sufficient operating system resources
have been allocated to your database system, you should tune following in this order:
•
Database design
•
Database application
•
Memory management
•
I/O management
•
Database contention
