Manage your Informix database with the IDS OpenAdmin Tool, Part 3: Performance analysis of Informix Dynamic Server made easy through the OpenAdmin Tool

Learn how to analyze IBM® Informix® Dynamic Server (IDS) performance using the IDS OpenAdmin Tool (OAT). OAT is an open source, Web-based interface to IDS and is supported on Version 11.10 and later. Parts 1 and 2 of this series introduced the tool and showed you how to migrate from the Informix Server Administrator. Now, see how easily you can monitor performance through statistical data and charts that give you a quick way to comprehend the information.

Bharath Sriram, Associate Software Engineer, Informix Team, IBM

Bharath SriramBharath Sriram worked with the integration team and on OAT while at IBM. He is certified in systems administration for IBM Informix Dynamic Server V11. He holds a master's degree in computer science from Ohio State University. His research interests include information retrieval and text mining in social networks.



Chiranjeevi Vishnu Saran Sudha (chisudha@in.ibm.com), Associate Software Engineer, Informix Team, IBM ISL

Vishnu SudhaVishnu Saran is an Associate Software Engineer at India Software Labs, IBM. He works for the IDS Integration Team across various IDS Products and also works on the OpenAdmin Tool for IDS 11. He is certified in System Administration for IBM Informix Dynamic Server V11.



17 July 2008

Introduction

Parts 1 and 2 of this series introduced the IDS OpenAdmin Tool (OAT), a Web-based interface for administering Informix Dynamic Server, and showed how to migrate from the Informix Server Administrator. OAT offers many advantages:

  • A single installation of OAT can administer multiple data servers
  • No additional software is required on the data server, and OAT can be run on any Web browser
  • Since OAT is open source, it is easy to edit and modify the tool to meet your needs

Before getting into the details of what the OAT can do with respect to performance analysis, let's look into the parameters that determine the performance of a database server. The following measures describe the performance of an Online Transaction Processing (OLTP) System:

  • Throughput
  • Response time
  • Cost per transaction
  • Resource utilization

OAT helps to measure each of the above parameters with its "Performance Analysis" toolkit, composed of the following functions:

  • SQL Explorer
  • Performance History
  • System Reports
  • Session Explorer

This article examines each of these functions.


SQL Explorer

The SQL Explorer page displays a summary of all SQL statement types that have been executed on the current database server. Using the information on this page, you can determine how many times a type of statement has been run, along with the average response time and memory usage, maximum response time, and number of rows processed.

Click on the statement type to see further detail about that statement type, such as the number of times a particular statement has been run at the server. For example, click on SELECT to view statistics about SELECT statements that have been captured with SQL trace. You can then drill down on the saved SELECT queries. You can also see which statements have been run the most with a pie chart (see Figure 1) that breaks out each statement type by percentage of statements that have been run. Move the pointer over each section of the pie chart to view the percentage for the statement type.

Figure 1. SQL statement summary
SQL statement summary

Select Data to view this information in table format:

Figure 2. Data view
Data view

Before you start using the various features offered by this component, you need to enable the SQLTRACING option. Trying to activate the SQL Explorer without enabling it will display the following message:

Figure 3. SQL tracing disabled
SQL tracing disabled

An option to activate SQLTRACING is provided as follows:

Figure 4. Activate the SQLTRACE
Activate SQLTRACING

Select the Mode to On. You can also set the "Number of traces", which is the number of SQL statements to trace before reusing the resources. The range is 500 to 2147483647. The "Trace Size" refers to the maximum size of variable length data to be stored. The range is 1KB to 100KB. If this buffer size is exceeded, the database server discards saved data.

The trace level can be one of the following:

  • Low: This tracing level, which is enabled by default, captures statement statistics, statement text, and statement iterators.
  • Medium: This tracing level captures all of the information included in low-level tracing, plus table names, the database name, and stored procedure stacks.
  • High: This tracing level captures all of the information included in medium - level tracing, plus host variables.

Alternately, disable the SQL Tracing option by setting the Mode to Off.

Note: By default, the SQL Tracing option is disabled.

