This article describes some actions that improves the performance and the manageability of your IDS instance. They might not be essential for a developer installation, but they show some good practices for larger installations. The nice side effect is that you learn even more about IDS

Please make sure that you have created the suggested directory structure and the necessary environment settings are active. If the env.sh has been executed, variable $PENGUIN should point to our /home/informix/instances/penguin directory.

In the following examples we will only add a single data, index and temporary dbspace. However you might add additional dbspaces if it is appropriate for your specific environment. Adding dbspaces can be done by the onspaces command line utility or thru the new SQL Admin API.

You will need about 1 GB of free diskspace for creating the additional dbspaces

• touch $PENGUIN/data/physdbs.chk1

• chmod 660 $PENGUIN/data/physdbs.chk1

• onspaces -c -d datadbs1 -p $PENGUIN/data/physdbs.chk1 -o 0 -s 102400

• execute function sysadmin:task('create dbspace', 'physdbs', '$PENGUIN/data/physdbs.chk1', '100m');

You don't need to create the dbspace (chunk) file yourself as with onspaces. The SQL Admin API does it for you. It also recognizes our environment variable $PENGUIN. Great !!. So we will exclusively use the SQL Admin API for our other dbspaces.

Normally you will execute the SQL Admin API functions from your application. However you might also use the dbaccess utility to execute the API functions directly from the shell in two ways:

1. Piping the command via echo directly to the dbaccess utility

   • echo "execute function sysadmin:task('create dbspace', 'physdbs', '$PENGUIN/data/physdbs.chk1', '100m');" \
     | dbaccess -e sysadmin -

2. Creating a SQL command file and executing the dbaccess utility from the shell

   • vi create_dbspace.sql

     execute function sysadmin:task('create dbspace', 'physdbs', '$PENGUIN/data/physdbs.chk1', '100m');

   • dbaccess -e sysadmin create_dbspace.sql

The onstat utility with the -d option shows you an overview of all allocated dbspaces and chunks in your IDS instance:

• onstat -d

IBM Informix Dynamic Server Version 11.10.FC1DE   -- On-Line -- Up 00:03:43 -- 46552 Kbytes

Dbspaces
address          number   flags      fchunk   nchunks  pgsize   flags    owner    name
45068e88         1        0x60001    1        1        2048     N  B     informix rootdbs
46781028         2        0x60001    2        1        2048     N  B     informix physdbs
 2 active, 2047 maximum

Chunks
address          chunk/dbs     offset     size       free       bpages     flags pathname
45069028         1      1      0          15000      2631                  PO-B  /home/informix/instances/penguin/data/rootdbs.chk1
467811c0         2      2      0          51200      51172                 PO-B  /home/informix/instances/penguin/data/physdbs.chk1
 2 active, 32766 maximum

NOTE: The values in the "size" and "free" columns for DBspace chunks are
      displayed in terms of "pgsize" of the DBspace to which they belong.

Expanded chunk capacity mode: always

• execute function sysadmin:task('create dbspace', 'logdbs', '$PENGUIN/data/logdbs.chk1', '100m');

• execute function sysadmin:task('create dbspace', 'datadbs1', '$PENGUIN/data/datadbs1.chk1', '400m');

• execute function sysadmin:task('create dbspace', 'idxdbs1', '$PENGUIN/data/idxdbs1.chk1', '200m');

It's not always a good idea to have dbspaces just for indexes, as these will frequently become “hot”, especially if there are many small indexes in the space. However, if you have very large indexes, it is worth placing them in their own dbspace (or preferably, dbspaces!)

• execute function sysadmin:task('create tempdbspace', 'tempdbs1', '$PENGUIN/data/tempdbs1.chk1', '200m');

If you create the temporary dbspace using the onspaces utility, make sure that specify the -t option. Otherwise a normal (logged) dbspace will be created and any activities in this dbspace will be recorded in the logical logs.

You need to change the DBSPACETEMP onconfig parameter in order to make the IDS instance aware of the new temporary dbspace.

Moving objects outside of the rootdbs is recommended. In production systems you might move the physical log and the logical logs to faster devices to further increase the throughput of your system. Here we will simply move them out of the rootdbs to achieve a cleaner structure of our penguin instance.

The physical log can be moved online without interruption of ongoing transactions. We will move it to our newly created dbspace physdbs. You can either choose the onspaces utility or the SQL Admin API whichever is more convenient to you.

• onparams -p -s 100000 -d physdbs -y

• execute FUNCTION sysadmin:task('alter plog', 'physdbs', '100000k');

We can't directly move logical logs to another dbspace. So we will create new logical logs and afterwards delete the old ones.

• #!/bin/bash
  
  for i in {1..20}
  do
     onparams -a -d logdbs -s 5000    # create a new logical log
     echo "Logical Log $i added..."
  done

