Tuning is the act of changing an application and DBMS environment to improve system performance
Performance is usually measured in terms of response time
- Time to get the first tuple
- Time to get all the tuples to a query
A workload consists of various operations performed by the system and their frequency
- It is important to tune to improve workload, not just a single query
- Some queries are bottlenecks: performed frequently and/or are very expensive
Remember: when you improve the performance of one operation, other operations may slow down
For example, creating an index to speed up a query will slow down insert/update operations
Database Tuning Steps¶
Step 1: buy more or faster hardware
memory is crucial for buffering query operations and caches for various operations
hard disk speed is crucial, buy faster and more disks to improve the parallelism
Step 2: tune the system installation
databases provide a large number of tunable parameters, read database administration books
learn about making better use of multiple CPUs
- A cache is a set of buffer pages maintained by the DBMS for a specific purpose
- Data cache for reading pages containing the index or the relation
- Procedure cache for storing previously constructed query plans
- Caches are usually shared between concurrent users
- Any requested item must be brought to cache from disk to read/modified
- If it is already in the cache, then the cache has a hit, otherwise the cache has a miss
- Since each hit is a savings in time, hit ratio must be maximized (some application designers seek 90% hit ratio)
Cache replacement algorithms¶
- If a new item has to be inserted into the cache, another item might need to be removed.
- Cache replacement algorithm decides what should be removed, LRU
(least recently used), MRU (most recently used)
- A recently used page may be used for an update in near future (LRU)
- A page read in table scan is no longer needed (MRU)
- Sophisticated caches may take the algorithm using the database into account
- How would you use the cache for an index page?
- A dirty page is a page modified by an uncommitted transaction -if this page is moved out of cache, it must be written back to disk
Tuning the cache¶
Divide the cache and bind a specific item to a cache (different tables may be cached in different caches)
Divide the cache into pools of varied size, 2K, 4K, 8K, etc.
- The query processor can choose the best available cache for a query (retrieve large sequences for table scans, even prefetch disk pages that are expected to be requested next)
Procedure cache may use previously optimized query plans
Hint: use program variables to increase possible reuse
SELECT P.name FROM Professor P WHERE P.deptId = :deptid
When you use prepared statements such as this, query and parameters are sent separately, allowing the same query for different parameters to use the same query plan.
Step 3: partition your data
Vertical partitioning divides the attributes in the relation and distributes them to different disks or tablespaces
Frequently queried attributes could be separated from infrequently queried attributes.
Horizontal partitioning divides the tuples in the relation to multiple disks
Allows parallelism in reading data from disk
Some optimizers are able to concentrate on a single partition given a specific query
Step 4: change your data model
Normalization reduces redundancy and null values
Lower storage requirements, simple queries and updates will be faster
Results in more tables, hence complex queries need more joins
SELECT FAN.Title FROM Films F , FilmAlternateNames FAN WHERE F.filmid = FAN.filmid;
Denormalization stores relations in a non-optimal manner
Examples of denormalization¶
Store alternate names in a string and use application code to update and print the alternate names
Add extra columns for frequently accessed information
Example: number of movies per actor:
SELECT a.name , a.surname , count(distinct mr.movieid) FROM actors a , movieroles mr WHERE a.id=mr.actorid GROUP BY a.id , a.name , a.surname ;
Add a column “NumMovies” instead for each actor, no join or group by is needed.
This column must be updated in the application anytime an update is made to the casts relation. If updates are not frequent, then this could be acceptable.
Certain attributes might be duplicated if they are used often
Example: Store stagename attribute in the casts relation
Queries involving this attribute are now fully answered from casts (avoiding a costly join)
Attributes other than stagename might be queried rarely but take a lot of space causing the stagename to take up a lot of space
Anytime a new actor is added or stagename is changed, the changes must be reflected to the casts relation by updating
multiple tuples (this may be rare compared to the queries)
The CASTS relation now stores redundant information and is larger in size
Step 5: Rewrite queries to improve their speed, avoid nested queries, use joins whenever possible
Nested queries are hard to optimize.
Inner and outer expressions are optimized separately.
For correlated expressions, inner query is executed many times for each tuple in the outer expression.
Certain possible optimizations could be missed with a nested query (suppose an index for casts on (actorid, filmid) existed)
SELECT DISTINCT m.title FROM movies m , movieroles mr WHERE m.id=mr.movieid and exists ( select * from actors a where a.surname like '%Bacon' and a.id = mr.actorid);
All queries below are equivalent to the above one
-- uncorrelated query is better SELECT DISTINCT m.title FROM movies m , movieroles mr WHERE m.id=mr.movieid and mr.actorid in ( select a.id from actors a where a.surname like '%Bacon'); -- join query is even better SELECT DISTINCT m.title FROM movies m , movieroles mr , actors a WHERE m.id=mr.movieid and mr.actorid=a.id and a.surname like '%Bacon';
Drops of wisdom¶
Avoid sorts (distinct, order by, group by, union, except) if possible, they are costly
Some queries do not need a distinct or can be rewritten to avoid sorts
Avoid full table scans
- A search on a condition like A <> 3 or A like ‘%Bacon’ might result in a table scan
- A search like A in (1,2,4) might be preferable (depending on the availability of statistics)
Avoid retrieving tuples into application code, use stored procedures and even complicated queries might be preferable to the added communication cost
Use views wisely
Even though views are useful in application development, use a view in an application that is useful for the given query
CREATE VIEW together(actorid1, stagename1, actorid2, stagename2) AS SELECT a1.actorid, a1.stagename, a2.actorid, a2.stagename FROM actors a1 , actors a2 , movieroles mr1 , movieroles mr2 WHERE a1.id=mr1.actorid and mr1.movieid=mr2.movieid and a2.id=mr2.actorid and a1.id <> a2.id ; SELECT t.actorid1 FROM together t WHERE t.name = 'Kevin' and t.surname = 'Bacon';
- None of the joins are necessary to answer this query. The optimizer might miss some faster query plans
The use of indices¶
- Indices speed up query, but slow down insert/delete/update operations
- A clustered index allows fast access to a range query
- There is only one clustered index per relation
- Databases usually create one for the primary key by default
- Reconstruction of clustered indices is costly
- Step 6: choose the most useful indices
- Find the most useful clusters and use them if they are very useful for a range of queries and supported by the database
- Next, find the most selective indices to add
- Finally, find indices that might help with index only scans
We can create clustered indices in Postgresql by using an index:
create index mr_idx on movieroles(movieid,actorid) ; cluster movieroles using mr_idx ;
Clusters are generated once and are not modified incrementally
You need to recluster periodically if there are frequent updates:
The reorganization may be too costly for very large tables.
Create a clustered index for attributes frequently queried with a range or has multiple matching attributes for a value
Above cluster is very useful for finding actors in a movie:
select a.name , a.surname from movies m , movieroles mr , actors a where m.name = 'Harry Potter and the Goblet of Fire' and m.id = mr.movieid and mr.actorid = a.id;
Clustered indices also provide a sorted order to the relation
Create unclustered indices on attributes with high selectivity
SELECT A.name, A.surname FROM Actors A WHERE A.gender = 'F'; SELECT A.firstname , A.lastname FROM Actors A WHERE A.name = 'Kevin' and A.surname ='Bacon';
- Gender is not a selective condition, but name and surname are.
Index nested loop join is also beneficial when there is a highly selective index
SELECT mr.movieid FROM actors a, movieroles mr WHERE a.name = 'Kevin' and a.surname = 'Bacon' and a.id = mr.actorid;
For frequently asked queries, indices might be created to allow index only searches.
For example, given (name, surname, id) for actors, answering a query like one below now requires only an index search for actors.
actors a , movieroles mr
a.name = ‘Kevin’ and a.surname = ‘Bacon’ and a.id = mr.actorid;
- This is in effect a type of vertical partitioning.
For example, given a query like the one below:
SELECT a2.name , a2.surname FROM actors a1 , movieroles mr1 , movieroles mr2 , actors a2 WHERE a1.name = 'Kevin' and a1.surname = 'Bacon' and a1.id = mr1.actorid and mr1.movieid = mr2.movieid and a2.id = mr2.actorid;
for A1, the index is searched in the usual way.
For A2, the index on(stagename, actorid) can be searched fully instead of the relation to speed up the query.
Indices do not always help reduce the cost of queries:
- they must be selective
- they must be significantly smaller in size than the relation they are indexing
- they must be used often in queries where they make a difference
Foreign keys introduce hidden costs to updates since they must be checked for all updates that relate to them
Count queries can be answered using indices on attributes with a “NOT NULL”” constraint (check if the index indices null values)
- Partition data to multiple disks
- Place data that is accessed sequentially on its own disk
- Invoke parallel query processing when multiple CPUs are available
- Create more detailed statistics (histograms)
- Recompute statistics periodically as needed
- Examine the query plans generated by the system and influence them as necessary
Postgresql’s CBO (cost-based-optimizer) relies heavily on table statistics being available for all tables used in a query.
will recompute the statistics for all the tables in a database.
Must be run periodically for updated statistics.
You can ask the optimizer to give you the query plan for a query.
EXPLAIN query ; EXPLAIN SELECT mr.movieid FROM actors a, movieroles mr WHERE a.name = 'Kevin' and a.surname = 'Bacon' and a.id = mr.actorid; QUERY PLAN ---------------------------------------------------------------------------------------------- Hash Join (cost=3302.62..8991.86 rows=2 width=4) Hash Cond: (mr.actorid = a.id) -> Seq Scan on movieroles mr (cost=0.00..4695.07 rows=265107 width=8) -> Hash (cost=3302.61..3302.61 rows=1 width=4) -> Seq Scan on actors a (cost=0.00..3302.61 rows=1 width=4) Filter: (((name)::text = 'Kevin'::text) AND ((surname)::text = 'Bacon'::text)) (6 rows)
- There are many methods from changing data model, queries and storage methods to improve performance.
- Performance must be improved for the whole workload. Compute the total cost of all queries multiplied by their frequency before and after tuning.
- Tuning is a complex optimization problem due to dependencies between different actions. Concentrate on queries that contribute most to the workload.