The SQL Explorer page is similar to the server side onstat -g his output. By default, only the DBSA can view onstat -g his syssqltrace information. However, when UNSECURE_ONSTAT = 1, all users can view this information. The steps below illustrate how to work with the Query Drill-Down feature. The SQL statement being drilled-down is the CREATE TABLE statement.

  1. Enable the SQLTRACE feature.
  2. Execute the CREATE TABLE command either using dbaccess or through the OAT SQL Editor component.
  3. Select the SQL Explorer component under the Performance Analysis tab.
  4. Click on the CREATE TABLE SQL statement, listed in the SQL Statement Summary:
    Figure 5. SQL Statement Summary
    SQL Statement Summary
  5. The resulting page displays performance information about an SQL statement, such as how many times the statement has been executed at the server (Count) and average run time.

    To view the details of each execution of the SQL statement, click on Drill Down:
    Figure 6. SQL Frequency Summary
    SQL Frequency Summary
  6. The resulting page provides details of an SQL statement that has been selected for drill down. The SQL statement is displayed, as well as the following statistics:
    • Session ID: Database session ID of the user running the SQL statement
    • User ID: User ID of the statement running the SQL statement
    • Response Time: The length of time required to process the SQL statement
    • Rows/Second: The average number of rows produced per second
    • Rows Processed: The number of rows processed for the SQL statement
    • Lock Wait Time: Time the system waited for locks during SQL statement
    • Wait IO Time: The number of times an IO operation had to wait


    Click on Drill Down to view detailed statistics for the SQL statement:
    Figure 7. Drill Down screen
    Drill Down screen
  7. The resulting page displays a detailed profile of an SQL statement execution, selected through drill down. Use the information on this page to analyze the performance of each SQL statement:
    Figure 8. Statement Statistics
    Statement Statistics

    Statement Statistics output description:

    • Page Reads: Number of pages that have been read from disk
    • Buffer Reads: Number of times a page has been read from the buffer pool and not read from disk
    • Read Cache: Percentage of times the page should be read from the buffer pool
    • Index Buffer Read: Number of buffer reads for index pages
    • Page Write: Number of pages written to disk
    • Buffer Write: Number of pages modified and sent back to the buffer pool
    • Writes Cache: Parentage of time that a page was written to the buffer pool but not to disk
    • Lock Requests: Total number of locks required by this statement
    • Lock Waits: Number of times this SQL statement waited on locks
    • Lock Wait Time: Time spent waiting for locks during this SQL statement in seconds
    • Disk Sorts: Number of sorts for this SQL statement that were executed on disk
    • Memory Sorts: Total Executions - Total number of times this statement has been executed or the number of times this cursor has been re-used
    • Total Time: Total time executing this statement in seconds
    • Average Time: Average time this state takes to execute in seconds
    • Max Time: Total time to run the SQL statement in seconds, excluding any time taken by the application
    • Lock Wait Time: Amount of time the statement waited for application locks
    • Average IO Wait: Amount of time the statement waited for I/O, excluding any asynchronous I/O
    • Average Rows/Second: Average number of rows a second produced by this statement
    • Estimated Cost: Cost associated with the SQL statement
    • Estimated Rows: Estimated number of rows returned, as estimated by the optimizer for the statement
    • Actual Rows: Number of rows returned for this statement
    • SQL Error: The SQL error number
    • ISAM Error: The RSAM/ISAM error number
    • Isolation Level: Isolation level this statement was run with
    • SQL Memory: Number of bytes this SQL statement requires

Performance History

Performance history through OAT allows you to monitor critical aspects of performance. Through this tool, you can monitor the historical data captured by the IDS Scheduler DBCron. OAT provides five profiled fields, as shown in Figure 9 below, to view the performance:

Figure 9. Profiled Fields
Profiled Fields

You can click on the relevant profiled field to see the performance of IDS in that field. To view performance data for the current database server, expand the list and select a type of performance data. A graph is generated, displaying the requested information. You can zoom in and out of a data range, and the graph will adjust accordingly.

Now, let's look into each of the Profiled Fields in detail.

Auto Checkpoints

Auto Checkpoints allows the server to trigger checkpoints more frequently to avoid transaction blocking. This menu provides many checkpoint parameters, shown in the drop down box in Figure 10 below:

Figure 10. Auto Checkpoints
Auto Checkpoints

To view the history of the maximum disk flush time, click on acp_longest_dskF. Figure 11 shows a snapshot of the output. The graph displays the value with respect to an instant of time. Also available with the Profiled Fields menu is a Legend option to help you easily interpret the graph. To make the graph more readable, a zoom-in and a zoom-out option have been provided, which looks similar to the following: Red Mark

For example, in the graph shown in Figure 11 below, the Maximum Disk Flush time is "5" on 03-June-2008, 2:01 AM. Zooming-in further also specifies the value at that particular instant of time.

Figure 11. Graph showing the Maximum Disk Flush
Graph showing the Maximum Disk Flush

To view this in text format, click on the Data button at the bottom left on the frame:

Figure 12. Data view
Data view

Disk

This component provides performance history details of the database server in terms of the various disk operations. When you click on the "Disk" drop-down menu, it displays the following submenus, as shown in Figure 13:

Figure 13. The Disk tab
Disk tab
  • Disk Reads: Total number of read operations from disk
  • Disk Writes: Total number of write operations to disk
  • Disk Flushes: Total number of writes from a buffer to disk
  • Chunk Writes: Total number of chunk writes (chunk writes, performed as sorted writes, are the most efficient writes available to the database server)
  • Foreground Writes: Number of foreground writes at a given instant of time.

Selecting each of the above submenus expands the list, providing the option for you to choose disk operations for different page sizes, as shown in Figure 14 below:

Figure 14. Disk pages
Disk pages

Buffer

This component provides the shared memory buffer-related information required to analyze the database server performance. The submenus are similar to that of the "Disk" component.

General Profile

This component provides performance history data like checkpoint information, deadlocks, rollbacks, commits, updates, and deletes, as shown in Figure 15 below:

Figure 15. The General Profile tab
General Profile tab

B-Tree Scanner

The B-Tree Scanner improves transaction processing for logged databases when rows are deleted from a table with indexes. The B-Tree Scanner threads remove deleted index entries and rebalance the index nodes. The B-Tree Scanner automatically determines which index items are to be deleted. After all indexes above the threshold are cleaned, indexes below the threshold are added to the hot list. The default threshold is 500. The B-Tree Scanner component expands to display list items like bts_rangesize, bts_threads, bts_flags, and so on.


System Reports

This component of the OAT is extremely attractive from the database administrator's point of view. The OAT provides the much needed information required by the DBA like the disk space usage, log files, SQL statements occupying the most IO time, the slowest SQL statements, and so on. A DBA can use this report and tune the performance of the IDS if required. Therefore, the "System Reports" component acts like a yard stick to measure the current server performance through various parameters. The "System Reports" page displays a variety of reports that you can view individually, or you can also build a report from many smaller reports:

Figure 16. System Reports
System Reports

To display a report, click on the link with the name of the report. The report appears with current information. For example, clicking on the Server Configuration, as shown in the red circle in Figure 16 above, takes you to the current configuration of the server. Here, you can cater to the "Recommendations" provided by the OAT for certain configuration parameters or set the configuration parameter to a value of your choice. However, only some configuration parameters are dynamic and can be set from the OAT.

An alternate way to build a report is through the check-box option, also shown in Figure 16 above. To build a report from multiple smaller reports, select the check boxes for the chosen reports, and click on Create Report. The reports are concatenated for displaying current information. In this method of viewing the reports, a separate browser window is opened, and the report is displayed in this new window. The report specifies the time at which the report was created, the "Server uptime", and the actual data. The report begins with the list of all the small reports it is made up of. You can select each report and directly view the smaller report, then navigate back to the top by clicking the Top link provided, as shown in Figure 17 below:

Figure 17. Log Reports
Log Reports
Figure 18. Logical Logs
Logical Logs

To filter the list of reports, click on the Report Type selection list. Select the type of report you want. The check boxes for each of these reports are selected. Click Create Report to view the report.


Session Explorer

This page displays a list of the user sessions currently running at the server. Click on a value in the SID (Session ID) column to open detailed information about the session. The session details can be obtained on the server side using the onstat -g ses utility. It is also possible for you to end a session by clicking on the thunder button: Thunder button

