When configuring the Informix database server, there are some fundamental points you should always consider for satisfactory performance – regardless of the data volume or user scenario. Below, we answer the most important and frequently asked questions about the Informix configuration:
How large you have to size the bufferpool?
As large as possible! This can certainly mean choosing a buffer pool large enough to hold the entire database. The bufferpool is the core of performance. The larger the buffer pool, the more data can be held in (fast) RAM, and consequently, less needs to be read from slow disks. When sizing the buffer pool, ensure that the operating system has sufficient RAM and comply with the manufacturer's licensing requirements (compliance) regarding shared memory limits. These vary depending on the Informix edition used. Violations of compliance can result in additional licensing fees and penalties!
How important are SHMVIRTSIZE and SHMADD?
Extremely important! Both are central components of the shared memory (which also contains the buffer pool) and are therefore responsible for the performance and stability of the database management system. SHMVIRTSIZE is allocated once at the start. If this memory is insufficient, additional memory segments are added using SHMADD. Frequent reallocation leads to fragmentation and poor performance. The goal is to minimize memory expansions. Therefore, set SHMVIRTSIZE as large as possible and SHMADD not too small. Values between 512 MB and 2 GB are common.
Are different types of dbspaces useful?
Yes! Separation is essential! Data belongs in dbspaces, logs in log dbspaces, and temporary data in temp dbspaces. Following this approach the data are written to separate disks in the storage, which improves performance.
What is better – raw devices or cooked files?
Raw devices. Traditionally, the Informix database works with raw devices. This means Informix communicates directly with the storage device, bypassing the operating system and its cache. This results in better performance compared with cooked files. DIRECT_IO now ensures that the performance of cooked files is nearly similar that of raw mode. DIRECT_IO has been known in the Linux and Informix world for many years, but since Informix 14.10, it is also available for temp spaces. Previously, it was only possible to activate DIRECT_IO for "normal" DBSpaces. DIRECT_IO and raw devices produce similar results.
What should be considered when choosing the filesystem (cooked files)?
Do not use so-called "journaling filesystems"! They are disastrous for the performance of Informix! Avoid EXT3, EXT4 (journaling enabled), ZFS, and BTFS. JFS2/OpenJFS are acceptable. On Linux, EXT2 and 3 (journaling disabled) are best.
How large you should size the logs?
There are no fixed sizes, but some general guidelines and rules of thumb – otherwise, test! Keep in mind: the logical and physical logs together account for up to 50% of all writes. Therefore, the number and size of the logs must be appropriately sized for the load. The physical log should be at least as large as the buffer pool; a checkpoint occurs when the physical log is 75% full.
How should virtual processors be handled?
Informix uses virtual processors. The key factor here are the CPUVPs. The number of CPUVPs equals the number of CPU cores (or slightly less). Typically, on small and medium-sized systems, at least one core is always reserved for the operating system – depending on the system size, possibly more.
How often should Update Statistics be run?
Daily! Update Statistics supports the optimizer in generating query plans and is therefore a key performance driver! Update Statistics (LOW) should run daily. It's fast, uses few resources, and updates basic statistics – so it should be run as a regular job. If the data volume and distribution have changed significantly (approx. 30%), it makes sense to run Update Statistics Medium. This also takes data distribution into account. However, the load is noticeably higher than with LOW. If query times are poor, it's always worth checking whether Update Statistics has been run before considering any major tuning measures.
