database performance - test setup

database performance: test setup: Informix

Informix provides a time-limited version for free download (valid for 6 months from the installation date - registration required).
The software comes as a large TAR file containing the RPM packages, the installation scripts and some documentation in HTML and TXT format.

After extracting this archive, we must create a user, directory structure, and add a service to the network services list:

adduser informix
export INFORMIXDIR=/opt/informix
mkdir $INFORMIXDIR
chown -R informix.informix $INFORMIXDIR
echo	"informix        1525/tcp"	>> /etc/services
echo	"informix        1525/udp"	>> /etc/services

Then we launch the installer which takes care of the RPM packages, you may need to install some compatibility libraries (compat-libstdc++):
./install_rpm -acceptlicense=yes

The rest of the setup should be done as the informix user.
First, we save the settings to the profile of the informix user and reload those settings (example here given for bash):

su - informix
echo export INFORMIXDIR=/opt/informix >> .bash_profile
echo export INFORMIXSERVER=demo_on >> .bash_profile
echo export PATH=$INFORMIXDIR/bin:$PATH >> .bash_profile
. .bash_profile

Then, we need to create two crucial configuration files: etc/onconfig and etc/sqlhosts.
You should use the sample configuration files provided as a template (etc/onconfig.std and etc/sqlhosts.std).
The minimum settings that must be configured in etc/onconfig are:

ROOTPATH to point to your root device. A standard empty file that you can create with touch or a raw disk device (ie: /dev/sdb9)
LTAPEDEV to point to your backup device (or /dev/null for testing)
DBSERVERNAME to be set to a name of your choosing (ie: demo_on)
NETTYPE to match the value in sqlhosts (ie: soctcp)

You may also want to set the number of processors to match your setup by adjusting MULTIPROCESSOR and NUMCPUVPS.

Then you will need to edit etc/sqlhosts to add a line similar to:

demo_on	onsoctcp        localhost       informix

Note: the first part is the DBSERVERNAME, the second matches the NETTYPE (with the 'on' prefix added), the third is the hostname to attach to and the fourth is the name of the service we have previously added to /etc/services.

Finally, we can initialize the database with:
oninit -iv
For a normal startup without the initialization, simply run oninit as the informix user.
We then use the 'dbaccess' utility to create the 'test' database, using (buffered) logging (to be able to use transactions).

The database can be accessed with the JDBC URL:
jdbc:informix-sqli://localhost:1525/test:informixserver=demo_on;user=informix;password=XXXXXXX
The default connections will use isolation level 2: "Read Committed".

Note: this setup is very basic and does not support large objects.
By default mts will tweak the default connection to the server with "set lock mode to wait;" in order to allow multi-threaded tests to access the same tables concurrently without getting locking errors, and even when doing this we did occasionally get deadlock errors because of the dreadful performance. None of the obvious tweaks (increasing buffers, threads, etc) were enough to make a noticeable difference.