Transcript Database Tuning

An Introduction to Database Tuning
Spring 2002
Prof. Sang Ho Lee
School of Computing, Soongsil Univ.
[email protected]
1
Database Tuning
Database tuning is the activity of making a database application
run more quickly. “More quickly” usually means higher throughput,
though it may mean lower response time for some applications.
Database tuning is difficult, because it requires a broad and deep
understanding of applications, database systems, operating
systems, hardware.
2
What is Different about Tuning
Conventional query optimization research:
Invent algorithms that would help make DBMSs run faster
Entails changing DBMSs
Does not assume knowledge of application context
Tuning:
Invent methods that will make my applications run faster on
my DBMS
Cannot change DBMSs
Can make use of application context
3
Tools
Tools
DEC’s RDB Expert
AT&T Teradata Data Navigator’s Configurator
And more
4
Further Reading
D. Shasha, Database Tuning: A Principled Approach, Prentice Hall PTR, 1992.
D. Shasha, Tuning Databases for High Performance, ACM Computing
Surveys 28(1): 113-115, 1996.
D. Shasha, Tuning Database Design for High Performance, The Computer
Science and Engineering Handbook: 995-1011, Allen B. Tucker ed., CRC
Press, 1997.
Special Issue on Self-Tuning Databases and Application Tuning, IEEE Bulletin
of the Technical Committee on Data Engineering 22(2): 3-40, 1999.
D. Shasha and P. Bonnet, Database Tuning: Principles, Experiments, and
Troubleshoot Techniques, Morgan Kaufmann, 2002.
5
Chapter 1: Four Basic Principles
Think globally, fix locally
Bottlenecks are the enemy, Partitioning is the cure
Start-up costs should be minimized
Render onto server what is due onto server
6
Think Globally, Fix Locally
Effective tuning requires a proper identification of the problem and minimalist
intervention
Example 1
Condition: Query is slow
Tune it?
Wait: Check accounting statistics to make sure it is important
Tuning infrequent queries does not pay off system wide
Example 2
Condition: Disk is saturated
Buy a new disk?
Wait: Does a query scan instead of using an index
Is log on a disk by itself
7
Partitioning Breaks Bottlenecks
Bottleneck = system resource that is fully utilized, creating an
upper limit on the throughput
Partitioning = dividing work to avoid saturating resource
by dividing the load over more resource
by spreading the load over time
When you find a bottleneck, first try to speed up that component.
If that doesn’t work, then partition
Partitioning could entail additional communication expense
8
Partitioning Examples
Bank has N branches. Most clients access their account data
from their home branch — partition accounts pertaining to branch
i in subsystem i (spatial partitioning)
Contention between long transactions and on-line ones —
perform the long transaction when there is little online transaction
activity (for example, at night) (temporal partitioning)
Lock contention on free lists — create more free lists (logical
partitioning)
9
In Oracle, tuning parameter is FREE_LIST_PROC
…
LATCHES
…
Two free lists
of length n
…
…
LATCHES
…
Four free lists
of length n/2
…
10
Start-up Costs are High; Running Costs are Low
Nearly same time to read a track as to read part of one --- so
scans and log writes should avoid seeks
Parsing time is high --- so good to save complied queries
Client/server interaction is expensive --- so retrieve set at a time
Obtain the effect you want with the fewest possible start-ups
11
Render onto Server What Is Due onto Server
An important design question is the allocation of work between
the database system (server) and the application program (client)
The relative computing resources of client and server
Data buffer for compute-intensive applications in client
workstations
12