copyright, harris corporation & ophir frieder, 19981 object placement “data files should not...

Copyright, Harris Corporation & Ophir Frieder, 1998

1

Object Placement

“Data files should not be stored on the same disk drive that stores the database’s redo log file.”

-Oracle7 Server Administrator’s Guide

“..you can store data files on the same drive as some redo log file.”

-Oracle7 Server Administrator’s Guide (same page!)


2

Objectives

• Define the database object placement problem.

• Discuss the relationship between object placement and performance.

• Discuss the relationship between object placement and reliability.

• Discuss typical strategies for placing objects.


3

Background

• The typical database consists of a variety of data items:– user tables

– table indices

– system logs

– temporary storage structures

– system catalogs

• In a relational system, these are usually just tables or indices, each of which can be either user-owned, or system-owned.


4

Background, Cont.

• Relational databases typically consist of multiple tables, which vary in terms of:– number, type, and width of columns

– number, type, and width of indices

– number of rows

– access patterns and frequencies*

• Platforms supporting non-trivial databases typically consist of:– multiple disks

– multiple device (i.e., disk) controllers

– RAID devices


5

The Object PlacementProblem Statement

• Question:– Given a collection of database objects (e.g., tables and indices) and a

particular supporting platform, how should the objects be distributed to the devices so as to optimize performance?

• Issues:– What does “optimize performance” mean? Maximize Transactions Per

Second (TPS), or minimize average response time?

– Does “optimizing performance” include reducing lock contention or device contention?

– Are there cases where performance for some processes should be improved at the expense of others? Do users or tables have priorities?


6

Observations ConcerningThe Problem

• Many versions of the problem exist, each of which differs in a variety of ways, e.g., what is being optimized.

• The problem has a combinatorial nature to it; in the general case, there are an exponential number of ways to place the objects on the devices.

• Even if the notion of “optimality” is precisely defined, the best, or rather, optimal placement may change over time.

• Note the importance of access frequencies and access patterns. Without them, the only information you have to go on is size.


7

Algorithmic Solutions

• Input consists of the platform description (controllers, devices, etc.), a description of database objects (tables, indices, etc), and their properties.

• Output consists of an assignment of the objects to the platform devices that is “optimal.”

• Method: (you name it!)


8

Algorithmic Solutions, Cont.

• Given the combinatorial nature of the problem, it is unlikely that an efficient, optimal algorithmic solution exists for most versions of the problem.

• The method used will have to be a (non-optimal) heuristic; neural network, simulated annealing, genetic algorithm, greedy heuristic, exhaustive search, etc.


9

Non-Algorithmic Solutions

• Traditionally, object placement is performed manually, during physical design, and later during maintenance by a DBA.

• Vendors typically provide rules or guidelines for placing objects.

• Many placement rules are vendor and system specific.

• Placement is determined by the characteristics of the objects being placed, but also by access patterns and frequencies.

• Simulations and, eventually, actual system monitoring will also be key to determining an optimal placement.


10

Example:Sybase Transaction Log

• In Sybase, a database is a collection of user and system tables supporting a specific purpose (e.g., TPC-A, TPC-B, project XYZ, project ABC, etc).

• Each database has a system table called syslogs, that records database modifying transactions.

• Syslogs will have one or more rows appended to it for each transaction.

• Syslogs grows sequentially, on disk, until it is either truncated, or dumped to some other storage device, such as a tape.


11

Sybase Transaction Log, Cont.

• Observations:– Placing Syslogs on the same disk as other tables can increase disk head

movement.

• Guideline:– Isolate Syslogs on it’s own disk to help minimize disk head movement.

• Benefits:– Placing Syslogs on it’s own disk will allow that disk’s head to move

sequentially.

– Placing Syslogs on it’s own disk will eliminate interference with any other disk’s head movement.


12

Example:Sybase TempDB

• In Sybase, the database server maintains an area called tempdb, which is used during the generation of temporary or intermediate results.

• There is only one tempdb, and it is used and available to all processes.

• Tempdb is used implicitly, by sorts, joins, and other operations.

• Tempdb can also be used explicitly to create a sequence of temporary, intermediate, internal tables.


13

Sybase TempDB, Cont.

• Observation:– Tempdb is used by virtually every process that accesses a database.

– The performance of tempdb is critical to the performance of all processes.

• Guideline:– Place tempdb on it’s own large, fast disk.


14

Example: Oracle

• Oracle proposes solving the problem in two phases:– Logical design: assigning tables and indices to tablespaces

– Physical design: assigning tablespaces to disk locations

• In general:– Oracle’s RDBMS is more complex than Sybase’s.

– Object placement is more involved for Oracle.


15

Logical Design:Oracle’s Optimal Flexible

