8. Database Tuning Flashcards
Tuning principles
- Think ___, fix ___
- ___ breaks ___ (dividing the load on over more resources)
- Start-up costs are ___; running costs are ___ (order data, store on cache…)
- Render unto server what is due ___ (depends on resources of client and server)
- Be prepared for ___ (increasing speed is a combination of memory, disk and computational resources)
- Think globally, fix locally
- Partitioning breaks bottlenecks
- Start-up costs are high; running costs are low
- Render unto server what is due unto server
- Be prepared for trade-offs
Normalization
A relation R is normalized if A only ___ on X and X is the ___ of R
Depends
Only key
Functional dependency
Functional dependency X → A holds for
relation R, iff
- two differet record r and r’ have the same value of X and ___
A as well
Vertical Partitioning
Process of dividing a table in __ smaller tables that respect to only one object
It improves performance if only a few atributes are accessed
Two or more
Tuning Denormalization
Sacrificing ___ for the sake of
___
Normalization
Performance
Horizontal Partitioning
Instead of partitioning by atributes, do partitions by ___ to the atributes (selections) like A < 1000 and A >= 1000
Conditions
Aggregate Maintenance
Aggregate sums, averages, total values, etc in tables (___)
Materialized views
Index Usage
Many query optimizers will not use indexes in the presence of ___ expressions, ___/upper/lower expressions, numerical ___ of different types and comparisons with null
Arithmetic
Substring
Comparisons
Eliminate Unneeded DISTINCT
When distinct is used with already ___ atributes (primary-keys for example)
Distinct
Nested Queries
Rewrite queries to avoid nested queries, by, for example, put FROM table1, table2
Query Tuning
- ___ usage
- ___ of DISTINCT
- ___queries
- Use of ___
- Join ___
- Use of ___
- Use of ___
- Index usage
- Elimination of DISTINCT
- Nested queries
- Use of temporaries
- Join conditions
- Use of HAVING
- Use of views
Use of Temporaries
Avoid ___ table if not needed
Temporary
Join Conditions
Prefer join on ___ indexes, and then on ___ attributes rather than ___ (more efficient)
Clustered
Numerical
Strings
Use of HAVING
Do not use HAVING when ___ is enough
Just use it for ___ on groups
WHERE
aggregates
Use of views
Views may cause queries to execute ___
For example when we just want a simple user, but we use the view for that
Inefficiently
Types of Queries
1- ___ query - returns one value
2- ___ query - returns multiple values
3- ___ query - returns values inside a range
4- ___ query - returns a value with the desired prefix
5- ___ query - returns max or min value
6- ___ query - returns ordered values
7 - ___ query - returns grouped values
8 - ___ query - returns joined tabled values
- Point
- Multipoint
- Range
- Prefix
- Extremal
- Ordering
- Grouping
- Join
Index Structures
- ___ sndexes
- ___ indexes
B+-tree
Hash
Clustered index
___ of the table organization
Non-clustered index
___ of the table organization
Dependent
Independent
Dense index
One index per ___
Sparse Index
One index per ___
Any value v between extremes of page is inside the page
Clustered Indexes can be ___ and Non-clustered indexes must be ___
Record
Page
Dense or Sparse
Dense
Covering/Composite Indexes
Indexes that use two ___or more for a specific query
Attributes
Indexes on Small Tables
Use if offten have ___ updating a single record
multiple transactions
Tuning the recovery system
- Put the log on a ___ disk to avoid seeks
- Delay writing updates to database disks until a ___ time (usually threshold of data)
- Set intervals for database ___ and ___
- ___ the size of large update transactions
- Separate
- Convenient
- Dumps and checkpoints
- Reduce
Lock Tuning
- ___, reduce ___ and avoid ___
- Snapshot ___ (Each transaction executes against the version of the data that existed when the transaction started)
- Eliminate ___ Locking (one transaction or read-only transactions)
- Use ___ isolation levels when application allows
- Control the ___ of locking (rows instead of tables)
- ___ data definition statements (change tables, indexes, …)
- Circumvent Hot ___ (item assessed many times that forces several lock requests)
- Prefer ___ transactions to mitigate blocking
- Serialize / blocking / deadlocks
- Isolation
- Unnecessary
- Weaker
- Granularity
- Avoid
- Spots
- Shorter
Threads can be used to:
- Manage database ___
- Perform ___ tasks
- HAve a pool of ___ threads
- Connections
- System
- Worker
Database Buffer
Ideally, store as much as possible in ___ to avoid disk accesses
RAM
RAID usage
Improves ___ performance on ___ disks
Read
Multiple
Cache usage
Cache improves ___ and reduces data ___
Throughput
Volume
Hardware configuration
Add memory, disks, or ___
Processors
Producer-consumer hierarchy
– High-level consumers Ex ___
– Intermediate consumers/resources Ex ___
– Primary resources Ex ___
Aplications
Database Systems
Resources managed by the OS
Monitoring Tools
- ___ Explainers
- ___ Monitors
- ___ Monitors
- Query Plan Explainers
- Performance Monitors
- Event Monitors