6. Common Questions about the Engine

6.15 More tuning suggestions for Online

Tom Hogarty (hogarty.pad@sni.de), whose address bounces, suggests:

Points from bottom up.

  1. Check that the Unix system is performing properly by using sar or other Unix version tools to monitor CPU and Disk activity. Look for bottlenecks i.e contention on a particular disk.
  2. Think about reconfiguring your Informix installation so that four logical units (rootdbs, physical log, logical logs and your database dbspace reside on physically seperate devices) BTW if you don't use raw devices in your system (not always the best) then check it out, RTFM, find out if your platform can take advantage of raw devices. Further refinement is possible by placing heavily used tables in a seperate dbspace/device. The broad picture is to have all unix resources working with you and not against each other.
  3. Monitor the state of your data by checking your extents from time to time, use tbcheck -ce to get warnings on extents that have overreached the 8 extent recommendation. Informix works very hard to run with tables spread over a lot of extents (we used to call it data fragmentation). Reorganise the data with unload/delete/load on the offending table and calculate the next extent size to suit your own systems needs.
  4. Further to point 3, if the system catalog tables (systables, syscolumns, etc.) have managed to run to more than 8 extents then a dbexport of your database is advisable. When the export runs it produces an .sql file to control the dbimport of the data you may edit this file to include the ALTER TABLE command (preferably near the beginning of the control file/.sql) for these system catalog tables so that you MODIFY NEXT SIZE nnn. i.e. ALTER TABLE systables MODIFY NEXT SIZE 100 ; The value of nnn for each affected table may be ascertained by running tbcheck -pT <dbname>:owner.<tabname> read how many pages have been used in the current schema, change that to Kb and make that the size of your next extent (minus the first extent , if you want to get picky).
  5. Various releases of dbexport have their own canny charactaristics and, in particular one feature (nudge,nudge) of my current version is that it aborts because of a swapping problem. If you live with such a version then su to root and change the obvious(?) ulimits to unlimited (i.e. ulimit -v unlimited). su to informix and run the dbexport from there.
  6. Another feature(?) of dbexport in some versions is the ommision of the NEXT EXTENT for tables and/or the LOCK MODE of tables, these can be preserved by running the two scripts which follow this posting.

I know that tuning/optimisation is/can be a complex subject and would not hope to cover the subject in any sufficient completeness here, but there is always the Fine Manual to refer to along with

     DATABASE TUNING...        by Dennis E. Shasha ISBN 0-13-205246-6
     SED & AWK                 by Dale Dougherty   ISBN 0-937175-59-5
     SYSTEM PERFORMANCE TUNING by Mike Loukides    ISBN 0-937175-60-9
     Informix OnLine Performance Tuning            ISBN 0-13-124322-5
                               by Elizabeth Suto Published by Prentice Hall

The first is general db tuning while the second is an invaluable guide to sed/awk which I find a great in managing the various tbstat/tbcheck outputs. and the latter is a guide to Unix system tuning. Elizabeth Suto's book has been described as "both excellent and concise". Joe Lumbley's well publicised Survival Guide may also be worth a look

I hope some of you can further refine and correct my outpouring so that the message is clear.. optimise resources, remove botlenecks and keep on reading the manual.

The scripts mentioned in part 6:

# Name    : lmode
# Author  : Tom Hogarty
# Program : Integrated script/awk program to read/format/output
#           the current (row level) lock modes in a database.
# Syntax  : lmode >>modes.sql
#           ( update the new db calling [isql|dbaccess] <dbname> modes )
#           call as user informix
echo " select 'ALTER TABLE ', owner, tabname
       from systables
       where locklevel = 'R' " >/tmp/tom
# edit the following line
[isql|dbaccess] <dbname> - </tmp/tom  |
awk '
    $0 ~ /ALTER TABLE/ { tabname=hoch1$3hoch2$4
                         printf " %12s %24s %34s \n",
                         "ALTER TABLE", tabname, " LOCK MODE (ROW) ; commit work ;"
    }  ' hoch1=\"  hoch2=\".

