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