Thank you Vladimir for useful tips on initial consideration of AIX resources impacting Informix environments, as a base for other specific tuning based on type of database load
Original Message:
Sent: Wed June 08, 2022 03:24 PM
From: Vladimir Kolobrodov
Subject: Optimize AIX 7.2 on Power9/Power8 settings for Informix 12.10
For starters - Informix will run best when (dedicated, capped) LPAR is using resources (CPU, RAM) with best possible affinity. Check and understand output from "lssrad -av"
Informix using resources from multiple "nodes" (affinity domains) may negatively affect performance. Using resources from different CECs, is the worst. Big problem in virtualized environments is that folks forget that having one core in LA and another in NY does not help performance. Same with having cores in D.C. and memory in Ireland.
Interestingly - it's workload specific. Some workloads are OK with that.
There is a debate on which SMT setting to use. I'd say it's a matter of personal preference, for low latency - use less threads, for better throughput - use all. It all depends. Performance "sweet spot" for optimized configuration is between 2 and 3 active (concurrent) sessions per Informix CPUVP, and, please, don't configure more CPUVPs than virtual processors you have in LPAR. That's what SMT is for, if you need to optimize core utilization.
If, in your setup, there is very low number of active concurrent sessions, overall performance may benefit from lower SMT.
With affinity out of the way - next low hanging fruit is using O/S pages larger than default 4k. Obvious choice would be 64k pages:
export LDR_CNTRL="DATAPSIZE=64k@TEXTPSIZE=64K@STACKPSIZE=64K@SHMPSIZE=64K"
oninit
unset LDR_CNTRL
Note: this will not work (process will continue using 4k pages) if AME (active memory expansion) is enabled.
In which case one either needs to turn off AME or preallocate larger pages and use them. Use of 64k pages is much easier, when possible, and performance benefit is virtually the same as when using larger pages.
Next - CPUs are quite powerful nowadays, so make sure to have and allocate enough memory for Informix (buffer pool) to cache the "working set". Otherwise there will be a need for all-flash storage, Informix can easily generate up to and in excess of million IOPS with less than (recent) 32 cores.
These three things (resource affinity, decently sized O/S pages, mostly cached working set) will get you started with reasonable performance. The rest, as other folks mentioned, may require workload specific tune-up.
It's recommended, especially on AIX, to use the latest (as of now) Informix 14.10.FC8
It has several performance fixes for AIX. One (from 14.10.FC6) enables use of Large Segment Aliasing (LSA) which reduces SLB faults for deployments with "large" memory footprint (for AIX application - large, I think, is more than 11 x 256MB)
Another (14.10.FC8) improves performance when Informix is using resources spread across nodes (affinity domains). Note that deploying Informix inside LPAR which uses resources with best affinity is still the recommended starting point.
In addition 14.10 has also significant performance improvements in replication (RSS, SDS, HRD) and ER.
------------------------------
Vladimir Kolobrodov
Original Message:
Sent: Wed June 08, 2022 09:27 AM
From: Marcus Haarmann
Subject: Optimize AIX 7.2 on Power9/Power8 settings for Informix 12.10
Hi,
I doubt if anybody could give you a recommended setup, not knowing what you
are doing with this DB instance (how big it is, are you working with buffered/unbuffered logging,
are you working with a secondary instance (HDR), is this a OLAP like database (which might get loaded
over and over) or more a huge relational construction.
Do you have multiple instances running or is this a dedicated service, are you doing partitioning ?
How many parallel sessions do you expect, what amount of data will be changed in a specific time frame
(in order to define the size of the logical logs correctly).
The machine spec is also important, what type of storage are you using, raw or cooked setup,
amount of available memory, number of CPU cores etc.
BTW these questions need to be answered for almost any database setup, Informix 11,12,14 or
even for other database products such as Oracle, Postgres etc.
In case you have a running setup for a 11.x instance, read the release notes for upgrading, mostly
the parameters can be kept in the first step. In this case, check the old instance for bottlenecks
(duration of checkpoints, sequential scans, buffer usaged, shared memory usage), which will give you some hints.
When you have more memory available compared to an older setup, increase buffers as a first step.
When you have more CPUs available, increase NUMCPUVPS.
Best,
Original Message:
Sent: 6/8/2022 8:43:00 AM
From: Milan Rafaj
Subject: Optimize AIX 7.2 on Power9/Power8 settings for Informix 12.10
Hello, I am looking for recommendation how to tune AIX 7.2 tunables on Power9/Power8 to run with Informix 12.10 (or 14.10. if recs for 12.10. are not available) for the best performance both AIX and Informix. There were some papers in the past, but not sure if still valid.
Thanks a lot
------------------------------
Milan Rafaj
Infrastructure Architects and Specialists Team Leader
Kyndryl
+420737264248
------------------------------
#Informix