Architecture (OFA)• General Guidelines For Logical Design:

– Segment types that are used in the same way should be stored together.– The system should be designed for standard usage.– Separate areas should exist for exceptions.– Contention among tablespaces should be minimized.– The data dictionary should be isolated.

• Information Required:– Knowledge of the applications.– Activity levels of the tables and indices at various times.– Standard v.s. exceptional transaction characteristics.– Customer goals & expectations (reliability v.s. performance).


16

OFA Tablespace Categories

• The System Tables:– Should store only the data dictionary tables, which are generally small,

and the rollback segment.

• Data:– Should store all tables associated with a particular application (typically

subject to high-traffic, growth, highly fragmented memory).

• Indices:– Should store all indices associated with application tables (also typically

subject to high-traffic, growth, highly fragmented memory).

• Tools:– Many Oracle and third party tools create tables in the Sytem tablespace by

default. Permissions and defaults for these tools should be changed so that they create their tables in an isolated tablespace.


17

OFA Table SpaceCategories, Cont.

• RBS:– All rollback segments should be stored on a tablespace that contains

nothing but rollback segments (I/O against rollback segments is typically concurrent with I/O against the Data and Index tablespaces; separating them out helps avoid I/O contention).

• Temp:– Temporary segments should not be stored with any other types of

segments (contain dynamic objects created during large sorting operations; SELECT DISTINCT, UNION, and CREATE INDEX).

• Users:– Should create their objects in an isolated tablespace (user objects are

typically transient, unplanned, sized unpredictably, located in the System tablespace by default).


18

OFA Extensions

• Low & High Usage Data Segments

– The data tablespace can usually be divided into two or more categories based on usage:

• Static tables

• Dynamic tables

– Tables in these categories should be placed in separate tablespaces.

• Reduced concurrent I/O contention.

• Isolates memory fragmentation and maintenance for dynamic tables.


19

OFA Extensions, Cont.

• Low & High Usage Index Segments

– The indices for static tables will also typically be static. Similarly for dynamic table indices.

– Indices in these categories should be placed in separate tablespaces.

• Reduced concurrent I/O contention.

• Isolates memory fragmentation and maintenance for dynamic indices.


20


• Separating Tool Indices– Indices for the tools tablespace should be placed on a separate table space

if the tools tablespace is subject to high I/O activity.

• Specialty Rollback Segments– Create a separate rollback segment for transactions that monopolize

existing production segments (e.g., a large table load).

– Isolate these rollback segments on their own tablespaces.

– Deactivate or drop these segments when the transactions are not executing.


21


• User-Specific Temporary Segments– Create a temporary segment for any user that generates exceptionally

large temporary results.

– Isolate this temporary segment in it’s own tablespace.


22

OFA, Extensions &Logical Design Summary

Tablespace UseSYSTEM Data dictionary

DATA Standard-operation tables

DATA_2 Static tables used during standard operation

INDEXES Indices for the standard-operation tables

INDEXES_2 Indices for the static tables

RBS Standard-operation rollback segments

RBS_2 Specialty rollback segments used for data loads

TEMP Standard-operation temporary segments

TEMP_USER Temporary segments created by a particular user

TOOLS RDBMS tools tables

TOOLS_1 Indices for RDBMS tools tables

USERS User objects, in development databases


23

Physical Design:Oracle’s Approach

• Physical design involves assigning all tablespaces to disk locations

• Physical design must also consider placement of the following files:– Redo/transaction logs

– Control files

– Archive logs

– Oracle software

– Application software

– Export dump file

– Any other non-Oracle applications, software, servers, data, etc.


24

Redo/Transaction Logs(Oracle)

• Store a record of each transaction in the database.

• From a reliability point of view, these are not backed up and, consequently, must be mirrored. Placing other data on the same disk gets that mirrored as well.

• From a performance point of view, the above would create contention between the LGWR (Log Writer) and DBWR (Database Writer) background processes. Also, it would create interference with an otherwise sequentially moving disk head.


25

Control Files(Oracle)

• Contains information about the physical structure of a database, e.g., the names and location of all associated files.

• Multiple copies should be maintained (at least 3), spread across multiple drives (at least 3).

• Experience relatively little I/O.


26

Archive Logs(Oracle)

• When in ARCHIVELOG mode, the ARCH background process makes copies of the redo logs before they are over-written, typically to a disk.

• This will conflict with the LGWR background process, which writes to the redo logs.

• Distribute the redo log across multiple disks.

• The placement rules for a redo log apply to an archive redo log as well, since it will behave similar to a redo log.


27

Oracle, Application & Third Party Software

• Avoid placing database files and software files on the same disk.