# Name    : nextx
# Author  : Tom Hogarty
# Program : Integrated script/awk program to read/format/output
#           the current next extent sizes in a database.
# Syntax  : nextx >>extents.sql
#           ( update the new db calling [isql|dbaccess] <dbname> extents )
#           call as user informix
echo " select 'EXTENTS',
         from systables
         where owner  <>  'informix'
         order by systables.owner,
                  systables.tabname;" >/tmp/tom

# edit the following line
[isql|dbaccess] <dbname> - </tmp/tom |
awk '
    $0 ~ /EXTENTS/ {  tabname=hoch1$2hoch2$3
                      printf "%12s %24s %18s %4s %15s\n",
                      "ALTER TABLE ",tabname," MODIFY NEXT SIZE ",
                       nextsize, "; COMMIT WORK ;"
    }  ' hoch1=\" hoch2=\".

On 11th Dec 1997 kagel@ns1.bloomberg.com (Art S. Kagel) wrote:-

BTW: I noticed your NETTYPE parameter for onipcshm in CPU VPs with 1 listener. If you increase this to 4 listeners there will be a listener in each CPU VP and you will 1) spread the load across the CPU VPs better and 2) be more responsive. The problem, I have found, is that the VPs with listeners take most of the work onto themselves which makes them too busy to listen but when they free up to listen they are not busy so they add the next job to their own queue instead of handing it off to another CPU VP. So 3 of your CPU VPs are in wait loops waiting for work most of the time while CPU VP1 is swampped. Check the CPU time distribution over the VPs in onstat -g glo with the current config and with 4 listeners and you can convince yourself. We started with 28 CPU VPs and 8 listeners as Informix consultants thought we should only need a few listeners. There were always some applications that were periodically paused waiting for service but 20 of the CPU VPs were never busy. We increased to 28 listeners and voila! No more delays and better VP utilization.

On 28th Oct 1997 kagel@ns1.bloomberg.com (Art S. Kagel) wrote:-

OK. Basically a NET VP (shown as class tli, soc, ipx, shm, str in the onstat -g glo report) is a Virtual Processor whose only job is to poll the various connections for new sessions and additional queries on existing sessions and pass the work off to CPU VPs as needed. The CPU VPs are doing actual query work for various sessions and in addition can handle exactly one poll thread to do listening itself. Basically it is best for networked connection types to have listeners in NET VPs and shared memory connection listeners in CPU VPs. Shared memory listeners in NET VPs will poll incessantly burning CPU time. Network listener threads in CPU VPs use more system call overhead. The difference has to do with the fact that the NET VPs have nothing better to do than monitor the connections and so can block waiting for network activity but they cannot block on shared memory operations and so have to poll in a loop. Inversely the CPU VPs cannot block waiting for network activity, they have better things to do with their time, and so have to poll network connections each time they have completed some user thread operation. Meanwhile the very fact that they are busy most of the time doing real work means that they can only poll shared memory connections occasionally which reduces the polling overhead for shared memory connections versus NET VPs.

6.16 DATETIME, INTERVAL and other tricks

Jack Parker has provided "a useful record of the sorts of problems you have to deal with when using DATETIME and INTERVAL types". It's a bit large to put in the main body of the FAQ, but here's a slightly edited copy of his introduction. The full, unabridged, document appears later as Appendix I.

A word about datetime/intervals:

As most of you know a DATETIME identifies a moment in time. For example: On Oct 11, 1987 at 11:05:32.32AM my wife and I exchanged wedding vows.

Say we have a recurring event - an appointment which occurs every month at a particular time - say on the 4th at 11:30. In this case we don't care about the year or the month, just the day and the hour.

A datetime datatype has the granularity to measure whatever you want. It can go from YEAR to FRACTIONs (5 decimal places) of a second - depending on your OS (syntax supports 5 places, but our OS only fills two of them).

There is a reasonable discussion of all this in Appendix J of TFM (4gl ref).

An Interval measures a time span. For example '2 hours ago'. While this is not a datetime concept, it uses much the same syntax and you can specify it in the same manner (YEARs through FRACTION). So 'how long have you been married' is an interval question. 'When did you get married' is a datetime question.

Who in their right mind cares? Anyone who wishes to mark a specific moment in time.

