I/O Tuning

OnLine/DSA/IDS have a number of parameters that affect the I/O performance. Unfortunately, they are all inter-related, so changing one are will impact on another area. These parameters control how and when data is read/written to/from the disks.

It is crucial to performance that most of the work is done in shared memory and not directly from the disk, there are exceptions but generally the data pages that are required will be processed quicker if they are already held in memory. The onstat -p utility will display the % cached of read and writes from the shared memory buffer pool.

%cached(reads) = 100*(bufreads - diskreads)/buf reads

%cached(writes) = 100* (bufwrits - diskwrits) /bufwrits

The goal is maximise these percentages, although 100% is obviously not possible figures of over 98% should be easily achievable on most systems. The engine minimises actual I/O by deferring buffer writes to disk, and retaining the most recently accesses buffers in memory. The percentage cached is maximised when the data remains in shared memory buffers as long as possible.

The simplest method to increase the percentage cached figures is to increase the number of BUFFERS. Once increasing the number of BUFFERS makes little appreciable difference to the percentage cache then the BUFFERS are probably at or close to their optimal setting.

In situations, such as decision support, then large tables are often sequential scanned and PDQ is used. PDQ uses a light scan to process the data. Light scans bypass the buffer cache and uses large multi-page buffers instead which are created in the virtual portion of shared memory. Generally, decision support systems do not require large buffer caches.

The write cache rate will depend on what program code you are running. The cache percentage decreases if the the application is mainly inserting and rises if the majority of the work is updates. For write intensive processing then any rate over 75% is acceptable.

The shared memory buffer pool is managed through the use of several linked lists of pointers to the buffer table, the Free LRU (FLRU) and the modified (MLRU). The configuration parameter LRUS determines the number of LRU queues for the OnLine system.

The page cleaner threads read pages from the LRU queues during the cleaning activity. The sqlexec threads read the LRU queues when looking for a page. Both of these types of threads run on the CPU VP. The goal in configuring the number of LRU queues is to reduce contention for an LRU queue. Since one thread runs on a CPU VP at one time, the official Informix recommendation is to allocate as many LRU queues as there are CPU VPs, with a minimum of four LRU queues. But significant performance improvements can be made by setting the LRUs higher even on small single CPU systems.

When a thread needs a buffer and the page is not already in the buffer pool the thread uses a random hash to select which LRU to get the buffer from. If the selected LRU is available i.e. not locked by another process then the thread will place a lock on that queue. The thread then takes the clean buffer, moves it to the queue's dirty list, locks the buffer page and releases the lock on the LRU.

If the selected LRU is locked the thread spins (BUFWAIT) before retrying. If it is still unable to gain access to the selected LRU queue then it re-calculate the hash and chooses another queue.

The BUFWAITs are the number of times a thread had to spin before the lock was achieved. As this can only happen on reading a new page into the buffers it is worthwhile examining the dskreads in conjunction with the BUFWAITS.

If there are many process wishing do execute updates and there are a small number of LRUs then the likelihood of the LRU being locked is high. The process must wait until it can gain the lock it requires. As the number of LRUs is increased then the thread is more likely to gain the lock at the first request. If the page is already held within the buffer then the number of LRUs is still important. The more buffer the greater the likelihood that the required page is already in the cache. With more LRUs it is less likely that the required LRU is locked while another process is moving pages between the free and dirty lists.

LRUs are not used evenly, due to the hashing algorithm, and this is exaggerated the lower the number of LRUs. So with more LRUs configured the pages are more evenly spread which means the flushing at checkpoint will be quicker as the number of cleaners should equal to the number of LRUs.

Setting LRUs to the maximum of 128 might be overkill but the LRU queue overhead is slight. Alternatively set the LRU to an arbitrary figure, say 32, and monitor the checkpoint time. Then increase the number of LRUs and observer the change in the checkpoint time

Note: If when setting LRU to 128 some versions of the engine display a physical log warning on start up there is no need to worry, it is a false warning. Setting LRUs to 127 clears the problem.

To increase the page cleaning activity between checkpoints then the LRU percentages should be decreased. The LRU_MAX_DIRTY defines the maximum percent of LRU queue pair that can be dirty before page cleaning activity begins. Once the number of dirty buffers on the MLRU queue, relative to the total number of buffers on the queue pair, exceeds the number set by the LRU_MAX_DIRTY, page cleaner threads will begin flushing dirty buffers to disk. The LRU_MIN_DIRTY defines the percent of LRU queue pairs that can remain dirty specifying when the page cleaner threads will stop their cleaning activity.

If the LRU percentages are set to high then there is an increased likelihood of foreground writes occurring.

The default settings for LRU_MAX_DIRTY is 60%, and LRU_MIN_DIRTY is 50%. There should nearly always be reduced significantly.

A checkpoint can be thought of as synchronising the data on disk with the data in the buffer cache. The time this synchronisation takes is the checkpoint duration. When the engine is check pointing all activity is suspended therefore the checkpoint duration should be minimised.

During a checkpoint, each page cleaner thread is assigned to a particular chunk in the database. The page cleaner reads through the buffer headers looking for pages that are associated with its chunk. At the end of this process, the page cleaner sorts all pages into disk sequence by sorting the page numbers. Then, the request to write these pages is sent to the AIO subsystem. Since OnLine suspends most other activity when a checkpoint is occurring, it is important to keep checkpoint duration to a minimum.

The time between the end of one checkpoint and the start of another is known as the checkpoint interval or checkpoint frequency. OnLine suspends most other activity when a checkpoint is occurring. If the checkpoint intervals are too short, performance is hampered because OnLine must keep suspending threads until the checkpoint completes.

