Informix

 View Only

  • 1.  performance issue on Table

    Posted Fri September 25, 2020 05:47 PM
    Hi All,

    We had a table of 260GB which was round robin fragmented with 4 partitions. It hit the pages per fragment limit and we have added 4 more new fragment. Since then application team is complaining about slow performance. Any idea what could be the issue and ways to troubleshoot or any workaround.

    ------------------------------
    Sandip Nawale
    ------------------------------

    #Informix


  • 2.  RE: performance issue on Table

    IBM Champion
    Posted Sat September 26, 2020 08:35 PM
    The slowness is because the engine is trying to put data into the 4 full partitions 50% of the time. You need to redistribute the data across all 8 partitions with: ALTER FRAGMENT ON TABLE INIT ROUND ROBIN IN ...


    ------------------------------
    Art Kagel
    ------------------------------

    Original Message


  • 3.  RE: performance issue on Table

    IBM Champion
    Posted Mon September 28, 2020 01:28 AM

    Hi Sandip, the solution is: set  AUTOLOCATE greater as the default 0. e.g. AUTOLOCATE=1 . This will bring you back to the same performance as before.

    BUT: Please note, that AUTOLOCATE have many site effects during creating tables/indizes AND during Fragment extensions at runtime.
    We ( leolo IT) working hard with Informix advanced support at TS004062092.  The behavior of AUTOLOCATE is not deep enough tested from the R&D team. Means, we run in some funny auto creation of new table partitions (nobody fin the reason until, yet).

    At summary: your performance issue will fixed, but please note the unstable behavior of the AUTOLOCATE > 0 setting. Maybe you set this in your session local to push this special batch job.

    Be care, Henri



    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    ------------------------------

    Original Message