Advantage CAIDMSDB Database Design for Performance

1
Advantage CA-IDMS/DB Database Design for
Performance
Part 2

• Presented by Manfred Hoefer Computer Associates
• UKIUA ROADSHOW 2004

2
Abstract

• This class analyzes and researches factors in
  physical database design that affect performance
  for non-SQL defined and SQL defined Advantage
  CA-IDMS/DB databases. The instructor will present
  alternative approaches within the database design
  and modification of the design for high volume,
  high output systems. The instructor will lead
  discussions on the causes of poor performance,
  physical design options, data compression, record
  clustering, causes of bottlenecks and deadlocks,
  and SQL physical design options.

3
Agenda

• Part 1
• Causes of Poor Performance
• Physical Design Options
• Data Compression
• Part 2
• Record Clustering
• Bottlenecks/Deadlocks
• SQL Physical Design Options

4
Record Clustering

• Page size selection will have a great impact on
  the number of I/O operations needed to process a
  cluster
• Things that can go wrong
• Clustering around the wrong owner
• Cluster size that are too large
• Too many record occurrences
• Too many record types clustered together
• Mixed clusters of volatile and static record
  types

5
Record Clustering
(continued)

• Clustering around the improper owner can result
  in unwanted I/O during critical processing

C
ORDER
ORDER-ITEM 15
V
ITEM
PRODUCT-ITEM 11000
C
PRODUCT
6
Record Clustering
(continued)

• If the majority of tasks just retrieve an ORDER
  and its ITEM records, the selection of the VIA
  set for the ITEM record will have the following
  effects
• Use of the ORDER-ITEM set as the VIA set will
  result in one I/O to process the average set
• Use of the PRODUCT-ITEM set as the VIA set will
  result in 6 I/Os to process the average set

7
Record Clustering
(continued)

• Be aware of the deviation from the average set
  length, especially when the set is a user-owned
  index set

ORDER-ITEM MA IO ASC ITEM-NUM
Average length 100 80 of sets 1-5
20 of sets 1000-50,000
8
Record Clustering
(continued)

• The longer sets would require an extremely large
  amount of space for SR8s which would clash with
  other set occurrences

9
Record Clustering
(continued)

• A system-level index gives more flexibility in
  assigning index options to insure an efficient
  index structure. This in turn provides for more
  control over record clustering

ASC (ORD-NUM, ITEM-NUM)
10
Record Clustering
(continued)

• When a VIA set is extremely large, there are many
  record types within the cluster, or a cluster is
  composed of volatile and static record types,
  consider moving some member record types to
  another area

C
C
V
CUSTOMER
ORDER
AREA-1
AREA-2
11
Record Clustering
(continued)

• ORDER records will reside in the same relative
  position in the member area with respect to the
  owning CUSTOMER record occurrences within the
  owner area. This will produce the desired
  clustering effect.

12
Record Clustering
(continued)
D1
P1
P2
D2
D1/P1 D2/P2
01 02 03
04 05 06
13
Record to Area Mappings

• Varying record sizes
• Clustering considerations
• Varying processing requirements by record type
• Serial processing (area sweeps)
• Physical sequential processing
• Indexes

14
Record to Area Mappings
(continued)

• Backup/recovery and DBA maintenance
• Segmentation of large databases

15
Area to File Mapping

• Areas can map 11 to files
• Easiest to recover and maintain

16
Area to File Mapping
(continued)

• Areas can map 1M to files
• Can be most efficient for large databases

17
Area to File Mapping
(continued)

• Avoid mapping areas M1 to files
• Restricts buffer allocation
• Complicates DBA maintenance

18
Concurrency

• Timeouts

T1 (ACCESS)
ORDER1
T1 (ACCESS)
T3 (WAITS)
T2 (WAITS)
19
Concurrency
(continued)

• Deadlocks

T1 (WAITS)
T1 (ACCESS)
T2 (ACCESS)
ORDER1
T2 (DEADLOCKS)
20
Bottlenecks/Deadlocks

• Single-thread the resource at the latest possible
  time
• FIND/OBTAIN KEEP EXCLUSIVE
• Enqueue
• Release the resource as soon as possible
• COMMIT ALL
• Dequeue

21
Bottlenecks/Deadlocks
(continued)

