The Sysmaster database, a pseudo-database that's part of the Informix Dynamic Server (IDS) installation, provides a peek into the shared memory structures of an IDS server. It's a great tool for monitoring server status and performance. I've given several talks and written many articles about it.

IDS 11 (formerly code-named "Cheetah") adds many new features to the Sysmaster database. This column and future editions will give examples of how you can use the new Sysmaster monitoring tables. I'll also be presenting on this topic at the IBM Information On Demand conference this month-sit in on my session if you're attending.

The new Sysmaster tables I'm really excited about are:

• Syscheckpoint, which keeps track of the last set of checkpoints since the server started
• Sysenv, which shows the environment variables in effect when the server was started
• Sysenvses, which shows the environment variables in effect for user sessions
• Sysmgminfo, which shows Parallel Database Query (PDQ) information
• Sysnetclienttype, Sysnetglobal, and Sysnetworkio, which show network stats
• Sysonlinelog, which keeps track of the online log
• Syssqltrace, Syssqltrace_info, Syssqltrace_iter, which show SQL profile and trace information
• Systhreads, which keeps track of threads and their wait stats.

The new Sysadmin database, part of IDS 11's Automatic Monitoring and Scheduler system, keeps track of tasks and schedules. Let's take a look at five of these new tables; in future articles, I'll explain the command_history table and other topics.

SYSCHECKPOINT

This table monitors and stores in memory the last 20 checkpoints since the IDS server was started. Table 1 lists the fields in this table. Syscheckpoint isn't a physical table, but a view into a shared memory structure.

Table 1. Syscheckpoint table fields.
Click on thumbnail for larger view.

Checkpoints have completely changed in IDS 11, as I explained in my previous column. The goal of a checkpoint is to ensure that memory buffers are in sync with the disk structures. Without checkpoints, you run the risk of a corrupted server. In previous versions, there were two types of checkpoints. A standard checkpoint blocked all user write activity until every buffer was written to disk and all memory structures were synchronized. Version 9.x introduced a fuzzy checkpoint in which user activity was stopped only while the list of dirty buffers was logged; user activity was allowed to continue while dirty buffers were written to disk in the background. IDS 11 introduces a new, patented checkpoint algorithm that replaces both the previous two types of checkpoints. Most checkpoints no longer stop users from doing writes while buffers are flushed to disk, and the new checkpoints are also more reliable than fuzzy checkpoints.

The Syscheckpoint table lets you monitor your checkpoints. Some of the questions you can use this data to answer are:

• What caused a checkpoint to be called? Was the physical log too small? Or was the logical log (or a user) issuing an onmode -c command to force a checkpoint?
• How long did the checkpoint take?
• How many users performing updates were blocked (if any), and how were they blocked?
• How many buffers were flushed to disk as a result of the checkpoint?

SYSENV

Many environment variables affect Informix server operation and server performance characteristics. Once an IDS server is running, the Sysmaster table gives you a way to monitor the environment variables that affect the server. Table 2 shows the definitions for this table. IDS servers rarely need to be shut down or restarted. When an IDS server has been running for months or years, the environment variables in effect when the server started are likely forgotten. This table will show what was in effect when the server was started. You can use this table with the Sysenvses table to help debug problems that arise when environment variables in the IDS server and client differ.

SYSENVSES

Each IDS user session can have different environment variables. When debugging a user session, it helps to see what variables are in effect as the session is operating. The Sysenvses table gives you a way to monitor the environment of a user session. Table 3 shows the table definitions.

Sysenv and Sysenvses are helpful when you're trying to debug problems that may be caused by differences in client and server environment variables. I once helped a customer debug a load problem that involved an error in a date field; it turned out that the environment variable DBDATE was set differently on the IDS server and client. When data was loaded from the client, dates were in an incorrect format for the server, causing an error. These two tables would have made identifying and solving that problem much easier. The same concept applies to sort problems where the DB_LOCALE might be set differently on the client and server.

You can also use this information in determining a user's environment when debugging a performance issue. These tables provide information about environment settings such as PDQ_PRIORITY, PSORT_NPROCS, or PSORT_DBTEMP, which can affect the user's performance.

SYSONLINELOG

One of the configuration parameters in your ONCONFIG file is MSGPATH, which defines the location of your online log file. MSGPATH is where the server writes out startup, processing, and error messages. The sysonlinelog table is a virtual view into this file and allows you to use SQL statements to view and monitor your online log file. Table 4 shows the structure of this simple, three-field table. The only field that really matters is the message text, which is the line of text in your online log.

Tables 2, 3, 4: Click on thumbnail for larger view.
Table 2. Sysenv definitions.
Table 3. Sysenvses definitions.
Table 4. Sysonlinelog fields.

SYSMGMINFO

My favorite new Sysmaster table is Sysmgminfo. This table shows information about Parallel Data Query (PDQ) sessions running on your IDS server. It's similar to some information displayed by running the command onstat -g mgm. This table gives you an easy way to capture and view PDQ settings, number of active sessions, number of sessions waiting on resources, and totals and averages for sessions and resources since the server was started (see Table 5). The table contains only one record showing the current settings from memory.

Tables 5 and 6: Click on thumbnail for larger view.
Table 5. Sysmgminfo fields.
Table 6. Example results of select* from sysmgminfo.

I ran the SQL statement select * from sysmgminfo on my laptop with one PDQ query running as an example; Table 6 shows the output. I have added comments about what the results mean in the column next to the results.

Important fields to keep track of are ready and cnt_ready. These fields tell you how many PDQ queries are waiting for resources to run and the count of the total number that have waited for resources to run. On one of the large data warehouse systems I support, users will occasionally complain about queries taking too long to run. However, when I look at the system, the queries aren't actually running, but are blocked while waiting for resources. This field gives you an easy way to keep track of information on ready-to-run queries that are blocked.

CLUES TO MONITOR AND TUNE

The ability to get good information about your Informix IDS server and its performance is the key factor in monitoring and tuning. The new Sysmaster tables in IDS 11 give you a lot of the information you'll need. Try a few select statements on these new tables in dbaccess or Server Studio to get a feel for the type of information they can provide.

Lester Knutsen [lester@advancedatatools.com] is president of Advanced DataTools Corporation, an IBM Informix consulting and training partner specializing in data warehouse development, database design, performance tuning, and Informix training and support. He is president of the Washington D.C. Area Informix User Group, a founding member of the International Informix Users Group, and an IBM Gold Consultant.