Optimise your RAM Disk and Informix

Abstract

With effective RAM disk and Informix management, you can make your disk space work harder.

Most editions of Informix Dynamic Server limit the amount of shared memory that can be allocated: you can compare the Informix Version 12 editions here.

This primarily constrains how big the buffer pools (disk cache) can be, while still leaving enough for other essential memory pools. However, modern machines will often have much more RAM than this, which could be put to better use.

This article will provide the complete process to use spare memory as RAM disk file systems so you can get more out of your RAM disk and Informix. Their contents are volatile, so they can only be used for temporary tables (DBSPACETEMP) and sort/merge files (PSORT_DBTEMP). The commands to create them on all supported Linux and UNIX flavours will be given, along with other relevant environment and configuration parameter settings.

Temporary data is then never written to disk, dramatically improving run times of larger queries and preventing interference with OLTP sessions by reducing buffer turnover. In an actual case, disk writes were reduced by 96%, eliminating SAN contention with other applications.

Even using Ultimate Edition which has no shared memory limit, index builds in temp dbspaces are not only slower but can crash the instance on some versions if they run out of space. This can be avoided by setting PSORT_DBTEMP to use file systems instead, and run time can be made shorter still if these are RAM disks.

Note that RAM disk should not be confused with solid state drives (SSD), which are persistent and a better technology for logical and physical logs when combined with RAID 1 or 10.

Content

Scenario

The following is based on an actual case. You have been given a set of Linux (or UNIX) physical machines with 48GB RAM to deploy as pure database servers running the Growth Edition of Informix Dynamic Server. The hardware specification was not recommended by you, but was a corporate standard for database servers, and therefore contains more RAM than you can address, as Growth Edition limits total allocated shared memory to 16GB (Express 8GB, Innovator 2GB).

Meanwhile, one of these systems in a development environment has dbspace chunks stored on a SAN shared with other unrelated applications. You have been asked to reduce the amount of disk activity, as it is adversely impacting another less capable database product.

Analysis

You discover via one of the following methods that 96% of disk writes (and most disk reads) are to temp dbspaces:

AGS Server Studio “Chunk I/O activity” report
“onstat -D” command
sysmaster database query

You advise that the contents of temp dbspaces are discarded anyway when Informix is restarted, so do not need to be persistent after a reboot and therefore could be implemented in memory, massively reducing disk activity and improving performance.

Solution level 1

As a first step, you decide that large unindexed sorts and joins (exceeding the per session memory limit defined in $ONCONFIG parameter DS_NONPDQ_QUERY_MEM) should be performed in file systems rather than temp dbspaces, so you append:

Copy to Clipboard

to the login profile for user “informix”, log out/in again, and restart Informix. This makes it the default for all sessions, but it can still be overridden on the client side by setting it to an empty value or different directories if required.

As file system caching is performed automatically by the operating system when there is free RAM, such operations now effectively take place in memory if they are short-lived.
Large index builds in particular are not only much quicker but also more stable, as some Informix versions crash when the index being created is too big for the temp dbspaces (actual cases on IDS 10.00.FC5 and 11.50.FC8).

Solution level 2

As you have so much spare memory, you decide to ensure that sort and join files are never written to physical disk, by creating ‒ and declaring in environment variable PSORT_DBTEMP ‒ a RAM disk file system. You have two options:

pure implementation in which the RAM disk is pinned in memory;
“tmpfs” (usually preferred if supported) which is virtualised and can be swapped to paging space by the operating system if necessary, with unused portions used for normal file system caching.

This was done on Linux as follows to create a 16GB file system:

Copy to Clipboard

The second line on the different major UNIX variants would be:

Copy to Clipboard

Solution level 3

Even with PSORT_DBTEMP set, you may find that the temp dbspaces are still very active if the applications rely heavily on explicit temporary tables. In this case, it is worth moving the temp dbspaces themselves to RAM disk. There should always be at least three of them, so the following was done to create replacements of 2GB each:

Copy to Clipboard

You can then edit DBSPACETEMP accordingly in the $ONCONFIG file to use the new dbspaces, and restart Informix. Whenever the system is rebooted thereafter, you will need to reinstate the chunk files. This was prepared as follows:

Copy to Clipboard

The following lines were then added to the “informix” user login profile:

Copy to Clipboard

You might need to revisit whether you have enough Informix AIO VPs assigned in the $ONCONFIG file. Defaults in later versions should be fine, especially if you have “AUTO_AIOVPS 1”. If not, you might need to add 8 more (2 per AIO chunk). Note that Informix AIO rather than Kernel AIO seems to be used for temp spaces in file systems regardless of the “DIRECT_IO” setting.

Solution level 4

Disk writes to the SAN have been decimated, and the changes have been rolled out to production systems. You begin to consider what other file system constructions do not need to persist during a reboot, and might be moved to the RAM disk. The obvious other Informix usage is to set the following in the $ONCONFIG file to decouple checkpoints on HDR and/or RSS servers from the primary without any load on the local disk file system:

Copy to Clipboard

Conclusion

With improved RAM disk and Informix management, there are easy ways to use spare RAM over and above Informix edition limits, which can improve performance by massively reducing disk activity without requiring any application changes or significant reconfiguration.

Disclaimer

Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.