Informix

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

    Posted 23 days ago
    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
    ------------------------------


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

    Posted 23 days ago
    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






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

    Posted 23 days ago
    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
    ------------------------------



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

    Posted 23 days ago
    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








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

    Posted 23 days ago
    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
    ------------------------------



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

    Posted 23 days ago
    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






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

    Posted 23 days ago
    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
    ------------------------------



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