Tune the Informix instance for performance
It is very important to tune the configuration of the database system for optimal performance. Gather the information about your system resource availability such as number of CPUs, amount of memory, storage, and then tune your database system accordingly.
On a test system (for both remote and local database instances) there was at least a 100% improvement in the performance by just tuning some of the crucial parameters in the ONCONFIG parameter file.
The following table outlines the default and changed values of these crucial parameters on the test system with 4 CPUs and 8 GB memory:
Table 1. Sample ONCONFIG parameters
|Default value in 11.5||Tuned value|
|CPU-related configuration parameters|
|MULTIPROCESSOR 0||MULTIPROCESSOR 1|
|VPCLASS cpu,num=1,noage||VPCLASS cpu,num=4,noage|
|VP_MEMORY_CACHE_KB 0||VP_MEMORY_CACHE_KB 4096|
|AIO-related configuration parameter|
|DIRECT_IO 0||DIRECT_IO 1|
|Shared memory configuration parameters|
|RESIDENT 0||RESIDENT 1|
|SHMBASE 0x80000000L||SHMBASE 0x11000000L|
|SHMVIRTSIZE 32656||SHMVIRTSIZE 614400|
|SHMADD 8192||SHMADD 131072|
|SQL statement cache configuration parameters|
|STMT_CACHE 0||STMT_CACHE 2|
|STMT_CACHE_HITS 0||STMT_CACHE_HITS 1|
|STMT_CACHE_SIZE 512||STMT_CACHE_SIZE 20000|
|Parallel database query (PDQ) configuration parameters|
|DS_NONPDQ_QUERY_MEM 128||DS_NONPDQ_QUERY_MEM 122880|
|BUFFERPOOL and LRU configuration parameters|
|BUFFERPOOL default, buffers=10000, lrus=8, lru_min_dirty=50.000000, lru_max_dirty=60.500000||BUFFERPOOL default, buffers=200000, lrus=8, lru_min_dirty=50.000000, lru_max_dirty=60.500000|
|BUFFERPOOL size=4K, buffers=10000, lrus=8, lru_min_dirty=50.000000, lru_max_dirty=60.500000||BUFFERPOOL size=4K, buffers=200000, lrus=8, lru_min_dirty=50.000000, lru_max_dirty=60.500000|
We haven't fully utilized all the available memory because the size of the sample database that we were working with was small. In a production environment you can definitely utilize more memory for the configuration of BUFFERPOOL and other memory related parameters. The ONCONFIG.%INFORMIXSERVER% file is located at %INFORMIXDIR%\etc directory.
You would have to restart the database instance (by stopping and starting the database service in the Windows services) after making these changes in the ONCONFIG file.
Apart from tuning the ONCONFIG file, the following best practices should be considered:
- Have enough free-space in the root DBSpace.
- Do not create any user databases in the root DBSpace.
- Move logical and physical logs out of the root DBSpace and create them in a separate user DBSpace.
- Tune your logical and physical log size as per your production requirements.
- Take regular backups of your production database instances.