System Administration Certification exam 919 for Informix 11.70 prep, Part 4: Performance tuning

Tune IBM Informix® database server and its different subsystems for optimum performance. After an overview, follow along with examples on how to look at the database server and its subsystems. Learn about important database optimization elements, including checkpoints, recovery, physical logging, logical logging, asynchronous I/O VP, network parameters, disk resources, CPU VP resources, PDQ, memory grant manager, scan threads, index creation, statistics maintenance, and self tuning. Use this tutorial, the fourth in a series of eight tutorials, to help prepare for Part 4 of the Informix 11.70 exam 919.

Share:

Jay B Peterson (jaybp@us.ibm.com), Staff Software Engineer, I.B.M.

Photo of Jay PetersonJay Peterson is a front-line technical support analyst. He has been working in Informix support since 1998, specializing in performance-related issues. Jay is the author of many technical articles about Informix products.



Frank Arias (farias@us.ibm.com), Advanced Support Engineer, I.B.M.

Photo of Frank AriasFrank Arias works at IBM as an advanced support engineer in Informix Down System Diagnostics.



01 March 2012

Also available in Chinese Portuguese

Before you start

Learn what to expect from this tutorial, and how to get the most out of it.

About this series

Thinking about seeking certification on System Administration for Informix version 11.70 (Exam 919)? If so, you've landed in the right spot to get started. This series of Informix certification preparation tutorials covers all the topics you'll need to understand before you read that first exam question. Even if you're not seeking certification right away, this set of tutorials is a great place to start learning what's new in Informix 11.70.

About this tutorial

This tutorial introduces and describes the subsystems within Informix 11.70, with emphasis on recent changes. In this tutorial, you will learn about the following to optimize your Informix server.

  • Checkpoints
  • Recovery
  • Physical logging
  • Logical logging
  • Asynchronous IO VP
  • Network parameters
  • Disk resources
  • CPU VP resources
  • PDQ and the memory grant manager
  • Scan threads
  • Index creation
  • Statistics maintenance
  • Server self tuning

This tutorial is the fourth in a series of eight tutorials to help you prepare for the System Administration for Informix V11.70 Certification (Exam 919). The material in this tutorial primarily covers the objectives in Section 4 of the test, which is entitled Performance tuning.

If you haven't already done so, consider downloading and installing a copy of IBM Informix 11.70. Installing Informix will help you understand many of the concepts that are tested on the System Administration for IBM Informix V11.70 Certification exam.

Objectives

After completing this tutorial, you should be able to:

  • Understand operating system resource considerations
  • Use Informix features to optimize performance

Prerequisites

To understand the material presented in this tutorial you must be familiar with the following:

  • The Informix environment (configuration file and parameters, installation and administration)
  • Database server commands (onstat, onmode, oncheck, dbschema)
  • Informix concepts and terminology (dbspaces, chunks, physical log, logical logs, checkpoints, and so on)

Complete these steps to prepare for this tutorial:

  1. Download and install a copy of IBM Informix 11.70.
  2. Create a server instance.
  3. Create the stores_demo database using the command %dbaccessdemo7 -log -nots

You should be able to use your instance and the stores_demo database as the starting point for testing concepts presented in this tutorial.


Tuning performance on Informix

Performance tuning overview

Performance tuning is an elaborate process. To approach performance tuning, keep in mind that seemingly unrelated things could affect each other. When tuning for improved performance, make sure you document the changes and performance of sample runs so you can always go back if you need to back out something that has made a negative impact on performance.

How to get started

One of the big problems with performance tuning is that there are so many subsystems that it's difficult to know that something needs tuning. Previous experience and experimentation is helpful for developing an intuition of what is operating optimally and what might need a little tweaking. Experiment more to see if your intuition predicts changes that improve performance on your server.


Affecting system operations with operating system resources

The performance of your database server application depends on the following key factors:

  • Hardware resources
  • Operating system configuration
  • Network configuration and traffic
  • Memory management

You must consider these factors when you attempt to identify performance problems or make adjustments to your system.

Hardware resources

Hardware components include the following.

  • CPU
  • Disk I/O subsystems
  • Physical memory

Operating system configuration

The database server depends on the operating system to provide low-level access for devices, to process scheduling, to interprocess communication, and to offer other vital services.

The configuration of your operating system has a direct impact on how well the database server performs. The operating system kernel uses a significant amount of physical memory that the database server or other applications cannot use. Furthermore, you must reserve adequate kernel resources for the database server.

Besides tuning the kernel, there are soft limits on various resources, such as stack size, number of file descriptors, and so on. These can be examined and adjusted using the ulimit command. The ulimit command is not covered in this tutorial.

The temp directory is a common repository for many common applications (such as vi, ed, or the kernel). Temp may need to be adjusted accordingly, depending on the needs of the users and applications running on the operating system.

Disk and controller I/O bottlenecks

The database server might need to perform I/O operations on more than one object (such as a table and a logical log file) located on the same disk. Contention between busy processes with high I/O demands can slow them down. It is important to monitor disk usage. In such a scenario, you can see performance gains by load-balancing (moving the tables to another disk).

Network configuration and traffic

Applications that depend on a network for communication with the database server and systems that rely on data replication to maintain high availability are subject to the performance constraints of that network. Data transfers over a network are typically slower than data transfers from a disk. Network delays can have a significant impact on the performance of the database server and other application programs that run on the host computer.

Memory management

The operating system needs to have a page in memory to do any operations on that page. If the operating system needs to allocate memory for use by a process, it first will try to scavenge any unused pages within memory that it can find. But if no free pages exist, the memory-management system then has to choose pages that other processes are still using. The operating system tries to determine the pages that seem least likely to be needed in the short run that can be replaced by the new pages. This process of locating such pages that can be displaced is called a page scan. A page scan can increase CPU utilization.

Most memory-management systems use a least-recently-used algorithm to determine which pages can be replaced in memory. Once identified, these pages are copied out to disk. The memory is then freed for use by other processes. When a page is written out to disk, it is written to a specific area called swap space or swap area, where it is available for reading back into memory. This space is typically a dedicated disk or disk partition. The process is called paging. Paging uses I/O resources and CPU cycles to do its work.

At some point, the page images that were paged out must be copied back in for use by the processes that need them. And so the cycle starts again with other older pages (pages that have not been used relatively recently). If there is enough paging back and forth, the operating system might reach a point at which the kernel is almost totally occupied with copying pages in and out. This state is called thrashing. If the system is thrashing, all useful work comes to a halt.

In order to prevent thrashing, some operating system's memory-management algorithms actually scale coarser at a certain threshold. Instead of looking for older pages, it swaps out all pages (to swap space on disk) for a particular process. This process is called swapping.

Each and every process that is swapped out of memory must eventually be swapped back in. The disk I/O to the swap device dramatically increases the time required to switch between processes, because each context switch must read (into memory) all pages involved with the process. Performance is limited by the speed at which those pages can be transferred. A system that is swapping is severely overloaded, and throughput is impaired.

Many operating systems have commands that provide information about paging and swapping activity. Important statistics reported include the following:

  • Number of pages paged or swapped out of memory
  • Number of page scans (this is an early indicator that memory utilization is becoming a bottleneck)
  • Number of pages paged or swapped into memory (this number is often not as reliable an indicator of a problem as paging out, because paging in includes initial loading of processes and loading of paged out pages when active processes terminate and memory is freed)

Configuring the server

This section emphasizes recent server configuration enhancements that individually affect performance and are not covered later in broader topics.

VPCLASS

Because Informix is scalable, it can be tuned to accommodate large instances. The main parameter for tuning virtual processors (VPs) is the VPCLASS parameter.

The VPCLASS parameter replaced the following deprecated parameters:

  • NUMCPUVPS
  • SINGLE_CPU_VP
  • AFF_SPROC
  • AFF_NPROCS
  • NOAGE
  • NUMAIOVPS

VPCLASS syntax

Listing 1 shows the syntax of the VPCLASS parameter.

Listing 1. VPCLASS syntax
VPCLASS classname,options

The classname in the VPCLASS parameter provides the name of the virtual-processor class that you are configuring. The name is not case sensitive.

You can define new virtual-processor classes for user-defined routines or DataBlade® modules, or you can set values for a predefined virtual-processor class. The class names in Table 1 are predefined.

Table 1. Predefined VPCLASS class names
admlioshmadt
mscsoccpu ntk
strjvpopttli
kioaiopioencrypt

The classname variable is required. Unlike most configuration parameters, VPCLASS has several option fields that can appear in any order, separated by commas. You cannot use any white space in the fields. VPCLASS has the following optional secondary parameters:

  • num=num_VPs
  • max=max_VPs
  • aff=affinity
  • noage
  • noyield

Each virtual processor is instantiated as an operating system process. You can therefore use VPCLASS to dedicate a class of activities to one oninit process. For more information, see Resources.

Configuring for multiple CPUs

