Informix

Not entirely sure how virtualization handles affinity groups. There may be a way to allocate cpu + memory from the same node (or limited number of nodes) for a VM and that would be desirable configuration.

Here is a Linux example on how to run Informix using resources from specific node on "bare metal"

1. find out CPU numbers and memory size for specific node using "numactl"

     numactl -H

....

node 0 cpus: 0 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 42 44 46
node 0 size: 195269 MB

.....

2. configure Informix so that total memory footprint (buffer pool + virtual segment) would not exceed amount of memory for the node, and number of CPU VPs matches number reported for the node.

3. (re) start Informix  using numactl command, for this case it will be -

     numactl --cpunodebind=0 --membind=0 $INFORMIXDIR/bin/oninit -v

4. One would think that it's all that's needed, but Informix resets CPU mask at startup, so it's needed to set it back to run on selected node, which can be done on Linux with some scripting around "taskset":

    proc="0,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40,42,44,46"

    onstat -g glo | sed -n '/Thread/,/tot/p' | grep -v usercpu | awk 'NF>7 {print "taskset -p -c",c,$2 }' c="$proc" | sh -x

The CPU mask can be verified using taskset, for the described case:

taskset -p 262362
pid 262362's current affinity mask: 555555555555

The only remaining thing is to make sure that significant (application) activity is not running on CPUs used by Informix.
 

This method can be extrapolated for running Informix on 2 or more nodes to see what works best for your specific workload.

Caveats - if Informix tries to add more memory than is available for node(s) selected by numactl - Linux will not allow it.
Set SHM total appropriately or make sure that additional allocations don't happen.

I guess I have to comment on "other big database vendor  has zero problems scaling". That's not entirely true. It's well documented fact that efficiency (performance per computing unit- core) is the best when running on resources with max affinity for high volume transactional workloads. However some workloads, ones that suitable for the Informix Warehose Accelerator ("IWA") will scale much better across nodes. I suspect that SAP HANA type workloads will also have reasonable scaling across nodes.

Another thing to pay attention to is presence of other bottlenecks, which may be present due to application design, storage configuration and layout, or maybe other factors.  My favorite example from early days if internet is placing a hit counter on the web page - which decreased ability to serve this page by several orders of magnitude. Having a small table with few rows being hit by every session may have approximately same effect. Likewise - if I/O is a bottleneck, throwing more CPU resources at Informix will not improve performance. Partitioning the data with the right fragmentation strategy may also need to be given some thought. 

For current processor capabilities, the "working set" (frequently accessed data) MUST be cached in memory, the I/O simply cannot keep up. Having all-flash storage, if fitting hot data in memory is not possible, helps to a degree, but it's best to look at the data and maybe normalize to reduce working set size.

"should we switch to Intel Platinum 24-core CPUs or maybe 28-core ..." - I would expect that Informix running on one 28 core processor (with memory from the same node) will run better than on 2 x 14 core processors, but since a lot depends on the workload - that remains to be seen.  

Hope this helps a bit.

Not entirely sure how virtualization handles affinity groups. There may be a way to allocate cpu + memory from the same node (or limited number of nodes) for a VM and that would be desirable configuration.

Here is a Linux example on how to run Informix using resources from specific node on "bare metal"

1. find out CPU numbers and memory size for specific node using "numactl"

     numactl -H

....

node 0 cpus: 0 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 42 44 46
node 0 size: 195269 MB

.....

2. configure Informix so that total memory footprint (buffer pool + virtual segment) would not exceed amount of memory for the node, and number of CPU VPs matches number reported for the node.

3. (re) start Informix  using numactl command, for this case it will be -

     numactl --cpunodebind=0 --membind=0 $INFORMIXDIR/bin/oninit -v

4. One would think that it's all that's needed, but Informix resets CPU mask at startup, so it's needed to set it back to run on selected node, which can be done on Linux with some scripting around "taskset":

    proc="0,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40,42,44,46"

    onstat -g glo | sed -n '/Thread/,/tot/p' | grep -v usercpu | awk 'NF>7 {print "taskset -p -c",c,$2 }' c="$proc" | sh -x

The CPU mask can be verified using taskset, for the described case:

taskset -p 262362
pid 262362's current affinity mask: 555555555555

The only remaining thing is to make sure that significant (application) activity is not running on CPUs used by Informix.
 

This method can be extrapolated for running Informix on 2 or more nodes to see what works best for your specific workload.

Caveats - if Informix tries to add more memory than is available for node(s) selected by numactl - Linux will not allow it.
Set SHM total appropriately or make sure that additional allocations don't happen.

