Database Tuning

• 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

Disk caches

• 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.

Partitioning

• 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

Denormalization

• 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

Query Restructuring

• 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

Clustered Indices

• 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:

  cluster movieroles;
  

• 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.

  ::

  SELECT

  mr.movieid

  FROM

  actors a , movieroles mr

  WHERE

  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)

Other hints

• 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 Optimizer

• Postgresql’s CBO (cost-based-optimizer) relies heavily on table statistics being available for all tables used in a query.

  analyze;
  

  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)
  

Summary

• 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.