As mentioned, Informix is scalable and can be configured to accommodate and take advantage of multiple CPU machines. The number of CPU VPs can be figured, as shown in Listing 2.

Listing 2. Configuring for 3 CPU VPs
VPCLASS  cpu,num=3

Java VPs

The JVP option of the VPCLASS configuration parameter sets the number of Java virtual processors. This parameter is required when you use the IBM Informix JDBC Driver. On UNIX, you must define multiple Java virtual processors to execute Java user-defined routines in parallel.

Network VPs

For VP classes tli, shm, str, and soc, you must set the NETTYPE configuration parameter's VP_class field to NET.

For example, you might set the VPCLASS parameter as shown in Listing 3.

Listing 3. VPCLASS parameters to use NET
VPCLASS  shm,num=1
VPCLASS  tli,num=1

The NETTYPE parameter should be set as shown in Listing 4.

Listing 4. NETTYPE parameters with NET
NETTYPE  ipcshm,1,100,NET
NETTYPE  tlitcp,1,100,NET

Configuring shared memory parameters

Shared memory allocations to the Informix database server depend on several configuration parameters, as shown in Table 2.

Table 2. Shared memory parameters
ParameterDescription
EXTSHMADDSpecifies the size of an added extension segment
SHMADDSpecifies the increment of memory that is added when the database server requests more memory
SHMBASESpecifies the shared-memory base address and is computer dependent. The value depends on the platform and whether the processor is 32 bit or 64 bit. For information on which SHMBASE value to use, see the computer notes.
SHMTOTALSpecifies the maximum amount of memory the database server is allowed to use
SHMVIRTSIZESpecifies the size of the first piece of memory that the database server attaches
BUFFERPOOLConfigures shared-memory page cache. Number of buffers per (page size) pool, page size, and LRU parameters

Carefully consider shared memory configuration parameters. The major performance considerations are maximal page caching (BUFFERPOOL) and adequate virtual memory allocation (SHMVIRTSIZE). If initial virtual memory is inadequate for long-term processing, dynamic addition of virtual segments (SHMADD) can create excessive numbers of virtual segments during processing, which can adversely affect performance.

DIRECT_IO

The DIRECT_IO parameter enables direct I/O and concurrent I/O for performance enhancements with chunks defined using cooked files. I/O using cooked files is generally slower than raw devices due to an extra I/O layer and buffering used for read and write on the cooked files. Informix has no control over this operating system subsystem. Certain operating system platforms, however, support direct I/O, which bypasses the I/O layer and buffering for cooked file chunks. Performance for cooked files can approach the performance of raw devices used for dbspace chunks.

Direct I/O can be used only for regular dbspace chunks. It is not used for temporary dbspaces. The file system and operating system must support direct I/O for the page size. Direct I/O is not supported with raw devices. Kernel asynchronous I/O (KAIO) is the preferred method of I/O for chunks that are placed on raw devices.

Concurrent I/O, currently supported on AIX®, adds the concurrent feature on top of direct I/O. Concurrent I/O allows multiple concurrent reads and writes to a file. The performance enhancement is most noticeable with I/O to single chunks striped across multiple disks.

To determine whether Informix is using direct or concurrent I/O for a chunk, monitor the fifth position of the flags field of onstat -d, as shown in Table 3.

Table 3. DIRECT_IO configuration
DIRECT_IO settingEffectonstat -d flag
0Direct I/O off-
1Direct I/O onD
2Direct and concurrent I/O onC

In some operating systems that enable direct I/O, implementation uses KAIO. If direct I/O is enabled, the database server tries to do the work with KAIO. The number of AIO virtual processors may be reduced if KAIO is enabled. This assumes that KAIO is turned on (KAIOOFF is not set in the environment).

Windows does not support the ONCONFIG parameter. DIRECT_IO as direct I/O is turned on by default on the Windows platform.

SQLTRACE

You can configure the SQLTRACE configuration parameter in a variety of ways to collect performance data for individual queries. Use SQLTRACE to define the scope of the tracing. The default settings in the configuration file are shown in Listing 5.

Listing 5. Default settings
SQLTRACE level=low,ntraces=1000,size=2,mode=global

The trace data are stored in sysmaster tables and visible with the onstat -g his command. For example, you can view query plan cost estimates, number of rows returned, and profile data. You can also enable and disable sqltracing using task() and admin() functions from the sysadmin database.

SQL tracing is particularly useful for studying individual queries that execute within applications that run many queries.

BATCHEDREAD_TABLE

Informix sometimes performs light scans on large data tables, reading many data pages at once and bypassing the buffer pool. You can turn on light scans for compressed tables, tables with rows that are larger than a page, and tables with any data, including VARCHAR, LVARCHAR, and NVARCHAR types by enabling BATCHEDREAD_TABLE in the configuration file. This parameter is automatically enabled.

The light scans bypass the buffer pool and provide a performance improvement for some queries. Monitor light scan activity using the onstat -g scn command.

BATCHEDREAD_INDEX

Configure BATCHEDREAD_INDEX to direct the server to fetch a set of keys from an index buffer when appropriate, thereby decreasing buffer reads.


Optimizing communication performance

Tuning a named server connection

As a database administrator, you want to tune network parameters to optimize communications performance for the various server connections. Connections are made to the server or alias, and these are tuned independently. The DBSERVERNAME and DBSERVERALIASES server configuration parameters each correspond to a line in the sqlhosts file. The protocol (second field) for that line maps the connection to a NETTYPE entry by matching the protocols. Configure the NETTYPE entries to tune. The NETTYPE definition is shown in Listing 6.

Listing 6. NETTYPE definition
NETTYPE protocol,poll_threads,conn_per_thread,VP_class

The NETTYPE configuration designates the virtual-processor class (VP_class) on which the poll threads will do their work. Poll threads process incoming connection requests and pass the requests to listener threads, which accept the client connection and start an sqlexec thread. Poll threads can run either inline on CPU virtual processors (VPs) or on NET (network) VPs. Poll threads generally run more efficiently on CPU VPs for single-CPU machines. On a multiprocessor computer with a large number of remote clients, however, running them on a NET VP might get better throughput.

You can tune poll_threads and conn_per_thread for the size of the connection load you expect the database server to handle. One poll thread is usually sufficient for smaller systems. For systems that can have 200 or more concurrent network users, better performance might result from adding more poll threads. With high user load, you might need to experiment to determine the optimal configuration of poll threads (whether inline or running on NET VPs).

Note that there is one network virtual processor created for each poll thread. The NETTYPE in Listing 7 configures 3 poll threads on 3 soc virtual processors for a total of 600 user connections.

Listing 7. Example NET VP definition
NETTYPE soctcp,3,200,NET

The database server can add resources for additional network connections as needed. Listing 7 does not limit the sockets connections to 600. However, in the case of shared memory connections, the number configured is a hard limit.

When NETTYPE is not configured, the default is to run the poll thread for the protocol used for the DBSERVERNAME server in-line (CPU VP) and the DBSERVERALIASES on network virtual processors spawned for the designated protocol type. Because one poll thread runs on one virtual processor, Informix spawns additional network virtual processors if there are more poll threads configured than CPU virtual processors.

If you expect a large number of connections, and your operating system supports fast polling, enable FASTPOLL for fast polling your network and optimal connection performance, as shown in Listing 8.

Listing 8. FASTPOLL command
FASTPOLL  1

Allocating additional listener threads

Listener threads authenticate users, establish connections to the database server, and start sqlexec threads. You can add listener threads if you feel that the server is not handling connection requests satisfactorily. A listener thread is dedicated to the port on which it runs. You will need an additional DBSERVERALIASES parameter to specify a dbservername for each additional port and a corresponding line in the sqlhosts file with the correct protocol and a unique port.

Adding another network-interface card

There might be a situation when a network-interface card for the host computer cannot handle the desired connection throughput. Or, there might be a need to connect the database server to more than one network. In such cases, you can add a network-interface card.

To support multiple network-interface cards, you must assign each card a unique hostname or network address. After you add a network card, add an additional listener thread to run on that card. The database server will use the hostname entry in the sqlhosts file to determine which card to use.

Network buffers

The database server attempts to optimize connection memory use. Client requests prompt network memory buffer allocation from the global memory pool, and when the buffers are freed, they remain in a pool. This prevents unnecessary reallocation. This free buffer pool is available to several protocols, including SOCTCP, IPCSTR, and TLITCP. There is a mechanism for returning buffers from the free pool to the global memory pool to avoid needless allocation of memory when use is down. The server calculates a free buffer threshold, and when that number is exceeded, the buffers are returned to the global pool.

The database server uses the formula in Listing 9 to calculate the threshold for free buffers in the network buffer pool.

Listing 9. Formula for threshold for free buffers
free network buffers threshold = 100 + (0.7 * number_connections)

The number_connections value should be the third field of the NETTYPE parameter.

The database server sums the calculated number for the various protocols that use the free buffer pool. That sum is compared to the number of buffers in the pool. If the buffer number in the free pool is larger than the summed thresholds, then buffers are returned to the global pool.