I guess I have to comment on "other big database vendor  has zero problems scaling". That's not entirely true. It's well documented fact that efficiency (performance per computing unit- core) is the best when running on resources with max affinity for high volume transactional workloads. However some workloads, ones that suitable for the Informix Warehose Accelerator ("IWA") will scale much better across nodes. I suspect that SAP HANA type workloads will also have reasonable scaling across nodes.

Another thing to pay attention to is presence of other bottlenecks, which may be present due to application design, storage configuration and layout, or maybe other factors.  My favorite example from early days if internet is placing a hit counter on the web page - which decreased ability to serve this page by several orders of magnitude. Having a small table with few rows being hit by every session may have approximately same effect. Likewise - if I/O is a bottleneck, throwing more CPU resources at Informix will not improve performance. Partitioning the data with the right fragmentation strategy may also need to be given some thought. 

For current processor capabilities, the "working set" (frequently accessed data) MUST be cached in memory, the I/O simply cannot keep up. Having all-flash storage, if fitting hot data in memory is not possible, helps to a degree, but it's best to look at the data and maybe normalize to reduce working set size.

"should we switch to Intel Platinum 24-core CPUs or maybe 28-core ..." - I would expect that Informix running on one 28 core processor (with memory from the same node) will run better than on 2 x 14 core processors, but since a lot depends on the workload - that remains to be seen.  

Hope this helps a bit.

> memory of a single NUMA node is too small

Well, as with everything in performance you need to figure what is your primary bottleneck first.

When your "working set" does not fit in memory (Informix buffer pool) then, most likely, your workload will be I/O bound and the effect of NUMA will be secondary to that.

So it would make perfect sense that when you add (allow Informix to use) more memory - performance improves.

Same goes for CPU. If you only have 2 - 4 cores per node, then using ALL available processing resources without regard for NUMA may work better for performance.

In addition, NUMA affects different types of workloads differently.

Practically - if, when configuring Informix to use more CPUs, you see higher CPU utilization but no proportional increase in performance, then you might want to look at optimizing affinity of the resources used by Informix, that's when NUMA awareness can be useful.

And, in general, testing how different configurations work for you is the right thing to do always.

Appreciate your comments!

Not entirely sure how virtualization handles affinity groups. There may be a way to allocate cpu + memory from the same node (or limited number of nodes) for a VM and that would be desirable configuration.

Here is a Linux example on how to run Informix using resources from specific node on "bare metal"

1. find out CPU numbers and memory size for specific node using "numactl"

     numactl -H

....

node 0 cpus: 0 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 42 44 46
node 0 size: 195269 MB

.....

2. configure Informix so that total memory footprint (buffer pool + virtual segment) would not exceed amount of memory for the node, and number of CPU VPs matches number reported for the node.

3. (re) start Informix  using numactl command, for this case it will be -

     numactl --cpunodebind=0 --membind=0 $INFORMIXDIR/bin/oninit -v

4. One would think that it's all that's needed, but Informix resets CPU mask at startup, so it's needed to set it back to run on selected node, which can be done on Linux with some scripting around "taskset":

    proc="0,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40,42,44,46"

    onstat -g glo | sed -n '/Thread/,/tot/p' | grep -v usercpu | awk 'NF>7 {print "taskset -p -c",c,$2 }' c="$proc" | sh -x

The CPU mask can be verified using taskset, for the described case:

taskset -p 262362
pid 262362's current affinity mask: 555555555555

The only remaining thing is to make sure that significant (application) activity is not running on CPUs used by Informix.
 

This method can be extrapolated for running Informix on 2 or more nodes to see what works best for your specific workload.

Caveats - if Informix tries to add more memory than is available for node(s) selected by numactl - Linux will not allow it.
Set SHM total appropriately or make sure that additional allocations don't happen.

I guess I have to comment on "other big database vendor  has zero problems scaling". That's not entirely true. It's well documented fact that efficiency (performance per computing unit- core) is the best when running on resources with max affinity for high volume transactional workloads. However some workloads, ones that suitable for the Informix Warehose Accelerator ("IWA") will scale much better across nodes. I suspect that SAP HANA type workloads will also have reasonable scaling across nodes.

Another thing to pay attention to is presence of other bottlenecks, which may be present due to application design, storage configuration and layout, or maybe other factors.  My favorite example from early days if internet is placing a hit counter on the web page - which decreased ability to serve this page by several orders of magnitude. Having a small table with few rows being hit by every session may have approximately same effect. Likewise - if I/O is a bottleneck, throwing more CPU resources at Informix will not improve performance. Partitioning the data with the right fragmentation strategy may also need to be given some thought. 

