The configuration tips for a first-time setup apply to the Informix Server Version 11.70.FC7 and later and to Version 12.10.FC1. Based upon the background discussed in the last blog entry New Configuration Tips (1), we will now take a closer look at:
IWA Configuration on a SMP System
Probably the first consideration for configuring IWA on a SMP system is to determine, whether IWA will use the system exclusively or share it with any other application(s). For simplicity and better understanding, the following description assumes that IWA uses the SMP system exclusively. If IWA should share the system with other applications, the allocation of resources will have to be adapted accordingly.
The major characteristic of an SMP machine is, that all resources, CPU and memory, are accessible by all processes, enabling each IWA node to fully utilize available resources. As described before, the architecture of IWA requires a minimum of two IWA nodes also on a SMP system, a coordinator node and a worker node. Each of these has its specific tasks to perform, therefore this role separation is also useful on a SMP system. With the multithreading built into each IWA node, the single coordinator node as well as the single worker node are both capable of performing tasks in parallel by utilizing multiple CPUs and shared memory (SHM) among the threads of an IWA node. Using the values of configuration parameters CORES_FOR_LOAD_THREADS_PERCENTAGE and CORES_FOR_SCAN_THREADS_PERCENTAGE and based on the number of existing CPU cores, each IWA node will determine, how many parallel threads it will start for the particular tasks. With this, a single coordinator node and a single worker node will be sufficient for most IWA installations on a SMP system. The NUM_NODES configuration parameter should remain at the default value of 2. The coordinator node will share resources of the SMP system with the single worker node. This applies to memory as well as CPU resources. Therefore, if the configuration parameters for CPU resources are specified, they need to be set accordingly.
Increasing the parameter NUM_NODES for IWA on a SMP system should be considered very carefully. On a SMP system with many CPU cores (e.g. 64 or above), it may turn out, that the single IWA worker node starts so many parallel threads, that the overhead of synchronizing them becomes a bottleneck. Only in such a case may IWA performance benefit from configuring multiple worker nodes by increasing the value of NUM_NODES even on a SMP system. At the same time, the values of parameters CORES_FOR_LOAD_THREADS_PERCENTAGE and CORES_FOR_SCAN_THREADS_PERCENTAGE should be lowered accordingly, as the parallel threads of multiple worker nodes will compete for available CPU resources. As a result of the configuration change, each worker node should start less threads, decreasing the synchronisation overhead between them. Parallelism will still be at a high level as the coordinator node will distribute the work among the multiple worker nodes. All this, however, comes at the high price of increased memory consumption. As each worker node has its own part of SHM, dimension table data will be duplicated into the SHM portion of each worker node. This may be acceptable for small dimension tables, but on a system with big dimension tables available memory may not be sufficient. Adding to this increase of needed SHM is also the increase of temporary (private) memory needed for query acceleration. The threads of multiple worker nodes will have (at least portions of) dimension table data in their memory during query processing. Last but not least, changing NUM_NODES will require that existing data marts be dropped, re-created and re-loaded, so that the fact table data can be distributed correctly among the configured number of worker nodes. After having increased NUM_NODES, it may be a good idea to test queries of a representative workload to verify they still can be accelerated without failure due to memory limitation.
There is always one coordinator nodes that can access all available resources on a SMP system.
For the configuration of IWA SHM, parameter WORKER_SHM is the most important, as it mainly specifies how big the data marts can be. Though the configured amount of memory is not allocated initially, SHM for data marts will be allocated as data marts get created and loaded with data. If the limit of WORKER_SHM is reached, no more data can be loaded. As IWA threads also use temporary (private) memory additionally to the SHM, for data load processing as well as query acceleration, WORKER_SHM must be configured to leave enough space for these additional memory requirements. On the SMP system the worker node also has to share resources with the coordinator node. The threads of the coordinator node also use private memory for final processing of results. For generally small result sets, this may be almost negligible. But for large result sets, even when only selecting the first 10 data records of it, the amount of memory needed by the coordinator for result processing may be considerable. Hence, this also needs to be accounted for when determining the value for WORKER_SHM. To maximize memory and CPU utilization it may be worth considering different application scenarios.
By default, the configuration parameters for CPU utilization are set equally for both tasks, data loading and query acceleration. This should be optimal for an application scenario, where loading data marts never concurs with ongoing query acceleration, as all available resources can be given to both tasks. E.g. if there is only a single data mart, there is no query acceleration while the data mart is loaded with data, as the data mart is not available during this time. It will go into state "Active" only after the load is complete. Hence data loading and query acceleration exclude each other and the tasks will not compete for resources. Thus the setting of WORKER_SHM does not need to account for the possibility of temporary memory needed for both tasks at the same time.
In an application scenario where certain data marts are loaded while other data marts are in use for continued query acceleration, it may be possible, that query acceleration performance suffers noticably during the phase of concurrent data loading. In such a specific case it may be desirable to give query acceleration preference over data mart loading tasks, and configuration parameters CORES_FOR_SCAN_THREADS_PERCENTAGE and CORES_FOR_LOAD_THREADS_PERCENTAGE can be given different values. When specifying these parameters it should be noted, that both parameter values are effective per IWA node. Data load concurring with query acceleration can also mean, that more temporary (private) memory will be allocated, and WORKER_SHM may need to be lowered accordingly to accommodate this.
Whether query acceleration requests can arrive concurrently or are always strictly serial, can make another difference of application scenarios. Though IWA executes queries serially, there can be a certain overlap between the data scanning by a worker node and the final result processing (like order by or group by) by the coordinator node. Worker nodes can start processing a new query while the coordinator node is still busy with final result processing of the previous query and relaying such results to the database server. If query acceleration requests arrive fast enough for this overlap to occur, this can mean that worker threads as well as coordinator threads need to allocate and use their temporary (private) memory at the same time. A lower value for WORKER_SHM may be necessary to satisfy this. In an extreme case, temporary (private) memory of the worker node and that of the coordinator node can both be as big as the biggest data mart. To be on the safe side for successful query execution, WORKER_SHM would need to be set accordingly.