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

  • 1.  Huge pages and parallelism

    Posted Tue November 26, 2019 04:11 PM
    Hello,

    I'm using an Informix database to store time series for an IoT platform.

    The Informix instance has 4 CPU and 8 GB RAM. It stores 40 different databases.
    The time series containers are configured to use 16K pages.

    I did a performance test using the 8 GB of RAM and let the BUFFERPOOL automatically manage their memory. The onconfig parameters where the following:
    • SHMTOTAL 8192000
    • SHMVIRTSIZE 786432
    • SINGLE_CPU_VP 0
    • VPCLASS cpu,num=1,max=4,noage,autotune=1
    • BUFFERPOOL default,lrus=8,lru_min_dirty=50,lru_max_dirty=60,extendable=1,start_memory=auto,memory=auto
    • BUFFERPOOL size=2K,lrus=8,lru_min_dirty=50,lru_max_dirty=60,extendable=1,start_memory=auto,memory=auto
    • BUFFERPOOL size=16K,lrus=8,lru_min_dirty=50,lru_max_dirty=60,extendable=1,start_memory=auto,memory=auto
    During the test, I mainly query the databases through the following time series function: GetLastElem, GetClosestElem, PutElemNoDups, AggregateRange, select ranges of values through virtual tables.

    To improve performances, I enable the huge pages. Here is my thought for the configuration of huge pages.
    Based on my understanding and my tests, the huge pages can only be reserved by Informix at program startup.
    So, I allocate 3000 huge pages of 2MB and configure the Informix (onconfig) with the same parameters as above except for BUFFERPOOL in order to be sure that they reserve a lot of their space on huge pages:
    • BUFFERPOOL default,lrus=8,lru_min_dirty=50,lru_max_dirty=60,extendable=1,start_memory=auto,memory=auto
    • BUFFERPOOL size=2K,lrus=8,lru_min_dirty=50,lru_max_dirty=60,extendable=1,start_memory=2490368,memory=auto
    • BUFFERPOOL size=16K,lrus=8,lru_min_dirty=50,lru_max_dirty=60,extendable=1,start_memory=2555904,memory=auto

    I get surprised by the results. The majority of the queries run faster by a factor of 30% except:
    • select ranges through virtual tables: increase of 10% of the execution time;
    • AggregateRange: increase of 250% of the execution time.
    My second surprise is that now, the Informix instance use only 1 to 1.5 CPU out of the 4 allocated (the 4 were used without the huge pages). It seems that with huge pages Informix is no more able to use parallelism during queries execution.


    Can someone explain to me why Informix reacts like this? Did I misunderstand how the huge pages work?

    At the same time, could you, please, tell me if you have best practices in the allocation of huge pages for Informix (amount of huge pages to allocate, calculation of the values for the parameters SHMVIRTSIZE, BUFFERPOOL, ... in the case of huge pages)?

    Thanks in advance for your help.
    Regards,
    Renaud

    ------------------------------
    Renaud Demarneffe
    ------------------------------

    #Informix


  • 2.  RE: Huge pages and parallelism

    Posted Wed November 27, 2019 11:24 AM
    Edited by System Admin Fri January 20, 2023 04:43 PM
    I checked a little more on the Informix monitoring and I think that with huge pages there is a lot of buffer locks.

    The command "onstat -g wst" shows a lot threads wasting time in the "yield bufwait" state. I think that due to the buffer locks, Informix is not able to consume more than 1 cpu and causes the execution times to be execrable.

    The output of "onstat -g cpu" command.

    I tried to increase the number of lru per bufferpool from 8 to 128 but it doesn't improve the performances.

    Do you think I'm on the right track?
    Anyone have an idea to reduce these "yield bufwait"?

    ------------------------------
    Renaud Demarneffe
    ------------------------------
    #Informix


  • 3.  RE: Huge pages and parallelism

    Posted Wed November 27, 2019 12:18 PM
    Hi Renaud,

    I guess the (cor)relation with huge pages either doesn't exist, or something gets so much faster that you're now suffering from some sort of buffer contention.

    Use 'onstat -u' to obtain the address(es) of buffer(s) being waited on, then 'onstat -b' (-B) to find out what page(s) this relates to.  From that page physical address (chunk:page_offset) you'd be able to determine the partition it belongs to, the name of that partition and what object it is, also the logical position within that object could be calculated - not trivial, but doable ;-)

    In case only few buffers affected, let us know by providing a screen shot here.

    BR,
     Andreas

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

    Original Message


  • 4.  RE: Huge pages and parallelism

    Posted Wed November 27, 2019 12:21 PM
    ... or those bufwaits are just another form of IO wait, namely read-ahead doing the IO for us, so we'd wait for the buffer to be filled by read-ahead.

    Would really have to know more about what's going on there...

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

    Original Message


  • 5.  RE: Huge pages and parallelism

    Posted Wed November 27, 2019 12:24 PM
    If you are getting many bufwaits increase the number of LRU queues!



    Original Message


  • 6.  RE: Huge pages and parallelism

    Posted Mon December 02, 2019 09:01 AM

    Maybe not the answer you are looking for ...

    First - huge (Linux) pages have nothing to do with bufwaits. Usually, if you have Informix using lots of memory (64GB or more) then, for very intense OLTP workload,  you may actually notice some difference - on otherwise well tuned system. 

    For the problem at hand - you need to track down the buffer / buffers where wait happens - map them to the Informix part number and then figure out what to do next.  Usually using more than one fragment for the data and/or helps, but if application stubbornly tries to update same (or very few) rows in some table - then you'll need to take a closer look at the application design. 

    Start with "onstat -X" and go from there.



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

    Original Message


  • 7.  RE: Huge pages and parallelism

    Posted Thu February 06, 2020 10:42 AM
    Hello,

    Thank you for your responses and sorry for my very very late response (I had a lot of work to tackle).

    I tried your solutions but I did not find the bottleneck.
    So, I have build a fully new instance and retried the test with Hugepages. On this new instance, I'm able to process queries with Hugepages and on all of the allocated CPU.

    Thanks for your help.
    Regards,
    Renaud



    ------------------------------
    Renaud Demarneffe
    ------------------------------

    Original Message


Related Content