Informix

 View Only

  • 1.  Optimize AIX 7.2 on Power9/Power8 settings for Informix 12.10

    Posted Wed June 08, 2022 08:43 AM
    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


  • 2.  RE: Optimize AIX 7.2 on Power9/Power8 settings for Informix 12.10

    Posted Wed June 08, 2022 09:27 AM
    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,
    MARCUS HAARMANN




    Original Message


  • 3.  RE: Optimize AIX 7.2 on Power9/Power8 settings for Informix 12.10

    Posted Wed June 08, 2022 09:50 AM
    Hello Marcus, I did not mean informix onconfig params, but some specific AIX 7.2 parameters for HW platform Power9/Power8 which can support database processing. You are right most of them can be common for various rdbms like Informix, Oracle (they recommend specific AIX params setting in install guides - I used some of them for Informix as well in the past). And I saw in the past specific white paper how to tune AIX for Informix.

    ------------------------------
    Milan Rafaj
    Infrastructure Architects and Specialists Team Leader
    Kyndryl
    +420737264248
    ------------------------------

    Original Message


  • 4.  RE: Optimize AIX 7.2 on Power9/Power8 settings for Informix 12.10

    Posted Wed June 08, 2022 10:22 AM
    Hello Milan,

    the settings are depending on what you want to do with the DB instance.
    E.g. it might be a good idea to use large pages.
    Or you are working on a cooked filesystem and want to tune the access.
    It is different if you are using external hard disk arrays (which type of controller, which type
    of raid/mirror setup) or if you are using internal SSD drives (again with which controller,
    what kind of raid (btw do not use raid5), which might be tuned differently.
    KAIO tuning might make sense.
    Basically, the system settings are typically recommended in the area of SHM and SEM settings.
    (in order not to reach any limits). 
    The shared memory usage is dependent on the number of sessions you want to do in parallel and other parameters
    like buffering.
    There are certainly other settings which might affect performance in terms of scheduling algorithm,
    memory usage (large pages can be a benefit), swapping behaviour, location of your temp dbspaces
    aso.
    In my experience, we are always using very much higher settings for shared memory than recommended
    in the release notes. We also define large pages, make use of the NUMCPUVPS parameter, tune the 
    system in a way that it never starts swapping aso.
    But it all depends on the load you want the database to manage without running into 
    • long checkpoints
    • reaching memory limits
    • reaching io limits
    • getting slow because of a wrong DBS setup, cooked vs. raw, missing DIRECT parameters for cooked files
    much more to mention.

    Best,
    MARCUS HAARMANN



    Original Message


  • 5.  RE: Optimize AIX 7.2 on Power9/Power8 settings for Informix 12.10

    Posted Wed June 08, 2022 10:38 AM
    Yes exactly, I am looking for general recommendation of this kind. Customer uses raw devices from SVC.
    I have paper  "Informix on Power7 Best Practices.pdf" and I am looking now for similar ones for Power9 and/or Power8 as the internal Power architecture changed much.
    There are many recommendation here for all important areas like CPU, RAM, I/O, AIO, SMT and others.

    ------------------------------
    Milan Rafaj
    Infrastructure Architects and Specialists Team Leader
    Kyndryl
    +420737264248
    ------------------------------

    Original Message


  • 6.  RE: Optimize AIX 7.2 on Power9/Power8 settings for Informix 12.10

    Posted Wed June 08, 2022 11:09 AM
    As written in the paper you mentioned:
    IBM Informix on POWER7 Best Practices
    6
    Database workload
    Before we address best practices for Informix on the POWER7 architecture, you must
    understand the type of workload that you expect to have. A mission critical database server that
    runs a consistent workload with a large number of users has different requirements than a
    database server that has a light workload where that work is sparse throughout the day

    As already said: it depends. A system with 8GB Ram and 4 CPU cores which is designed to feed 25-50 users needs to be configured differently than a system running on 512GB Ram and 96 Cores, running for 20000 Users or a very IO 
    stressing workload from OLAP parallel loading processes. 
    MARCUS HAARMANN




    Original Message


  • 7.  RE: Optimize AIX 7.2 on Power9/Power8 settings for Informix 12.10

    Posted Wed June 08, 2022 11:15 AM
    Thank you, Marcus.
    Yes I know it and I am able to tune both AIX and informix dependently on type of db load. Just want it know if there are updates in which areas of AIX tuning might be changes on Power9/8 over Power7 from Informix can benefit.

    ------------------------------
    Milan Rafaj
    Infrastructure Architects and Specialists Team Leader
    Kyndryl
    +420737264248
    ------------------------------

    Original Message


  • 8.  RE: Optimize AIX 7.2 on Power9/Power8 settings for Informix 12.10

    Posted Wed June 08, 2022 03:25 PM

    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


  • 9.  RE: Optimize AIX 7.2 on Power9/Power8 settings for Informix 12.10