It makes no sense to 'add' two instants in time (or two datetimes) - what you want to do is take an instant in time (DATETIME) and add a time span (INTERVAL). "In two days I'm getting married". Sure this makes sense talking about it like this, but it's not 'intuitively obvious to the most casual observer' when working with the stuff. It's real easy to screw up what you put and where you put it. I probably wasted half of the time I spent on this trying to manipulate one datetime with respect to another datetime.


Like I said at the start, see Appendix I if you want to learn more.

6.17 Reading SE Audit Trails

From: lester@access.digex.net (Lester Knutsen)

Informix SE has a feature (this is not available in Informix Online) to create an audit trail of all adds, deletes and updates to a table. This can be used to trace which user is changing critical data. The procedures to create an audit file are simple and well documented. However, it is not well documented on how to read or use the audit file. The SQL syntax to create an audit trail is:

create audit for table_name in "pathname"

The full pathname is required for this command.

An Informix audit file is structured the same as the original data file (.dat file) with a header. One way to access an audit file is to convert it into an Informix database table. Then you can perform searches on the changes made to your data. The following steps will create an Informix database table out of an audit file. As an example, I will use the stores database and the orders table that come with Informix products. I recommend that you try this in a test environment first. To create an audit file on the orders table, type the following SQL command in dbaccess or isql.

create audit for orders in "/usr/lester/demo/orders.audit"

Every change to the orders table will be captured in this file. The next step is to create a way of loading this into a database and using the data.

First you need to create an SQL schema for the new audit table. The schema will be based on the table you are auditing with an additional five field header. You can use dbschema to do this by typing:
dbschema -s stores -t orders a_orders.sql
Edit the a_orders.sql script and add the additional fields for the audit header. The audit file includes the following five header fields:
a_type         char(2)   Type of record where aa = added, dd = deleted,
                         rr = before update image, ww = after update
a_time         integer   Integer internal time value.
a_process_id   smallint  Process ID that changed the record.
a_usr_id       smallint  User ID that changed the record.
a_rowid        integer   Original rowid.

You will also need to change the table name in the SQL script produced by dbschema to the name you want to call the audit table. I like to use the old table name with an "a_" prefix. The Index statements will also need to be changed. There must be one index on this table for the next step with bcheck to work. The old unique indexes should be removed because in the audit file the same record could appear for multiple changes. Change the index statements to use the new table you are creating. The following example is the script for the orders table:

create table a_orders   (
     a_type         char(2),
     a_time         integer,
     a_process_id   smallint,
     a_usr_id       smallint,
     a_rowid        integer,
     order_num      serial not null,
     order_date     date,
     customer_num   integer,
     ship_instruct  char(40),
     backlog        char(1),
     po_num         char(10),
     ship_date      date,
     ship_weight    decimal(8,2),
     ship_charge    money(6,2),
     paid_date      date  );
create index a_order_num_idx on a_orders ( order_num );
Create the table with the new name. This should produce an empty table ready to hold your audit file data.
Copy the audit file to replace the new table data file. This step will destroy any data in the a_orders.dat table so proceed with caution. Look up the pathname of the data file created for this table. One way is to perform the following select:
select dirpath from systables where tabname = "a_orders"

On my system dirpath was "a_order007". Change to the directory where the database files are located and copy the audit file to replace the a_order007.dat file.

cd stores.dbs
cp /usr/lester/demo/orders.audit a_order007.dat
After overwriting the ".dat" file the data and the index will be out of sync. Use bcheck, the Informix index repair tool to fix the index file. Type the following command.
bcheck a_order007

You now have an Informix table of your audit records and you can run SQL or build perform screens to see changes made to your data. Repeat steps 4 and 5 every time you need to update the table. The following is an example SQL statement. The results show an add (aa), change ( rr and ww) and a delete (dd) on the orders table.

select a_type, a_time, a_process_id, a_usr_id, a_rowid, order_num 
  from a_orders

6.18 Recovering broken (or missing) indexes in SE

When your table's .idx file magically disappears from your .dbs directory, firstly be assured that you're not alone in experiencing this phenomenon, and secondly here's how you can regain access to your data.

