Performance Tuning

 Tuning the performance of a system involves adjusting various

parameters and design choices to improve its performance for a
specific application.
 Various aspects of a database-system design—ranging from high-
level aspects such as the schema and transaction design to
database parameters such as buffer sizes, down to hardware
issues such as number of disks—affect the performance of an
application.
 Each of these aspects can be adjusted so that performance is
improved. Unit 1 (QueryOptimization) 1
 Improving Set Orientation
 When SQL queries are executed from an application program, it is
often the case that a query is executed frequently, butwith
different values for a parameter.
 Each call has an overhead of communication with the server, in
addition to processing overheads at the server.
 For example: Find the total salary of all instructors in the
department:
select sum(salary) from instructor where dept name= ?

Unit 1 (QueryOptimization) 2
 Improving Set Orientation
 If the instructor relation does not have a clustered index on dept
name, each such query will result in a scan of the relation.
 Even if there is such an index, a random I/O operation will be
required for each dept name value.
 Instead, we can use a single SQL query to find total salary
expenses of each department:
select dept name, sum(salary) from instructor group by dept
name;

Unit 1 (QueryOptimization) 3
 Improving Set Orientation
 This query can be evaluated with a single scan of the instructor
relation, avoiding random I/O for each department.
 The results can be fetched to the client side using a single round of
communication, and the client program can then step through the
results to find the aggregate for each department.

Unit 1 (QueryOptimization) 4
 Improving Set Orientation
 Combining multiple SQL queries into a single SQL query can
reduce execution costs greatly in many cases–for example, if the
instructor relation is very large and has a large number of
departments.
 The JDBC API also provides a feature called batch update that
allows a number of inserts to be performed using a single
communication with the database.
 Another aspect of improving set orientation lies in rewriting
queries with nested subqueries.

Unit 1 (QueryOptimization) 5
Tuning of Bulk Loads and Updates
 When loading a large volume of data into a database (called a
bulk load operation), performance is usually very poor if the
inserts are carried out a separate SQL insert statements.
 One reason is the overhead of parsing each SQL query; a more
important reason is that performing integrity constraint checks
and index updates separately for each inserted tuple results in a
large number of random I/O operations.
 If the inserts were done as a large batch, integrity-constraint
checking and index update can be done in a much more set-
oriented fashion, reducing overheads greatly; so performance
Unit 1 (QueryOptimization) 6
Tuning of Bulk Loads and Updates

 To support bulk load operations, most database systems provide a

bulk import utility, and a corresponding bulk export utility.
 The bulk-import utility reads data from a file, and performs
integrity constraint checking as well as index maintenance in a
very efficient manner.

Unit 1 (QueryOptimization) 7
 Location of Bottlenecks
 The performance of most systems (at least before they are tuned)
is usually limited primarily by the performance of one or a few
components, called bottlenecks.
 For instance, a program may spend 80 percent of its time in a
small loop deep in the code, and the remaining 20 percent of the
time on the rest of the code; the small loop then is a bottleneck.

Unit 1 (QueryOptimization) 8
 Location of Bottlenecks
 Improving the performance of a component that is not a
bottleneck does little to improve the overall speed of the system.
 But improving the speed of the bottleneck loop could result in an
improvement of nearly 80 percent overall, in the best case.

Unit 1 (QueryOptimization) 9
 Tunable Parameters
 Database administrators can tune a database system at three
levels.
 The lowest level is at the hardware level.
 Options for tuning systems at this level include adding disks or
using a RAID system if disk I/O is a bottleneck, adding more
memory if the disk buffer size is a bottleneck, or moving to a
faster processor if CPU use is a bottleneck.

Unit 1 (QueryOptimization) 10
 Tunable Parameters
 The second level consists of the database-system parameters,
such as buffer size and check-pointing intervals.
 The exact set of database-system parameters that can be tuned
depends on the specific database system.
 Most database-system manuals provide information on what
database-system parameters can be adjusted, and how you
should choose values for the parameters.
 Well-designed database systems perform as much tuning as
possible automatically, freeing the user or database administrator
from the burden. Unit 1 (QueryOptimization) 11
 Tunable Parameters
 The third level is the highest level.
 It includes the schema and transactions.
 The administrator can tune the design of the schema, the indices
that are created, and the transactions that are executed, to
improve performance.
 Tuning at this level is comparatively system independent.

Unit 1 (QueryOptimization) 12
 Using Materialized Views
 Maintaining materialized views can greatly speed up certain types
of queries, in particular aggregate queries.
 Creating a materialized view storing the total salary for each
department can greatly speed up such queries.
 Materialized views should be used with care, however, since there
is not only space overhead for storing them but, more important,
there is also time overhead for maintaining materialized views.

Unit 1 (QueryOptimization) 13
 Using Materialized Views
 In the case of immediate view maintenance, if the updates of a
transaction affect the materialized view, the materialized view
must be updated as part of the same transaction.
 The transaction may therefore run slower.
 In the case of deferred view maintenance, the materialized view is
updated later; until it is updated, the materialized view may be
inconsistent with the database relations.
 The database administrator is responsible for the selection of
materialized views and for view-maintenance policies.
Unit 1 (QueryOptimization) 14
Automated Tuning of Physical Design
 Most commercial database systems today provide tools to help
the database administrator with index and materialized view
selection, and other tasks related to physical database design
such as how to partition data in a parallel database system.
 These tools examine the workload (the history of queries and
updates) and suggest indices and views to be materialized.

Unit 1 (QueryOptimization) 15
Automated Tuning of Physical Design
 The database administrator may specify the importance of
speeding up different queries, which the tool takes into account
when selecting views to materialize.
 Often tuning must be done before the application is fully
developed, and the actual database contents may be small on the
development database, but expected to be much larger on a
production database.
 Thus, some tuning tools also allow the database administrator to
specify information about the expected size of the database and
related statistics. Unit 1 (QueryOptimization) 16
Tuning of Concurrent Transactions
 Concurrent execution of different types of transactions can
sometimes lead to poor performance because of contention on
locks.
 For example consider read-write contention of a banking
database.
 During the day, numerous small update transactions are executed
almost continuously.
 Suppose that a large query that computes statistics on branches is
run at the same time.
Unit 1 (QueryOptimization) 17
Tuning of Concurrent Transactions
 If the query performs a scan on a relation, it may block out all
updates on the relation while it runs, and that can have a
disastrous effect on the performance of the system.
 In write-write contention, Data items that are updated very
frequently can result in poor performance with locking, with many
transactions waiting for locks on those data items.
 Such data items are called update hot spots.
 Update hot spots can cause problems even with snapshot isolation,
causing frequent transaction aborts due to write validation failures.
Unit 1 (QueryOptimization) 18
 Thank You !

Unit 1 (QueryOptimization)
19