• I/O will depend on specific software, and must be monitored at the operating system level (this I/O is not recorded by Oracle).


28

Export Dump File

• A utility for backing up all or a subset of data in an Oracle database.

• Reads the database, including the data dictionary, and writes the output to a binary file called an export dump file.

• Once data has been exported, it can be imported via Oracle’s import utility.


29

Disk Layout Goals

• Goals for recovery and performance, which often times dictate different design decisions, must be established and prioritized.

• The reliability goals of the system must be defined:– The database must be recoverable to some degree.

– The disk mirroring policy and architecture must be established:

• The on-line redo log files should most likely be mirrored.

• Disks can be mirrored 1-to-1 (RAID-1)

• Disks can be parity-checked across a collection of disks (RAID-3 or RAID-5).

– The database should be run in ARCHIVELOG mode.


30

Disk Layout Goals, Cont.

• The performance goals of the system must be defined:– Process contention (when multiple processes such as ARCH, LGWR and

DBWR access data on the same disk at the same time) should be considered and perhaps minimized.

– Process interference (when sequential reads and/or writes are interupted by reads or writes to other files on the same disk, perhaps even at different times) should be considered and perhaps minimized.

– The database file I/O weights must be estimated, and used to distribute tablespaces and files

• The hardware options must be known:– Disks must be dedicated to the system


31

Estimating RelativeI/O Contention

• Assign the most active data tablespace a weight of 100.

• Estimate the I/O for other data tablespaces relative to the most active.

• Assign the SYSTEM tablespace a weight of 35.

• Assign the index tablespaces a value equal to one-third of that for their associated data tablespaces.


32

Estimating RelativeI/O Contention, Cont.

• Values for the TOOLS, RBS, and TEMP tablespaces vary widely, and depend on the database:– TOOLS will experience little I/O in a production database.

– RBS will generally be between 10 and 40, but may go as high as 75.

– TEMP can be anywhere from 0 to 75, and is highly dependent on the database.

• Assign weights to redo logs based on that for the rollback segments; transactions written to RBS will also be written to the redo logs.

• Similarly for the archive log file if the database is running in ARCHIVELOG mode.


33

Estimating RelativeI/O Contention, Cont.

• Oracle, application, other third-party software, as well as the export dump file, are installation specific, with the first two depending on specific site-licenses.


34

The 22 Disk Solution

*Note that the following distributions assumes that all disks are identical and that on-line redo log files are being mirrored via the operating system.

Disk #1 Oracle software Disk #12 Control file 2Disk #2 SYSTEM tablespace Disk #13 Control file 3Disk #3 RBS tablespace Disk #14 Application softwareDisk #4 DATA tablespace Disk #15 RBS_2Disk #5 INDEXES tablespace Disk #16 DATA_2Disk #6 TEMP tablespace Disk #17 INDEXES_2Disk #7 TOOLS tablespace Disk #18 TEMP_USERDisk #8 Online Redo log 1 Disk #19 TOOLS_IDisk #9 Online Redo log 2 Disk #20 USERSDisk #10 Online Redo log 3 Disk #21 Archived redo logDisk #11 Control file 1 Disk #22 Export dump file


35

The 22 Disk Solution, Cont.

• Eliminates contention between all datafiles by giving each it’s own separate disk.

• Lower numbered disks contain more critical files.

• Eliminates LGWR-ARCH contention by giving each redo log it’s own separate disk.

• Gives the Oracle and application software their own disk.

• Solution is excessively expensive; a whole disk is used for each control file (Control files are typically small (<200k) and accessed infrequently).


36


Disk #1 Oracle software Disk #15 TEMP_USERDisk #2 SYSTEM tablespace Disk #16 Archived redo

logDisk #3 RBS tablespace Disk #17 Export dump fileDisk #4 DATA tablespaceDisk #5 INDEXES tablespaceDisk #6 TEMP tablespaceDisk #7 TOOLS (& TOOLS_I) tablespaceDisk #8 Online Redo log 1, Control file 1Disk #9 Online Redo log 2, Control file 2Disk #10 Online Redo log 3, Control file 3Disk #11 Application softwareDisk #12 RBS_2Disk #13 DATA_2Disk #14 INDEXES_2


37


• Control files will interfere with redo logs, but only at log switch points and during recovery.

• Assuming a production environment, TOOLS_I and TOOLS do not need to be separate (only required during intense development).

• Similarly, users do not need resource privileges, so it no longer needs to be considered.


38


