Configuring memory for Informix Warehouse Accelerator

vm.overcommit_memory and the vm.overcommit_ratio kernel parameters

To avoid issues that might arise if the Linux™ kernel runs out of memory, set the vm.overcommit_memory parameter and the vm.overcommit_ratio parameter to values that are optimal for Informix Warehouse Accelerator.

1. Add the following lines to the /etc/sysctl.conf file:

   vm.overcommit_memory = 2
   vm.overcommit_ratio = 99

2. Run the sysctl -p command for the settings to take effect.

For more information, see your operating-system documentation.

SHMMAX kernel parameter

The SHMMAX kernel parameter defines the maximum size in bytes of a single shared memory segment. For Informix Warehouse Accelerator, the optimal value of the SHMMAX kernel parameter is the size of physical memory. The minimum value is the WORKER_SHM parameter * 1048576 .

Use this command to check the value of the SHMMAX kernel parameter. The output value is printed in bytes.

To change the value of the SHMMAX kernel parameter:

1. Add the following line to /etc/sysctl.conf file where bytes is the number of bytes:

   kernel.shmmax = bytes

2. Run the sysctl -p command for the setting to take effect.

For more information, see your operating-system documentation.

Shared memory for the worker nodes and the coordinator node (/dev/shm)

The shared memory is used to hold the data mart data. The more worker nodes that you designate, the faster the data is loaded from the database server. However, the more worker nodes you designate, the more memory you need because each worker node stores a copy of the data in the dimension tables. If you do not have sufficient memory assigned to the coordinator node and to the worker nodes, you might receive errors when you load data from the database server.

Informix Warehouse Accelerator uses the shared memory (/dev/shm) for the shared memory of the coordinator node and the worker nodes.

The default size of /dev/shm is half of the total memory. If you change the size of /dev/shm, it must be smaller than the total memory in order to leave enough memory for other tasks.

For Informix Warehouse Accelerator installed on a single computer, the sum of values for the WORKER_SHM parameter and the COORDINATOR_SHM parameter must fit in available space of /dev/shm.

For Informix Warehouse Accelerator installed on a hardware cluster, the value of the WORKER_SHM parameter/ (NUM_NODES parameter - 1) must fit in available space of /dev/shm on each worker node. The value of the COORDINATOR_SHM parameter must fit in available space of/dev/shm on the coordinator node.

You specify the values for the WORKER_SHM parameter and the COORDINATOR_SHM parameter in the dwainst.conf configuration file.

To check the size of the available space in /dev/shm, run this command:

Example output:

Filesystem            Size  Used Avail Use% Mounted on
tmpfs                 1.0G     0  1.0G   0% /dev/shm

To change the size of /dev/shm, use the tmpfs mount option "size=nbytes". Use the suffix k for kilobytes, m for megabytes, or g for gigabytes.

Example line in the file-system table file/etc/fstab with the size of /dev/shm set to 1 gigabyte:

tmpfs   /dev/shm   tmpfs   defaults,size=1g   0 0

mount -o remount /dev/shm

Swap space configuration

Even on systems that have a large amount of total memory, configuring the swap space can be an advantage. Swap space can prevent unexpected situations where the system might need more memory than is available.

Check the total and the used swap space with this command:

Example output (in megabytes):

             total       used       free     shared    buffers     cached
Mem:          2010        664       1345          0        124        357
-/+ buffers/cache:        182       1827
Swap:         3138          0       3138

Configure the swap space as recommended by your Linux vendor. For example, for Red Hat Enterprise Linux (RHEL) 5:

Total memory            Total swap space 
4GB or less             min 2GB
4GB to 16GB             min 4GB
16GB to 64GB            min 8GB
64GB to 256GB           min 16GB
256GB to 512GB          min 32GB

For more information, see your operating-system documentation.

Virtual memory usage

Some types of SQL queries might need a lot of memory, which is allocated on an as-needed basis. This additional memory is different from the shared memory that is used for the data mart data. For optimal performance, avoid swapping out memory pages to disk. First check if memory pages are currently swapped out to disk. If memory pages are swapped out to disk, reduce the total size of the loaded data marts or add physical memory.

Check if the memory pages are swapped out to disk with this command:

Example output in megabytes, and 3-seconds intervals:

procs -----------memory-------- ---swap-- -----io---- --system-- -----cpu-----
 r  b  swpd   free  buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0    54   1843     3    200    0    0    17    20   14    9  2  0 97  0  0
 1  0    54   1673     3    201    0    0   263     0 1028  193 11  3 85  2  0
 0  0    54   1803     3    201    0    0     0    36 1019  222 35  9 56  0  0

If values in so column are greater than zero, then pages are swapped out in the interval.

An alternative method for automated monitoring is this command:

Example output (in megabytes, swap-related lines only):

          201 m swap cache
         3291 m total swap
           54 m used swap
         3237 m free swap
      6761440 pages swapped in
      7111235 pages swapped out

If value of pages swapped out has incremented since the last time that you ran the command, then the pages have been swapped out.

For more information, see your operating-system documentation.