For current processor capabilities, the "working set" (frequently accessed data) MUST be cached in memory, the I/O simply cannot keep up. Having all-flash storage, if fitting hot data in memory is not possible, helps to a degree, but it's best to look at the data and maybe normalize to reduce working set size.

"should we switch to Intel Platinum 24-core CPUs or maybe 28-core ..." - I would expect that Informix running on one 28 core processor (with memory from the same node) will run better than on 2 x 14 core processors, but since a lot depends on the workload - that remains to be seen.  

Hope this helps a bit.

> memory of a single NUMA node is too small

Well, as with everything in performance you need to figure what is your primary bottleneck first.

When your "working set" does not fit in memory (Informix buffer pool) then, most likely, your workload will be I/O bound and the effect of NUMA will be secondary to that.

So it would make perfect sense that when you add (allow Informix to use) more memory - performance improves.

Same goes for CPU. If you only have 2 - 4 cores per node, then using ALL available processing resources without regard for NUMA may work better for performance.

In addition, NUMA affects different types of workloads differently.

Practically - if, when configuring Informix to use more CPUs, you see higher CPU utilization but no proportional increase in performance, then you might want to look at optimizing affinity of the resources used by Informix, that's when NUMA awareness can be useful.

And, in general, testing how different configurations work for you is the right thing to do always.

Appreciate your comments!

Not entirely sure how virtualization handles affinity groups. There may be a way to allocate cpu + memory from the same node (or limited number of nodes) for a VM and that would be desirable configuration.

Here is a Linux example on how to run Informix using resources from specific node on "bare metal"

1. find out CPU numbers and memory size for specific node using "numactl"

     numactl -H

....

node 0 cpus: 0 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 42 44 46
node 0 size: 195269 MB

.....

2. configure Informix so that total memory footprint (buffer pool + virtual segment) would not exceed amount of memory for the node, and number of CPU VPs matches number reported for the node.

3. (re) start Informix  using numactl command, for this case it will be -

     numactl --cpunodebind=0 --membind=0 $INFORMIXDIR/bin/oninit -v

4. One would think that it's all that's needed, but Informix resets CPU mask at startup, so it's needed to set it back to run on selected node, which can be done on Linux with some scripting around "taskset":

    proc="0,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40,42,44,46"

    onstat -g glo | sed -n '/Thread/,/tot/p' | grep -v usercpu | awk 'NF>7 {print "taskset -p -c",c,$2 }' c="$proc" | sh -x

The CPU mask can be verified using taskset, for the described case:

taskset -p 262362
pid 262362's current affinity mask: 555555555555

The only remaining thing is to make sure that significant (application) activity is not running on CPUs used by Informix.
 

This method can be extrapolated for running Informix on 2 or more nodes to see what works best for your specific workload.

Caveats - if Informix tries to add more memory than is available for node(s) selected by numactl - Linux will not allow it.
Set SHM total appropriately or make sure that additional allocations don't happen.

I guess I have to comment on "other big database vendor  has zero problems scaling". That's not entirely true. It's well documented fact that efficiency (performance per computing unit- core) is the best when running on resources with max affinity for high volume transactional workloads. However some workloads, ones that suitable for the Informix Warehose Accelerator ("IWA") will scale much better across nodes. I suspect that SAP HANA type workloads will also have reasonable scaling across nodes.

Another thing to pay attention to is presence of other bottlenecks, which may be present due to application design, storage configuration and layout, or maybe other factors.  My favorite example from early days if internet is placing a hit counter on the web page - which decreased ability to serve this page by several orders of magnitude. Having a small table with few rows being hit by every session may have approximately same effect. Likewise - if I/O is a bottleneck, throwing more CPU resources at Informix will not improve performance. Partitioning the data with the right fragmentation strategy may also need to be given some thought. 

For current processor capabilities, the "working set" (frequently accessed data) MUST be cached in memory, the I/O simply cannot keep up. Having all-flash storage, if fitting hot data in memory is not possible, helps to a degree, but it's best to look at the data and maybe normalize to reduce working set size.

"should we switch to Intel Platinum 24-core CPUs or maybe 28-core ..." - I would expect that Informix running on one 28 core processor (with memory from the same node) will run better than on 2 x 14 core processors, but since a lot depends on the workload - that remains to be seen.  

Hope this helps a bit.