Disk #1 Oracle software Disk #15 Export dump fileDisk #2 SYSTEM tablespaceDisk #3 RBS, RBS_2 tablespacesDisk #4 DATA tablespaceDisk #5 INDEXES tablespaceDisk #6 TEMP, TEMP_USER tablespacesDisk #7 TOOLS (& TOOLS_I) tablespaceDisk #8 Online Redo log 1, Control file 1Disk #9 Online Redo log 2, Control file 2Disk #10 Online Redo log 3, Control file 3Disk #11 Application softwareDisk #12 DATA_2Disk #13 INDEXES_2Disk #14 Archived redo log


39


• RBS and RBS_2 are placed together because they are seldom used concurrently, since RBS_2 is only used during data loads, which should not occur during regular usage.

• No good justification for moving TEMP_USER and TEMP together.

• Note that tablespaces have begun to merge.


40


Disk #1 Oracle softwareDisk #2 SYSTEM tablespace, Control file 1Disk #3 RBS, RBS_2 tablespaces, Control file 2Disk #4 DATA tablespace, Control file 3Disk #5 INDEXES tablespaceDisk #6 TEMP, TEMP_USER tablespacesDisk #7 TOOLS (& TOOLS_I), INDEXES_2 tablespacesDisk #8 Online Redo log 1, 2, 3Disk #9 Application softwareDisk #10 DATA_2Disk #11 Archived redo logDisk #12 Export dump file


41


• Collocating redo logs will cause concurrent I/O and interference contention between the LGWR and ARCH processes - not appropriate for high-transaction systems running in ARCHIVELOG mode.

• Control files should be moved since the redo logs have been collocated. Since they are small and not I/O intensive, moving them to the SYSTEM, RBS, and DATA tablespaces should not be a problem.

• No justification given for collocating the TOOLS and INDEX_2 tablespaces

• Note that most changes have involved moving items on high numbered disks to lower numbered disks.


42

The 9 Disk Solution

Disk #1 Oracle softwareDisk #2 SYSTEM tablespace, Control file 1Disk #3 RBS, RBS_2 tablespaces, Control file 2Disk #4 DATA tablespace, Control file 3Disk #5 INDEXES tablespaceDisk #6 TEMP, TEMP_USER, and DATA_2 tablespacesDisk #7 TOOLS (& TOOLS_I), INDEXES_2 tablespacesDisk #8 Online Redo log 1, 2, 3, Export dump fileDisk #9 Application software, Archived redo log


43


• DATA_2 is combined with the TEMP tablespaces since the static tables are less likely than the DATA tables to have group operations performed on them.

• On-line redo logs never increase in size (usually < 15M), and the process of exporting a database causes very little transaction activity.

• Application software is assumed to be both static and small (<10% disk space), thus leaving the Archiver ample space to write log files while avoiding conflicts with the DBWR.


44


• Weighting for disk #1 is installation specific and depends on what Oracle software has been licensed.

• Weightings for disks #8 and #9 are based on weighting for the rollback segments.

• Any additional merging would force indices to be on the same disk as their tables.


45

The 7 Disk Compromise

Disk #1 Oracle softwareDisk #2 SYSTEM, TOOLS (&TOOLS_I), INDEXES_2 tablespaces,

Control file 1Disk #3 RBS, RBS_2 tablespaces, Control file 2Disk #4 DATA tablespace, Control file 3Disk #5 INDEXES, TEMP, TEMP_USER, and DATA_2 tablespacesDisk #6 Online Redo log 1, 2, 3, Export dump fileDisk #7 Application software, Archived redo log


46

The 7 Disk Compromise, Cont.

• Four disks contain all the tablespaces.

• Each of the four contains one of the top I/O weighted files.

• Merging 6 and 7 would compromise recoverability to an unacceptable degree.

• Merging tablespaces would likely compromise performance to an unacceptable degree.

• Any further merging of tablespace must be based on actual I/O measurements.


47

Final Observations On OFA

• Candidate solutions are somewhat simplified– Disks are assumed to all be identical in terms of size and performance

characteristics.

– No RAID devices are mentioned.

– Database size is not discussed.

• The Oracle approach tends to focus on disk head movement.– Note that no explicit mention was made of disk controllers.

• Note that the use of multiple disks is considered fundamental; this is true for other vendors as well.


48

Summary

• Placement is based on processes and guidelines.

• Placement is inherently vendor specific, however there are some general principles:– Establishing goals for reliability and performance; note that these are

often times in conflict with one another.

– Minimum reliability requirements include isolating system tables.

– Minimizing disk head movement.

– Minimizing concurrent process contention on disks and controllers.

– Distributing files, tables, and indices based on access patterns/frequency, which requires knowledge of such, obtained by either monitoring or simulation.

– Dedicated platform with multiple disks and controllers.

copyright, harris corporation & ophir frieder, 19981 object placement “data files should not...

Documents

optimal placement

placement rules

collection of database

multiple tables

platform devices

optimal algorithmic

object placementdata

performance mean