Sizing network buffers to accommodate a typical request can improve CPU utilization by eliminating the need to break up a request into multiple messages. Proceed with caution, however, because the database server dynamically allocates network buffers of the indicated sizes for all active connections. If too large a size is configured, then a large amount of memory can be consumed.

The database administrator can control the size of each buffer using either of the following methods, which are explained in detail.

  • IFX_NETBUF_SIZE environment variable, and b (client buffer size) option in the sqlhosts file or registry
  • IFX_NETBUF_PVTPOOL_SIZE environment variable

IFX_NETBUF_SIZE environment variable

The IFX_NETBUF_SIZE environment variable specifies the size of each network buffer in the common network buffer pool and the private network buffer pool. The default buffer size is 4KB.

Using IFX_NETBUF_SIZE to configure a larger buffer size reduces the amount of overhead required to receive each packet. If you know that clients send packets greater than 4KB in size, then configure IFX_NETBUF_SIZE to the average packet size. Clients might send large packets during any of the following situations:

  • Table loads
  • Rowsize greater than 4KB
  • Sending simple large objects

The b option for sqlhosts corresponds to the IFX_NETBUF_SIZE parameter. The value for the sqlhosts option should typically match the value for IFX_NETBUF_SIZE.

IFX_NETBUF_PVTPOOL_SIZE environment variable

The database server provides a private network buffer pool for each session that uses SOCTCP, IPCSTR, or TLITCP network connections. The buffers in these pools are allocated from the pools mentioned earlier: the global pool and the free pool.

In an environment in which many connections and sessions are constantly active, these private network buffers provide the following advantages:

  • Less contention for the common network buffer pool
  • CPU resources can be conserved, because it is not necessary to allocate and deallocate network buffers to and from the common network buffer pool for each network transfer. These buffers will be statically available.

The size of the private network pool for each session is specified by the IFX_NETBUF_PVTPOOL_SIZE environment variable. The default size is 1 buffer.

Onstat commands to monitor and tune network buffer usage

Use the onstat options in Table 4 to monitor the network buffer usage.

Table 4. Monitoring network buffers with onstat
OptionOutput fieldField description
onstat -g ntuq-pvtCurrent number and highest number of buffers that are free in the private pool for this session
onstat -g ntmq-exceedsNumber of times the free buffer threshold was exceeded

The onstat -g ntu option displays the format for the q-pvt output field, as shown in Listing 10.

Listing 10. q-pvt output field
current  number  /  highest  number

If the number of free buffers (value in q-pvt field) is consistently 0, you can perform one of the following actions:

  • Use the environment variable IFX_NETBUF_SIZE to increase the size of each buffer
  • Use the environment variable IFX_NETBUF_PVTPOOL_SIZE to increase the number of buffers

The q-exceeds field indicates the number of times that the threshold for the shared network free-buffer pool was exceeded. When this threshold is exceeded, the database server returns the unused network buffers (over this threshold) to the global memory pool. Optimally, this value should always be either 0 or a low number. This is an indicator that the server is not allocating or de-allocating network buffers.

You can use the onstat command in Listing 11 to see the network buffer size.

Listing 11. Onstat command for network buffer size
onstat  -g  afr  global  |  grep  net

The size field in the output shows the network buffer size in bytes.


Updating statistics

The Informix server uses a cost-based optimizer. When the optimizer determines the query plan, it assigns a cost to each possible plan and then chooses the plan with the lowest cost. Some of the factors that the optimizer uses to determine the cost of each query plan include the following:

  • The number of I/O requests that are associated with each file system access
  • The CPU work that is required to determine which rows meet the query predicate
  • The resources that are required to sort or group the data
  • The amount of memory (specified by the DS_TOTAL_MEMORY and DS_MAX_QUERIES parameters) available for the query

To calculate the cost of each possible query plan, the optimizer does the following:

  • Uses a set of statistics that describes the nature and physical characteristics of the table data and indexes
  • Examines the query filters
  • Examines the indexes that could be used in the plan
  • Analyzes the cost of moving data to perform joins locally or remotely for distributed queries

The UPDATE STATISTICS statement updates statistics in the system catalogs. The optimizer uses those statistics to determine the lowest-cost query plan. Several types of statistics data are collected and at various resolutions, depending on the required statistics precision and time constraints for collecting the data.

To ensure that the optimizer selects a query plan that best reflects the current state of your tables, run UPDATE STATISTICS at regular intervals when the statistics are not generated automatically for tables that are dynamic (in other words, the data is changing).

Table 5 summarizes when to run different UPDATE STATISTICS statements. If you have many tables, you can write a script to generate these UPDATE STATISTICS statements.

Table 5. Guidelines for running UPDATE STATISTICS
Statistics statementTargetUse
UPDATE STATISTICS LOW DROP DISTRIBUTIONSIndexes, drop distributions
  • Number of rows has changed significantly
  • After migration from previous version of database server
UPDATE STATISTICS LOWIndexes
  • For all columns that are not the leading column of any index
  • (All columns in multicolumn index) For queries that have a multicolumn index defined on join columns or filter column
UPDATE STATISTICS MEDIUM DISTRIBUTIONS ONLYData distributions
  • Queries that have non-indexed join columns or filter columns
UPDATE STATISTICS HIGHData distributions and indexes
  • Table or leading column in an index for queries that have indexed join columns or filter columns
  • (First differing column in multicolumn index) For queries that have a multicolumn index defined on join columns or filter columns
  • Queries that have many small tables (fit into one extent)

Improved statistics maintenance

In earlier versions, update statistics were performed manually with SQL statements. The statement you executed was completed in its entirety. Informix now has built-in mechanisms to prevent the server from doing unnecessary distribution calculations when the data have not changed significantly. Furthermore, some update statistics statements are now executed with DDL statements. Update statistics can also be fully automated, launching behind the scenes using the scheduler.

Administrators typically run update statistics according to the guidelines on a defined interval. Data distributions are often re-calculated when the data have not changed appreciably. You can now configure the server to calculate distributions during a standard UPDATE STATISTICS statement only after a threshold change in table data. You can also have distributions recalculated on a fragment basis so that only the subset of table fragments that met the threshold change criterion would have distributions refreshed.

The STATCHANGE configuration parameter sets the percent of stale statistics that trigger distribution recalculation. The AUTO_STAT_MODE configuration parameter enables server-wide use of STATCHANGE for controlling the update statistics. The configuration in Listing 12 enables STATCHANGE and sets the level to 12 percent.

Listing 12. STATCHANGE configuration
AUTO_STAT_MODE  1
STATCHANGE  12

When automatic mode is enabled, UPDATE STATISTICS statements use the STATCHANGE value to identify table, index, or fragment distributions whose statistics are missing or stale, and update only these. AUTO_STAT_MODE can be changed dynamically with the onmode command.

You can use the sql SET ENVIRONMENT statement to set AUTO_STAT_MODE and STATCHANGE at the session level. Session settings override the configuration value. For even finer granularity, set STATCHANGE in CREATE TABLE or ALTER TABLE statements in the new statistics option clause. These override all other settings.

Selective update statistics on table fragments are accomplished with another option to the CREATE TABLE and ALTER TABLE statements. The STATLEVEL attribute is accompanied by any of the following options:

AUTO
Enables fragment-level statistics and automatic determination if a fragment should have statistics refreshed. This mode is available for fragmentation by expression, list, or interval. A table would have to have more than one million rows. AUTO is the default.
FRAGMENT
Enables fragment-level statistics
TABLE:
Designates that statistics will be collected on a table basis

The UPDATE STATISTICS statement itself has an AUTO or FORCE option. The AUTO option forces the update statistics to use the governing STATCHANGE value to decide which statistics to refresh. The FORCE option tells update statistics to use the classic mode of refreshing all statistics.

Update statistics is now run automatically with certain DDL statements, including the following:

  • ALTER FRAGMENT ATTACH
  • ALTER FRAGMENT DETACH
  • ALTER TABLE ADD CONSTRAINT
  • ALTER TABLE MODIFY
  • CREATE INDEX

With or without the ONLINE keyword, CREATE INDEX generates the following statistics automatically:

  • Index-level statistics, equivalent to the statistics gathered in the UPDATE STATISTICS operation in LOW mode, for all types of indexes, including b-tree, virtual index interface, and functional indexes
  • Column-distribution statistics, equivalent to the distribution generated in the UPDATE STATISTICS operation in MEDIUM mode, for a non-opaque leading indexed column of an ordinary B-tree index

Specifying the SAMPLING SIZE

You now have the option of controlling the sampling size used for UPDATE STATISTICS LOW on b-tree indexes of large tables. Enable sampling with USTLOW_SAMPLE set in the configuration file or with the SET ENVIRONMENT statement. The default statistics low command touches each index leaf page in sequence. This is very time consuming. Indexes with 100,000 leaf pages or more will be sampled when USTLOW_SAMPLE is set to 1. The savings in time can be substantial, but the query optimizer will have less data with which to work.

