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.
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).
- 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.
The second line on the different major UNIX variants would be:
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:
The following lines were then added to the “informix” user login profile:
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.
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.
Contact us
If you have any questions referring RAM disk and Informix and would like to find out more, simply contact us.
