Performance Issue with Informix 14.10 after migrating to LINUX

Hello everyone, I would like to bring the issue we are having with file loading after migrating Informix to LINUX VM. In current world, we have application and Informix DB (V11.70) running on same Solaris server. As part of migration, we have moved Informix DB (V 14.10) to LINUX VM. Application still runs on Solaris. Currently we are in testing phase. While testing, we found that the performance has reduced after migrating Informix to LINUX VM. We have a data file (with 1M records) takes 6 mins to process when both App and DB on Solaris. But after migration, the file process taking more than 30 mins. Is anyone faced this kind of performance issue? Any help would be greatly appreciated. Thanks Balakrishna Talasu ------------------------------ Balakrishna Talasu ------------------------------ #Informix

In the middle of 3 V11.7 to 14.10 migrations at the moment, performance is better in all cases. That is despite moving to VMs. Turning off hyperthreading on the real tin have a 10% gain under heavy loads What is your disk IO looking like Cheers Paul > Hello everyone, > > I would like to bring the issue we are having with file loading after > migrating Informix to LINUX VM. In current world, we have application and > Informix DB (V11.70) running on same Solaris server. As part of migration, > we have moved Informix DB (V 14.10) to LINUX VM. Application still runs on > Solaris. Currently we are in testing phase. While testing, we found that > the performance has reduced after migrating Informix to LINUX VM. We have > a data file (with 1M records) takes 6 mins to process when both App and DB > on Solaris. But after migration, the file process taking more than 30 > mins. Is anyone faced this kind of performance issue? Any help would be > greatly appreciated. > > > Thanks > Balakrishna Talasu > > ------------------------------ > Balakrishna Talasu > ------------------------------ > > > Reply to Sender : > https://community.ibm.com/eGroups/PostReply/?GroupId=4147&SenderKey=ec45d765-bc67-4470-adad-7b3db594334a&MID=45649&MDATE=7575458458&UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved > > Reply to Discussion : > https://community.ibm.com/eGroups/PostReply/?GroupId=4147&MID=45649&MDATE=7575458458&UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved > > > > You are subscribed to "Informix" as paul@oninit.com . To change your > subscriptions, go to > http://community.ibm.com/community/user/preferences?section=Subscriptions&MDATE=7575458458&UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved. > To unsubscribe from this community discussion, go to > http://community.ibm.com/HigherLogic/eGroups/Unsubscribe.aspx?UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved&GroupKey=60eb97a2-57c0-4130-9b6d-57174f97d5a8. -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com Oninit? is a registered trademark of Oninit LLC Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians

Balakrishna: There are just too many possible causes for this to properly solve your problem online like this. We can try though. Here are some things to consider: Does the VM have the same or better CPU cycles available? (DO NOT COUNT INTEL HYPERTHREADS!) Have you performance tuned the instance? Did you clear and rebuild the data distributions, perhaps using dostats? What hypervisor is the VM running under? IO performance under some is very poor. If the VM is running on a large shared frame, has it been allocated dedicated or shared CPU resources? Is the VM sharing storage with other systems or does it have dedicated storage? What filesystem types are you using for chunks? Is DIRECT_IO enabled? Are huge memory pages enabled? Does the Informix instance on the new system have at least as much memory available to it as the old system did? Did you make any changes in the way tables and indexes were allocated to dbspaces going to the new server? Do you have sufficient CPU VPs, AIO VPs, and NET VPs configured? I assume that now the application and that load file are loading over the network when they used to connect to the database via shared memory. Is the network fast enough? Is there other traffic on the network that may be interferring? Where archives running during the data load run on the new system that didn't used to run at that time on the old one? There's more I would ask and/or examine. Maybe you need to contract for an in depth server health check or project specific examination from my company or one of the other excellent Informix consultancies to figure this out? ------------------------------ Art Kagel ------------------------------

Just done some testing on VMs and huge pages, found it made zero difference if enabled or disabled. Not the result I expected but ... it is what I found Cheers Paul > Balakrishna: > > There are just too many possible causes for this to properly solve your > problem online like this. We can try though. > Here are some things to consider: > > > > > > Does the VM have the same or better CPU cycles available? (DO NOT COUNT > INTEL HYPERTHREADS!) > > Have you performance tuned the instance? > > Did you clear and rebuild the data distributions, perhaps using dostats? > > What hypervisor is the VM running under? IO performance under some is very > poor. > > If the VM is running on a large shared frame, has it been allocated > dedicated or shared CPU resources? > > Is the VM sharing storage with other systems or does it have dedicated > storage? > > What filesystem types are you using for chunks? Is DIRECT_IO enabled? > > Are huge memory pages enabled? > > Does the Informix instance on the new system have at least as much memory > available to it as the old system did? > > Did you make any changes in the way tables and indexes were allocated to > dbspaces going to the new server? > > Do you have sufficient CPU VPs, AIO VPs, and NET VPs configured? > > I assume that now the application and that load file are loading over the > network when they used to connect to the database via shared memory. Is > the network fast enough? Is there other traffic on the network that may be > interferring? > > Where archives running during the data load run on the new system that > didn't used to run at that time on the old one? > > > There's more I would ask and/or examine. Maybe you need to contract for an > in depth server health check or project specific examination from my > company or one of the other excellent Informix consultancies to figure > this out? > > > > ------------------------------ > Art Kagel > ------------------------------ > ------------------------------------------- > Original Message: > Sent: Tue March 03, 2020 08:40 AM > From: Balakrishna Talasu > Subject: Performance Issue with Informix 14.10 after migrating to LINUX > > Hello everyone, > > I would like to bring the issue we are having with file loading after > migrating Informix to LINUX VM. In current world, we have application and > Informix DB (V11.70) running on same Solaris server. As part of migration, > we have moved Informix DB (V 14.10) to LINUX VM. Application still runs on > Solaris. Currently we are in testing phase. While testing, we found that > the performance has reduced after migrating Informix to LINUX VM. We have > a data file (with 1M records) takes 6 mins to process when both App and DB > on Solaris. But after migration, the file process taking more than 30 > mins. Is anyone faced this kind of performance issue? Any help would be > greatly appreciated. > > > Thanks > Balakrishna Talasu > > ------------------------------ > Balakrishna Talasu > ------------------------------ > > > Reply to Sender : > https://community.ibm.com/eGroups/PostReply/?GroupId=4147&SenderKey=0daacae6-8487-4b74-8d8a-0dd63d1a05f3&MID=45687&MDATE=7575458458&UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved > > Reply to Discussion : > https://community.ibm.com/eGroups/PostReply/?GroupId=4147&MID=45687&MDATE=7575458458&UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved > > > > You are subscribed to "Informix" as paul@oninit.com . To change your > subscriptions, go to > http://community.ibm.com/community/user/preferences?section=Subscriptions&MDATE=7575458458&UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved. > To unsubscribe from this community discussion, go to > http://community.ibm.com/HigherLogic/eGroups/Unsubscribe.aspx?UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved&GroupKey=60eb97a2-57c0-4130-9b6d-57174f97d5a8. -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com Oninit? is a registered trademark of Oninit LLC Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians

Hi Balakrishna, Is the server where the Linux VM is installed similar or better than the existing Solaris server specification? If the application is slow even though the specs are better than before, you should check what Art or Paul mentioned. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

We also did some testing with huge pages, and with approximately 300GB of shmem, we expected some improvements, but got none. Quite surprising, actually. This was also in a VM ------------------------------ Øyvind Gjerstad Developer/Architect PostNord AS ------------------------------

This "processing" - is it done in parallel or by single thread / session ? Could be a simple case of "local" vs "remote" - where local has (10x,100x) microsecond latency for communication vs latency in millisecond range for network communication. With properly configured VM, where most important is to NOT use memory overprovisioning, Informix should run fine, with performance impact not exceeding 20% compared to baremetal. Unless you migrate from SPARC M7 or newer processor - deployments on recent Intel / AMD processors should see improved performance. ------------------------------ Vladimir Kolobrodov ------------------------------

Might simply be that your application and database are now in different machines and latency between them matters for your workload. I have a few in house applications that behave that way, making millions of "small" queries to the database and any latency increase in the connection to the database has a huge impact on the application. ------------------------------ Luis Marques ------------------------------

Was the Solaris on physical hardware? VM performance is always worse than physical hardware. Thanks - Kate Tomchik, Principal Systems Engineer, The Home Depot , (770)437-4949 Home Depot Marietta Technology Center | 2211 Newmarket Parkway | Marietta, GA 30067 The best way to avoid being consumed by predators is to taste badly. PRODUCTION ISSUES – X12000 , ITO_OCC_Database@homedepot.com To create TICKETS: https://servicecatalog.apps.homedepot.com/home/catalog/Databases/databasesupport Click on " Support for Existing Database ", Enter your LDAP and extension, select "Development Support" –or – "ITO Database L1". The information in this Internet Email is confidential and may be legally privileged. It is intended solely for the addressee. Access to this Email by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. When addressed to our clients any opinions or advice contained in this Email are subject to the terms and conditions expressed in any applicable governing The Home Depot terms of business or client engagement letter. The Home Depot disclaims all responsibility and liability for the accuracy and content of this attachment and for any damages or losses arising from any inaccuracies, errors, viruses, e.g., worms, trojan horses, etc., or other items of a destructive nature, which may be contained in this attachment and shall not be liable for direct, indirect, consequential or special damages in connection with this e-mail message or its attachment.

Hi Bala, me back. Question # 1 Do you use kernel IO / Direct IO ? I no, I am sorry :-) If yes, you problem might be there. I explain Informix uses kernel of simulates it on linux if you don't use raw device. To enable that functionality, you must set the ONCONFIG DIRECT_IO to 1. then, Informix, thru the cpu vps, will fire use of kernel io. You can check that your instance runs on kaio when runnign onstat -g iof, and non null values for kaio_reads and kaio_writes. you will also see kaio threads in onstat -g ath. Now here is the important part. When using kaio, Informix will use a configurable kernel resource called aio-max-nr, which allows a process to operation several I/O ops at the same time. Informix takes that resource at bounce time, that you will see by the message: " VP X is requesting n kaio event resources" Basically what happens is that each cpu vp will take the total of available aio events divided by the number of cpu vps configured, which may be ponderated by the KAIOON env variable. Having too few aio events available will result in dramatically bad IO performance. Having the right number is a real game changer. I had this problem yesterday at a customers, where his parameter was too low, and I had checkpoints during one hour... another symptom was the cpu efficiency extremely low ( onstat -g glo | grep cpu ) running by 3-5 ish %, starting at 100% at boot time and decreasing drastically at the first checkpoint, during a massive data load. The kernel parameter is fs.aio.max.nr first stop informix ( onmode -yuck) you can show it this way (under root user) sysctl -a | grep aio I had the value of 65536. I changed it to 1 million, editing the /etc/sysctl.conf file modifying the line fs.aio-max-nr = 1000000 (better use a 1024 factor) then sysctl -p /etc/sysctl.conf restart informix ( oninit -v) and try After setting the parameter to the right value, checkpoints went from one+ hour to 2 minutes max, and onstat -g glo | grep cpu went back to 80% ish Hope this helps Eric ------------------------------ [eric] [Verceletto] [] [Founder] [kandooerp.org] [Pont l'Abbé] [France] [+33 626 52 50 68] ------------------------------