As with many of these parameters, you can use the onmode command to change USTLOW_SAMPLE while the engine is online.

The SAMPLING SIZE keywords are used with UPDATE STATISTICS MEDIUM to specify the minimum number of rows to sample when calculating column distribution statistics. The number of rows sampled will be the larger of the following two values:

  • The value specified
  • The number of rows required to fill the percentage of rows in each bin and to also fulfill the confidence level

The default percentage of rows in each bin will be 2.5%, and the minimum confidence will be 0.80. For example, the statement in Listing 13 calculates statistics for three columns of the customer table. Index information will not be updated. At least 300 rows will be sampled, but the actual size of the sample might be larger than 300. However, if more rows are required to provide the default 0.80 confidence level, then more rows will be sampled for a sample distribution that uses 60 equivalence categories. The average percentage of sampled values in each bin will be 3%.

Listing 13. Sample use of SAMPLING SIZE
UPDATE STATISTICS MEDIUM FOR TABLE customer (address1, city, state)
SAMPLING  SIZE  300  RESOLUTION  3  DISTRIBUTIONS  ONLY;

The Informix server always records in the system catalog the actual sampling size (as a percentage of the total number of rows in the table) when UPDATE STATISTICS MEDIUM is run.

Using fully automated update statistics (AUS)

Update statistics is fully automated now. The server collects data on table use, much like you see in the onstat -g ppf (partition profile) output. The server uses those data to determine if data distributions need updating. Specific update statistics commands are created based on those data and executed, according to a predefined schedule.

Automatic update statistics is implemented using three tasks in the sysadmin database as well as stored procedures, triggers, and other supporting database structures. Following are the tasks, defined in rows of the ph_task table:

mon_table_profile
Collects the profile data by partition or fragment and stores the data in the mon_table_profile table.
Auto update statistics evaluation
Determines which tables and column data statistics need to be refreshed, and stores the resulting update statistics statements in the aus_command table.
Auto update statistics refresh
Executes pending update statistics commands located in the aus_command table

The user has the option of modifying the default task execution schedules by updating fields of the task row directly using SQL or by running the OpenAdmin Tool (OAT).

Enabling and disabling automatic update statistics

You might want to continue with your own schedule of update statistics commands instead of relying on automatic update statistics. Following are some ways to prevent the database server from updating statistics automatically.

Disable the AUS evaluation and AUS refresh tasks
This is the recommended method for disabling automatic update statistics or any other task. It is the most specific way to shut down AUS. Update the tk_enable field of the ph_task table for the tasks, setting the value to f. You can later enable the tasks by setting the values tot. This field serves as an on/off switch for the task scheduler. This method is specific to the given task.
Scheduler tasks can stop and start the threads while the engine is online
This runs the function task (scheduler stop)
Prevent creation of the sysadmin database
The sysadmin database is created by default when the instance is initialized or with upgrade from a previous version of Informix. To prevent the database from being created, create (as user Informix) a file named stop (the stop file) in $INFORMIXDIR/etc/sysadmin. The sysadmin database will be created at a later time if you remove the stop file and execute oninit to bring the engine online.

IBM Informix recommends against preventing the sysadmin database from being created. The database tasks are used for a variety of purposes beyond AUS.

Prevent creation of threads that support sysadmin functions
The dbScheduler and dbWorker threads manage the tasks of the sysadmin database. You can prevent these threads from being formed by creating a stop file and recycling the instance.

As with preventing sysadmin database creation, this may adversely affect other essential tasks.

Tuning automatic update statistics

The ph_threshold table of the sysadmin database contains parameters for setting the update statistics expiration policies (criteria for determining when statistics need refreshing) and the PDQ priority used for the automatic update statistics. Among the settings is AUS_CHANGE. This is analogous to STATCHANGE, which itself has no effect on controlling update statistics executed through the scheduler.


Using indexes

Using CREATE and DROP INDEX ONLINE statements

Indexes can now be created and dropped in a non-exclusive method. Use the CREATE INDEX ONLINE and DROP INDEX ONLINE statements to create and drop indexes in an online, or dynamic, environment. The database and its associated tables will not be locked exclusively, which means they will be available for updates or reads.

You do not need to place an exclusive lock on the table during the index build if an index is created with the ONLINE keyword. Reads and updates can occur on the table. Creation of an index will not have to wait until an exclusive lock can be placed on the table.

If an index is created with the ONLINE keyword, the database server logs the operation with a specific flag. Restore and recovery operations will now check for that flag and can recreate the index.

The advantages of creating indexes using the CREATE INDEX ONLINE statement are as follows:

  • An index can be immediately created without a lock placed over the table
  • Updates can occur to a table while the index is being created
  • The optimizer can update statistics in unlocked tables, enabling more optimal query plans

Attached indexes can be created using CREATE INDEX ONLINE, but ONLINE only applies when dirty read is the transaction isolation level. The index creation places an exclusive lock on the table and waits for all other concurrent processes scanning the table to finish using index partitions before it creates the attached index. If the table is being read or updated, the CREATE INDEX ONLINE statement waits for the exclusive lock.

If LOCK MODE is not set to WAIT, the creation of the attached index could fail, because it will not wait for other users to finish. Note that the engine places a lock after index creation for a short time while it updates system catalog information.

Listing 14 provides an example use of CREATE INDEX with the ONLINE syntax.

Listing 14. Sample CREATE INDEX with ONLINE syntax
CREATE  INDEX  i1  ON  tab1(c1)  ONLINE=3

ONLIDX_MAXMEM

The ONLIDX_MAXMEM configuration parameter is used to limit the amount of memory allocated to a preimage log pool or to an updater log pool. These pools are created in shared memory when an index is created with the ONLINE keyword. This might be helpful if you plan to complete other operations on a table column while an index is being created (using the ONLINE syntax) on that column.

The range of values of the ONLIDX_MAXMEM is 16KB through 4294967295KB. The default size in the onconfig.std is 5120KB.

The ONLIDX_MAXMEM configuration parameter can be modified dynamically with the onmode -wf command or superseded with the onmode -wm command.

DROP INDEX ONLINE

The DROP INDEX ONLINE parameter enables you to drop an index without the necessity of an exclusive lock. Indexes can be dropped (using the ONLINE keyword) even when dirty read is the transaction isolation level.

The advantages of dropping indexes using the DROP INDEX ONLINE statement are as follows:

  • An inefficient index can be dropped without disturbing ongoing queries, even queries using that particular index
  • The query optimizer will be notified not to use the index for new SELECT operations on tables

The execution of a DROP INDEX ONLINE statement does not occur until after a table update is completed. After you issue the DROP INDEX ONLINE statement, no new operations can reference the index, but concurrent operations can use the index until the operations are completed. The database server waits to drop the index until all current users have finished accessing the index.

Listing 15 contains an example use of DROP INDEX with the ONLINE syntax.

Listing 15. Sample DROP INDEX with ONLINE syntax
DROP  INDEX  i1  ONLINE

Index self-join

An index self-join is a join in which a table is joined to itself. An index self-join can help performance in either of the following circumstances:

  • A query involving the lead key, or the most important column (usually the first one), of an index has man duplicates
  • The cost or number of rows to examine with a lead-key is judged to be higher than the cost of looking through rows returned by non-lead-key columns

The way to conceptualize how this works is to think of the query being broken into a union of many subqueries. Each subquery is the result set of the non-restrictive lead-key column. The optimizer can then look at those columns and use them to look at the more restrictive non-lead-key columns.

Creating an index self-join

To produce a self-join, use aliases such that the same table is listed twice in the from clause, as shown in Listing 16.

Listing 16. Sample syntax to generate a self-join
SELECT  a1.customer_num
FROM  customer  a1,  customer  b1
WHERE  a1.customer_num  =  b1.customer_num  AND
...  Additional  logic  here

Note the use of two aliases that both reference the same table.

Self-join query plans and sqexplain output

A self-join can be determined in a query plan very simply with set explain output, as shown in Listing 17.

Listing 17. Example of set explain output showing a self-join
Index  Self  Join  Keys  (customer_num  lname  )
  Lower bound: informix.a.customer_num >= 00702 AND 
  (informix.a.lname >= 'Aaron ' )
  Upper bound: informix.a.customer_num <= 12345 AND
  (informix.a.lname <= 'Croyle' )

Note that the keys involved in the columns involved in the index self-join will be shown.

Self-join optimizer directives

Following are two optimizer directives can be used to direct the optimizer to use the self-join functionality:

  • The INDEX_SJ directive forces an index self-join path by using the specified index or by choosing the least costly index in a list of indexes, even if data distribution statistics are not available for the leading index key columns.
  • The AVOID_INDEX_SJ directive prevents a self-join path for a specified index or for a list of indexes.

Using the NO_SORT index parameter and the NO_SORTINDEX environment variable

