Minimize disk I/O contention in Informix

Beyond the possibility of acquiring faster disk drives and controllers, the only real way to reduce disk I/O contention for your IBM Informix database is to balance the I/O across the entire file system by distributing files that experience a high frequency of I/O with those that do not.

• Set up temporary dbspaces.

  By default, Informix uses the root dbspace when it needs temporary space for sorting. The creation of a large index, such as an R-tree index, can fill the root dbspace, resulting in a server crash. In the case of loading data using ArcGIS Desktop, the creation of the R-tree index (the last step in loading data) will fail, and the layer loaded will remain in load-only I/O mode.Use at least two temporary dbspaces totaling 300 MB as a starting point. Then add chunks accordingly, making sure that these chunks span different disks whenever possible.

• Create and properly size the smart large object storage space.

  The Spatial DataBlade module stores offline geometry values greater than or equal to 930 bytes. These values are stored in a designated smart large object. When geometries are written to a smart large object, an inline pointer of 64 bytes references the geometry. On Windows platforms, the default sbspace and syssbspace (found in the onconfig file) are created during the installation of the server. You can add additional chunks of sbspace to this default sbspace or create additional sbspaces to access through the %SDEHOME%\etc\dbtune.sde file. On UNIX platforms, an sbspace must be created. Create the device for the sbspace, then use the onspaces utility to create the sbspace, set the default smart large object space parameter SBSPACENAME in the onconfig file to the sbspace you created, then restart the Informix server.

• Place high-use tables in the middle disk drive partitions to minimize disk head movement.

  Placing high-use tables in the middle partitions of a disk drive reduces disk head travel. Based on the law of averages, arranging data so that the disk head spends most of its time in the middle partition reduces travel. Consult your operating system configuration manual for directions on partitioning your disk drives. Allocate a single chunk to the partition and assign the dbspace of the high-use tables to it.

• Group smaller tables together into dbspaces by usage.

  Creating a separate dbspace for each table in your database is unrealistic. Each dbspace has an associated overhead cost, and it is cumbersome to manage a large number of dbspaces. Group smaller tables together into a single dbspace. You should also group the related indexes into another dbspace so they may be placed on a separate disk drive.Grouping the smaller tables by usage into separate dbspaces allows you to place the high-use smaller tables into the middle partitions.

• Optimize extent sizes.

  Estimating the size of your tables and indexes allows you to allocate the initial extent to contain the entire data object. For data objects grouped into the same dbspace, this prevents their extents from becoming interleaved. Interleaved extents can reduce performance if the disk head has to seek over the extents of other tables.

• Assign individual dbspaces to large tables.

  Large tables should have their own dbspaces. This allows you to move these tables easily throughout the file system. Some tables may be so large that the dbspace assigned to them requires more than one chunk. If so, it's a good idea to separate the chunks onto different disk drives and separate controllers if possible. Doing so allows multiple access to data of the same table and reduces overall seek time.

• Properly size log files and log file buffers.

  The size of the buffers used for logical and physical log files determines how frequently the content of the buffers needs to be written to disk. The size of the logical logs can affect how often you need to create backups of the log files.For more information on log file settings, see Informix initialization parameters.