8. Database Tuning

Question 1

Tuning principles

1. Think ___, fix ___
2. ___ breaks ___ (dividing the load on over more resources)
3. Start-up costs are ___; running costs are ___ (order data, store on cache…)
4. Render unto server what is due ___ (depends on resources of client and server)
5. Be prepared for ___ (increasing speed is a combination of memory, disk and computational resources)

Answer 1

1. Think globally, fix locally
2. Partitioning breaks bottlenecks
3. Start-up costs are high; running costs are low
4. Render unto server what is due unto server
5. Be prepared for trade-offs

Question 2

Normalization

A relation R is normalized if A only ___ on X and X is the ___ of R

Answer 2

Depends

Only key

Question 3

Functional dependency
Functional dependency X → A holds for
relation R, iff
- two differet record r and r’ have the same value of X and ___

Answer 3

A as well

Question 4

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

Answer 4

Two or more

Question 5

Tuning Denormalization
Sacrificing ___ for the sake of
___

Answer 5

Normalization

Performance

Question 6

Horizontal Partitioning

Instead of partitioning by atributes, do partitions by ___ to the atributes (selections) like A < 1000 and A >= 1000

Answer 6

Conditions

Question 7

Aggregate Maintenance

Aggregate sums, averages, total values, etc in tables (___)

Answer 7

Materialized views

Question 8

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

Answer 8

Arithmetic
Substring
Comparisons

Question 9

Eliminate Unneeded DISTINCT

When distinct is used with already ___ atributes (primary-keys for example)

Answer 9

Distinct

Question 10

Nested Queries

Rewrite queries to avoid nested queries, by, for example, put FROM table1, table2

Question 11

Query Tuning

1. ___ usage
2. ___ of DISTINCT
3. ___queries
4. Use of ___
5. Join ___
6. Use of ___
7. Use of ___

Answer 11

1. Index usage
2. Elimination of DISTINCT
3. Nested queries
4. Use of temporaries
5. Join conditions
6. Use of HAVING
7. Use of views

Question 12

Use of Temporaries

Avoid ___ table if not needed

Answer 12

Temporary

Question 13

Join Conditions

Prefer join on ___ indexes, and then on ___ attributes rather than ___ (more efficient)

Answer 13

Clustered
Numerical
Strings

Question 14

Use of HAVING
Do not use HAVING when ___ is enough
Just use it for ___ on groups

Answer 14

WHERE

aggregates

Question 15

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

Answer 15

Inefficiently

Question 16

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

Answer 16

1. Point
2. Multipoint
3. Range
4. Prefix
5. Extremal
6. Ordering
7. Grouping
8. Join

Question 17

Index Structures

• ___ sndexes
• ___ indexes

Answer 17

B+-tree

Hash

Question 18

Clustered index
___ of the table organization
Non-clustered index
___ of the table organization

Answer 18

Dependent

Independent

Question 19

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 ___

Answer 19

Record
Page
Dense or Sparse
Dense

Question 20

Covering/Composite Indexes

Indexes that use two ___or more for a specific query

Answer 20

Attributes

Question 21

Indexes on Small Tables

Use if offten have ___ updating a single record

Answer 21

multiple transactions

Question 22

Tuning the recovery system

1. Put the log on a ___ disk to avoid seeks
2. Delay writing updates to database disks until a ___ time (usually threshold of data)
3. Set intervals for database ___ and ___
4. ___ the size of large update transactions

Answer 22

1. Separate
2. Convenient
3. Dumps and checkpoints
4. Reduce

Question 23

Lock Tuning

1. ___, reduce ___ and avoid ___
2. Snapshot ___ (Each transaction executes against the version of the data that existed when the transaction started)
3. Eliminate ___ Locking (one transaction or read-only transactions)
4. Use ___ isolation levels when application allows
5. Control the ___ of locking (rows instead of tables)
6. ___ data definition statements (change tables, indexes, …)
7. Circumvent Hot ___ (item assessed many times that forces several lock requests)
8. Prefer ___ transactions to mitigate blocking

Answer 23

1. Serialize / blocking / deadlocks
2. Isolation
3. Unnecessary
4. Weaker
5. Granularity
6. Avoid
7. Spots
8. Shorter

Question 24

Threads can be used to:

1. Manage database ___
2. Perform ___ tasks
3. HAve a pool of ___ threads

Answer 24

1. Connections
2. System
3. Worker

Question 25

Database Buffer | Ideally, store as much as possible in ___ to avoid disk accesses

Answer 25

RAM

Question 26

RAID usage | Improves ___ performance on ___ disks

Answer 26

Read | Multiple

Question 27

Cache usage | Cache improves ___ and reduces data ___

Answer 27

Throughput | Volume

Question 28

Hardware configuration | Add memory, disks, or ___

Answer 28

Processors

Question 29

Producer-consumer hierarchy – High-level consumers Ex ___ – Intermediate consumers/resources Ex ___ – Primary resources Ex ___

Answer 29

Aplications Database Systems Resources managed by the OS

Question 30

Monitoring Tools 1. ___ Explainers 2. ___ Monitors 3. ___ Monitors

Answer 30

1. Query Plan Explainers 2. Performance Monitors 3. Event Monitors