NO_SORT is a new option for creation of indexes that can help performance in specialized scenarios (such as static clustered tables). With NO_SORT, you can use a function returning a numeric spatial key to create a statically clustered table, according to a functional b-tree index. When an r-tree index is created on the resulting clustered table, the r-tree secondary access method does not need to sort the data. It will build an index from the bottom up, because the table is already sorted according to the same criteria that the r-tree bottom-up build would use. The B-tree functional index does the sorting.

To create an r-tree index using the NO_SORT index parameter, complete the following steps.

  1. Check the DataBlade module documentation for a function that, given an object of the data type that is being indexed, returns a spatial key.
  2. Create a clustered functional b-tree index on the table using this function, as shown in Listing 18.
    Listing 18. Create a clustered functional b-tree index on the table using the DataBlade function
    CREATE CLUSTER INDEX btree_functional_index ON table1 (SpatialKey(col1));

    btree_functional_index is the name of the clustered functional b-tree index. table1 is the name of the table. SpatialKey is the fuction. col1 is the name of the column that contains the spatial data.

  3. Create an r-tree index on the spatial keyed column using the syntax NO_SORT="YES", as shown in Listing 19.
    Listing 19. Create an r-tree index on the spatial keyed column using the syntax NO_SORT="YES"
    CREATE  INDEX rtree_index  ON  table1  (col1 op1)
    USING  RTREE  (NO_SORT  =  'YES');

    rtree_index is the name of the r-tree index. op1 is the name of the operator class associated with the data type of column col1.

  4. Drop the now-extraneous b-tree index, as shown in Listing 20.
    Listing 20. Drop the now-extraneous b-tree index
    DROP  INDEX  btree_functional_index;

The NOSORTINDEX environment variable

If the NOSORTINDEX environment variable is set in the environment, then the default behavior of creating an r-tree index will be equivalent to setting NO_SORT="YES".

Forest of trees (FOT) index

Improved performance on index searches using is possible using the new forest of trees (FOT) indexing scheme, which was introduced in Informix 11.70. A single large b-tree index is replaced by a number of smaller b-tree indexes. The FOT index is built by hashing one or more columns in the index. Queries hash on that value and are directed to the correct index in the FOT index. The upper levels of an index are the most heavily accessed pages. The FOT index reduces contention by reducing heavy access of the top index pages.

To create a FOT index, add the HASH ON clause to the CREATE INDEX statement, as shown in the examples in Listing 21.

Listing 21. Examples of creating an FOT index
 CREATE INDEX idx1 ON tab1(c1) HASH ON (c1) WITH 100 BUCKETS;
CREATE INDEX idx2 ON tab2(c1, c2, c3) HASH ON (c1, c2) WITH 10 BUCKETS;

Tuning index cleaning

The BTSCANNER configuration parameter was introduced in Informix 10.00. New function was recently added in the form of adaptive linear index cleaning (alice mode, Informix 11.10) and b-tree compression (Informix Dynamic Server 11.50).

Scanning enabled with the alice mode (logged databases only) keeps a bitmap for each index partition that shows the index keys marked for cleaning. The scanning to clean items marked for deletion excludes parts of the index where no deletes should be done. Using alice mode provides a marked performance improvement over the older, range-scanning method. The alice mode is not used for tables with multiple attached indexes, where a leaf scan should be used.

B-tree compression is designed to keep a b-tree index size at a level that favors efficient searches. The compression setting sets thresholds for merging partially filled index pages.

Listing 22 shows the onconfig.std values for BTSCANNER.

Listing 22. BTSCANNER examples
BTSCANNER num=1, threshold=5000, rangesize=-1, alice=6, compression=default

The server starts num threads and puts the index on a hotlist for cleaning when it hits the threshold number of items to delete. The rangesize is the size in KB that an index fragment must exceed before the index is cleaned with range scanning. It is recommended that this value be set to -1 (off) for Informix 11.10 and above so that alice mode will be used where appropriate. The alice parameter sets the initial bitmap size. Set it to 6 or 7 for small to medium size systems, and set it to larger values for systems expected to be large. The compression is set to default (medium), low, medium, or high, depending on the processing behavior you expect on the system.

Your desired setting for compression might change as an index grows or shrinks. The administrative API (sysadmin database) has a built-in function for changing the setting, as shown in Listing 23.

Listing 23. Changing the compression setting
EXECUTE FUNCTION task("set index compression","1048611","high");

The compression for index with partnum 1048611 is set to high in this example.


Improving performance with parallel database query and the memory grant manager

Understanding PDQ

Parallel database query (PDQ) can improve performance dramatically. PDQ enables the database server to distribute the work for a query. For example, if an index needs to be built on a large table, the work can be distributed among multiple threads and processes.

Allocating and limiting PDQ resources

PDQ includes functionality for resource management. When the database server uses PDQ to perform a query in parallel, a heavy load can be placed on the operating system. The following resources can be tuned:

  • Memory
  • CPU VPs
  • Disk I/O (to fragmented tables and temporary table space)
  • Scan threads

When configuring the database server, you must consider how the use of PDQ affects other users. For example, if a query is running that is taking up all CPU resources, then the database server might not be as responsive to another query that usually runs very quickly. Care must be taken so that critical non-PDQ queries are still able to run with acceptable performance.

You can use the following methods to control how the database server uses resources:

  • Limit the priority of parallel database queries
  • Adjust the amount of memory from the memory grant manager
  • Limit the number of scan threads
  • Limit the number of concurrent queries

PDQPRIORITY

The PDQPRIORITY environment variable determines the degree of parallelism resources. The variable is used in conjunction with MAX_PDQPRIOIRTY as a scaling factor in how the database server allocates resources, as shown in Listing 24.

Listing 24. Setting the PDQPRIORITY environment variable
setenv--PDQPRIORITY--+-HIGH--------------------------------+
                     +-LOW---------------------------------+
                     +-OFF---------------------------------+
                     '-resources--+------------------------+
                                  |     (1)                |
                                  '-------------high_value-'

Table 6 shows the PDQPRIORITY settings.

Table 6. PDQPRIORITY settings
SettingDescription
HighWhen the database server allocates resources among all users, it gives as many resources as possible to the query.
Low or 1Data values are fetched from fragmented tables in parallel.
OFFPDQ processing is turned off.
resourcesAn integer between 0 and 100; sets the percentage of the user-requested PDQ resources actually allocated to the query.
Optional high valueOptional integer value that requests the maximum percentage of memory. When you specify this value after the resources value, you request a range of memory expressed as a percentage.

The more resources a database server dedicates to a query, typically the faster the server can complete the query. Contention for those resources can result, however, if other queries are trying to access those same resources. This can result in degraded performance.

The SQL statement SET PDQPRIORITY can be used to adjust the priority manually for a particular session.

Limiting the priority using MAX_PDQPRIORITY

MAX_PDQPRIORITY is the ONCONFIG parameter that limits the PDQ resources that the database server can allocate to any one DSS query. MAX_PDQPRIORITY is used as a percentage against the PDQPRIORITY for which any particular client asks. For example, suppose that a user is in a hurry to get his data and sets his PDQPRIORITY to 100. However, the DBA realizes that certain batch jobs have to be run at the same time every night, and the DBA sets MAX_PDQPRIORITY to 50. Fifty percent of 100 is 50, so the maximum percent of PDQPRIORITY resources that the user can actually get is 50.

You can use onmode -D to change the value of MAX_PDQPRIORITY while the database server is online.

On a system with both OLTP and DSS queries, a balancing act must be maintained. If you set a MAX_PDQPRIORITY too high, and OLTP queries will suffer. If you set the value is too low, DSS queries will not perform optimally. A DBA must therefore take care in tuning this variable.

You can set MAX_PDQPRIORITY to one of the values described in Table 7.

Table 7. MAX_PDQPRIORITY settings
SettingDescription
0Turns off PDQ. DSS queries use no parallelism.
1Fetches data from fragmented tables in parallel (parallel scans), but uses no other form of parallelism.
100Uses all available resources for processing queries in parallel.
Any numberAn integer between 0 and 100; sets the percentage of the user-requested PDQ resources actually allocated to the query.

Adjusting memory resources with the memory grant manager

The ONCONFIG parameter DS_TOTAL_MEMORY specifies the amount of memory available for PDQ queries. The amount is specified in KB, and it is allocated from the virtual portion of the Informix instance shared memory. DS_TOTAL_MEMORY should be set large enough to allow a sizable chunk of work to be loaded into memory at one time. Following are considerations to take into account when configuring DS_TOTAL_MEMORY:

  • The total memory on the computer (do not exceed it)
  • Overhead, such as the buffer pool
  • Other processes on the computer. Reduced performance benefits and actual degradation of performance can ensue if paging or swapping results from a too-high setting.

SHMTOTAL specifies all the memory for the database server (total of the resident, virtual, and message portions of memory). SHMVIRTSIZE specifies the initial size of the virtual portion of shared memory.

