Disk Layout

Analysing the system and what it is to achieve is fundamental to getting the disk layout right.

	Determine which disks will be available for OnLine use.
	Determine what are the most used SQL statements and the table they access. If SQL is not yet available the documentation for both application and the database design should allow educated guesses are to the busiest tables. In production database the onstat -g iof can be used to determine I/O rates that are currently been seen across the existing dbspaces
	From your analysis of expected activity in the previous step, you can determine the most used tables in each database.
	Decide how best to spread the OnLine I/O across the available disks.

The simplest method of placing tables on a specific disk is to create a dbspace with one or more chunks, all on the same disk. Then create the table in the new dbspace. Each table can have it's own dbspace but normally this leads to a system that is difficult to administer. It is more common to place large tables or highly used tables in their own dbspaces. The tables that are only occasionally accessed might all be grouped together in a single dbspace.

Tables that are regularly joined in SELECT statements should be in different dbspaces. onstat -g iof only displays a dbspace summary of the I/O being seen, to see the activity at a table level then SQL must run against the sysmaster database.

Once the number of dbspaces starts to increase the administrative overhead will also increase as the DBA now needs to monitor each space to ensure it doesn't run out of space. This is can be readily achieved with audit scripts or alternatively DBAudit can be used.

By creating the dbspace from two or more chunks on separate disks then the table can be spread across multiple disks, but with the introduction of fragmentation the preferred route is to use multiple dbspaces.

Prior to version 7 then life is not as simple although it can be achieved. By aim is to use the way extents are allocated. If the first chunk of a dbspace has insufficient space for the next extent of a table then the next chunk will be used. This can be achieved with the use of dummy tables and manipulation of the extent sizes.

With the introduction on fragmentation the need to 'trick' the engine into placing rows on separate disks has been removed as Informix fragments the table by placing separate parts of the table in separate dbspaces, and each dbspace can be on a different disk.

The order in which the chunks are created is important, and can impact on the system performance.

During a system checkpoint, page cleaners are assigned to chunks in the order in which the chunks were added to the system and they run in parallel. Consider the system below with 4 disk (2 chunks per disk) and 4 page cleaners configured.

When the checkpoint starts the page cleaners are allocated to chunks 1, 2 ,3 and 4 which results in disk 1 and 2 receiving all the disk activity while disk 3 and 4 are inactive. When one of the page cleaners completes it will be assigned to chunk 5 and disk 3 will start to receive activity, but disk 4 is still inactive. The preferred situation is for chunk cleaning to occur on all four drives in parallel during the start of a checkpoint.

By creating the chunks in a different order then the checkpoint time can be drastically reduced as all four disks are receiving disk activity at the same time.

Temporary tables should be placed in temporary dbspaces, these are not as the name suggests temporary, but dbspaces that sole use is for the storage of temporary tables. Temporary dbspaces are not logged.

If multiple spaces are used then the creation of temporary table will use a round-robin fragmentation strategy, which will improve the performance of parallel sorting. There should be a minimum of 3 temporary dbspaces and they should be on separate disks and ideally separate disk controllers.

If 'real' dbspaces are being used to store temporary tables then the dbspaces can become fragmented affecting the performance of the permanent tables.

Temporary tables can be located with the onstat -t output, by looking for a tblnum column with a value of AOOxxx, where A is the number of the dbspace used to hold temporary tables. Alternatively the sysmaster database can be used to find the tables in a temporary dbspace.

Informix Online/DSA/IDS can use two methods for accessing disk, kernel AIO or the AIO vps.

Both type of I/O can be used if the engine supports KAIO, although some releases of Informix seem to have performance problems if KAIO is used. Also not all Unix's have KAIO activated by default. If KAIO is enabled at a Unix level and the engine has at least one raw device configured then there will be a KAIO thread running and this can be seen using onstat -g at or h.

When a SELECT statement is made  the sqlexec thread in the CPU vp will read the buffer cache for the pages it needs to satisfy the query. If the required pages are not in the buffer cache an AIO request is made for the data to be returned from disk. The sqlexec thread then yields control and enters a sleep state until the data is ready.

The AlO vp which is continually monitors the I/O request queue sees this request for data and retrieves the required data placing it in the buffer cache. Once the data is successfully in the buffer the sqlexec thread is woken and placed back on the ready queue so the session can continue.

All things being equal raw devices are faster than cooked files systems. Raw devices bypass significant levels of software associated with UNIX filesystems handling and buffering mechanism.

But all things are not always equal. Informix use O_SYNC when writing to cooked files to ensure that the data has really placed on the disk and is not held within the filesystem buffers. This means the data is first copied to the filesystem buffers and then O_SYNC is used to force the buffering system to place the data on disk. Without O_SYNC the data would be transferred as any other file using the fsflush techniques. With raw disk the write takes the data directly from the buffers and writes directly to disk bypassing the filesystem buffers entirely

If O_SYNC is not used then the cooked file system is faster than raw but the data is not physically on the disk when Informix thinks it is. What complicates the situation is the latest disk arrays and advanced filesystems that use NVRAM for the buffering to disk, which means the data will not be lost in the event of a system failure. In these situations the write confirmation is coming from memory and not the disk itself.

But comparing like with like raw disk is approximately 20% faster than cooked filesystems. But what is raw disk.

{added something about address blocking}

Kernel AlO is only available on raw devices.

Correctly sizing the extents for a table ensures that the data does not become fragmented across the disk. There is a limit to the number of extents a tblspace can have although the extent doubling algorithms means is rarely reach. The number of extents can be monitored using oncheck -pe or directly from within the sysmaster database.

The backups utilities can archive dbspaces separately, so parallel backups can be be undertaken.

Other applications on the server also required access to disk so these need to be considered and allowed for.