Informix

 View Only
Expand all | Collapse all

Performance V 14 is significantly slower compared to v 11

  • 1.  Performance V 14 is significantly slower compared to v 11

    Posted Thu October 27, 2022 09:36 AM
    I had opened a series of support tickets with IBM and we had been battling this issue for months. We are migrating from IDS 11.7 to 14_10 FC7 and had set identical configuration, on both. We changed a little on v14 on the following 

    moved to 3 temp dbspaces  from 2, and from  40Gb each to 50Gb each
    v11 we had all user dbspaces on 8K page size, index dbspaces (for detached indexes), temp dbspaces...  System dbspaces (root, physlogs) are on 2K and blob spaces on 16K
    v14 we left data on 8K pages and index dbspaces moved to 16K. Temp dbspaces on 8K (tested on 4K 8K and 16K and 8K perform better), sys dbspaces on default 2K and blobs on 16K.  So the only difference from old to new is Index dbspaces on 16K, 
    We added a bufferpool to account for 16K pages
    we have close to same bufferpool  from v11 to v14 except the additional 16K pages on v14. 

    v11 on rhel6  
    v14 on rhel8 
    Huge pages are set the same, as well as semaphores and file descriptors for both systems. 
    Capacity is exactly the same 24Gb ram, 4 cores, and using raw disk for every chunk. Our data pool size is about 700Gb worth of data.  Both systems are VMs
    we have several data pools (3) on v11 as system had grown through time. On v14 we have only 1 huge pool where we all chunks are created for those dbspaces and tried to set 1 chunk per dbspaces to keep contiguous pages. 
    Migrated data over with HPLs, and done LOW stats drop dist, MED dist only and HIGH on specific indexes Dist only. 
    Our reports and data processing loads run almost double the time. 
    Some reports sorting thousands of records using temp areas for sorting run 80% slower, from 3 mins to 5.8 mins. 
    Processes using cursors to load data running from 20 mins to 50 mins. 
    Newest 4GL.ESQL installed on v14 and all 4GL code had been recompiled with no errors. 
    We had tried numerous variables to twitch to get to v11 performance with no luck. 

    I came across this  post and followed it, but they are comparing apples to oranges. Here I have apples to apples (the closest I can get)  and v14 performs bad compared to OLD v11 

    Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    https://community.ibm.com/community/user/hybriddatamanagement/communities/community-home/digestviewer/viewthread?GroupId=4147&MessageKey=3a5517e6-a8c8-4d11-81b5-b098a4d0fff7&CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365&tab=digestviewer


    I can attach any file require for analysis. 
    Thanks 


    ------------------------------
    Tereso Garcia
    ------------------------------

    #Informix


  • 2.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Thu October 27, 2022 10:29 AM

    Hi Tereso,

    Your reports run slower. Are you using ORDER BYs or GROUP BYs in your reports that are slower.

    If that is the case, you can try changing the DS_NONPDQ_QUERY_MEM to a higher value and run the reports again. Do noy make this too big since it applies to all of the sessions.

    You can modify this parameter without bouncing the engine:

    onmode -wm DS_NONPDQ_QUERY_MEM=<new value>

    or

    onmode -wf DS_NONPDQ_QUERY_MEM=<new value> (modification in memory and in the onconfig file).

    Hope that this helps


    --  Cordialement, Regards,    Khaled Bentebal Directeur G??n??ral - ConsultiX T??l: 33 (0) 1 39 12 18 00 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web:  www.consult-ix.fr





  • 3.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Thu October 27, 2022 01:32 PM
    I'd say the general answer to your rather general question should and definitely is: no, 14.10 is not slower than 11.x, in many respects it even is considerably faster.

    And there the hard work of spotting any differences in actual behavior and their - possibly common - root cause(s) begins... but probably not on this forum.

    If the same load against an identical setup, with only the db server version differing, behaves differently then it should be possible to determine the cause. It only is two computer systems, after all.  It is two, right? If so, how identical are they?

    ------------------------------
    Andreas Legner
    ------------------------------



  • 4.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Thu October 27, 2022 07:15 PM

    Hi, Tereso,

    first - it's very unlikely for the Informix 14.10 to be two times slower compared to 11.70.

    Therefore you'll need to figure out the cause and address it.

    I'd start with the basics. Assuming you still have access to both environments (rhel6+11.7 and rhel8+11.4)

    1. get lscpu output from each and compare (if you don't have / cannot add lscpu package - get output from "cat /proc/cpuinfo" but that would be somewhat more difficult to compare)

    2. find repeatable workload which you can run on both, and which consistently shows 2x difference.

        In each environment, when there is no other activity, do "onstat -z ; /usr/bin/time -p (workload script) ; onstat -p"
        Compare items in "onstat -p" outputs. (more latching, more  cpu or system tim, etc)
        In parallel, if you feel like it,  you can run "onstat -g ath -r 5" during the test (save to file) and do something simple like:

        grep sql (ath-file) |cut -c50-64 | sort | uniq -c | sort -nr

          the "c50-64" part may need to be adjusted for 11.70

          These steps, hopefully, will give you some hint where to look next.

    Alternatively, you can put 11.70 on rhel8 VM (I doubt that it can be installed, you can try, though,  but "rsync" or tar with proper switches will likely work) and confirm that it's as fast there as on rhel6 VM



    ------------------------------
    Vladimir Kolobrodov
    ------------------------------



  • 5.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Thu October 27, 2022 07:43 PM
    Tereso:

    In addition to Vladimir's suggestions, I would also look closely at the two VMs. Is it possible that the new VM was configured with shared virtual cores while the original one that is running v11.70 is configured with physical dedicated cores?

    Most virtualized sites run multiple VMs on a massive frame running many VMs concurrently. Are the two VMs running on the same frame or different frames (if they are on cloud, they are almost guaranteed to be running on different frames)? You don't know what other load is running on that same frame! If performance is much different depending on the day or time of day, that would point to this problem - that v14.10 is running on a busier frame.

    Storage: Are the drives configured for the two environments identical in configuration? All RAID10 on both for example versus one on RAID10 and the v14 host connected to RAID5? You don't know what other IO traffic you are competing with for drive bandwidth or pipeline throughput. Were the two VMs configured with the same IO throughput guarantees? I have seen that if server is faster it can push up against the IO guarantee at cloud sites causing the vendor to throttle IO slowing the server down!

    I'll assume this is an Intel x86-64 environment. Could you have hyper-threading disabled on the v11.70 server (recommended) but enabled on the v14.10 server (not recommended)?

    These are factors that will make enough difference to explain what you are seeing.

    Art

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



  • 6.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Fri October 28, 2022 09:47 AM
    Thanks Art for you input. We have disk setup the same on both systems. In terms of the virtualizer, yes we tested running both VMs on the same virtualizer node to discard any variation on the node or frame if that is what you are referring. For some reason as we move the VMs among nodes we noticed a faster response, but then it goes back to the usual slow situation. We also looked at the VM tuneD profiles to be the same on both. Yesterday we started to test boosting IO by switching the IO scheduler still testing that today and I will report results the next day as we have some developers to test loads that some test takes hrs to complete. So we do have regular loads we have been testing on both systems and timing out. On v14 we have almost no worload, and on v11 we have some and still perform faster. 
    Some loads take 20 mins, other take 40 mins on the old system, as part of regular weekly data loads and processing we do.   
    I will also try to isolate v11 workload and capture those results to compare  as Vladimir Kolobrodov  suggested to check. 


    ------------------------------
    Tereso Garcia
    ------------------------------



  • 7.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Fri October 28, 2022 02:52 AM
    Hi Tereso.

    If you have slow sorts and joins, it may be faster to have them use file systems - preferably in memory - instead of temp spaces by setting PSORT_DBTEMP in the "oninit" environment. See:

    https://www.oninitgroup.com/faq-items/ram-disk-and-informix

    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------



  • 8.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Fri October 28, 2022 04:15 AM
    Hello, just adding an important topic here that might give you some help.
    The default disk IO scheduller on RHEL was changed on version 8.

    And the new one is not the best oerformatic for Databases.


    Alexandre Marini





  • 9.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Fri October 28, 2022 07:53 AM
    Alexandre:

    So, how does one change the IO scheduler in RHEL 8 to behave better for Informix?

    Art

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



  • 10.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Wed November 02, 2022 09:20 AM

    @Alexandre Marini

    this is the scheduler  on one raw data disk on v 11
    noop anticipatory [deadline] cfq                                                                                              

    This is on the new system data partition (also raw) 
    [none] mq-deadline kyber bfq                                                                                                          

    IDS v 11 7 is on rhel 6 
    IDS v14 is on rhel 8 
    What is best setting for the IO scheduler on rhel 8 ? ​

    ------------------------------
    Tereso Garcia
    ------------------------------



  • 11.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Wed November 02, 2022 09:23 AM
    On any VM installation you should be using the "noop" scheduler! Just start out configuring the scheduler the same as you have it on the RHEL7 system. Then see how that goes.

    Art

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



  • 12.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Wed November 02, 2022 07:32 PM
    On rhel8  there is  noop anymore. It seems that @Alexandre Marini  is right on that the scheduler changed on rhel8  like below



    ​So to what we have currently on our systems 
    OLD
    noop anticipatory [deadline] cfq                                                                                              

    NEW
    [none] mq-deadline kyber bfq 

    It seems we have the equivalent, just not in the same order.  Should those be on specific order ? 


    this is the documentation found on that matter for that reference 
    https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/8/html/monitoring_and_managing_system_status_and_performance/setting-the-disk-scheduler_monitoring-and-managing-system-status-and-performance


    I am doing a script to capture onstat -p  and onstat -g ath and try to figure out where we have resources workload  differences. 

    We had noticed with one query we been using to benchmark  and compare  uses  nested loop joints on v11 while on v14 it decides to use Dynamic hash joints. 



    We had  tested with SQL_DEF_CTRL  variable from 080  to 040  on the v11 and that forces the optimizer to use Dynamic hash joints  from Nested loops that best performs. 
    But we cant determine what is the best option to try on that variable on v14 to force the optimizer to use nested loops joints. 
    WE changes OPT_GOAL = 0   from default = -1  and that executes faster  on this specific report that only returns about 8 records but joints and sorts millions for that.  The only issue with that is that variable forces the optimizer to return the "first Rows" that satisfies the joints and predicates but wont return all the results.  We later tested on other workloads that had been taking 20 more mins compared to old system and still performing bad.  These workloads uses 4GL cursors and massive inserts on temp tables and on new tables to re-process data

    We also switched this undocumented variable SQL_FEAT_CTRL from default to x9008  and improves so little. 

    We are looking inside informix and at the OS level and not getting any lucky. 























    ------------------------------
    Tereso Garcia
    ------------------------------



  • 13.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Thu November 03, 2022 06:48 AM
    OPTCOMPIND controls whether the optimizer favors nested loop or hash joins. For an OLTP environment it should be set to zero while the default is 2.

    Another setting to try is the relatively new and undocumented parameter OPT_SEEK_FACTOR. The default for 11.70.FC6 and earlier was 10 and from 11.70.FC7 and later it is set to 6. There is no documentation revealing what this parameter defaults to in v12.10 or v14.10.

    You can try to set OPT_SEEK_FACTOR to 0. OPT_SEEK_FACTOR was introduced in v11.70.FC1 and is the weight given 
    by the optimizer to IO Seeks to determine the cost of different query plans. Higher values tend to overestimate the cost of using indexes and so can often cause the optimizer to favor a hash join even with OPTCOMPIND set to zero. If your storage is SSD or other low latency seek storage then OPT_SEEK_FACTOR should be set to zero. That will restore the pre-11.70 optimizer calculations.

    As you figured out, you should probably not be setting OPT_GOAL to anything other than the default of -1. OPT_GOAL 0 was implemented to make interactive form based applications seem more responsive so that the app could display the first rows quickly when a returning data set includes many many rows. It tends to avoid sorting and prefers using an index on the sort key if available which can make returning the entire data set a bit slower but the first N rows arrive much more quickly. Originally it forced a MERGE JOIN but I believe that the merge join code was removed again in v11.10 (it had been removed in 7.30 and reinstalled in 7.31). This feature was implemented for me when I was at Bloomberg and for Sears Holding back in the mid-90s when both customers were having problems with interactive apps that appeared slow. Without the MERGE JOIN code it's not as useful. Also from your comments, it looks like it may be broken.



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



  • 14.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Thu November 03, 2022 07:42 AM
    Hello Art,
    the comments on OPT_SEEK_FACTOR sound exciting. However, it only seems to be set explicitly in onconfig and not session specifically via an environment variable or via "set environment ...". ?

    Best Regards
    Henri

    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    IBM Champion 2021 and 2022
    ------------------------------



  • 15.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Thu November 03, 2022 08:53 AM
    Henri:

    You are correct. I was not clear, OPT_SEEK_FACTOR is an ONCONFIG file parameter. It is available as a dynamic setting using onmode -wm or -wf however, so a restart is not needed to modify this parameter's setting.

    Art

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



  • 16.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Sat November 05, 2022 07:58 PM
    "I believe that the merge join code was removed again in v11.10"

    What? Informix cannot do MERGE JOINs any more? SQL Server can!

    DB2 can - https://www.ibm.com/docs/en/db2/9.7?topic=optimization-joins !!!

    Andreas.

    Can you confirm if MERGE JOINS exist in V12/V14?

    David.

    ------------------------------
    David Williams
    ------------------------------



  • 17.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Fri November 04, 2022 05:30 PM
    We had tested on the IO scheduler for [none] and for [mq-deadline] which are the 2  profiles recommended for Databases but that on rhel6 and rhel7 based on this redhat article  https://access.redhat.com/solutions/54164


    Now on rhel8 those profiles only changed names  from noop to none and from deadline to mq-deadline. 
    After testing all disks on same profile,  [none] tested worse than [mq-deadline]  and none of those are close to the v11. 

    Our current tuneD profile is latency-performance. Is there any better profile that this ?   We had tested virtual guest as its the default and also tested slow. 

    Not sure to test every single one if there is a preferred profile under rhel8. Also is there any relation on tuneD and IO scheduler profiles?  Should both math any configuration or recommendation ?  



    ------------------------------
    Tereso Garcia
    ------------------------------



  • 18.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Fri November 04, 2022 10:42 PM
    We had tested IO schedulers and below the results  all the below for v14
    [NONE] that is the equivalent of [NOOP]
    Results in minutes
    run 1 = 7.05 
    run 2 = 7.11
    3 = 7.14

    [KYBER]
    run 1 = 6.57 
    run 2 = 6.52
    3 = 6.49

    [mq-deadline]  = default value for disk on rhel8 
    run 1 = 6.44 
    run 2 = 6.26
    3 = 6.25

    All the above on tuneD profile = latency-performance

    On  old system, v11 the same query takes 3.55 avg



    We also tested  the suggested variable OPTCOMPIND = 0   from default value= 2
    All these teste on disk back to [mq-deadline] 
    1 = 6.31
    1 = 6.23
    3= 6.36


    I will test the other variable OPT_SEEK_FACTOR on the weekend and post result along with query plans and if the optimizer keeps choosing Dyn hash over Nested lops.   Remember that this specific query performs better on v11 using nested loop joint
    I will also capture workload and post results early next week 



    ------------------------------
    Tereso Garcia
    ------------------------------



  • 19.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Sat November 05, 2022 07:53 PM

    Hi,

    The next thing is to compare query plans and look for sequential scans e.g. in syssesprof.

    "we have close to same bufferpool from v11 to v14 except the additional 16K pages on v14. "
    Does that mean a smaller bufferpool is beng used for the relevant indexes on v14?
    Can you test with the same pagesizes and bufferpools on v14?

    ​Andreas/Art, does bufferpool size affect the optimizer choices?

    Regards,

    David.



    ------------------------------
    David Williams
    ------------------------------



  • 20.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Sun November 06, 2022 04:55 AM
    David,

    Buffer pool size does do affect the optimizer.

    The optimizer works based on the statistics existing at the time of finding the best path and the selectivity factors of each clause in the query( =, matches, or, and, etc) and whether you have indexes or not ( the uniqueness of indexes is taken into consideration). It determines also whether it needs temporary space or not.
    After finding the most optimal path, it goes and runs the query using the available ressources ( memory, temp dbspaces, ...)

    This is just quick view of the optimizer phase of the 3 levels that the query goes thru( parser, optimizer, run phase).

    Khaled Bentebal de mon portable




  • 21.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Sun November 06, 2022 02:46 PM

    David:


    I agree with Khaled. The number of buffers or the page size of a table or index does not affect the optimizer's selection of query plans.

    What does are the following:
    • Data distributions properly created (such as by using dostats)
    • Existence of indexes
    • OPT_SEEK_FACTOR
    • OPTCOMPIND
    • OPT_GOAL
    • Level of clustered-ness of clustered index tables
    • Query details such as join conditions, filters, group by, order by

    Also, David: Yes, for some reason the developers do not like merge-joins and have at least twice put the code in and removed it. I honestly do not KNOW the current status, but I have not been able to cause the engine to perform a merge join since maybe V10, so I can only assume that it was removed again in v11.10. Any HCL lurkers care to comment? The nested-loop join code was reinstated in v7.31 or so in order to implement OPT_GOAL 0 (aka FIRST ROWS optimization) and the FIRST_ROWS optimizer directive to satisfy requests from my at Bloomberg and my counterpart at Sears Holding.

    Art

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



  • 22.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Mon November 07, 2022 01:16 PM
    @Art Kagel
    I finally tested changing OPT_SEEK_FACTOR = 0  from its default = 6
    All previous variables were left as
    OPTCOMPIND = 0    (as suggested)   from default =2   
    OPT_SEEK_FACTOR = 0x080   as we have it on v11 and added to v14 that tested better with it 
    Results 
    R1 = 7.21
    R2 = 7.18 
    R3 = 7.05 

    So it seems it performs even worse with those variable combinations


    So based on the Query plan on V11 (OLD system) that makes the difference on OLD vs NEW . 
    The OLD system favors a nested loop on temp area but creates Indexed tables on Temp (AUTOINDEX PATH) 


    Estimated Cost: 634784972800                                                                                                      
    Estimated # of Rows Returned: 127874752512                                                                                        
                                                                                                                                      
                  1) (Temp Table For Collection Subquery): SEQUENTIAL SCAN                                                            
                                                                                                                                      
                  2) (Temp Table For View): AUTOINDEX PATH                                                                            
                                                                                                                                      
                    (1) Index Name: (Auto Index)                                                                                      
                        Index Keys: vendor_id fine_type_id                                                                            
                        Lower Index Filter: ((Temp Table For Collection Subquery).vendor_cd = (Temp Table For View).vendor_id AND     
    (Temp Table For Collection Subquery).fine_type_id = (Temp Table For View).fine_type_id )                                          
                                                                                                                                      
                ON-Filters:((Temp Table For Collection Subquery).vendor_cd = (Temp Table For View).vendor_id AND (Temp Table For C    
    ollection Subquery).fine_type_id = (Temp Table For View).fine_type_id )                                                           
                NESTED LOOP JOIN(LEFT OUTER JOIN)                                                                                     
                                                                                                                                      
              3) (Temp Table For View): AUTOINDEX PATH                                                                                
                                                                                                                                      
                (1) Index Name: (Auto Index)                                                                                          
                    Index Keys: invoice_nbr                                                                                           
                    Lower Index Filter: (Temp Table For Collection Subquery).invoice_nbr = (Temp Table For View).invoice_nbr          
                                                                                                                                      
            ON-Filters:(Temp Table For Collection Subquery).invoice_nbr = (Temp Table For View).invoice_nbr                           
            NESTED LOOP JOIN(LEFT OUTER JOIN)                                                                                         
                                                                                                                                      
          4) (Temp Table For View): AUTOINDEX PATH                                                                                    
                                                                                                                                      
            (1) Index Name: (Auto Index)                                                                                              
                Index Keys: vendor_fines_id                                                                                           
                Lower Index Filter: (Temp Table For Collection Subquery).vendor_fines_id = (Temp Table For View).vendor_fines_id      
                                                                                                                                      
        ON-Filters:(Temp Table For Collection Subquery).vendor_fines_id = (Temp Table For View).vendor_fines_id                       
        NESTED LOOP JOIN(LEFT OUTER JOIN)                                                                                             
                                                                                                                                      
                                                                                                                                      


    on the new system V14, no matter what variables we change, it keeps solving using DYNAMIC HAS JOIN but I dont see autoindexing on those temp tables created to pull data for those joins and causing a sequential SCAN instead. 

    Estimated Cost: 39397240                                                                                                                   
    Estimated # of Rows Returned: 504782913536                                                                                                 
                                                                                                                                               
      1) (Temp Table For Collection Subquery): SEQUENTIAL SCAN                                                                                 
                                                                                                                                               
      2) (Temp Table For View): SEQUENTIAL SCAN                                                                                                
                                                                                                                                               
                                                                                                                                               
    DYNAMIC HASH JOIN                                                                                                                          
        Dynamic Hash Filters: ((Temp Table For Collection Subquery).vendor_cd = (Temp Table For View).vendor_id AND (Temp Table For Collection 
    Subquery).fine_type_id = (Temp Table For View).fine_type_id )                                                                              
                                                                                                                                               
      3) (Temp Table For View): SEQUENTIAL SCAN                                                                                                
                                                                                                                                               
                                                                                                                                               
    DYNAMIC HASH JOIN                                                                                                                          
        Dynamic Hash Filters: (Temp Table For Collection Subquery).invoice_nbr = (Temp Table For View).invoice_nbr                             
                                                                                                                                               
      4) (Temp Table For View): SEQUENTIAL SCAN                                                                                                
                                                                                                                                               
                                                                                                                                               
    DYNAMIC HASH JOIN                                                                                                                          
        Dynamic Hash Filters: (Temp Table For Collection Subquery).vendor_fines_id = (Temp Table For View).vendor_fines_id                     
                                                                                                                                               
                                                                                                                                               

    and this specific step takes 3 + more minutes on the NEW system v14 


    Any suggestions ?  

    We had tested  IO scheduler, and TuneD profiles. We had moved the VM to another virtualizer node to get similar slow results 
    We had tested numerous scenarios with several variable combinations  to get similar results only seconds variation but not enough to get closer to v11 

    After a new variable is set, I reset stats with "onstat -z" to make the optimizer go fresh

    We recalculated stats doing low stats dropping distributions, followed by medium stats on distributions only and High stats on indexes on distributions only on default resolution 2.5. 
    we had checked the specific tables to find the index distribution buckets and those look good for the several indexes on v14 that are close enough to v11. 

    So far on all scenarios we had got nothing to make the optimizer to behave differently from the same path  on v14  going same path that differs from v11. 

    Is that a product defect ?   It that a bug ?   Our system is 750 Gb of data and growing.  We have no confident v14 is any better than v11.  We added CPU and more RAM to v14 and still makes no difference whatsoever as increasing VPCLASS on CPU and increasing bufferpools, as some of you had previously commented that bufferpools do not affect the optimizer. 


    the biggest tables involved  (size in recors)
    15 515 286  records 
    2 699 675  records 
    82 737 064   records
    1 750 447  records




































































    ------------------------------
    Tereso Garcia
    ------------------------------



  • 23.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Mon November 07, 2022 03:12 PM
    Tereso:

    Comments:
    • OK, so this was not a difference in how the optimizer was recognizing index IO or reducing OPT_SEEK_FACTOR would have helped.
    • This is the first time you have posted the actual query plans. I see that the query, which you did not include, appears to involve joining four VIEWs each of which has to be instantiated into a temp table. In order to complete the joins the engine has to decide whether to scan the tables and do a hash join (as it decides to do in v14.10) or create indexes on all four temp tables (Auto Index) on the fly and perform a nested loop join. I think that there is obviously a difference in how the V14 and V11 are interpreting the sizes of the instantiated VIEWs because v11 is estimating that 127 billion rows will be returned while v14 is estimating over 500 billion rows while you have stated that the tables in both instances are the same sizes. To me that says some things:
    1. You probably do not have IFX_FOLDVIEW set to 1 in either engine or the engine would likely not be instantiating the VIEWs but rather would have folded the four queries together into a single SELECT on the base tables behind the view. Though it is possible that the views are so complex that folding is not possible. And:
    2. That despite your best efforts your data distributions (UPDATE STATISTICS) may not sufficiently detailed to support the optimizer making good decisions (though, again, the queries behind the four views may be so complex that no amount of data distributions on the base tables behind them will help). Have you tried using my dostats utility to generate the data distributions?
    Side note: You said in you latest that "After a new variable is set, I reset stats with "onstat -z" to make the optimizer go fresh". Note that onstat -z does nothing that affects how the optimizer works. It only zero's out the counters that keep track of performance statistics, not optimizer statistics. So, while you may want to run onstat -z before a test run so you can compare those performance stats reported by other onstat options after each run to each other, each run will perform the same whether you do so or not. 

    Anyway, if you do not have IFX_FOLDVIEW set, try setting it. If that does not help I would seriously look into rewriting the query your self to use only the base tables behind the four views and not the views themselves. (Yes, I know that 11.70 is doing a find job using the views, but imagine how much better that query would run, even on v11.70, if it did not a) have to instantiate each view into a temp table and b) did not have to build auto-indexes for all four temp tables. If you can't figure out how to rewrite that query, you could hire someone like me to do it for you. I get my jollies rewriting complex queries.  B^)

    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.






  • 24.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Mon November 07, 2022 04:38 PM
    Hello @Art Kagel

    We found that IFX_FOLDVIEW was giving us lots of performance issue. And compared to the old system v11 it also has it disabled. 
    I just enable it again and ran a couple of tests and execution time almost doubled 
    R1 = 13.13 mins
    R2 = 13.18
    R3 = 13.20

    That variable set to 1 along with the previous suggested: 
    OPTCOMPIND = 0
    OPT_SEEK_CTRL = 0

    OPT_GOAL = -1

    It did in fact changed the query behavior but took 6 more mins to resolve the query 
    below part of the query plan 
    Estimated Cost: 31067486                                                                                                                                             
    Estimated # of Rows Returned: 3211                                                                                                                                   
                                                                                                                                                                         
                  1) informix.a: INDEX PATH                                                                                                                              
                                                                                                                                                                         
                        Filters: (informix.a.department_nbr = 100 AND DATE (informix.a.generated_date ) >= 01/01/10 )                                                    
                                                                                                                                                                         
                    (1) Index Name: informix.ix_vendor_fines8                                                                                                            
                        Index Keys: fine_type_id   (Serial, fragments: ALL)                                                                                              
                        Lower Index Filter: informix.a.fine_type_id = 6                                                                                                  
                                                                                                                                                                         
                    (2) Index Name: informix.ix_vendor_fines8                                                                                                            
                        Index Keys: fine_type_id   (Serial, fragments: ALL)                                                                                              
                        Lower Index Filter: informix.a.fine_type_id = 7                                                                                                  
                                                                                                                                                                         
                  2) informix.b: INDEX PATH                                                                                                                              
                                                                                                                                                                         
                    (1) Index Name: informix.ix_vendor_fines_exemption2                                                                                                  
                        Index Keys: vendor_id   (Serial, fragments: ALL)                                                                                                 
                        Lower Index Filter: informix.a.vendor_cd = informix.b.vendor_id                                                                                  
                                                                                                                                                                         
                ON-Filters:(informix.a.vendor_cd = informix.b.vendor_id AND informix.a.fine_type_id = informix.b.fine_type_id )                                          
                NESTED LOOP JOIN(LEFT OUTER JOIN)                                                                                                                        
                                                                                                                                                                         
              3) informix.c: INDEX PATH                                                                                                                                  
                                                                                                                                                                         
                (1) Index Name: informix. 163_195                                                                                                                        
                    Index Keys: invoice_nbr   (Serial, fragments: ALL)                                                                                                   
                    Lower Index Filter: informix.a.invoice_nbr = informix.c.invoice_nbr                                                                                  
                                                                                                                                                                         
            ON-Filters:informix.a.invoice_nbr = informix.c.invoice_nbr                                                                                                   
            NESTED LOOP JOIN(LEFT OUTER JOIN)                                                                                                                            
                                                                                                                                                                         
          4) (Temp Table For View): SEQUENTIAL SCAN                                                                                                                      
                                                                                                                                                                         
        ON-Filters:informix.a.vendor_fines_id = (Temp Table For View).vendor_fines_id                                                                                    
        DYNAMIC HASH JOIN (LEFT OUTER JOIN)                                                                                                                              
            Dynamic Hash Filters: informix.a.vendor_fines_id = (Temp Table For View).vendor_fines_id                                                                     
                                                                                                                                                                         
                                                                                                                                                                         
                                                                                                                                                                         
    Union Query:                                                                                                                                                         
    ------------                                                                                                                                                         
                                                                                                                                                                         
                  1) informix.a: INDEX PATH                                                                                                                              
                                                                                                                                                                         
                        Filters: (informix.a.department_nbr = 100 AND DATE (informix.a.generated_date ) >= 01/01/10 )                                                    
                                                                                                                                                                         
                    (1) Index Name: informix.ix_hist_vendor_fines8                                                                                                       
                        Index Keys: fine_type_id   (Serial, fragments: ALL)                                                                                              
                        Lower Index Filter: informix.a.fine_type_id = 6                                                                                                  
                                                                                                                                                                         
                    (2) Index Name: informix.ix_hist_vendor_fines8                                                                                                       
                        Index Keys: fine_type_id   (Serial, fragments: ALL)                                                                                              
                        Lower Index Filter: informix.a.fine_type_id = 7                                                                                                  
                                                                                                                                                                         
                  2) informix.b: INDEX PATH                                                                                                                              
                                                                                                                                                                         
                    (1) Index Name: informix.ix_vendor_fines_exemption2                                                                                                  
                        Index Keys: vendor_id   (Serial, fragments: ALL)                                                                                                 
                        Lower Index Filter: informix.a.vendor_cd = informix.b.vendor_id                                                                                  
                                                                                                                                                                         
                ON-Filters:(informix.a.vendor_cd = informix.b.vendor_id AND informix.a.fine_type_id = informix.b.fine_type_id )                                          
                NESTED LOOP JOIN(LEFT OUTER JOIN)                                                                                                                        
                                                                                                                                                                         
              3) informix.c: INDEX PATH                                                                                                                                  
                                                                                                                                                                         
                (1) Index Name: informix. 163_195                                                                                                                        
                    Index Keys: invoice_nbr   (Serial, fragments: ALL)                                                                                                   
                    Lower Index Filter: informix.a.invoice_nbr = informix.c.invoice_nbr                                                                                  
                                                                                                                                                                         
            ON-Filters:informix.a.invoice_nbr = informix.c.invoice_nbr                                                                                                   
            NESTED LOOP JOIN(LEFT OUTER JOIN)                                                                                                                            
                                                                                                                                                                         
          4) (Temp Table For View): SEQUENTIAL SCAN                                                                                                                      
                                                                                                                                                                         
        ON-Filters:informix.a.vendor_fines_id = (Temp Table For View).vendor_fines_id                                                                                    
        DYNAMIC HASH JOIN (LEFT OUTER JOIN)                                                                                                                              
            Dynamic Hash Filters: informix.a.vendor_fines_id = (Temp Table For View).vendor_fines_id                                                                     
                                                                                                                                                                         
                                                                                                                                                                         


    I wish We can rewrite all those queries, but this is a massive system and there are hundreds of queries. Our developers had tested data loads that usually takes 20 mins to finish and now those take almost 50 mins.  
    We have processes that go up to 45 mins, or past the hr to process. Our expectation is that moving to v14 all those scripts workloads would improve. We have years, decades of written code that is coming along and rewrite would take years. We rather put the effort to move away from informix to another platform since Informix becoming more cumbersome to setup right.

    ------------------------------
    Tereso Garcia
    ------------------------------



  • 25.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Tue November 08, 2022 06:08 AM
    Hi Tereso,

    Here is an approach I might take if faced with your problem.

    If you haven't already raise a support case. You'll be able to share far more detail with IBM support than you perhaps feel you can in a public forum like this, such as full table schemas and data distributions, maybe even the actual data.

    Looking at the problem my instinct tells me the principal issue isn't slow storage or which I/O scheduler the Linux kernel uses, but the query plan chosen. I would attempt to use directives to force the v14 optimiser to avoid the dynamic hash joins and ultimately restrict it in such a way as to match the 11.70 query plan exactly, or stop when you get something faster. You can then compare like with like and this will at least tell you whether the problem is isolated to the query optimiser, even if you don't see this as an eventual solution. At the moment you have a lot of variables.

    The 'avoid_hash' directive is documented here:
    https://www.ibm.com/docs/en/informix-servers/14.10?topic=statements-join-method-directives

    As mentioned already I would expect 'OPTCOMPIND 0' to be beneficial because it should favour nested loop joins.

    Another approach is to use 'SET EXPLAIN STATISTICS;' and let the query run to completion. This will show up any discrepancy between the estimated cost and the actual cost of an operation. Maybe you could share the output here? In some limited scenarios I have seen higher resolution distributions (more than the standard 0.5, 200 buckets) help.

    Ben.

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



  • 26.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Tue November 08, 2022 06:42 AM
    I agree with Ben. I also think that the data distributions is one of the main problems. Tereso: can you post the statistics sections from the end of the sqexplain output from all three query plans (v11, v14 default, v14 w/IFX_FOLDVIEW)? That might help us understand. Meanwhile, definitely open a support case with IBM.

    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.








  • 27.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Wed November 09, 2022 11:47 AM
    Attaching query  plans for v11
    and for v14 ( 2 scenarios) 
    1st scenario is with VM  I/O scheduler on mq_deadline and OPTCOMPIND = 0 and OPT_SEEL_FACTOR = 0 

    2nd scenario  with the above plus added the ifx_foldview = 1 that doubled the execution plan

    ------------------------------
    Tereso Garcia
    ------------------------------



  • 28.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Wed November 09, 2022 12:19 PM
    Attaching query  plans for v11
    and for v14 ( 2 scenarios) 
    1st scenario is with VM  I/O scheduler on mq_deadline and OPTCOMPIND = 0 and OPT_SEEL_FACTOR = 0 

    2nd scenario  with the above plus added the ifx_foldview = 1 that doubled the execution plan

    ------------------------------
    Tereso Garcia
    ------------------------------



  • 29.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Wed November 09, 2022 04:34 PM
    Teresco:

    I hope that you do not take this the wrong way, but that query is a horror! Not productive, I know, but I had to get that out of the way first.

    I also know that overly complex SQL is not the issue here, but getting v14.10 to perform for you at least as well as 11.70 does is the issue.
    So, even though you have previously noted that you do not want to rewrite tons of old complex SQL in many places in many applications, still, I am going to tell you what I see that could help. The first two items do not involved modifying the SQL, however, so worth checking out.

    Here is what I can see, in no particular order:

    1 - I can see from the estimated versus actual rows produced and from the differences in the estimates between v11 and v14 that the data distribut
    ions on v14 are simply insufficient. I STRONGLY suggest running the latest (or at least a recent) release of my dostats utility with the "-Z 0
    .33" option so that MED stats sample at least 33% of the rows in the table. Then try the query with the various configuration options.  

    2 - The GROUP BY clauses all require sorting. Make sure that the DS_NONPDQ_QUERY_MEM parameter in the ONCONFIG file is set large enough to accommodate these sorts completely in memory. This is a dynamic parameter. (Reach out to me privately if you don't know how to check that).

    3 - There are several older style joins that do not have the JOIN keyword nor the ON clause join filters but have the join filters in the WHERE clause which forces the optimizer to join every row to every row and filter post-join. That is mixed in with some ANSI style LEFT (outer) joins which may confuse the optimizer. Simply changing all of the joins so that all are ANSI style joins and moving those join filters such that every join has an ON clause may help. I do know that all of the "LEFT" joins are in a subquery, so not strictly mixing ANSI and traditional join styles, but still I would be more comfortable if the query was consistent.

    4 - There are several instances of code expansion and code translation using CASE statements. These would probably be faster if the codes and their expansions/translations were stored in a small table. That would also eliminate the need to edit the queries when/if new codes are added in the future. Just add a row to the lookup table.  

    5 - The inner subquery with the UNION of selects against v_vendor_fines and v_hist_vendor_fines would actually be a good candidate to be replaced
    by a VIEW that is simply joined to the other tables and let the optimizer fold it back into the query. It is not being folded as it stands now.

    6 - The outer subquery containing the subquery in #5 could then just be a simple join of the VIEW I mention in #5 and the three LEFT OUTER tables
    and the three remaining inner join tables (a12, a13,  a14) with the folded subquery a15 which also does not need to be a subquery since it does not include any aggregations.

    Art 



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



  • 30.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Sun November 06, 2022 02:45 AM
    Hi, 
    I would try to install the old 11.x Instance with identical settings on the new machine and new OS and check the same query in order to get to know if the OS is relevant or IDS.
    There are obviously too many parameters changed for just blaming IDS to be slower. My experience is that 14.x is not slower, but our environment is not really comparable to yours.

    Just my 50 cts.

    Marcus Haarmann 





  • 31.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Fri October 28, 2022 03:59 AM
    Edited by System Fri January 20, 2023 04:25 PM
    Hi Tereso,

    try to set the onconfig parameter:
    DIRECT_IO 0x5
    to speed up the temp dbspace work with direct IO .

    Best Regards
    Henri

    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    IBM Champion 2021 and 2022
    ------------------------------



  • 32.  RE: Performance V 14 is significantly slower compared to v 11

    IBM Champion
    Posted Fri October 28, 2022 04:25 AM
    I'd say the task at hand is restoring confidence in v14.10, i.e. understanding and eliminating causes for (negative) differences to older versions under *seemingly* similar or identical conditions and configuration.

    Once that's accomplished, nothing should keep anyone from exploring all the ways v14.10 can shine even brighter then previous versions ;-)

    As I said, it can be tedious, but also rewarding.  So far we know far too little, regarding type of load as well as resource consumption and limiting factors.

    If the two systems still can be compared side by side, it should be possible to spot behavioral differences.
    If not, it had to be determined what are the top limiting factors: first of all, and for every work load separately, is the system cpu, disk or maybe network bound? Then what could be reasons for this within the database server, or outside/underneath.

    ------------------------------
    Andreas Legner
    ------------------------------



  • 33.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Fri October 28, 2022 08:59 AM
    My impression is that raw disk is faster than coocked disk and direct I/O. Since v11 had been on raw disk, we left the same on v14. 
     Originally we have those 3 temp chunks from same log vol disk on the san, then we split those on individuals log vol, but it made no difference.

    ------------------------------
    Tereso Garcia
    ------------------------------



  • 34.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Fri October 28, 2022 09:08 AM
    I have the impression that raw disk is faster that cooked disk + direct IO. We decided to use raw to have similar results as from v11. Originally the 3 temp partitions were sharing disk on same log vol as the root and physlogs. Then we decided to test splitting them each on its own log vol, but still made not much difference. PErf came slightly faster but not enough to compete with old system

    ------------------------------
    Tereso Garcia
    ------------------------------



  • 35.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Fri October 28, 2022 04:35 AM
    On the specific point of 14.10.xC5 being slower than 12.10, I didn't contribute to that thread as I had nothing to offer at the time. However in some situations it definitely was and we had the load test results to show it. All the issues I am aware of are fixed in 14.10.xC6+, so it probably isn't relevant to your issue. Ben.

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



  • 36.  RE: Performance V 14 is significantly slower compared to v 11

    Posted Mon November 07, 2022 01:25 PM
    Same here I was thinking that I update then this will work faster but it is slower than that, I hope the issue will be resolved.

    ------------------------------
    Hasnat Ali
    ------------------------------