Monitoring Informix Dynamic Server for Higher Performance by Jianing Fan firstname.lastname@example.org. you can modify the alarm program provided by Informix during IDS installation. The alarm program is a. Some of the events that the database server reports to the message log trigger the alarm program. The class messages indicate the events that the database server reports. IBM® Informix® 12.10. Interpreting event alarm. Event Alarms - Ibm Informix tutorial. Free online course material. Only on Wisdom Jobs. 1 Customizing the New IDS 9.4/10.0 Alarm Program Tom Girsch Hilton Hotels Corp. Session: N10 Wednesday, May 10, 2006 • 2:30 p.m. – 3:40 p.m. Informix Dynamic Server 2000 Version 9.21.UC4 -- On-Line -- Up 38 days 11:26:22. To take even more active approach in monitoring, you can modify the alarm program provided by Informix during IDS installation.
Monitoring Informix Dynamic Server for Higher Performance. Informix provides two main tools for monitoring. SMI) tables. Both. SMI tables monitor IDS. The. onstat utility actually reads IDS shared memory.
It is easy to use, but. IDS was brought up and appear in a fixed format. On the. other hand, SMI tables are stored in the sysmaster. IDS initialization. SMI tables are not real tables; they. IDS shared memory structures.
Thus, the. data in SMI tables reflects IDS activity more. Moreover, SMI tables can be queried using. SQL and can display data in a more meaningful. IDS activity can be classified into three categories.
Using the tools provided by Informix, the. Monitoring Instance Activity. An IDS instance refers to Informix shared memory. Informix databases, and physical devices allocated to.
Informix. The following are some of the most important. Operating Mode. The first and most important instance activity is, of. IDS’s operating mode. Is IDS running all right? Is it having any problems?
Hello, Could someone share with me how to e-mail a message from the alert script on their Unix server to Exchange Server. Our database server does not have a modem, and I'm hoping there's a way to get the e-mail to the. This article describes methods to debug the alarm program scripts used to handle certain alarm events. IBM® Informix® 12.10. Connection Manager event alarm IDs. You can set your alarm program script to capture the Connection Manager class ID and message and initiate corrective actions or notifications. Recommended Informix 11.10 Parameters for Informix to coexist with STCLive T&D on AIX #***** # # INFORMIX SOFTWARE, INC. # # Title: onconfig.std # Description: Informix Dynamic Server. # System Alarm program path.
Is it down? The onstat - p. The first line displays the current IDS operating. In this case, the database is "On- Line." There are. Of those six, three are. Off- Line, Quiescent, and.
On- Line. The Off- Line mode indicates that IDS is not. The Quiescent mode indicates that IDS is. DBA can do. administrative and maintenance work.
The On- Line mode. IDS is running normal and all users can. In most circumstances, IDS should always be. On- Line mode. If for some reason IDS is down or.
Off- Line mode, the command will display the. In this case, you will need to check the online. Message Log" section below). In addition to the current operating mode, the above. The two %cached fields.
IDS is using its cache. The. first %cached field shows the percentage of read cache rate. Read cache rate and write cache rate can vary.
But, in. general, both read cache rate and write cache rate. If these rates are. BUFFERS parameter in your. Informix configuration file to achieve higher read and. Low read and write cache rates. IDS is doing a lot more disk reads and writes. The seqscans. field of the output indicates how many sequential scans.
If. this number is large (say over 1. OLTP environment, and a need to do some. We will discuss this in more.
The ovlock. field indicates the number of times IDS attempted to. If this number is. LOCKS parameter in the ONCONFIG file. The ovbuf field indicates.
IDS attempted to exceed the maximum. If this number is non- zero, you may. BUFFERS parameter in.
ONCONFIG file. The bufwaits field indicates the number of. If this. number is huge (say over 1. BUFFERS parameter so that users do not. This will reduce response time and, hence. Another group of important fields includes ixda- RA, idx- RA. RA, and RA- pgsused. Together, these fields.
IDS is using its read- ahead. Read- ahead is the operation that reads ahead. The ideal situation is. RA, idx- RA, and.
RA) is equal. to the number of pages used during the sequential scans. RA- pgsused), indicating that the.
If there is a significant difference. IDS is not using read- ahead very. RA_PAGES and RA_THRESHOLD) for better. Refer to the Administrator’s Guide. Message Log. The message log is also referred to as the online. It contains a variety of information about critical. The message log also contains.
Informix) such as disk I/O errors, down chunks, data. When assertion failures. The following is an excerpt from. The above output indicates that there was an internal. This. happened when user "informix" logged in, and when he was.
The file /tmp/af. In this particular case, you may need to check the. Chunk Status. Chunks are the physical disk storage devices. They. should always be on- line.
If any IDS chunk is off- line. The onstat - d command provides current. Notice the output contains two sections.
The first. section lists all dbspaces, and the second section lists. In the Chunks section, pay close attention. The first character of this field indicates if. P") chunk or a mirrored ("M"). The second character of the flags field indicates. O") or. off- line ("D").
Since "O" and "D" can look very much. PD" (onstat - d |grep.
PD) just to make sure that you don’t miss any down. If there are any down primary chunks, you will. Checkpoint Distribution. A checkpoint is the process of synchronizing pages on. During. checkpoints, IDS prevents user threads from entering.
Thus, if checkpoint duration is long, users may. This is especially true in. OLTP environments where there are thousands of. As. noted above, you can monitor checkpoint duration by.
The output displays the duration of the latest. If checkpoint duration is consistently longer than 1. LUR_MIN_DIRTY and LUR_MAX_DIRTY configuration parameters. Also, if. onstat - F shows extremely high chunk writes. AIO VPs to share the. You may need to. re- examine the CKPINTVL, LRU, CLEANERS, and NUMAIOVPS.
For detailed information on how to tune. Informix- On. Line. Dynamic Server Administration Guide. Usage. Knowing if there is enough space in each dbspace is. The script. below takes a snapshot of each dbspace, calculating. The output is as follows: This output helps to identify dbspaces that are. To be proactive, consider.
I/Odbspace I/O is measured by disk reads and writes. If. some dbspaces have heavy disk reads and writes while. I/O bottlenecks in. A well- balanced dbspace I/O will.
I/O loads and, hence, improve overall. The following script will display. I/O statistics for each dbspace: The output is as follows: The goal is to achieve balanced disk reads and writes. This is unrealistic in most cases.
The output above gives you an idea of how dbspace I/O is. If. disk reads and writes are extremely high for some. You can further identify what tables have the most. Based upon the output you get from this query, you. I/O balance. Shared Memory Segments. Too many virtual shared memory segments (usually more. This adversely effects IDS performance.
The. onstat - g seg command displays how many shared. If the output shows more than three virtual shared.
SHMVERSIZE parameter in your configuration file. The. idea is to let IDS allocate enough virtual shared memory. For detailed information on how to. IDS virtual shared memory segment size, please. Informix- On. Line Dynamic Server.
Administration Guide. Overall Operating System Performance.
To accurately evaluate IDS performance you need to. If IDS takes too much RAM. MB RAM and IDS. takes 4. MB or more for its shared memory) your. When there is not enough memory, the system has to. And if the system is short of. CPU may also suffer.
There are many UNIX. CPU and. memory utilization. Below is the output from "top": The output is broken into two sections.
The first. section gives you a summary of the overall operating. CPU and memory usage, and the second section.
Other utilities, such as vmstat, iostat, ps - ef. I/O distribution among all physical disks; and ps - ef. CPU, and memory usage.
In. addition, there are many graphic tools available that. Monitoring Database Activity. The purpose of monitoring database activity is to.
This implies that you need to. Here are a few things to watch for: Table Extents.
An extent is a block of physically contiguous pages. However, if a table has more than one extent, there is. Contiguity of physical pages is important to. When pages of data are contiguous, the time. If tables have too.
This harms performance a great deal. This slows down disk- seeking. The following script detects.
The output is as follows: If any of the tables has 1. For. details on how to estimate and allocate extent size for.
Informix- On. Line Dynamic. Server Performance Guide. Index Levels. Index levels may also adversely effect performance. The more index levels, the more reads IDS needs to get.
Furthermore, if a leaf node gets. For example, if an index.
The time used for this. This is. especially true in an OLTP environment where there are. The following. script identifies how many levels there are for each. The output of the script is as follows: If any index has more than 4 levels, you may need to. Index Uniqueness. A highly duplicate index can severely impact.
Suppose you have an. If the table has one. The B- tree would store the key. The problem occurs when you have to. IDS must search. through all the duplicates until it finds the correct. The following is a script to identify highly.
The output may look like this: Ideally, all values appearing in the nunique column would. Based on the number of rows. The higher the percentage, the more unique the index. To avoid the performance bottleneck of a highly.
Using the example. Sequential Scans. Sequential access to a table is sometimes harmful to. If the table is small, it is harmless to read it. Informix’s light scan. But if the table has 1. The. following script will identify tables with multiple.
The output is as follows: From the output above, you can see that the customers. If this is a. big table with thousands or millions of rows, you may. Monitoring Session Activity. Statistics about session activity are very useful in. What session activity statistics can. General Statistics.
The syssessions table in the sysmaster. ID, current state, and so on. You can query this.
The output looks like this: s_state is a. Informix- On. Line. Dynamic Server Administrator’s Guide for a detailed. C program. You can request this program from. Informix technical support. The syssesprof table in the sysmaster database.
With the following query, you can get a better idea how. The output is as follows: The access. You can calculate the percentage of each. The higher the percentage, the more efficient are. You can join syssession and. The following query can retrieve all this. You can also join the syssesions table with.
The output looks something like this : If there are some conflicts with lock usage, for. This command can only.
Query Statistics. Query statistics are critical in troubleshooting and. The onstat - g sql sid command.
ID that can be. plugged in either manually by hand or dynamically by a. UNIX shell program). For example, if you want to know. ID. and then plug this ID into the above command (e. The output will look like.