Informix Performance

Informix databases include many advanced concepts, such as parallel queries, multi-processor support, and virtual processors.

To increase performance for large sites on powerful multi-processor machines, Informix has changed some of the database default configurations, moving to a “cost based” optimization for its default query mode. This means that the query optimizer for Informix OnLine makes its decisions about optimization based purely on costs, without considering translation isolation mode. With this default, OnLine does not give preference to index scans (nested-loop joins and key base reads) over table scans (other join methods).

The behavior can be especially problematic in the case of benchmark testing. When the table is created, a mass insert of the records is performed on the database table. The indexes are created for the table, but the database has not yet gathered information about the distribution of data in the underlying table, or the usefulness of a particular index. The lack of statistical information within the database regarding the data in the tables results in possible poor decisions on the part of the query optimizer. The optimizer is unable to determine that an index exists that best matches the requirements of the query and will choose an alternate execution approach that will cause drastically poorer performance of the benchmark application.

Example

As an example of this problem, we will examine performance times from ACUCOBOL-GT’s “iobench” program.

The times between the two machines and databases that the benchmark were run on are comparable for all times except for the read operation. The read operation shows a significant performance degradation.

Isolating the problem

To isolate the source of the problem, you can insert into your COBOL program a call to Acu4GL’s utility program sql.acu. Before the Read benchmark test, we inserted the call:

CALL "sql.acu" USING SQL-COMMAND.

In the working-storage section, SQL-COMMAND is defined as follows:

77 SQL-COMMAND      pic x(75) value "SET EXPLAIN ON".

This call instructs the Informix database engine to print out the query optimizer’s execution plan to a file in the current directory called “sqexpain.out”. Here is a sample of the optimizer’s output:

QUERY:
------ 
SELECT *, rowid FROM idx1 WHERE idx_1_key >= ? ORDER
BY idx_1_key ASC

Estimated Cost: 2
Estimated # of Rows Returned: 3
Temporary Files Required For: Order By  

1) informix.idx1: SEQUENTIAL SCAN

    Filters: informix.idx1.idx_1_key >= '0000000900'

This output shows that the Informix query optimizer is performing a sequential scan on the table for each set of start/read operations. The optimizer is also making use of a temporary file during processing to sort the information in key order, as described by the “ORDER BY” clause. This clause is necessary to ensure that the COBOL application receives the records in the required sequence.

Problem resolutions

To correct the problems with the query optimizer, you can take one of several approaches:

1. Override the Informix database parameter in the database configuration files.
2. Override the new database parameter for a given user session.
3. Provide the query optimizer with more information so that it can choose a more efficient method of returning data.

Method 1: Configuration files

The Informix database engine reads a system configuration file each time it is started. Two configuration files apply: the file “onconfig.std” is used as a template for creating database configuration files when new databases are created; the “onconfig.<database>” file is the configuration file for a given database. In the database configuration file, locate the line

OPTCOMPIND    2    # To hint the optimizer

Modify this line to read

OPTCOMPIND    0    # To hint the optimizer

The OPTCOMPIND configuration parameter helps the optimizer choose an appropriate join method for your application. A setting of “0” indicates that when appropriate indexes exist, the optimizer chooses index scans (nested-loop joins), without considering the cost, over table scans (sort-merge joins or hash joins).

This new setting takes effect the next time you shut down and restart the ONLINE database engine. The altered setting is then applied to all operations with the query optimizer. To ensure that this change applies to newly created databases, you should modify the “onconfig.std” file.

Method 2: Altering a user session

You can modify how the query optimizer executes queries on an individual basis by setting a UNIX environment variable in the user’s environment. This environment variable should be set before executing your COBOL application. It cannot be set with the COBOL “SET CONFIGURATION” or “SET ENVIRONMENT” verbs. Use the syntax “setenv OPTCOMPIND 0” or “OPTCOMPIND=0; export OPTCOMPIND”, depending on which shell is being used.

Method 1 and method 2 resulted in the following “iobench” times:

We can verify the changes that were made by examining the output of the query optimizer. The above test resulted in these results:

QUERY:
------
SELECT *, rowid FROM idx1 WHERE idx_1_key >= ? ORDER BY idx_1_key ASC
Estimated Cost: 4
Estimated # of Rows Returned: 3
1) informix.idx1: INDEX PATH
    (1) Index Keys: idx_1_key 
        Lower Index Filter: informix.idx1.idx_1_key >= '0000000900'

Both method 1 and method 2 above are useful if you are processing new data. They have the drawback, however, that they permanently constrain the execution paths that the query optimizer has to choose from. To allow the query optimizer the greatest flexibility in working with data that exists day-to-day on your system, you will want to choose the next method.

Method 3: Provide the query optimizer with information

Database tables that are in use on a frequent basis can benefit from providing the query optimizer with more information. This allows the optimizer to work at its best in returning information from the database. You provide the optimizer with information of the tables by issuing the following SQL command:

UPDATE STATISTICS HIGH FOR TABLE <tablename>;

Executing this command updates the Informix system database catalog tables SYSTABLES, SYSCOLUMNS, SYSINDEXES, and SYSDISTRIB. The optimizer uses this data to determine the best execution path for queries. The database server does not update this statistical data automatically, however. Statistics are updated only when you issue an UPDATE STATISTICS statement.

Informix recommends that you run UPDATE STATISTICS in high mode for all columns that head an index. For the fastest execution time of the UPDATE STATISTICS statement, you must execute one UPDATE STATISTICS statement in the high mode for each such column. For each multi-column index, run UPDATE STATISTICS in low mode for all of its columns.

You may want to perform the UPDATE STATISTICS on the entire table periodically as shown above, instead of just on the indexes.

For more information on optimizing Informix performance and general maintenance issues, please refer to your Informix-OnLine documentation.