The onstat -m command can be used to monitor the checkpoint duration and interval. You can also edit the message log to sample a longer period of time. The checkpoint duration is listed after every checkpoint entry.

The checkpoint duration is dependent primarily on how many dirty pages must be written to disk. The bulk of the work during a checkpoint is writing modified buffers from memory to disk. The number of pages depends upon

Once the number of pages have been minimised the question is how fast can the pages can be written to disk.

The checkpoint interval is controlled via the CKPTINTVL, but checkpoint will occur sooner if the physical log becomes 75% full. The physical log fills quicker with write intensive applications.

The checkpoint interval can be controlled via the physical log. By increasing the size of the physical log it is possible to increase the time between checkpoint and hence more work will be done when a checkpoint is carried out.

Within the engine there are three types of buffer writes. Page cleaner effectiveness can be measured by comparing the different types of writes performed.

A foreground write is a very bad thing for performance. These occur when the application thread needs a buffer to write to and is unable to find one. The application thread selects one of the LRUS via a hash algorithm and tries to place a lock on the queue, if it can't place the lock or there are no free clean buffers it will try another queue. After this has been tried a number of times the thread will pick a dirty buffer from the current LRU queue and force the sqlexec thread to clean the buffer. It does this by forcing the buffer to be written, marking it clean and letting the normal I/O mechanism refill it. This is bad because it uses a synchronous write so the CPU VP it busy with the I/O request preventing other threads from using the VP.

The configuration parameters LRU_MIN_DIRTY and LRU_MAX_DIRTY determine how frequent the MLRU queue buffers are flushed to disk. LRU_MAX_DIRTY defines the maximum percent of an LRU queue pair that is permitted to be dirty before page cleaner activity begins. Once the number of modified buffers, relative to the total number of buffers on the queue, exceeds the percentage set by LRU_MAX_DIRTY, the page cleaner threads will begin to write pages to disk. LRU_MIN_DIRTY defines the percent of an LRU queue pair that is permitted to remain modified after the page cleaning activity finishes. Once the number of modified buffers, relative to the total number of
buffers on the queue, is below the percentage set by LRU_MIN_DIRTY, page cleaner threads will stop their activity. Page cleaning between checkpoints is different than cleaning during a checkpoint in two significant ways:

	The order of dirty pages in the LRU queues is completely random based on system activity When a page cleaner thread writes dirty pages to disk, it may write pages all over the chunks causing considerable disk head movement. In contrast, chunk writes at checkpoint time are sorted writes.
	Page cleaning between checkpoints is asynchronous to the rest of the system and does not cause user processes to wait. In contrast, page cleaning during a checkpoint will cause all user processes attempting to change a page to wait until the checkpoint is complete

For optimal performance, the physical and logical log buffers to be large enough to minimise the amount of physical I/O required for writing to the physical log or logical logs. However, as the physical and logical logs buffers are in shared memory making them too large will have a detrimental affect on the system.

The onstat -l utility can help determine if the log buffer sizes you are using are optimal. In the output section for Physical Logging and Logical Logging, examine two fields:

If pages/io is 75% of bufsize value or greater, the buffers are being used in an efficient manner Increasing the buffer size in this case will further reduce the amount of physical I/O performed when writing to the logs. If the pages/io value is less than 75% of the bufsize value, it means that the buffers are not being used to their fullest extent. In such a case, decreasing the buffer size reduces the shared memory size, which could have a small impact on overall system performance.

For sequential scans the read ahead parameters can have a significant effect of performance. When the engine detects a sequential scan the number of pages read are controlled by The RA_PAGES parameter. This configuration parameter also influences light scan buffer sizes.

The RA_THRESHOLD parameter specifies at what point more pages should be read. When RA_THRESHOLD pages are left in shared memory then more pages will be retrieved from disk. If the requested data is common across the users then the RA_THRESHOLD should be 30-40, but if data varies between users then it should be set to 5-10.

The RA sub-system behaves differently for key-only searches. For key-only searches the RA begins when the database server starts at the level-1 page and reads all the leaf pages the level-1 page points to. The RA completes when one of the following occurs:

	The database server reaches the end of the key range for the query.
	The database server has read all the leaf pages identified by the level-1 pages.
	The limit of the number of pages to be read-ahead is reached as defined by the maximum of the following. (# buffers / #sessions) 75% of the total number of buffers.

If both calculations yield a number less than what RA_PAGES is set to, then RA_PAGES will be used as the number of pages to read-ahead.

Read-aheads with sequential scans that use an index have to read ahead both index pages and data pages. Read-ahead begins when the database server starts at the leaf page and reads all the data pages the leaf page points to. The operation completes when

	The database server reaches the end of the key range for the query.
	The database server has read all the data pages identified by the level-1 pages.
	The limit of the number of pages to be read-ahead is reached as defined by the maximum of the following. (# buffers / #sessions) 75% of the total number of buffers.

Read Ahead can be disabled by setting RA_PAGES to 0. There are several ways to monitor the effectiveness of the read ahead configuration parameters:

	A decrease in the read buffer cache rate can occur if the RA parameters are too high, as buffers are being flushed that might contain information another user requires.
	If the read ahead operation is using the AIO subsystem the buf waits field in onstat -p is incremented. Therefore an unusually high value might be an indication the the RA_PAGES is too high or the threshold is too low.
	If the RA sub-system is running at 100% efficiency then RA_pgsused equals the sum of the ixda-RA, idx-RA and da-RA fields. In reality, RA_pgsused is lower than that.