When you are setting up an Informix instance you have two options for storing your data. One is to use chunk files, stored in file systems. The other is to access devices directly as raw chunks. This makes a difference to how you allocate the storage. It does not change the way you create and access databases, or the SQL and other commands used.
This post will concentrate on settings and options to use on file systems for chunk files. Other posts will cover using raw devices, RAID and SAN settings etc.
File systems
File systems are designed to efficiently manage hundreds and thousands of files. This is overkill for an Informix database, and some of the file system functionality is already handled internally by Informix. It is best to turn off the functionality that is not needed, and reduce the amount of file system overhead.
To achieve this, it is best to dedicate a file system to Informix chunks, and use other file systems to store applications and files. Then, tune the dedicated file system specifically with Informix chunk files in mind.
File system types
If the database grows, at some point you might need to extend the file system. So try to use a type that can be re-sized with out having to be un-mounted first. Otherwise, you will have to shut the database down completely to un-mount it.
On Linux, ext3 and ext4 file system types allow this. They also do journaling which needs to be turned off.
Turn off journaling
Because the file systems are design to handle large numbers of files, file system journals are used to track file changes. This speeds up recovery time if there is a server crash. But because Informix is using pre-allocated chunk files, and has its own logging, you can turn this feature off.
This improves database write performance.
This command is run once, before the file system is mounted. The setting is permanent and will take effect every time the file system is mounted.
Example: turn off journaling for the file system on device /dev/xvdb
tune2fs -O ^has_journal /dev/xvdb
Turn off volume read-ahead
If you are using a volume manager, such as LVM, you can turn off it's read-ahead feature and let Informix handle the read-ahead itself.
Because Informix is maintaining the internal structures of the chunk files, it has the most information about when to read-ahead.
This command should be performed when the logical volume is first created, or changed while the file system is un-mounted.
Example: turn off read-ahead on an LVM volume called /dev/dbvg/db00_lv.
lvchange /dev/dbvg/db00_lv -r 0
Disable file access time updates
By default, the file system maintains a record of the last access time. This is updated for every read of a file.
While we might be interested in the creation and modification time of the chunks, we can generally live with out the chunk access time.
Turning it off saves us that update every time a database read is performed.
This option is specified each time the file system is mounted.
Example: mount the file system on /dev/dbvg/db00_lv
mount -o noatime /dev/dbvg/db00_lv /db
To set this option each time the server is booted, specify the option in /etc/fstab or equivalent for your platform.
Use Informix direct i/o
To change these settings, you have to bounce the database before they take effect.
Set DIRECT_IO to 1 in your Informix ONCONFIG file. When the chunk file is opened, a flag is set so that the I/O to this file bypasses the file system buffer cache.
Then, allocate memory as buffers to Informix, and let it handle the cache.
Pin the buffer cahce
Where possible, do not have RESIDENT set to 0 in the ONCONFIG file. Either set it to -1 (all memory segments) or 1 to at least pin the buffer cache in memory. This ensures the buffer cache is not subject to paging.
Summary
- dedicated file system for Informix chunks
- use a re-sizable file system type
- turn off journaling
- turn off volume manager read-ahead
- use noatime
- use DIRECT_IO
- pin buffer cache in memory
What other tweaks can we do to improve file system performance?
No comments:
Post a Comment