The Health Check for IBM Informix Extended Parallel Server (XPS) is a carefully crafted program designed to review the efficiency as well as effectiveness of an IBM Informix XPS-based system. The efficiency of the system is evaluated by determining the extent to which the IBM Informix XPS product has been utilized; essentially, is the system “firing on all cylinders”?
The efficiency of a system takes into consideration issues such as:
- Are the XPS engine and operating system properly tuned?
- Are there sufficient hardware resources for the data warehouse or OLTP environment?
- Is this a hybrid, or mixed workload, environment?
- Have schemas been normalized or denormalized where appropriate?
- Are all co-servers configured optimally?
- Are the appropriate fragmentation schemes in place?
- Are critical administrative utilities run regularly?
- Does the staff have the requisite skills needed to maintain an efficient system?
In a complex computing environment, the extent to which these issues are addressed will affect the overall efficiency and effectiveness of the systems in place. The Health Check for IBM Informix XPS focuses on these areas of efficiency and effectiveness by having a skilled Informix XPS engineer assess your Informix XPS-based computing environment over a one to five day period.
Ideal System Performance
To achieve the optimal performance for a given system one must ensure the optimal performance of each component of the system. The components addressed in this performance analysis are:
- Hardware utilization including CPU, I/O, Memory
- Operating System Configuration LUW Database Manager and its constituent databases
- Implementation of the physical model
- Client Communications
- Application Implementation
- Operations and Maintenance including backup, recovery, consistency checks and others.
Each of these components is critically important to the optimal performance of the overall system.
The following are some of the areas of concern related to each system component.
Hardware Resources:
The server hosting your IBM Informix XPS engine relies primarily on three hardware subsystems for efficient performance – CPU, Memory, and I/O. These subsystems can exist on one physical server, or across multiple hardware components or nodes. Taking full advantage of all the server resources is critically important for optimal performance.
A well-architected system will show optimal CPU utilization without many threads waiting to run. A fully utilized CPU is ideal, but the additional check of the number of processes waiting for the CPU is required. Architecturally, CPU technology has changed dramatically since the inception of XPS. Therefore, some of the “old rules” need to be reconsidered and potentially modified for the client environment.
The I/O subsystem of the server hosting your Informix XPS system is critical to the performance of the database and its applications. Ideally, the disks in the system will be responding to requests quickly and there will be no queues forming on those disks. If RAID is being used, this will be analyzed for its effect on the database and applications. I/O configuration is the most flexible of the resources, since the database and system administrators can easily work together to balance the I/O load across all available resources.
A large XPS system may contain several gigabytes of main memory, often across multiple servers. The use of that memory has to be carefully divided among the functions of the server: applications, database, and operating system. The goal is to have ample free-memory to meet the peak demands of the workload, while maintaining good cache hit rates in the engine. Even within the engine itself, there are memory trade-offs for buffer caches and large sort/join pools.
The Operating System:
The operating system parameters will be analyzed and compared to the platform-specific recommendations provided by IBM. An important example is the parameter that controls the size of virtual memory segments created for the XPS engine. If the size of a single segment is too small, the engine may need to create a high number of segments in order to allocate sufficient memory. This is undesirable, as the use of many small segments is expensive compared to a few very large segments.
Informix ExtendedParallel Server (XPS) Database Server:
There are a number of items that relate to database performance. However, key elements of this performance will reflect the use of indexes, table fragmentation and I/O balance, optimizer statistics, parameter settings, transaction logging, database layout, session activity, cache utilizations, and others. These areas will be investigated extensively based on the overall behavior of the engine. Each component of the analysis will be detailed in the report which is delivered at the completion of the service.
Client Communications:
There are several optimizations available for clients connecting to your IBM Informix XPS system. These will be evaluated for appropriateness in the environment. Additionally, if clients are connecting to Informix XPS with older versions of software, there is potential opportunity for significant performance improvement through client library upgrades.
Other Influencers:
Other areas of the assessment focus on the operational stability of the environment. Looking at backup and recovery, logging, and upgrade strategies, as well as batch jobs and test platform capability will enable the engineer to provide recommendations for improving availability of the environment.
For the performance evaluation to be thorough and meaningful, you will need to provide access to your systems and key personnel. Key individuals in your environment include the database administrator, system administrator, application team leader, and others you identify as being able to provide insight into the design and workings of the current system. Operating System: Informix Extended Parallel Server (XPS) Database Server: Client Communications: Other Influencers: For more
Health Check for IBM Informix XPS (pdf/144kb)