Knowledge Collection: Informix Dynamic Server Performance

Overview
Prerequisites
Education
Configuration
Administering
Developing
Monitoring
Troubleshooting
Miscellaneous




Overview


This document will provide an overview of different topics related to the performance of IBM® Informix® Dynamic Server (IDS). It will give you links to the available information and tips how to troubleshoot certain performance situations.




Prerequisites

Before starting with performance tuning you should be familiar with the administration of the Informix Dynamic Server.
The tech info center and the classroom course IBM Informix Dynamic Server Administration should provide you a solid base to start performance tuning.

Back to top




Education


Documentation (Manual)
Check the Informix Performance Guide for an overview about the topics that could help improve performance.

Also, in the other manuals useful information can be found. The tech info center has the ability to do a full text search over all of the documentation.


IBM Informix Training / Education
Visit the IBM Informix Training site for an overview of the available training.

The following courses are are aimed at database administrators, and are especially useful for optimizing performance:

• IBM Informix Dynamic Server Performance Tuning IX400

Learn how to improve performance in your Informix Dynamic Server and UNIX environment and to monitor and analyse system activity.

• IBM Informix Dynamic Server Advanced Performance Tuning MW729

This worksho begins with a short, but intensive lecture period to explore a wide range of configuration and tuning issues in the IBM Informix Dynamic Server environment. The remainder of the workshop is spend working on a real-world decision support and Online Transaction Processing (OLTP) system, and getting extensive, hands-on experience configuring and tuning IBM Informix Dynamic Server on a state-of-the-art, high-performance hardware platform


There is also a course talking about the performance from a developers point of view:

• IBM Informix Programmer's Performance Workshop L2840

Learn more about how to use the features of the IBM Informix database server, the options of SQL, and application development techniques to write programs that run fast and use as few system resources as possible. Learn about the performance features available within the database server and how to create tables and indexes and write applications to take advantage of these features. #


Chat with the Labs
The Informix "Chat with the Labs" series currently has two talks concerend with Performance Tuning for Version 11.

• Optimizing IDS Performance (Teleconference)

This chat will address the topic of performance beginning with a refresh of the steps needed to get started with performance tuning. Version 10 has delivered another increase in performance and we will focus on the features in 10 that impacted the performance improvement.

• Tuning the IDS Server (Teleconference)

The focus of this Chat is on the new functionality in the IDS server to take advantage of non-blocking checkpoints. We discuss tuning LRU flushing and the new ONCONFIG parameters RTO_SERVER_RESTART, AUTO_CKPTS and AUTO_LRU_TUNING. We also discuss the new onstat -g ckp output. Our speakers for this Chat are Jerry Keesee, Director of the Informix Lab and Scott Lashley, IBM Senior Technical Staff Member, Informix IDS Kernel/Performance Architect.

There are alos two talks concerned with Performance Tuning for Version 10:

• Performance tuning for IDS v10.0 (Teleconference)

This chat will address the topic of performance beginning with a refresh of the steps needed to get started with performance tuning. Version 10 has delivered another increase in performance and we will focus on the features in 10 that impacted the performance improvement.

• More performance improvements with IDS v10.0 (Teleconference)

We've invited Jonathan Leffler back to discuss more of the performance improvement features of IDS version 10. He'll cover configurable page sizes, buffer pools and the truncate table feature.

It is well worth checking out the Chat with the Labs page for other interesting talks, covered topics are for example High Performance Loader or Backup and Restore

Back to top





Configuration


A list of the IDS Configuration parameters and environment variables can be found in the IBM Informix Administration guide (manual).

The Performance guide discusses the impact of parameters on CPU usage, memory usage and I/O.

The advantages and disadvantages of PDQ Queries is explained in the following document:
A quick start to Parallel Data Queries (PDQ) (article)
Use PDQ to improve slow query performance that can be parallelized. The advantages are greatest when typical single queries can be subdivided to run concurrently in different threads and/or on different CPUs. At best, the total query time can be reduced to a fraction by the ratio of the number of subqueries

For further discussion of other onconfig and environment variables and their relative impact to the performance of the system, please check the Performance Guide, the Release Notes and the " Search Support" site.

Back to top




Administering


New with version 11 are several features which help to improve and monitor performance.
For an general overview you might want to have a look at the tutorials for the IDS certification preparation for version 11. For the performance tuning point of view the following tutorials are especially interesting:
Part 2: System activity monitoring,
Part 4 : Performance Tuning
Part 8: SQL Features for administrators.


Non-blocking checkpoints in Informix Dynamic Server
This article by the Performance architect Scott Lashley promises to help you to Tune Informix Dynamic Server to take advantage of non-blocking checkpoints and RTO_SERVER_RESTART in an online transaction processing environment


Also check how the new sql trace feature can help you to identify slow queries
Use the Informix Dynamic Server scheduler and SQL API

