Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Back to discussions
Expand all | Collapse all

Informix 12.10 vs Informix 14.10 Performance Differences

  • 1.  Informix 12.10 vs Informix 14.10 Performance Differences

    Posted Mon February 17, 2025 10:09 AM

    Our company has (finally) begun an upgrade from Informix 12 to Informix 14, the version that we are trying to certify for our ERP software. We managed to migrate the 12.10FC14 instance to 14.10FC11 on an AIX 7.3 O/S platform.

    All went well and we have the instance up and running. But one thing we discovered was that on Informix 14, some of our queries are much slower than they were on the previous server. For example, we have an order entry program that does a lookup on the orders table, and just initiating the query takes 2 seconds on the old system, and 12 seconds or more on the new server.

    I have run UPDATE STATISTICS to make sure that the database/tables are up to date. I ran an oncheck -ci to confirm we have no corruption reported in the affected tables.  Nothing I do seems to improve the performance. 

    The one difference that I noted was when I activated the SET EXPLAIN ON to try and determine where the slowness lies.  On the new server, it indicates that a sequential scan was done instead of using the system generated primary key (1675-44183). See below.

    NEW SERVER (Model 9105-41B, Memory: 56832MB)

    QUERY: (OPTIMIZATION TIMESTAMP: 02-14-2025 11:53:04)
    ------
    select /*+INDEX(ortorh_rec orh_key) */ * from ortorh_rec where  ORH_REF_BRH =
    'HOU' and  ORH_ORD_NO >= 0 ORDER BY ORH_REF_BRH,  ORH_ORD_NO

    DIRECTIVES FOLLOWED:
    DIRECTIVES NOT FOLLOWED:
    INDEX ( ortorh_rec orh_key ) Invalid Index Name Specified.

    Estimated Cost: 573368
    Estimated # of Rows Returned: 145519
    Temporary Files Required For: Order By

      1) informix.ortorh_rec: SEQUENTIAL SCAN

            Filters: (informix.ortorh_rec.orh_ref_brh = 'HOU' AND informix.ortorh_rec.orh_ord_no >= 0 )


    Query statistics:
    -----------------

      Table map :
      ----------------------------
      Internal name     Table name
      ----------------------------
      t1                ortorh_rec

      type     table  rows_prod  est_rows  rows_scan  time       est_cost
      -------------------------------------------------------------------
      scan     t1     145519     145519    791848     00:01.24   290590

      type     rows_sort  est_rows  rows_cons  time       est_cost
      ------------------------------------------------------------
      sort     4          145519    145519     00:04.14   282778

    OLD SERVER (Model 9009-22G, Memory: 61440MB)

    QUERY: (OPTIMIZATION TIMESTAMP: 02-14-2025 12:21:26)
    ------
    select /*+INDEX(ortorh_rec orh_key) */ * from ortorh_rec
    where  ORH_REF_BRH = 'HOU' AND      ORH_ORD_NO >= 0
    ORDER BY ORH_REF_BRH,  ORH_ORD_NO

    DIRECTIVES FOLLOWED:
    DIRECTIVES NOT FOLLOWED:
    INDEX ( ortorh_rec orh_key ) Invalid Index Name Specified.

    Estimated Cost: 974511
    Estimated # of Rows Returned: 147179

      1) informix.ortorh_rec: INDEX PATH

        (1) Index Name: informix. 1675_44183
            Index Keys: orh_ref_brh orh_ord_no   (Serial, fragments: ALL)
            Lower Index Filter: (informix.ortorh_rec.orh_ref_brh = 'HOU' AND informix.ortorh_rec.orh_ord_no >= 0 )


    Query statistics:
    -----------------

      Table map :
      ----------------------------
      Internal name     Table name
      ----------------------------
      t1                ortorh_rec

      type     table  rows_prod  est_rows  rows_scan  time       est_cost
      -------------------------------------------------------------------
      scan     t1     4          147179    4          00:00.00   974511

    I guess what I am trying to understand is why it uses the primary key on IDS 12.10 but ignores it on 14.10 ? The same key exists on both servers. 

    I suspect I will need to drop the primary key, and then recreate it using the expected name (orh_key). Just not sure why it would react differently when this instance was an exact copy (created using ontape redirected restore to build the instance on a new server).



    ------------------------------
    Steve Nixon
    ------------------------------


  • 2.  RE: Informix 12.10 vs Informix 14.10 Performance Differences

    Posted Mon February 17, 2025 10:32 AM

    Steve:

    First, an aside, I would go to v14.10.FC11w2 (or later) as there are some bugs in FC11 fixed in FC11w1 & FC11w2.

    Now: question: Did you drop all of your distributions and recreate them from scratch after the upgrade? There are often subtle differences between how the distributions are collected from one release to the next, especially between major versions like going from v12 to v14! So, it is strongly recommended to run "UPDATE STATISTICS LOW DROP DISTRIBUTIONS;" on the whole database after any upgrade, then run whatever suite of update statistics commands you normally run, or better, get and run my dostats utility!

    Next, when you say that you "I have run UPDATE STATISTICS to make sure that the database/tables are up to date." do not forget that if you did not drop your v12 distributions and you have AUTO_STAT_MODE set to '1' then distributions are ONLY rebuilt if they are stale. That won't fix the issue of version differences. 

    If you are not currently using dostats, I personally recommend doing that. Dostats is included in my utils2_ak package which you can download from my web site (free) at www.askdbmgt.com/my-utilities

     



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 3.  RE: Informix 12.10 vs Informix 14.10 Performance Differences

    Posted Mon February 17, 2025 11:31 AM
    Thanks for the insight, Art.

    You were exactly right. When I ran the UPDATE STATISTICS, I had tried LOW on all tables and then HIGH on the affected table (ortorh_rec). That had not made a difference. But as soon as we used the DROP DISTRIBUTIONS, the query started behaving the way we expected.

    We are not DBAs where I work, and our platform is mostly on PostgreSQL at the moment, so I was not aware of the fact that distributions are only rebuilt if stale. I'm going to have to keep that little tidbit for any future Informix upgrades. 

    Appreciate the support and insight that I often see in the IBM community pages and the IIUG. I will also make note of the suggested update from FC11w1 to FC11w2.

    Thanks again,

    Steve Nixon


    This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.


    Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.'.




    Original Message


  • 4.  RE: Informix 12.10 vs Informix 14.10 Performance Differences

    Posted Mon February 17, 2025 02:50 PM

    For some reason that I will never fathom, this IBM Forum site will let me send private responses and receive them, but, while you and others seem to be able to reply to such messages, I cannot. So:

    You are correct, there is not v14.10.FC11w2. I think that I was thinking of 14.10.FC12 which is due out in March sometime. For now, however, the w1 patch is definitely better than the original FC11 release.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 5.  RE: Informix 12.10 vs Informix 14.10 Performance Differences

    Posted Tue February 18, 2025 09:57 AM
    If you need SSL stay away from FC10 (worse release in years) and FC11 without the W1

    On 2/17/2025 1:49 PM, Art Kagel via IBM TechXchange Community wrote:
    0100019515758bb8-48208e9c-451f-4e94-bbad-21c538fa113d-000000@email.amazonses.com">
    For some reason that I will never fathom, this IBM Forum site will let me send private responses and receive them, but, while you and others seem... -posted to the "Informix" group



    Original Message


  • 6.  RE: Informix 12.10 vs Informix 14.10 Performance Differences

    Posted Tue February 18, 2025 10:23 AM

    Good morning Art,

     

    I am unaware of 14.10.FC11W2.  Not listed on https://www.ibm.com/support/pages/defects-fix-list-informix-server-v1410-releases.

    Do you recommend a different repository?

     

    Thanks,

     

    Benoît

     

    Bell_RGB_Small_55

    Benoît Chamberland (il/lui – he/him)

    Administrateur de bases de données | Database Administrator

    T : 514-870-2440  M : 514-207-7015

     




    Original Message


  • 7.  RE: Informix 12.10 vs Informix 14.10 Performance Differences

    Posted Tue February 18, 2025 11:59 AM
    Benoit:

    There seems to not be a v14.10.FC11w2 I was probably thinking about the v14.10.FC12 that is due out next month.

    Art


    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.







    Original Message


  • 8.  RE: Informix 12.10 vs Informix 14.10 Performance Differences

    Posted Mon February 17, 2025 10:38 AM

    It is strange that it wouldn't use that index as it also matches the sort order, so would be the obvious choice.  Maybe the invalid directive confuses things, but it sure should not.

    Are you 100% sure that the primary key exists on the table in 14.10 and that it is enabled?

    Just for fun, try changing the directive to avoid the scan and see whether it decides to use the PK index and then compare the estimated cost of this method with what you ran already, e.g.

    select /*+AVOID_FULL(ortorh_rec) */ * from ortorh_rec where  ORH_REF_BRH =
    'HOU' and  ORH_ORD_NO >= 0 ORDER BY ORH_REF_BRH,  ORH_ORD_NO

    Did you update statistics and "drop distributions" after the upgrade?  This is recommended following an upgrade.

    Did you run specific stats against the two meaningful columns in this query?  Also include a FORCE just to make sure that the statistics are indeed updated:

    UPDATE STATISTICS HIGH FOR TABLE ortorh_rec(orh_ref_brh, orh_ord_no) FORCE;



    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------

    Original Message


  • 9.  RE: Informix 12.10 vs Informix 14.10 Performance Differences

    Posted Tue February 18, 2025 04:43 AM
    Edited by Benjamin Thompson Tue February 18, 2025 04:45 AM

    Steve,

    I appreciate you already have a solution, however I would like to add some thoughts on why that might have worked.

    In the SQL Explain output you have posted for the 12.10 instance you have:

      type     table  rows_prod  est_rows  rows_scan  time       est_cost
      -------------------------------------------------------------------
      scan     t1     4          147179    4          00:00.00   974511

    The estimated rows value is huge and as a consequence so is the cost. However when it came to do the actual work only four rows were scanned.

    The 12.10 optimiser decided to use the index and not go for a sequential scan. I think this could be for one of two reasons: either you have OPTCOMPIND set to 0 on the 12.10 server and the default value of 2 on the 14.10 server or there is a bias in the 12.10 optimiser favouring use of the index. It would be interesting to force a sequential scan on the 12.10 instance and see the estimated cost of that.

    What it also suggests is that on the 12.10 system you do not have an accurate distribution on the ortorh_rec.orh_ref_brh column. So the cause was there before the upgrade, it's just that the version change made it into a problem.

    Which brings me to the need to drop distributions during an upgrade from 12.10 to 14.10. My, maybe slightly controversial view in this forum, is that this is almost never necessary and anyway it is impossible on large systems to drop and rebuild distributions before letting users in without the business owners asking questions about the long downtime window required. I appreciate this is mentioned in the manual but it was there over 20 years ago and since then has been rolled over to new releases: https://www.ibm.com/docs/en/informix-servers/14.10?topic=automatically-drop-data-distributions-if-necessary-when-upgrading

    To have more insight on this we would need the output from dbschema -hd before/after running update statistics high for table ortorh_rec (orh_ref_brh) distibutions only;

    Finally a recommendation subject to the usual requirement to test on your system: if you don't want hash joins or sequential scans (except where no index path is possible) on your system, set OPTCOMPIND to 0.

    Ben.



    ------------------------------
    Benjamin Thompson
    ------------------------------

    Original Message


  • 10.  RE: Informix 12.10 vs Informix 14.10 Performance Differences

    Posted Tue February 18, 2025 07:32 AM

    Ben:

    I appreciate your point about not increasing the downtime after/during an upgrade by dropping distributions wholesale and rebuilding all distributions after an upgrade. I would just make a few counter points:

    1. While you are correct that doing this is not always necessary, I have seen more often than not that when there are performance issues after an upgrade, and the drop distributions was not done, that going back and performing the drop and rebuild resolves the issue, so why not just do it up front and avoid the possible necessity of experiencing the pain later.
    2. Does it matter that the problem wasn't a change in the internals of the distributions in this case, but an issued with the quality of the distributions in 12.10 before the upgrade that v12.10 happened to work around while v14.10 did not (or because of a different OPTCOMPIND setting)? Having done the drop & rebuild would have resolved the issue before it cause production time pain!
    3. It is not necessary to do a global UPDATE STATISTICS LOW DROP DISTRIBUTIONS against all tables at once and wait to put the server into production while all distributions are rebuilt. One could put the server into production and do this table-by-table during quiet times over the course of several days starting with the most critical tables. These critical tables could even be done before putting the server into production while leaving the rest for later.


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 11.  RE: Informix 12.10 vs Informix 14.10 Performance Differences

    Posted Tue February 18, 2025 11:09 AM

    I think my motivation for posting this is that as a younger DBA I found the optimiser, statistics and distributions the hardest area to understand and so I think it's important to have the best information out there in forums like this. Another reason is that if I fix a problem by updating statistics or distributions without understanding why it fixed it, it leaves other questions like how often do I need to keep updating them.

    I would like to understand more about why dropping a distribution first could be a requirement and what the starting conditions are for this. It is one of those mysteries.

    In this particular example I can see that HOU is the most common value in the distribution and that the optimiser knows nothing about the correlation of informix.ortorh_rec.orh_ref_brh = 'HOU' AND informix.ortorh_rec.orh_ord_no >= 0

    It is therefore not completely unreasonable for the engine to try a sequential scan to avoid 400000+ index look-ups.

    To investigate this more my approach would be to try some of the less common values of orh_ref_brh and see how these affect the plan. Maybe there is a bug here?

    Ben.



    ------------------------------
    Benjamin Thompson
    ------------------------------

    Original Message


  • 12.  RE: Informix 12.10 vs Informix 14.10 Performance Differences

    Posted Tue February 18, 2025 09:44 AM
    Edited by Steve Nixon Tue February 18, 2025 09:51 AM
      |   view attached

    Hi Benjamin,

    Thanks again for the suggestions from you, from Mike, and from Art. It helps to understand WHY you should do something or have certain settings. Many of us simply inherit the setup from a previous person and don't always question it or if there is a better way. The problem is indeed resolved, but in case someone else has the issue and is looking for solutions, I figured I would do my part to include additional info after the fact.

    You are correct about the $ONCONFIG settings. On 12.10, the OPTCOMPIND was set to zero (0). On the new 14.10 instance, we used the default value of 2.  We also have AUTO_TUNE = 1, which I believe was left as default because we assumed that the Informix engine would probably know better about tuning the instance than we would!

    As for wanting more insight from dbschema, it just so happens I have two instances. The "main" instance had the DROP DISTRIBUTIONS performed that Art had suggested. The second "historical/frozen" copy had not been fixed yet. Unfortunately, I messed up an overwrote the "before" schema info (by accidentally using the same filename). But the UPDATE STATISTICS HIGH ON TABLE ortorh_REC (orh_ref_brh) DISTRIBUTIONS ONLY command did not resolve the performance issue. It remained at 12 seconds for a lookup, still doing the sequential scan.

    So I went ahead with the DROP DISTRIBUTIONS method and that resolved the problem.

    Attached file is a copy of the dbschema -hd ortorh_rec -d estrie01 results AFTER the DISTRIBUTIONS ONLY but before I ran the DROP DISTRIBUTIONS that resolved it.

    Regards,

    Steve Nixon
    Invera

     



    ------------------------------
    Steve Nixon
    ------------------------------

    Attachment(s)

    txt
    schema_ortorh.txt   55 KB 1 version
    Original Message


Related Content