Huge pages and parallelism

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=autoDuring 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

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

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 ------------------------------

... 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 ------------------------------

If you are getting many bufwaits increase the number of LRU queues!

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 ------------------------------

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 ------------------------------