- Before you start
- Tuning performance on Informix
- Affecting system operations with operating system resources
- Configuring the server
- Optimizing communication performance
- Updating statistics
- Using indexes
- Improving performance with parallel database query and the memory grant manager
- Maximizing checkpoints
- Learning about table compression and defragmentation
- Making additional performance improvements
- Understanding Informix self tuning
- Downloadable resources
- Related topics
System Administration Certification exam 919 for Informix 11.70 prep, Part 4
This content is part # of # in the series: System Administration Certification exam 919 for Informix 11.70 prep, Part 4
This content is part of the series:System Administration Certification exam 919 for Informix 11.70 prep, Part 4
Stay tuned for additional content in this series.
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.
- 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.
After completing this tutorial, you should be able to:
- Understand operating system resource considerations
- Use Informix features to optimize performance
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:
- Download and install a copy of IBM Informix 11.70.
- Create a server instance.
- 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 components include the following.
- 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 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.
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.
Because Informix is scalable, it can be tuned to accommodate large
instances. The main parameter for tuning virtual processors (VPs) is the
VPCLASS parameter replaced the following
Listing 1 shows the syntax of the
Listing 1. VPCLASS syntax
classname in the
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
The classname variable is required. Unlike most configuration
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:
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
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
The JVP option of the
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.
For VP classes
soc, you must set the
NETTYPE configuration parameter's
VP_class field to
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
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
|EXTSHMADD||Specifies the size of an added extension segment|
|SHMADD||Specifies the increment of memory that is added when the database server requests more memory|
|SHMBASE||Specifies 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.|
|SHMTOTAL||Specifies the maximum amount of memory the database server is allowed to use|
|SHMVIRTSIZE||Specifies the size of the first piece of memory that the database server attaches|
|BUFFERPOOL||Configures 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
BUFFERPOOL) and adequate virtual
memory allocation (
SHMVIRTSIZE). If initial
virtual memory is inadequate for long-term processing, dynamic
addition of virtual segments (
create excessive numbers of virtual segments during processing, which
can adversely affect performance.
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 setting||Effect||onstat -d flag|
|0||Direct I/O off||-|
|1||Direct I/O on||D|
|2||Direct and concurrent I/O on||C|
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
Windows does not support the
DIRECT_IO as direct I/O is
turned on by default on the Windows platform.
You can configure the
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
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
admin() functions from the sysadmin
SQL tracing is particularly useful for studying individual queries that execute within applications that run many queries.
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 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
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.
NETTYPE entries to tune. The
NETTYPE definition is shown in Listing 6.
Listing 6. NETTYPE definition
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
You can tune
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
NETTYPE in Listing 7 configures
3 poll threads on 3 soc virtual processors for a total of 600
Listing 7. Example NET VP definition
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.
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
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
fast polling your network and optimal connection performance, as shown
in Listing 8.
Listing 8. FASTPOLL command
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.
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)
number_connections value should be the third field of the
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_SIZEenvironment variable, and
b(client buffer size) option in the sqlhosts file or registry
IFX_NETBUF_SIZE environment variable
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
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
IFX_NETBUF_SIZE to the average
packet size. Clients might send large packets during any of the following
- Table loads
- Rowsize greater than 4KB
- Sending simple large objects
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_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
variable. The default size is 1 buffer.
Onstat commands to monitor and tune network buffer usage
onstat options in Table 4 to monitor the network buffer usage.
Table 4. Monitoring network buffers with onstat
|Option||Output field||Field description|
|onstat -g ntu||q-pvt||Current number and highest number of buffers that are free in the private pool for this session|
|onstat -g ntm||q-exceeds||Number of times the free buffer threshold was exceeded|
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_SIZEto increase the size of each buffer
- Use the environment variable
IFX_NETBUF_PVTPOOL_SIZEto 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.
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_MAX_QUERIESparameters) 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
|UPDATE STATISTICS LOW DROP DISTRIBUTIONS||Indexes, drop distributions|
|UPDATE STATISTICS LOW||Indexes|
|UPDATE STATISTICS MEDIUM DISTRIBUTIONS ONLY||Data distributions|
|UPDATE STATISTICS HIGH||Data distributions and indexes|
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.
STATCHANGE configuration parameter sets
the percent of stale statistics that trigger distribution
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
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
STATCHANGE at the session level. Session
settings override the configuration value. For even finer granularity,
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:
- 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.
AUTOis the default.
- Enables fragment-level statistics
- 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
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
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
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:
- 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 to
t. 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
oninitto 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 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 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
16KB through 4294967295KB. The default size in the onconfig.std is
ONLIDX_MAXMEM configuration parameter can be modified dynamically with the
onmode -wf command or superseded with the
onmode -wm command.
DROP INDEX ONLINE
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
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.
- Check the DataBlade module documentation for a function that, given an object of the data type that is being indexed, returns a spatial key.
- 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_indexis the name of the clustered functional b-tree index.
table1is the name of the table.
SpatialKeyis the fuction.
col1is the name of the column that contains the spatial data.
- Create an r-tree index on the spatial keyed column using the
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_indexis the name of the r-tree index.
op1is the name of the operator class associated with the data type of column
- 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
NOSORTINDEX environment variable is
set in the environment, then the default behavior of creating an r-tree
index will be equivalent to setting
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
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
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
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
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:
- 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 environment variable
determines the degree of parallelism resources. The variable is used in
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
|High||When the database server allocates resources among all users, it gives as many resources as possible to the query.|
|Low or 1||Data values are fetched from fragmented tables in parallel.|
|OFF||PDQ processing is turned off.|
|resources||An integer between 0 and 100; sets the percentage of the user-requested PDQ resources actually allocated to the query.|
|Optional high value||Optional 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
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
You can set
MAX_PDQPRIORITY to one of the
values described in Table 7.
Table 7. MAX_PDQPRIORITY settings
|0||Turns off PDQ. DSS queries use no parallelism.|
|1||Fetches data from fragmented tables in parallel (parallel scans), but uses no other form of parallelism.|
|100||Uses all available resources for processing queries in parallel.|
|Any number||An 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
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
- 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
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
SHMTOTAL appropriately as follows so the minimum
number of segments is allocated:
- For OLTP applications, a base recommendation is to set
DS_TOTAL_MEMORYto between 20 and 50 percent of the value of
- If the database server is used for DSS queries exclusively, set
DS_TOTAL_MEMORYto between 90 and 100 percent of
- For systems that use both types of queries, a base recommendation is to
DS_TOTAL_MEMORYto between 50 and 80 percent of
You can set
DS_TOTAL_MEMORY dynamically with
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
Limit the number of scan threads
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
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)
onmode -Q command can be used to set
Using the memory grant manager
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
|1||Is there sufficient memory available?|
|2||Have you exceeded DS_MAX_SCANS?|
|3||This gate is a queue for all queries with the same priority.|
|4||Have you exceeded DS_MAX_QUERIES?|
|5||Check to make sure that you are not dynamically changing resources before allowing the query to run.|
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.
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.
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
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
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.
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
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_DIRTYwas used to tune the number of dirty buffers in the buffer pool.
CHKPTINTVLsets 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_CKPTSmodifies checkpoint frequency to prevent blocking due to lack of resources.
LRU_MAX_DIRTYto prevent foreground writes.
RTO_SERVER_RESTARTsets the checkpoint interval to achieve a configured fast recovery time.
CHKPTINTVLis ignored when
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
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
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
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
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
|Unset||Four parallel processes created|
|0||Allows the engine to decide. The engine allocates the lesser of the number of storage spaces or how many can be accommodated in shared memory.|
|1||Serial (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
VPCLASS option is used to define a new
VP class. The class name corresponds to the CLASS defined in the
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
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.
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
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 configuration parameter
specifies whether automatic LRU tuning is enabled or disabled when the
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
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
Automatic LRU tuning affects all buffer pools and adjusts
LRU_MAX_DIRTY values in the
BUFFERPOOL configuration parameter.
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
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.
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.
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
CKPTINTVLand 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
AUTO_LRU_TUNING is enabled.
Following are the disadvantages to enabling
- 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.
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
Each time a fast recovery occurs, these values are updated to reflect what the real recovery speed is. The units are pages per second.
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.
RTO_SERVER_RESTART is disabled.
The ranges of values allowed to configure this configuration parameter
are as follows:
RTO_SERVER_RESTARTis off, the value range is 0.
RTO_SERVER_RESTARTis on, the value range is 60-1800 pages per second.
You can change the
configuration parameter by modifying the configuration file manually
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.
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
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
ONCONFIGfile, 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
- 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.
- See the other tutorials in the series to continue your education.
- Use an RSS feed to request notification for the upcoming tutorials in this series. (Find out more about RSS feeds of developerWorks content.)
- Go to the developerWorks Informix page to read articles and tutorials and to get the resources you need to expand your Informix skills.
- Refer to the IBM Informix v11.70 Information Center to learn more about Informix. Find the information that you need to use Informix products and features.
- Learn more about resources for Informix certification at the IBM Information Management certification webpage.
- Download Informix Innovator-C Edition for free for small workload productions.
- Download Informix Ultimate Edition for a trial version.