What's new in performance for Informix, version 12.10

You can now assign a priority to each of your databases in the Auto Update Statistics (AUS) maintenance system. By default all databases have a medium priority. You can assign specific databases a high or a low priority to ensure that statistics for your most important databases are updated first. Statistics for low priority databases are updated after high and medium priority databases, if time and resources permit. For example, if you have a system with a production and a test database, you can assign the production database a high priority and the test database a low priority. You can also disable AUS for a database.

You can set AUS priorities in the IBM® OpenAdmin Tool (OAT) for Informix or by adding rows to the ph_threshold table in the sysadmin database.

Removing outstanding in-place alter operations improves performance and is a prerequisite for reverting to an earlier version of Informix. You can easily remove outstanding in-place alter operations for tables or fragments in IBM OpenAdmin Tool (OAT) for Informix or with the new table update_ipa or fragment update_ipa argument of the admin() or task() SQL administration command. Previously, you ran a dummy UPDATE statement to remove outstanding in-place alter operations.

You can remove outstanding in-place alter operations faster by including the parallel option with the table update_ipa or fragment update_ipa argument of the admin() or task() SQL administration command.

You can now compress, uncompress, and repack data or indexes faster by including the new parallel option with the table, fragment, or index argument of the admin() or task() SQL administration command.

You can configure the database server to adjust resources to improve performance:

• Increase the size of the buffer pool: Include the extendable=1 option in the BUFFERPOOL configuration parameter value to make the buffer pool extendable. Use the new memory field to specify the size of the buffer pool in units of memory, such as MB or GB, instead of units of pages. Buffer pools are now stored in the buffer pool segment of shared memory.
• Increase the number of logical log files: Set the AUTO_LLOG configuration parameter to 1, the name of the dbspace for logical logs, and optionally the maximum size of all logical log files.
• Increase the number of CPU and AIO virtual processors: Include the autotune=1 option in the VPCLASS configuration parameter values for the CPU and AIO virtual processor settings. Optionally include a maximum number of CPU VPs.
• Increase the size of the physical log size: Create a plogspace storage space to store the physical log by running the onspaces -c -P command. The plogspace is extendable by default.

AUTO_LLOG and its effect on logging

Checkpoints and the physical log

Setting the number of CPU VPs

Setting the number of AIO VPs

You can significantly increase the speed of loading or migrating large tables by temporarily preventing the database server from validating foreign-key referential constraints. You can disable the validation of constraints when you create constraints or change the mode of constraints to ENABLED or FILTERING.

• You include the NOVALIDATE keyword in an ALTER TABLE ADD CONSTRAINT statement or in a SET CONSTRAINTS ENABLED or SET CONSTRAINTS FILTERING statements.
• If you plan to run multiple ALTER TABLE ADD CONSTRAINT or SET CONSTRAINTS statements, run the SET ENVIRONMENT NOVALIDATE ON statement to disable the validation of foreign-key constraints during the current session.

The NOVALIDATE keyword prevents the database server from checking every row for referential integrity during ALTER TABLE ADD CONSTRAINT and SET CONSTRAINTS operations on foreign-key constraints. When those statements finish running, the database server automatically resumes referential-integrity enforcement of those constraints in subsequent DML operations.

Use this feature only on tables whose enabled foreign-key constraints are free of violations, or when the referential constraints can be validated after the tables are loaded or migrated to the target database.

When you run the ALTER TABLE ADD CONSTRAINT statement, some foreign-key constraints can be created faster if the table has a unique index or a primary-key constraint that is already defined on the columns in the foreign-key constraint.

Foreign-key constraints are not created faster, however, if the constraint key or index key includes columns of user-defined or opaque data types, including BOOLEAN and LVARCHAR, or if other restrictions are true for the foreign-key constraint or for the referenced table.

Previously such data types were converted with slow alter operations. In-place alter operations require less space than slow alter operations and make the table available to other sessions faster.

Private memory caches for CPU virtual processors now change size automatically as needed. You create private memory caches by setting the VP_MEMORY_CACHE_KB configuration parameter to the initial size of the caches. The size of a private memory cache increases and decreases automatically, depending on the needs of the associated CPU virtual processor. Previously, the size of private memory caches was limited to the value of the VP_MEMORY_CACHE_KB configuration parameter.

You can view the dependencies between blocking and waiting threads by running the onstat -g bth command. Run the onstat -g BTH command to display session and stack information for the blocking threads.

The sizes of memory caches are now twice the values that are set by the DS_POOLSIZE, PC_POOLSIZE, PLCY_POOLSIZE, or USRC_POOLSIZE configuration parameters. For example, if the DS_POOLSIZE configuration parameter is set to 127, 254 entries are allowed in the data distribution cache. If all entries in a cache are full, the cache size automatically grows by 10%. You can also dynamically increase cache sizes in memory.

You can view more information about caches and mutexes with onstat commands. You can view detailed information about memory caches by running the onstat –g cac command. The output can help you determine whether the cache is configured for optimal performance.

Cache access and management is optimized to provide faster performance for large systems that have many users. You can dynamically increase cache sizes in memory. You can view more information about caches and mutexes with onstat commands.

Data-distribution configuration

Monitor virtual processors with the onstat-g glo command