Allocating additional virtual memory for PDQ queries can trigger the dynamic addition of one or more virtual shared memory segments. Some operating systems experience performance overhead for each additional segment added to shared memory. Avoid this performance hit by tuning SHMVIRTSIZE and SHMTOTAL appropriately as follows so the minimum number of segments is allocated:

  • For OLTP applications, a base recommendation is to set DS_TOTAL_MEMORY to between 20 and 50 percent of the value of SHMTOTAL in kilobytes.
  • If the database server is used for DSS queries exclusively, set DS_TOTAL_MEMORY to between 90 and 100 percent of SHMTOTAL.
  • For systems that use both types of queries, a base recommendation is to set DS_TOTAL_MEMORY to between 50 and 80 percent of SHMTOTAL.

You can set DS_TOTAL_MEMORY dynamically with the onmode -M command.

Note that the value allowable for DS_TOTAL_MEMORY is platform dependent. The value for 32-bit systems must be an unsigned integer between 128 * DS_MAX_QUERIES and 1,048,576. On 64-bit systems, the limit is generally higher and varies with the operating system. On HP 9000 platforms, for example, the maximum value is 4,294,967,296.

Limit the number of scan threads

The ONCONFIG parameter DS_MAX_SCANS limits the number of PDQ scan threads that can run concurrently. If this parameter is set too high, the database server will have too many scan threads from multiple decision-support queries running concurrently. This will cause resource contention. The ready-queries queue in onstat -g mgm grows.

The formula in Listing 25 can be used to calculate the number of scans allocated to a query.

Listing 25. Calculating the number of scan threads allocated to one query
scan_threads = min (-nfrags-, (DS_MAX_SCANS * -pdqpriority- / 100 * MAX_PDQPRIORITY / 100) )

-nfrags- is the number of fragments in the table with the largest number of fragments. -pdqpriority- is the setting for that particular query.

You can set the maximum number of scan threads dynamically with the onmode -S option. This value must be an unsigned integer between 10 and 1,048,576.

Suppose a large table contains 50 fragments. If DS_MAX_SCANS is not set, there is no limit on the number of concurrent scans allowed. Fifty scans will be allocated by the database server. The engine would try to run all 50 scan threads to read this table. If this report can be run by any user, what happens if 50 people try to run that report simultaneously? Without being checked, the engine would allocate 50 threads for each running of that report. Twenty-five hundred threads would be spawned. Add overhead for other reports and other DSS queries, and there is a potential for major contention issues. Judicious use of DS_MAX_SCANS prevents this problem.

The balancing nature of database performance tuning is illustrated in this aspect of PDQ tuning. To reduce the time that scan threads for a large query will be in the ready-queries queue, reduce the number of scan threads allocated. However, if the number of scan threads for a query is less than the number of fragments, the query takes longer once it is underway.

Limiting the number of concurrent PDQ queries

DS_MAX_QUERIES is the ONCONFIG parameter that specifies the maximum number of PDQ queries that can run concurrently. The memory grant manager (MGM) reserves memory for a query based on the formula in Listing 26. The formula is also how the database server decides how much memory to allocate for a query.

Listing 26. Reserving memory for a query
memory_reserved = DS_TOTAL_MEMORY * (PDQ-priority/100) * (MAX_PDQPRIORITY/100)

The onmode -Q command can be used to set DS_MAX_QUERIES dynamically.

Using the memory grant manager

The onstat -g mgm option prints memory grant manager (MGM) resource information. You can use the onstat -g mgm option to monitor how MGM coordinates memory use and scan threads. Listing 27 shows sample output.

Listing 27. Sample onstat -g mgm output
IBM Informix Dynamic Server Version 11.50.FC9 -- On-Line -- Up 00:00:24 -- 250944 Kbytes

Memory Grant Manager (MGM)
--------------------------

MAX_PDQPRIORITY:  100
DS_MAX_QUERIES:    4
DS_MAX_SCANS:      1048576
DS_NONPDQ_QUERY_MEM: 128 KB
DS_TOTAL_MEMORY:   512 KB

Queries:   Active     Ready   Maximum
                0         0         4

Memory:     Total      Free   Quantum
(KB)          512       512       128

Scans:      Total      Free   Quantum
           1048576   1048576         1

Load Control:    (Memory)      (Scans)  (Priority)  (Max Queries)   (Reinit)
                   Gate 1       Gate 2      Gate 3         Gate 4     Gate 5
(Queue Length)          0            0           0              0          0

Active Queries:  None

Ready Queries:  None

Free Resource        Average #        Minimum #
--------------    ---------------     ---------
Memory              47.8 +- 2.7            32
Scans             1048575.3 +- 0.0          1048574

Queries              Average #        Maximum #    Total #
--------------    ---------------     ---------    -------
Active               1.0 +- 0.1             2       3371
Ready                0.0 +- 0.0             0          0

Resource/Lock Cycle Prevention count: 0

The MGM uses a series of gates, as shown in Table 8, to make sure that a PDQ query has enough resources to run correctly.

Table 8. Gate descriptions
Gate numberDescription
1Is there sufficient memory available?
2Have you exceeded DS_MAX_SCANS?
3This gate is a queue for all queries with the same priority.
4Have you exceeded DS_MAX_QUERIES?
5Check to make sure that you are not dynamically changing resources before allowing the query to run.

The quantum

PDQ queries are allocated memory in units called quanta. The number of quanta available to a query is determined by the percentage of PDQ resources available to that query. The onstat -g mgm output displays the size of one quantum. The size of one quantum is calculated as shown in Listing 28.

Listing 28. Calculating quantum
memory quantum = DS_TOTAL_MEMORY/DS_MAX_QUERIES

As an example, going back to the sample output from Listing 27, Listing 29 shows how to plug in the numbers.

Listing 29. Quantum calculation example
memory quantum = 512/4

The sample run generates a quantum of 128000 bytes.

As the math shows, the more queries you allow, the smaller a quantum will be.


Maximizing checkpoints

The Informix Dynamic Server does all of its work in shared memory. The server keeps pages in shared memory as long as it can to avoid needless I/O. For example, rows inserted into a data page in separate transactions can be done without reading the page from disk, modifying, and writing back to disk for each transaction. At some point, though, the database server must write that page back to disk. Room may be needed for other pages.

Retaining modified pages in memory creates recovery considerations. If the database server goes offline unexpectedly, it might leave modified pages in memory and unwritten to disk. During the recovery processes, the server needs to bring the data to a point of consistency before it can begin new processing. This is the process of fast recovery, which is achieved in two steps:

Physical recovery
Physical recovery is returning all pre-images of pages back to disk. A pre-image is the image of a page before it was modified.
Logical recovery
Logical recovery is the rolling forward of all transactions from the last checkpoint. All transactions that were committed will be replayed completely. But any transactions that were not committed will be rolled back.

Checkpoint

A checkpoint creates a point of synchronization between disk and shared memory. After synchronization, the database server has a known point of consistency. During a checkpoint, all buffers in the buffer pool plus the logical log buffers are flushed to disk so that they are stably stored.

When a checkpoint occurs, a checkpoint record is recorded in the logical log. It is also recorded in the reserve pages. During recovery, the reserve page checkpoint record is used to find the last point of data consistency, and it is the starting point for fast recovery.

Understanding checkpoints in previous versions

Before V11, the following situations would trigger a checkpoint:

  • Administrative events, such as the engine being shutdown, a chunk, or dbspaces being added
  • The physical log being 75% full
  • A checkpoint already in the logical log space: if the oldest logical log (the next to be written) contains the last checkpoint, then Informix has to write a new checkpoint so that the log can be available for reuse
  • ONCONFIG parameter CKPTINTVL

All four triggers remain in V11, but the interval between checkpoints can be modified by new features.

Informix needs a checkpoint for recovery, because it is a known point of consistency. Checkpoints are indirectly affected by the LOGSIZE and LOGFILES configuration parameters. When a logical log file is examined to see if it can be freed (and written), it is checked to see if it has the last checkpoint. Before it can be freed, the database server must write a new checkpoint record in the current logical-log file. The frequency of this happening could cause the frequency of checkpoints to increase. This is not usually a significant factor in V11.

If the physical log is very large, a long checkpoint interval can lead to an increase in the time needed in fast recovery. This can be vital in the case of an engine crash in a situation where database availability is important. In that situation, using a combination of CKPTINTVL and a moderately-sized physical log is recommended.

If CKPTINTVL expires and there have been no updates (nothing physically logged and no new administrative events), a checkpoint will not occur. When there are few page updates or changes to the system, it is common to have a longer CKPTINTVL setting.

