This section lists some strategies for optimizing the disk layout, given certain characteristics about the tables in a database. You can implement many of these strategies with a higher degree of control using table fragmentation. For a discussion of how to optimize your disk layout using table fragmentation, refer to your IBM Informix: Performance Guide.
You can place a table with high I/O activity on a disk device dedicated to its use and thus reduce contention for the data stored in the table. When disk drives have different performance levels, you can put the tables with the highest frequency of use on the fastest drives. Placing two high-use tables on separate disk devices reduces competition for disk access when joins are formed between the two tables or when the two tables experience frequent, simultaneous access from multiple applications.
To isolate a high-use table on its own disk device, assign the device to a chunk, and assign the same chunk to a dbspace. Finally, place the frequently used table in the dbspace just created using the IN dbspace option of CREATE TABLE. Figure 64 illustrates this strategy by showing optimal placement of three frequently used tables.
To take this strategy a step further, fragment a high-use table over multiple disk devices. If you choose an appropriate distribution scheme, the database server routes queries to the appropriate fragment, thereby reducing contention on any single fragment. For more information, see your IBM Informix: Performance Guide.
If you have doubts whether spreading your tables across multiple disks can improve performance for your particular configuration, run the -g iof option of onstat. This option displays the level of I/O operations against each chunk. For details about onstat, see the utilities chapter in the IBM Informix: Administrator's Reference.
You can mirror critical tables and databases to maximize availability. You specify mirroring on a chunk-by-chunk basis. Locate the primary and mirror chunks for critical tables on different disks. Ideally, different controllers handle the different disks.
Fragmentation gives you a higher level of control over this process. That is, you can mirror chunks that contain specific table fragments. For more information, see your IBM Informix: Performance Guide.
When you decide where to place your tables, keep in mind that if a device containing a dbspace fails, all tables in that dbspace are inaccessible. However, tables in other dbspaces remain accessible. The accessibility (or inaccessibility) of dbspace might influence which tables you group together in a particular dbspace.
Although you must perform a cold restore if a dbspace that contains critical information fails, you need only perform a warm restore if a noncritical dbspace fails. This situation might influence which dbspace you use to store critical information. If you use ON-Bar for your backup and restore tool, refer to your IBM Informix: Backup and Restore Guide for more information. If you use ON-Archive for your backup and restore tool, refer to your IBM Informix: Archive and Backup Guide for more information.
Fragmentation gives you greater granularity of backup and restore. When you fragment a table, you can still access the fragments located in the other dbspaces in the event of a dbspace failure. For more information, see your IBM Informix: Performance Guide.
To minimize disk-head movement, place the most-frequently accessed data in partitions as close to the middle of the disk as possible. See Figure 65. When a disk device is partitioned, the central partitions generally experience the fastest access time. Place the least-frequently used data on the outermost or innermost partitions. This overall strategy minimizes disk-head movement.
To place high-use tables on the middle partition of the disk, create a chunk using raw disk space that is composed of cylinders that reside midway between the spindle and the outer edge of the disk. Then create a dbspace with this same chunk as the initial and only chunk. When you create your high-use tables, use the IN clause of the CREATE TABLE statement to place them in the newly created dbspace.
For information about using raw disk space, see Unbuffered or Buffered Disk Access on UNIX.
When two or more large, growing tables share a dbspace, their new extents can become interleaved. (See Tblspaces.) This interleaving creates gaps between the extents of any one table. (See Figure 62.) Performance might suffer if disk seeks must span more than one extent. Work with the table owners to optimize the table extent sizes and thus limit head movement. For advice on how to alleviate this problem, see your IBM Informix: Performance Guide. You can also consider placing the tables in separate dbspaces.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]