Sample disk layouts

When setting out to organize disk space, the database server administrator usually has one or more of the following objectives in mind:

  • High performance
  • High availability
  • Ease and frequency of backup and restore

Meeting any one of these objectives has trade-offs. For example, configuring your system for high performance usually results in taking risks regarding the availability of data. The sections that follow present an example in which the database server administrator must make disk-layout choices given limited disk resources. These sections describe two different disk-layout solutions. The first solution represents a performance optimization, and the second solution represents an availability-and-restore optimization.

The setting for the sample disk layouts is a fictitious sporting goods database that uses the structure (but not the volume) of the stores_demo database. In this example, the database server is configured to handle approximately 350 users and 3 gigabytes of data. The disk space resources are shown in the following table.
Disk drive Size of drive High performance
Disk 1 2.5 gigabytes No
Disk 2 3 gigabytes Yes
Disk 3 2 gigabytes Yes
Disk 4 1.5 gigabytes No

The database includes two large tables: cust_calls and items. Assume that both of these tables contain more than 1,000,000 rows. The cust_calls table represents a record of all customer calls made to the distributor. The items table contains a line item of every order that the distributor ever shipped.

The database includes two high-use tables: items and orders. Both of these tables are subject to constant access from users around the country.

The remaining tables are low-volume tables that the database server uses to look up data such as postal code or manufacturer.
Table name Maximum size Access rate
cust_calls 2.5 gigabytes Low
items 0.5 gigabytes High
orders 50 megabytes High
customers 50 megabytes Low
stock 50 megabytes Low
catalog 50 megabytes Low
manufact 50 megabytes Low
state 50 megabytes Low
call_type 50 megabytes Low

Sample layout when performance is highest priority

To optimize performance, use multiple storage spaces and multiple disks. The following figure shows a disk layout that is optimized for performance. This disk layout uses the following strategies to improve performance:
  • Migration of the logical log and physical log files from the root dbspace
    This strategy separates the logical log and the physical log and reduces contention for the root dbspace. For best performance, take advantage of automatic performance tuning for the logical and physical logs:
    • Create a plogspace to enable the automatic expansion of the physical log.
    • Set the AUTO_LLOG configuration parameter to enable the automatic expansion of the logical log in a specified dbspace.
    If you create a server during installation, the plogspace is created and the AUTO_LLOG configuration parameter is set to a non-critical dbspace.
  • Location of the two tables that undergo the highest use in dbspaces on separate disks

    Neither of these disks stores the logical log or the physical log. Ideally you might store each of the items and orders tables on a separate high-performance disk. However, in the present scenario, this strategy is not possible because one of the high-performance disks is required to store the large cust_calls table (the other two disks are too small for this task).

Figure 1: Disk layout optimized for performance

This figure shows different types of information that are stored on different disks. The rootdbs and the phys_log_space database are stored on Disk 1. Data from the cust_calls database is stored on Disk 2. Data from other database tables is stored on Disks 3 and 4. The paragraphs that precede this figure contain more information about the content of the figure.

Sample layout when availability is highest priority

The weakness of the previous disk layout is that if either Disk 1 or Disk 2 fails, the whole database server goes down until you restore the dbspaces on these disks from backups. In other words, the disk layout is poor with respect to availability.

An alternative disk layout that optimizes for availability and involves mirroring is shown in following figure. This layout mirrors all the critical data spaces (the system catalog tables, the physical log, and the logical log) to a separate disk. Ideally you might separate the logical log and physical log (as in the previous layout) and mirror each disk to its own mirror disk. However, in this scenario, the required number of disks does not exist; therefore, the logical log and the physical log both are located in the root dbspace.
Figure 2: Disk layout optimized for availability

The paragraph that precedes this figure describes the content of the figure.