Depending on parameters such as the size of the physical log buffer, the size of the buffer pool, and how many dirty buffers there are, a checkpoint can take some time to finish. Before V11, the checkpoints would frequently block any threads from getting into a critical section of code, which is code that must be done all in one unit, such as writing a page. This sometimes had the effect of making user applications wait on checkpoints. Database administrators often expended a great deal of time tuning database parameters to minimize blocking. This often became an effort to keep the number of dirty buffers low to minimize the number cleaned during a checkpoint. The configuration used for tuning checkpoints was as follows:

  • LRU_MIN_DIRTY and LRU_MAX_DIRTY was used to tune the number of dirty buffers in the buffer pool.
  • CHKPTINTVL sets the interval between checkpoints.

However, minimizing the number of dirty pages in the buffer pool also meant reducing the beneficial effects of caching.

Introduced in Informix V9, fuzzy checkpoints were meant to reduce disk writes during a checkpoint. Fuzzy checkpoints have been deprecated and replaced with new configuration parameters in Informix V11.

Learning about new configuration parameters that affect checkpoints

Three new configuration parameters that affect checkpoints were introduced in Informix V11. This tutorial describes them in more detail in a later section that is focused on automatic tuning.

For checkpoint tuning, you have the option of retaining the checkpoints offered in previous versions or using the new features. The new feature parameters are as follows:

  • AUTO_CKPTS modifies checkpoint frequency to prevent blocking due to lack of resources.
  • AUTO_LRU_TUNING increases LRU_MIN_DIRTY and LRU_MAX_DIRTY to prevent foreground writes.
  • RTO_SERVER_RESTART sets the checkpoint interval to achieve a configured fast recovery time. CHKPTINTVL is ignored when RTO_SERVER_RESTART is enabled.

Using non-blocking checkpoints

The checkpoint algorithm was reworked for Informix V11. The new algorithm is almost non-blocking. The V11 database server allows threads that would have been blocked in previous engines to do work during a checkpoint.

LRU tuning for non-blocking checkpoints

Before V11, database administrators commonly tuned LRU flushing aggressively so that LRUs were constantly being drained. As a consequence, checkpoints would finish faster, because there was less work to do. Consequently the total time other threads had to wait for the checkpoint to finish was minimized.

It is no longer necessary to tune LRU flushing so aggressively. Because transactions are not blocked during checkpoints, you can typically maintain LRU_MIN_DIRTY and LRU_MAX_DIRTY values that are much higher than were practical in pre-V11 engines. The number of dirty buffers processed during a checkpoint is much higher, and checkpoints are often much longer. However, it has the advantage of performance gains due to less LRU flushing and more caching of dirty pages.

Places where a checkpoint will temporarily block processing

Certain aspects of the checkpoint process have not changed with the advent of non-blocking checkpoints. During checkpoint processing, transactions continue to write before images to the physical log and transaction records to the logical logs. The engine must write at least one checkpoint in the span of the logical logs. The following circumstances trigger checkpoints that might become blocking if the log resources become too low during the checkpoint:

  • Physical log is 75% full
  • Checkpoint is required because logical logs are almost spanned

Configuring the engine for non-blocking checkpoints

In order to avoid situations in which checkpoints block transaction processing, complete the following steps:

  • Turn on the automatic checkpoint feature. More frequent checkpoints will occur. This will prevent blocking from taking place due to lack of resources.
  • Increase the physical or logical log size. The server will place a message in the online log to suggest which resource to increase and what size the resource should be.

Further considerations for managing checkpoints

ONDBSPACEDOWN is a configuration parameter that dictates how the server will handle a non-mirrored regular dbspace going down from an I/O error. Depending on the setting, the engine could hang during a checkpoint. Temporary dbspaces are not affected by ONDBSPACEDOWN.

A message is read in the online log when a checkpoint completes. This message also specifies how long a checkpoint took to complete. Before V11, this was also an ad-hoc specification on how long users were blocked out of critical sections. DBAs could use this info to help tune their checkpoints. You can read these messages using onstat -m.

A new onstat option (-g ckp) enables you to track the checkpoint history for the previous 20 checkpoints. The onstat option gives checkpoint duration. It also presents the trigger that caused the checkpoint, which should be useful for configuration.


Learning about table compression and defragmentation

Inefficient use of disk space can degrade performance. If space is poorly utilized (deletes can free up a lot of row space), the I/O will increase for a given data set retrieval. When table pages are distributed across many extents, more seek time is needed to find the data. You can repack and shrink independently of compression. The repack consolidates free space, and the shrink returns free space to the dbspace, which shortens extents as much as possible. Repack can be performed while the engine is offline.

Defragmentation resolves the second problem by relocating and merging extents wherever possible.

Table compression and defragmentation are performed through the administrative API. Following are some examples of administration API commands:

Listing 30. Repack API command
EXECUTE FUNCTION task("table repack", "table_name", 
"database_name", "owner_name");
Listing 31. Shrink API command
EXECUTE FUNCTION admin("table shrink",
"table_name", "database_name", "owner_name");
Listing 32. Defragment API command
EXECUTE FUNCTION task("defragment", "database_name:owner_name.table_name");

You can administer these operations using the Open-Admin Tool (OAT), and they can be scheduled.


Making additional performance improvements

Introducing new fragmentation options

Before Informix 11.70, fragmentation was done by round robin and expression. In Informix 11.70, two new fragmentation schemes are available: interval and list. The interval strategy provides a mechanism for allowing fragments on values that are not yet known. The list strategy lets the user fragment on discreet values.

Improved parallelism during backup and restore

This section describes new parallelism approaches during backup and restore.

OnBar parallel whole system backup using BAR_MAX_BACKUP

Before V11, whole system backups were single threaded. One single archive checkpoint timestamp was taken for all dbspaces, and the checkpoints were archived serially. A parallel whole system backup can be done in V11 by appropriately configuring BAR_MAX_BACKUP, as shown in Table 9.

Table 9. Setting BAR_MAX_BACKUP
BAR_MAX_BACKUP settingDescription
UnsetFour parallel processes created
0Allows the engine to decide. The engine allocates the lesser of the number of storage spaces or how many can be accommodated in shared memory.
1Serial (single-threaded) archive and restore
#Start number of backup processes

A checkpoint is performed for all dbspaces just before the backup of the rootdbs. The root dbspace is archived first without parallelism. Up until this point, it is the same behavior as previous versions.

Starting in V11, backup of remaining dbspaces is executed in a new order. This order is the same as used for non-whole-system backups. A before-image processor thread (arcbackup2) is started for each dbspace. This enables more threads to run in parallel. As each dbspace backup is completed, the corresponding arcbackup2 thread exits, resulting in fewer arcbackup2 threads taking up resources as backup progresses.

The new dbspace backup order is based on the used-pages count at start time of backup. Dbspaces to be backed up are ordered by the number of used pages in the dbspaces in a descending order. This ensures better parallelism. This takes effect no matter how BAR_MAX_BACKUP is set or how many pages are to be archived.

Creating user-defined VPs

Special classes of virtual processors can be created to run user-defined routines or to perform the work for a DataBlade module. User-defined routines are typically written to support user-defined data types. If you do not want a user-defined routine to run in the CPU class, which is the default, you can assign it to a user-defined class of virtual processors (VPs). Another name for user-defined virtual processors is extension virtual processors.

Determining the number of user-defined virtual processors needed

If your UDR can do its work in parallel, you should configure enough user-defined virtual processors to handle the multiple tasks the UDR does. The database server will support as many user-defined VPs as the operating system will allow.

Using user-defined virtual processors

User-defined classes of virtual processors can protect the database server from ill-behaved user-defined routines. An ill-behaved user-defined routine has at least one of the following characteristics:

  • It does not yield control to other threads
  • It makes blocking operating system calls
  • It modifies the global VP state

Assigning any ill-behaved user-defined routines to a user-defined class of virtual processor protects the database server against unsafe execution, which enhances reliability of the server. User-defined VPs remove the following programming restrictions on the CPU VP class:

  • The need to yield the processor regularly. Because only a thread running the UDR is running on the extension virtual processor, there is no need to yield.
  • The need to eliminate blocking I/O calls.

UDRs running on user-defined virtual processors are not required to yield the processor. They might issue filesystem calls that block further processing by the processor until I/O is completed. Impact on other running threads is reduced when these UDRs are running on the user-defined virtual processor, rather than the CPU VPs.

Specifying a user-defined virtual processor

The VPCLASS option is used to define a new VP class. The class name corresponds to the CLASS defined in the UDR.

Assigning a UDR to a user-defined virtual-processor class

The CREATE FUNCTION statement registers a user-defined routine. The function name is defined, along with the return type and the name of the class that the function runs in.

As an example, in Listing 33, the user-defined routine AllLowerCase is defined. It specifies that the parameter type given is a char, and it defines that the return type is a BOOLEAN. Notice that the class name is also specified. This means that calls to this routine are run in the user-defined VP class named ALC.

Listing 33. Sample SQL to create a UDR
CREATE  FUNCTION  AllLowerCase(char)
RETURNS  boolean
WITH  (CLASS  =  ALC  )
EXTERNAL  NAME  '/usr/lib/objects/udrs.so'
LANGUAGE  C