• Concurrent access of a single set occurrence or
  database page by many tasks

TERM-ID
OOAK CALC
TERMINAL DIRECT
BECOMES
22
Bottlenecks/Deadlocks
(continued)

• Allow each terminal to isolate its PENDTRAN
  records

I
T3
T1
T2
P8
P6
P1
P3
P5
P9
P7
P2
P4
23
Bottlenecks/Deadlocks
(continued)

• Record types that support conflicting processing
  requirements

ACCT
BANK
INVEST
ACCTTRAN
24
Bottlenecks/Deadlocks
(continued)

• The BANK record contains retrieval data for
  investment transactions and update data for
  account transactions

25
Bottlenecks/Deadlocks
(continued)

• Separate update elements into their own record

BANK
ACCT
INVEST
ACCTTRAN
BANKBAL
26
SQL Physical Design Options

• SQL defined databases utilize the same physical
  structures as Non-SQL databases, so the same
  performance considerations apply
• SQL databases differ from Non-SQL databases in
  the manner in which database structures are
  implemented
• Defining Calckeys
• Indexes
• Referential constraints

27
SQL Calckeys

• Creating a calckey for a table causes each row of
  the table to be physically stored as a CALC
  record. In other words, randomized across its
  area based on the value of the column(s) named as
  the calckey
• A table might have a calckey defined for the same
  reasons as in a Non-SQL defined database
• Direct access based on a keys value
• Ability to distribute the rows across an area

28
SQL Indexes

• A default system-level index is defined for a
  table whenever a table is created
• The index resides in the tables area
• The index is a sorted DBKEY index
• IBC and SR8 row displacements are automatically
  calculated based on the estimated numbers of rows
• A default index can be removed with the ALTER
  TABLE command

29
SQL Indexes
(continued)

• A default index can be beneficial if the area is
  sparsely populated and all rows will be accessed
• Default indexes can compete for space on the same
  pages as data rows. This can cause overflow
  conditions to occur or inefficient index
  structures to be built
• Consider removing the default index if the area
  is densely populated or if access is typically by
  a columns value

30
SQL Indexes
(continued)

• Any number of sorted system-level indexes can be
  defined against a table using the CREATE INDEX
  command
• Indexes created through the CREATE INDEX command
  can be tuned like any other system-level index
• The index structure can be placed in its own area
• A tables rows can be clustered based on
  membership in an index

31
SQL Indexes
(continued)

• Key compression may be specified
• The IBC and SR8 displacement can be specified
• Indexes add I/O
• to maintain them on DELETE and INSERT commands
• on direct retrieval based on a columns value if
  that column(s) is also defined as a calckey

32
SQL Indexes
(continued)

• Indexes provide
• ordered retrieval
• secondary entry points
• generic processing

33
SQL Referential Constraints

• Besides providing referential integrity to an SQL
  defined database the CREATE CONSTRAINT command
  allows physical relationships (sets) to be
  established between rows of different tables
• Constraints may be
• UNLINKED
• LINKED

34
SQL Referential Constraints
(continued)

• LINKED constraints provide a physical
  relationship between rows of different tables
• CLUSTERED
• INDEX

35
SQL Referential Constraints
(continued)

• CLUSTERED constraints cause a VIA chain set to be
  established between two tables. The rows of a
  referencing table will be clustered around the
  referenced rows with the corresponding key value
• A CLUSTERED referential constraint should be used
  whenever a VIA set would be used in a non-SQL
  defined database

36
SQL Referential Constraints
(continued)

• INDEX constraints will create a sorted user-owned
  index between two tables. IBC and SR8
  displacements can be specified
• INDEX constraints are used to establish non-VIA
  sets. Care should be taken to insure that
  cluster sizes for the referenced tables take into
  consideration the indexs SR8 records

37
SQL Referential Constraints
(continued)

• A referential constraint can be specified as
  UNLINKED/CLUSTERED and the clustering will be
  honored by LOAD utility processing
• The referencing table can be assigned to the same
  area as the referenced table or it an be in an
  area of its own

38
Summary

• Goals of physical database design
• Minimize required I/O
• Minimize the work required from the DBMS
• Minimize resource contention
• Physical database design is an art not a
  science
• Design options must be evaluated in each
  situation to compare their benefits against the
  associated overhead

39
Questions Answers