Note: You cannot kill system-initiated sessions.

Figure 19. Session main page
Session main page

Output description:

  • SID: Session ID
  • Username: Name of the user connect in that session
  • PID: Process ID
  • Hostname: Displays the machine name in which the server instance is running
  • Memtotal: Total Memory used in that session
  • IOWaittime: The time elapsed in waiting for the IO
  • CPUTime: The amount of CPU time utilized by the session

Clicking on the SID number displays information about the selected user session. The Session Info section displays a report for the session, as shown in Figure 20 below:

Figure 20. Detailed session page
Detailed session page

Output description:

  • Connected: Displays the time when the session is started
  • Open Tables: Displays the number of the tables opened in that session
  • Current Statement: Displays the Current Executed SQL query in that session

The SQL tab

The SQL tab displays a list of statements that have been run by the selected session.

Figure 21. The SQL tab
The SQL tab

Output description:

  • ID: The ID of the SQL statement executed
  • Type: The type of the SQL operation performed
  • Statement: Displays the actual query executed

The Locks tab

The Locks tab displays a list of the table and page locks for this user session.

Figure 22. The Locks tab
The Locks tab
Table 1. Lock types and their descriptions
Lock typeDescriptionStatement usually placing the lock
SShared LockSELECT
XExclusive LockINSERT, DELETE, UPDATE
UUpdate LockSELECT in an Update Cursor
BByte LockAny Statement that updates VARCHAR Columns

Output description:

  • Table Name: Name of the table on which the locke existed
  • Lock Type: Type of the lock
  • Lock Duration: The duration which the lock existed
  • Row ID: The ID of the row which is locked
  • Index #: The Index number of the locked row
  • Waiters: Others waiting for the lock
  • Key Item Locked: The key pointing to the locked row

The Threads tab

The Threads tab displays details about the threads used in this session.

Figure 23. The Threads tab
The Threads tab

Output description:

  • Name: Name of the thread
  • Num_Scheduled: The total number of times the thread has been scheduled
  • Thread_ID: ID of the thread
  • Thread_Priority: Priority of the thread
  • Time_Slice: Fixed amount of execution time given to thread
  • Total_Time: Total time for thread execution
  • VPID: Virtual process ID
  • Wait_Reason: Reason the thread is waiting

The Memory tab

The Memory tab displays the used and unused memory for the selected session.

Figure 24. The Memory tab
The Memory tab

Output description:

  • Name: Session ID
  • Used: Amount of memory used
  • Free: Amount of memory free

The Network tab

The Network tab displays performance details about the network connectivity, IO, and so on for the selected session.

Figure 25. The Network tab
The Network tab

Output description:

  • Average_Recv: Amount of data received in that session per network read
  • Average_Send: Amount of data sent in that session per network write
  • Connect_Duration: Time since the connect is established for that session
  • Last_Read: Time of the last read from the network
  • Last_Write: Time of the last write from the network
  • Received_Data: Amount of data received in that session
  • Send_Data: Amount of data sent in that session
  • Session_Start_Time: Time this session is connected
  • Thread_Name: Name of the thread

The Environment tab

The Environment tab displays the settings of the IDS environment variables for the selected session.

Figure 26. The Environment tab
The Environment tab

The Profile tab

The Profile tab displays a report of all of the combined statistics for the user session.

Figure 27. The Profile tab
The Profile tab

Conclusion

This article has explained how OAT can be used to analyze the performance of IDS. The OAT provides a simple, easy-to-use GUI for the DBA to obtain the current performance details of IDS. These details are provided through raw data and pie-charts to comprehend the information more quickly. The Informix Dynamic Server is a powerful database engine. OAT provides an easy-to-use GUI to monitor IDS and makes the job of a DBA very easy.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

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

 


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

All information submitted is secure.

Choose your display name



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

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

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

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

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=321750
ArticleTitle=Manage your Informix database with the IDS OpenAdmin Tool, Part 3: Performance analysis of Informix Dynamic Server made easy through the OpenAdmin Tool
publish-date=07172008