The ONCONFIG file must include a VPCLASS parameter that defines the ALC class. If not, calls to the AllLowerCase function will fail.


Understanding Informix self tuning

Informix automatically tunes itself in several areas.

Automatic checkpoints

The ONCONFIG parameter AUTO_CKPTS enables the use of automatic checkpoints, and it is enabled by default. The database server automatically adjusts checkpoint frequency to avoid transaction blocking. Recall from earlier in this tutorial that the checkpoints triggered by physical and logical use can block processing. With automatic checkpoints in effect, the server monitors physical and logical log consumption, along with information about past checkpoint performance. The server triggers checkpoints more frequently to avoid transaction blocking. Informix added the following two tables to the sysmaster database to keep the checkpoint data.

  • The syscheckpoint table keeps history on the last 20 checkpoints.
  • The sysckptinfo table tracks checkpoint activity.

Because the database server does not block transactions during checkpoint processing, LRU flushing should be relaxed. If the server is not able to complete checkpoint processing before the physical log is consumed (which causes transaction blocking), and if you cannot increase the size of the physical log, you can configure the server for more aggressive LRU flushing.

The increase in LRU flushing impacts transaction performance, but it should reduce transaction blocking. If you do not configure the server for more aggressive flushing, the server will automatically adjust LRU flushing to be more aggressive only when the server is unable to find a low priority buffer for page replacement.

You can disable automatic checkpoint tuning by running onmode -wf AUTO_CKPTS=0 or by setting the AUTO_CKPTS configuration parameter to 0.

Automatic tuning of LRU

The LRU settings for flushing each buffer pool with interval checkpoints are not critical to checkpoint performance in V11. The LRU settings are necessary only for maintaining enough clean pages for page replacement, which makes LRU flushing less aggressive.

Starting in V11, the database server automatically tunes the LRU flushing whenever a page replacement occurs. The new configuration parameter for this is AUTO_LRU_TUNING. The AUTO_LRU_TUNING configuration parameter specifies whether automatic LRU tuning is enabled or disabled when the server starts.

If AUTO_LRU_TUNING is enabled, after a checkpoint, if a page-replacement foreground write occurred during the previous checkpoint interval, the database server decreases the LRU settings by five percent. The server continues to decrease the LRU flushing at each subsequent checkpoint until page-replacement foreground writes stop or the LRU_MAX_DIRTY for a given buffer pool falls below 10 percent. For example, if a page-replacement foreground write occurs and the LRU settings for a buffer pool are 80 and 90, the database server adjusts these 5 percent to 76 and 85.5.

LRU flushing is tuned more aggressively whenever a page fault replaces a hot page (high priority buffers) and non-high priority buffers are on the modified LRU queue. Automatic LRU adjustments only make LRU flushing more aggressive; they do not decrease LRU flushing.

LRU flushing is reset to the values contained in the ONCONFIG file on which the database server starts.

Automatic LRU tuning affects all buffer pools and adjusts LRU_MIN_DIRTY and LRU_MAX_DIRTY values in the BUFFERPOOL configuration parameter.

The AUTO_LRU_TUNING is enabled by default in the configuration file. You can dynamically enable or disable automatic LRU tuning by using onmode -wm or onmode -wf.

Recovery Time Objective (RTO) policy

Informix V11 introduces the configuration parameter RTO_SERVER_RESTART, which configures the number of seconds that the database server will try to finish recovery and come back to online or quiescent mode. This parameter is useful when the server needs to be back online within a given time after a crash. Manual estimation of the recovery time is difficult, because the user does not know the total I/O needed for recovery.

Recovery starts with reading data page before-images from the physical log into the buffer pool. Logical logs are then used to roll forward committed transactions and roll back uncommitted transactions. To fulfill the recovery time goal, Informix must manage the amount of I/O that will occur during recovery.

When RTO_SERVER_RESTART is configured, the server maintains current information on the physical and logical log space used since the previous checkpoint plus the I/O speed available to access these logs during fast recovery. The time to reboot the server is also known. Using these numbers, the server determines the checkpoint interval needed to maintain the restart policy.

RTO_SERVER_RESTART needs to be turned on while the engine is online so that pages can be marked to be populated in the buffer pool in fast recovery. Only after recycling, will the benefit be realized.

When RTO_SERVER_RESTART is enabled, the database server does the following:

  • Ensures that automatic checkpoints do not run out of critical resources (such as log space) and block by triggering more frequent checkpoints
  • Ignores the configuration parameter CKPTINTVL and sets a calculated checkpoint interval
  • Automatically adjusts the number of AIO virtual processors and cleaner threads
  • Automatically tunes LRU flushing to accommodate the increased number of checkpoints

Note that AIO virtual processors, cleaners, and LRU flushing are automatically adjusted when RTO_SERVER_RESTART is enabled, regardless of whether AUTO_AIOVPS or AUTO_LRU_TUNING is enabled.

Following are the disadvantages to enabling RTO_SERVER_RESTART:

  • Storing pages for logical recovery increases physical log activity, which might impact transaction performance
  • Increased checkpoint frequency, although the size of the physical log can be increased to alleviate this

At minimum, total log space needs to be only large enough to contain all the transactions for two checkpoint cycles. In the situation where RTO_SERVER_RESTART is enabled and the server has a combined buffer pool size of less than 4GB, the total log space recommended is 110% of the combined buffer pool sizes.

Two new ONCONFIG parameters, RAS_PLOG_SPEED and RAS_LLOG_SPEED, store the rate at which the physical and logical log can be recovered during fast recovery and are used for calculating the checkpoint interval for RTO policy. RAS_PLOG_SPEED is initially set when the physical log is initialized. RAS_LLOG_SPEED is initialized to 1/8 of RAS_PLOG_SPEED.

Each time a fast recovery occurs, these values are updated to reflect what the real recovery speed is. The units are pages per second.

IBM included RAS_PLOG_SPEED and RAS_LLOG_SPEED in the ONCONFIG file to enable DBAs who embed Informix into their applications to provide pre-computed values so no tuning is required to get optimal performance. DBAs should not need to change these values unless directed by IBM Technical Support.

By default, RTO_SERVER_RESTART is disabled. The ranges of values allowed to configure this configuration parameter are as follows:

  • If RTO_SERVER_RESTART is off, the value range is 0.
  • If RTO_SERVER_RESTART is on, the value range is 60-1800 pages per second.

You can change the RTO_SERVER_RESTART configuration parameter by modifying the configuration file manually or using onmode -wf RTO_SERVER_RESTART. The new configuration value of the RTO_SERVER_RESTART takes effect when the database server is stopped and restarted.

With Informix V11, fuzzy checkpoints have been deprecated. As a result, the new checkpoint algorithm requires more physical log activity. Applications configured for Informix V7 should notice little or no change in the rate of physical logging activity. Applications that ran on V9 might experience an increase in physical logging. Enabling RTO_SERVER_RESTART also causes an increase in physical logging activity.

A large physical log will not impact performance. A typical physical log size of 1GB to 4GB is usually sufficient.

Dynamic addition of CPU virtual processors

This is a new feature in Informix 11.70. The database server on a multiprocessor machine automatically adds CPU VPs if SINGLE_CPU_VP is set to 0 and the auto_tune_cpu_vps task in the ph_task table of sysadmin database is enabled. The server adds up to half the number of physical processors to a maximum (configured plus automatic) of eight. If you need more than eight CPU VPs, they will have to be configured. The addition is made at startup, and a message regarding dynamic CPU VP addition is printed to the online.log, as shown in Listing 34.

Listing 34. Message about dynamic CPU VP addition
15:45:27  Dynamically added 3 cpu VPs

Automatic AIO VP tuning

Starting with IBM Informix V11, you can use the AUTO_AIOVPS configuration parameter to enable automatic adjusting of the number of AIO VPS and flusher threads when the server detects that AIO VPs are not keeping up with the I/O workload.

The AUTO_AIOVPS is enabled by default in the configuration file. You can dynamically enable or disable the automatic increase of AIO VPs and flusher threads by using onmode -wm or onmode -wf.


Conclusion

This tutorial introduced some of the new performance features in Informix. The tutorial did the following:

  • Introduced some operating considerations that need to be taken into account when configuring your operating system to get the most out of Informix.
  • Discussed the ONCONFIG file, with some attention paid to configuring it for optimal usage of operating system resources.
  • Explained non-blocking checkpoints, which can provide a significant performance boost.
  • Considered update statistics, including statistics maintenance, which will guide the optimizer to choose the fastest query plan.
  • Discussed some performance enhancements and features of indexes, including self-join and NO_SORTINDEX.
  • Presented self-tuning features of the database server.

You should now be better prepared for Part 4 of the System Administration Certification exam 919 for Informix v11.70.

Continue your education with the other parts of the certification series.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=796001
ArticleTitle=System Administration Certification exam 919 for Informix 11.70 prep, Part 4: Performance tuning
publish-date=03012012