Informix

 View Only

  • 1.  4GL application performance over TCP / IP connections

    Posted Wed August 25, 2021 06:51 PM
    Hello community,

    I have this situation:

    1. We are migrating database and applications (4GL) from a power 7 to a power 9.
    2. In the previous architecture (Power 7) the applications (4GL) were running on the same LPAR as the database.
    3. In the new architecture it is proposed to separate the two layers, an LPAR for the database and another LPAR for applications (4GL)
    4. The tests that have been carried out show that the same application (4GL) executed over a TCP / IP connection (onsoctcp) has a delay greater than thirty percent.
    5. We have done tests with the environment variable FET_BUF_SIZE (4K / 8K / 16K / 32k / 64K, 128K, 256K) and the times have not improved much.

    I appreciate your recommendations on the network or in the configuration of the 4GL client environment

    Thank you

    ------------------------------
    arnulfo martinez ruiz
    ------------------------------

    #Informix


  • 2.  RE: 4GL application performance over TCP / IP connections

    Posted Thu August 26, 2021 03:29 AM
    Hi,

    That seems network problem!
    The hostnames are registered on DNS?
    Execute: time nslookup $(hostname) to see if there are any delays on resolution
    Over linux, I use [netstat -an] to check if the server is listening over IPV6 or IPV4, I think there will be something similar on AIX.


    SP

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------

    Original Message


  • 3.  RE: 4GL application performance over TCP / IP connections

    Posted Thu August 26, 2021 04:52 AM
    Indeed, TCP/IP connections are much slower than Shared memory ones. If you have diferent 4GL programs starting and stopping, each time it tries to open a database connection.

    I rcommend you to declare in the /etc/hosts all the 4GL client servers, because when the IDS server receives a TCP/IP conneciton request it tries to make a reverse DNS call trying to obtain the client name from its IP.

    Start doing this and maybe it's enough to improbe connection performance, but you'll never get the same performance as shared memory connections.

    ------------------------------
    Vicente Salvador Cubedo
    ------------------------------

    Original Message


  • 4.  RE: 4GL application performance over TCP / IP connections
    Best Answer

    Posted Thu August 26, 2021 07:42 AM
    Hi !
    I already experienced exactly the same situation years ago.
    My considerations:
    1. You will never achieve the same performance for applications with a high volume of embedded SQL in 4gl (where the network communication became the bottleneck).
      You are able to see this executing a tcpdump between 4gl / database connection.
    2. We are able to minimize the problem configuring the network between LPARs using VIO networking, this is an AIX/LPAR set.
    3. You can try to minimize the problem with :
      1. Setting : fet_buf_size, optofc , optmsg .  However, be careful with opt* , since they can change the behave of your application. 
      2. Review your application logic, preparing your SQLs, and using the prepared cursor (avoid the traffic of the SQL text on the network)
      3. Also review your code to avoid repeated SQLs where get over and over again the same information, when they aren't changed. 
        This is a very common behave code for 4gl developers.
      4. Consider migrating part of your application logic to the database, using SPLs (that is one of our main solutions here, this way also gives us the liberty to begin migrating our 4GL to newer languages with less work of business adjustments). However, SPL is very poor of resources, much simpler than 4gl language, you will be able to work with them only if your business logic isn't much complex.
    The tcpdump command is very useful to see how the network communication is improved when you optimize some 4gl programs with the items I mentioned above.
    With it you are able to collect information like the size of the packets and the amount of the packets traffic on the network.

    Regards
    Cesar



    Original Message


  • 5.  RE: 4GL application performance over TCP / IP connections

    Posted Thu August 26, 2021 03:58 PM

    Arnulfo,

    In a migration from v7 to v9, I generally change the SMT from 4 to 8 threads per cpu. If you check the output of mpstat, you will see that the first thread of each cpu is used and the rest are very low or zero.
    You have two options, reduce the SMT to 4 or 2, you can also change the strategy of using threads from raw to scalable.

    Check this URL
    http://aix4admins.blogspot.com/2011/08/commands-and-processes-process-you-use.html

    My best regards

    Adolfo Flores



    ------------------------------
    Adolfo Flores
    Database Consultant
    Gesytec
    ------------------------------

    Original Message


  • 6.  RE: 4GL application performance over TCP / IP connections

    IBM Champion
    Posted Fri August 27, 2021 09:00 AM

    On a pure DB server I have always found setting SMT to 0 has seen a marked improvement. Chatting to the AIX and Informix architecture people they said  the two internal thread scheduling mechanisms don't play nicely together.   As it doesn't require any reboot etc it so easy to test

     

    Cheers

    Paul

     




    Original Message


  • 7.  RE: 4GL application performance over TCP / IP connections

    Posted Fri August 27, 2021 03:32 AM
    Hello
    I also had to face this kind of situation where the network latency was impacting the application performance after migrating from single LPAR to separate LPARs for app server and DB server.
    If your two LPARs are on the same physical machine (and you don't plan to have them on separate physical machines), then I advise you to use a virtual network between them, I mean one single VLAN (no routing !), you should definitely avoid the additional latency of the software network virtualization performed by the SEA : if you stay on same VLAN and same physical machine then all network communication between both LPARs will go through the virtual network managed by the hypervisor without needing the cost of transferring packets from/to virtual network to/from physical network by the SEA.

    Another good solution is to use SR-IOV network adapters (use vNIC if you want to be able to implement LPM), as it provides hardware network virtualization which is much more performant than SEA and gives better latency.

    In my case the app running on the servers was badly coded with lots of big loops retrieving data one row at a time in a very inefficient manner so that the network latency was big compared to the queries execution time.
    In any case, if you want maximum performance, you have to minimize the network path length between the APP and the DB : avoid routing and software virtualization where possible.





    ------------------------------
    Fabrice PLATEL
    ------------------------------

    Original Message