The Open Admin Tool (OAT) provides a graphical interface for many administration tasks. For an overview of using them, see the articles listened in the Monitoring section.

In addition to the "Chat with the Labs" information mentioned above, the following teleconference deals specifically with update statistics:

• Unlocking the mysteries of update statistics (Teleconference)

Join the expert, John Miller, to learn about statistics and distributions, how they are used by the SQL optimizer and how to improve performance during their collection. Our speakers are Jerry Keesee, Director of the Informix Lab and John Miller, Senior Technical Staff Member.


This RedBook discusses Database Performance Tuning on the AIX platform, and has a chapter about Informix:

• Database Performance Tuning on AIX (RedBook)

The performance tips provided here relate to the things that a system administrator or database administrator can change. This book does not cover performance tuning issues that relate to application and database design, including SQL query tuning.

The following article has some performance guidelines for the usage of IDS together with Websphere:

• Performance Tuning When Using Informix Dynamic Server With WebSphere (article)

This tip provides some general performance tuning guidelines that can be used as a starting point for implementing an Informix Dynamic Server and WebSphere environment.

Back to top




Developing


For hints about optimizing SQL queries, check the SQL Trace feature mentioned in the section above. For background information review these articles:

• Fragmentation and Performance: A Case Study (article)

This article explains in detail the usage of fragmentation for performance improvements. If used properly, it will improve overall Informix Dynamic Server performance significantly; but if used without care, it may adversely affect performance. This article will discuss and analyze in detail a fragmentation case, and will provide some general tips as to planning and designing good fragmentation strategy for your Informix Dynamic Server.

• Part 1: Tuning Informix SQL (article)

SQL queries provide the backbone for Informix® database applications. This article discusses guidelines for SQL queries. It will look at factors you need to consider when tuning SQL queries, and examined some real life tuning examples gathered from the author's experience.

• Part 2: Tuning Informix SQL (article)

This is the second of a two-part series on tuning Informix® SQL. Now the author discusses the impact on performance of access methods, subqueries and extent management, and explains how you can affect performance by understanding index levels, uniqueness, fragmentation, and PDQ priority. Examples illustrate how you can apply these principles.


Regarding fragmentation and indexes, there is also a Chat with the Lab session which deals with these topics:

• Indexing and fragmentation strategies (Teleconference)

You can also check developerWorks for further articles regarding specific performance issues.

Back to top




Monitoring

Again, check OAT and the SQL API features to help you gather data on a regular base.

This Webcase by John Miller explains what is available:
Cheetah - Expanding the Administration Free Zone (Replay)

Hear John Miller discuss the new features of the next release of IDS that lowers the total cost of ownership by providing easy, scalable administration. Features such as SQL Tracing for detailed performance monitoring, the open Admin API for custom administration and a built-in database scheduler allow for an industrial strength, highly reliable environment. .
http://www-01.ibm.com/software/data/informix/webcasts/replay.html#1

To check out more details, the following two articles are helpful:


Manage your Informix database with the IDS OpenAdmin Tool, Part 1: Configuring and using the OpenAdmin Tool with IDS
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0807kudgavkar/index.html

Part 3: Performance analysis of Informix Dynamic Server made easy through the OpenAdmin Tool
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0807sudha/index.html

To get an overview of the performance of your instance, you should regularly gather some onstats commands output. The onstat commands are described in the IBM Informix Administrators reference.

The following article lists the information you should gather before updating IDS. However, this information is very helpful in monitoring IDS performance:

• Diagnostic information to collect before upgrading Informix Dynamic Server (article)

For further articles that describe techniques for monitoring the instance, check developerWorks

Back to top




Troubleshooting


For these distinct performance issues, see the following resources:

Checkpoints take too long:
Review the information found in the following article:

• Script to capture ONSTATS during a long checkpoint (article)

One query takes too long
The following two articles describe how to get a query plan and how use DIRECTIVES to change the way the query is executed:

• Configuring SET EXPLAIN to obtain a query plan report (article)

This article describes how to configure SET EXPLAIN for a local session and also how to configure it dynamically for a user session.

• How to force of a different join method in a query by using DIRECTIVES in the SQL statement. (article)

This document describes how to force of a different join method in a query by using directives in the SQL statement.

You can also review the Unlocking the mysteries of update statistics teleconference mentioned in the Administration section.


All queries take too long
No simple solution exits for this case. You probably have to go through the output of the different onstats discussed in the Monitoring section and try to localize the bottleneck from there.

Check also the following article:
What to collect if a IBM Informix IDS server gets hung (article)



Back to top




Miscellaneous



IIUG (Type: Other)
The International Informix User Group (IIUG) web site supports a Forums, FAQ, a Software Archive ( http://www.iiug.org/software), and links to many areas of interest related to IBM® Informix® Dynamic Server (IDS). Membership to the IIUG is free


Back to top