• execute function sysadmin:task('add log', 'logdbs', '5000k', '20');

• In order to drop the initial logical logs, we need to switch the current log flag as well as the last checkpoint flag to one of the newly created logs. Command onstat -l gives us an overview about all logical logs in our IDS instance:

  ...
  address          number   flags    uniqid   begin                size     used    %used
  46777050         1        U-B---L  39       1:271                1000       75     7.50
  467770b8         2        U---C--  40       1:1271               1000        1     0.10
  46777120         3        U-B----  35       1:2271               1000        2     0.20
  46777188         4        U-B----  36       1:3271               1000        3     0.30
  467771f0         5        U-B----  37       1:4271               1000        2     0.20
  46777258         6        U-B----  38       1:5271               1000        2     0.20
  466b0440         7        A------  0        3:28                 2500        0     0.00
  46777f08         8        A------  0        3:2528               2500        0     0.00
  ...

• In the above example log number 2 (flags: U—C–) is the current log and log number 1 (flags: U-B—L) has the last checkpoint recorded. We have to perform five log-switches to make the newly added log number 7 (flags: A——) the current log and afterwards manually trigger a checkpoint:

  #!/bin/bash
  # switch_logs.sh - Switch current logical log
  
  while [ $(onstat -l | perl -alne 'print ((split/:/,$F[4])[0]) if substr($F[2],4,1) eq "C"') = 1 ]
  do
     onmode -l
  done
  
  onmode -c      # trigger checkpoint
  onstat -l      # show logical log status

  ...
  address          number   flags    uniqid   begin                size     used    %used
  46777050         1        U-B----  39       1:271                1000       75     7.50
  467770b8         2        U-B----  40       1:1271               1000        3     0.30
  46777120         3        U-B----  41       1:2271               1000        2     0.20
  46777188         4        U-B----  42       1:3271               1000        2     0.20
  467771f0         5        U-B----  43       1:4271               1000        2     0.20
  46777258         6        U-B----  44       1:5271               1000        2     0.20
  466b0440         7        U---C-L  45       3:28                 2500        3     0.12
  46777f08         8        A------  0        3:2528               2500        0     0.00
  46777f70         9        A------  0        3:5028               2500        0     0.00
  ...

• Now log number 7 (flags: U—C-L) is our current log and it also contains the last checkpoint recorded. We could now drop the initial logs from number 1 to 6:

  #!/bin/bash
  # drop_logs.sh - Drop initial logical logs
  
  for i in {1..6}
  do
     onparams -d -l $i -y    # drop logical log
  done

  Or performing the same task thru the SQL Admin API:

  echo "select task('drop log', number) from sysmaster:syslogfil where chunk = 1; | dbaccess -e sysadmin

The sysadmin database is required for the database scheduler and is created in the root dbspace (rootdbs) by default. We will move it to our newly created dbspace datadbs1 using a SQL Admin API function:

• execute function sysadmin:task("reset sysadmin", "datadbs1");

The IDS configuration file is located under $INFORMIXDIR/etc and is referenced by the environment variable $ONCONFIG. This file contains quite a number of parameters. We will discuss here only a few important ones for our penguin IDS instance.

Depending on the memory available in your machine, you might increase the size of the IDS buffer cache. The size is specified thru the buffers option of the BUFFERPOOL parameter. The default is 5000 buffers which is about 10 MB for a 2K pagesize bufferpool. In the following example we will increase the number of buffers to 20000 which is about 40 MB:

• BUFFERPOOL   size=2k,buffers=20000,lrus=8,lru_min_dirty=50,lru_max_dirty=60

In order to activate our temp dbspace we need to change this parameter:

• DBSPACETEMP   tempdbs001

If you have created additional temp dbspaces, you can add those additional temp dbspaces separated by a comma (i.e. tempdbs001, tempdbs002, tempdbs003…).

This flag instructs IDS to open the dbspace chunks in a special mode that allows bypassing the filesystem buffer cache when reading/writing data. This avoids the double buffering (IDS buffer cache and filesystem buffer cache) of data and reduces CPU usage. You will find more information here.

• DIRECT_IO   1

This parameter disables the logging of temporary tables which increases the throughput of your application. Please note that a rollback will not undo the work performed on temporary tables if TEMPTAB_NOLOG has been enabled.

• TEMPTAB_NOLOG   1

This parameter specifies the default locking mode for new tables. If no explicit locking mode is specified in the create table statement, DEF_TABLE_LOCKMODE will be used. If DEF_TABLE_LOCKMODE is not set, the default lock mode is page. The recommendation is to set it to row in order to decrease the possibility of lock conflicts and deadlocks.

• DEF_TABLE_LOCKMODE   row