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