From Rudy Fernandes:

  1. Create another table, say junk, within the same database. junk must have an IDENTICAL structure to the table with the deleted idx. You will find a dat and idx file created.
  2. Copy/rename the junk table's idx file to the idx deleted.

    e.g cp junk___123.idx deleted105.idx

    (N.B the idx file's prefix will be identical to that of its dat)

  3. Run a 'bcheck -s' on the original table.

    e.g bcheck -s deleted105

  4. That's it!

6.19 What about Replication?

clema@informix.com (Clem Akins) writes:

It goes like this:

Data Replication, now called High-availability Data Replication, is the master/slave setup designed for disaster recovery.

Enterprise Replication (used to be Continuous Data Replication) is the far more powerful new capability. You can choose to replicate just columns, or selected rows, at different times, and deal with problems in any of several different ways.

On 8th Dec 1997 ddailey@informix.com (Doug Dailey) wrote:-

The problem is that you are wanting to do Enterprise Replication, not HDR. If your intention is to only replicate certain data sets from individual tables, then you best bet is to change your mode of thinking to ER.

There are skimpy release notes on this, SNMPDOC_7.2 and EREPDOC_7.2 in your $INFORMIXDIR/etc directory. But yes, you can replicate specific data from tables based on where filters setup. The hardware medium (platform) can be different and the disk layout and configuration of instance can be independant on one another. You will need Replication Manager 7.22.TC1 that runs on a NT 3.51 or greater Workstation in order to run replication although there is also an API that comes with 7.22.UC1 ESQL/C or by default with the 7.23.x engine and will be located in the $INFORMIXDIR/demo/esql directory.

Following is info on starting the SNMP and sub-agent for SCO:

SNMP Information

There are the following files that are used for the master agent on SCO. SCO is a PEER based protocol and INFORMIX does not ship a master agent with our product on this platform. These files are located in the /etc directory:

Snmpd {executable that starts the master agent}

lrwxrwxrwx 1 root root 32 May 22 1996 snmpd -> /opt/K/SCO/tcp/2.0.0Cl/etc/snmpd

snmpd.comm {needs the following entry}

hskgsk96 WRITE

snmpd.conf {needs the following entry}

descr=SCO TCP/IP Runtime Release 2.0.0 objid=SCO. contact= Doug Dailey {this is just what I used} location= Lenexa {same thing} smux_reserved=system smux_reserved=snmp smux_reserved=smux

snmpd.peers {needs the following entry}

"cdragt" 0.0 ""

snmpd.trap {not necessary, used for event trapping}

Notes on SCO and starting the agent:

It is not necessary to set SR_AGT_CONF_DIR if you are using the master agent of the OS. This is the basic rule for all platforms using SNMP master supplied by the OS vendor.

Start the master agent with the following syntax:

/etc/snmpd log_level 6 & {0-6, 6 being the most detail. Do a man on snmpd on SCO to get more detail}

The log file will be created in /usr/adm/snmpd.log.

Start the cdr sub-agent the same way you would on any other system:

$INFORMIXDIR/bin/cdragt -t 3 -f /tmp/cdr.out &

Here is also some info on a base test in getting this up and running:

Below are steps for setting up for replication on the UNIX side for the SUN Solaris platform. You will have to substitue the portions regarding the master and sub-agent with the above info regarding SCO:

I. Installation:

II. Configuration:

env for test1
setenv INFORMIXDIR /testing/ddailey
setenv INFORMIXSERVER test1tcp
setenv PATH ${INFORMIXDIR}/bin:/tsgoldopt/bin:${PATH}
setenv ONCONFIG onconfig.test1
setenv SR_AGT_CONF_DIR $INFORMIXDIR/snmp/snmpr

env for test2
setenv INFORMIXDIR /testing/ddailey
setenv INFORMIXSERVER test2tcp
setenv PATH ${INFORMIXDIR}/bin:/tsgoldopt/bin:${PATH}
setenv ONCONFIG onconfig.test2
setenv SR_AGT_CONF_DIR $INFORMIXDIR/snmp/snmpr

test1   group   -       -       i=1
test1tcp     ontlitcp        sunset             test1tcp g=test1

test2   group   -       -       i=2
test2tcp     ontlitcp        sunset             test2tcp g=test2

ROOTNAME        rootdbs         # Root dbspace name
ROOTPATH        /testing/ddailey/test1
ROOTOFFSET      0               # Offset of root dbspace into device (Kbytes)
ROOTSIZE        20000           # Size of root dbspace (Kbytes)

SERVERNUM       60              # Unique id corresponding to a OnLine instance
DBSERVERNAME    test1tcp                # Name of default database server
DBSERVERALIASES                 # List of alternate dbservernames
NETTYPE                         # Configure poll thread(s) for nettype
RESIDENT        0               # Forced residency flag (Yes = 1, No = 0)

MULTIPROCESSOR  0               # 0 for single-processor, 1 for
NUMCPUVPS       1               # Number of user (cpu) vps
SINGLE_CPU_VP   0               # If non-zero, limit number of cpu vps to one

ROOTNAME        rootdbs         # Root dbspace name
ROOTPATH        /testing/ddailey/test2
ROOTOFFSET      0               # Offset of root dbspace into device (Kbytes)
ROOTSIZE        20000           # Size of root dbspace (Kbytes)

SERVERNUM       61              # Unique id corresponding to a OnLine instance
DBSERVERNAME    test2tcp                # Name of default database server
DBSERVERALIASES                 # List of alternate dbservernames
NETTYPE                         # Configure poll thread(s) for nettype
RESIDENT        0               # Forced residency flag (Yes = 1, No = 0)

MULTIPROCESSOR  0               # 0 for single-processor, 1 for
NUMCPUVPS       1               # Number of user (cpu) vps
SINGLE_CPU_VP   0               # If non-zero, limit number of cpu vps to one

IV. Starting the Agents:

1. As root, go to $INFORMIXDIR/bin (in ksh)

./snmpdm -apall >/tmp/snmp.out 2>&1 &

2. As informix, go to $INFORMIXDIR/bin (in ksh)

./cdragt -t 5 -f /tmp/cdr.out 2>&1 &

V. Installing Rep Manager

1. Installed as Administrator and used at least the NT 3.51 version of 7.22.TC1 rep manager

2. REPHOSTS file:

test1 group - - i=1 test1tcp ontlitcp sunset test1tcp g=test1 test2 group - - i=2 test2tcp ontlitcp sunset test2tcp g=test2

VI. Test

1. create table on instance A : create table test (col1 char(1) primary key, col2 char(5)); create table on instnace B : create table test (col1 char(1) primary key, col2 char(5)); inserted 4 rows into table test on instance A prior to defining replicate 2. databases created in rootdbs with logging 3. synchronized the two servers through replication manager 4. defined a replicate with the following properties:

update anywhere transaction scope ris and ats spooling select * from test where test = "cc" ignore conflict resolution continuous replication test1 and test2 instances all participants defined the same? NO

5. update test set col2 = "cc"; {yes to all} /*updates not converted to inserts on instance B.*/ /*subsequent updates only propagated on newly inserted rows*/

6.20 How well does Online scale?

Nils.Myklebust@ccmail.telemax.no writes:
:   smb@oasis.icl.co.uk (Stuart Britt) writes:
:  Does anyone have any info on the following problem?  We are using Informix
:  version 7.10.UC1 for Intel UnixWare 2.01 on single processor and SMP
:  machines.
:  Using Informix on 1 processor we achieve good performance.  If we switch to
:  2 processors, then our performance actually degrades.  We have been given
:  some advice on tuning the system for dual processor machines and we have 
:  tried different settings for virtual processors and processor affinity.
:  We managed to get an improvement, but the best scaling we can get is
:  about 1.25.
:  If we move to a quad processor system the problem goes away and we 
:  approach expected performance levels.
:  Has anyone else experienced this?

This conforms exactly to what Informix said at the user conference this summer. According to that you can't rely expect any performance increase until you have 4 processors. This is due to extra resources required to do the swiching between virtual processors takes up more time than you save by an adittional processor.

Not long ago someone also said that on SCO Unix even a 4 processor configuration didn't give that much increased performance. As we also use Intel hardware and plan to upgrade to multiple processors it would be very interesting for us to here what your performance increase is.