When setting out to organize disk space, the database server administrator usually has one or more of the following objectives in mind:
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 | 1.5 gigabytes | No |
| Disk 2 | 2 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 | 1.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 |