Getting the most out of an Innovator-C instancePosted: 3 November, 2012 | |
IBM Informix Innovator-C edition provides a free version of the Informix engine, albeit with memory, CPU and feature restrictions. IBM describes it as useful for development, test and small production use, which is a fair summary. However, even for a test system you may find Innovator-C edition restrictive. To make things worse, with Informix 11.70 IBM reduced the number of CPU virtual processors (VPs) allowed from four to one and withdrew the 11.50 version, with its more generous limit. It also switched out the HDR/ER functionality. I suspect this was done because Innovator-C edition was abstracting revenue from paid-for editions.
This blog post is about getting the most out of an Innovator-C instance. The most obvious point is that if you still have an 11.50 download laying around you can still use it and configure it for up to four CPU VPs, unless you need 11.70 features of course.
Let’s take a look at what we can do with onconfig.
As you are restricted to a single CPU VP with 11.70, you’ll need to set the system up for this:
I have set MULTIPROCESSOR to 1 because I am using a multi-processor machine. Unfortunately VP_MEMORY_CACHE is another feature you can’t use with Innovator-C edition so I have set that to zero too. SINGLE_CPU_VP is set to 1 as it switches out some checks the engine performs when there are more than one CPU VPs. You may as well set this even though the performance difference is small because setting VPCLASS to anything other than num=1 or dynamically adding CPU VPs is disallowed.
The 2 Gb memory limit can prevent the engine adding extra memory segments as required. This is a serious problem if your application is greedy with memory. Keeping cursors open or having an excessive number of connections to the database are common causes of this; fixing the root cause is the best approach but not something a DBA can directly control. You’ll therefore have to make sure that your engine has enough shared memory at start-up or you keep the BUFFERPOOLs small so that there is free memory under the 2 Gb limit to allow adding segments to work. If things are really tight, you will need to configure minimal BUFFERPOOLs and assign every last kilobyte to the virtual segment. Furthermore, failing to add a shared memory segment because of a memory limit can sometimes cause an assert failure and possibly a shared memory dump depending on the value of onconfig parameter DUMPSHMEM. You may want to set this to something other than 1 to prevent 2 Gb being written to disk each time.
To improve performance on any single CPU VP instance, you will probably want to avoid using kernel AIO on Linux or Mac OS systems. This might sound wrong as kernel AIO is generally regarded as offering a performance advantage. On a single CPU VP instance, however, you don’t want your only CPU VP to be responsible for I/O, even if it is asynchronous. Instead use normal AIO VPs to do this work (and tuning these appropriately) which run as separate oninit processes and – even though they won’t be doing much processing – they can run concurrently to your only CPU VP and your operating system can run them on any other CPUs installed in your system. As kernel AIO is enabled by default, you have to set the environment variable KAIOOFF to 1 in the shell in which you start your engine. Run onstat -g iov and check you can’t see any kio class VPs.
If you’re performing sort operations, like building an index, Innovator-C edition does not support PDQ and so the MAX_PDQPRIORITY onconfig parameter is forced to zero. However you can still set the PSORT_NPROCS and PSORT_DBTEMP environment variables in your session. Those PSORT threads will all have to run one the single CPU VP but you may see a performance benefit. If your server has significantly more than the 2 Gb RAM you’re restricted to using, you could set PSORT_DBTEMP to point to a RAM disk for a significant performance boost.
One final point is that I find using Innovator-C edition unsatisfactory for test systems and you might too if your production environment is licensed using growth or ultimate edition. Your production environment may be using 11.70.FC5W1 but no such version exists for Innovator-C edition; you’d be stuck with 11.70.FC5IE: mismatched versions may or may not be good enough for your testing. Also, having a production sized test environment can be made difficult using Innovator-C edition which, while it ostensibly has no storage limits, does not support partitioniong and therefore table and index sizes are restricted to 256 Gb: 16 million pages times the largest page size available, 16 kb. Importing/exporting or cloning from an instance with partitioning is tricky and impossible respectively. You may want to consider buying growth or ultimate edition on a named user basis, which can be quite cost-effective.